Hello -
Is the Express Edition able to handle 300 simultaneous users that are only
adding new records? Can it handle a couple million records?
Any help will be greatly appreciated!
--
SandyExpress Edition is limited to 1 CPU, 1 GB of RAM, and 4 GB database size.
What's the row size of the table with a couple of million rows? When
populated, will the database exceed 4 GB? That would be my number one
concern.
Other than that, I think it's really more a hardware question--do you have a
high-speed CPU? High-speed RAM? High-speed hard drives (RAID-1+0)?
Multiple physical drives to provide separate spindles for the O/S, databases
,
and transaction log file?
You can always start with Express and upgrade when needed to Workgroup or
Standard edtion.
Hope that helps,
Joe
"Sandy" wrote:
> Hello -
> Is the Express Edition able to handle 300 simultaneous users that are only
> adding new records? Can it handle a couple million records?
> Any help will be greatly appreciated!
> --
> Sandy|||P.S. And it depends on the application too. In a typical day, how many
records will the average user insert into the database? And more
importantly, over what time period? For example, if all of the activity hit
s
at the end of a shift, you might have a problem. If it's spread over the
entire shift, it may work fine.
Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows /
300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows per
day. (To account for vacation and holidays, I assumed 47 work w
s * 5 day
s
per w
= 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||Hi Joe -
Thanks for your reply. The row size is 567 bytes. I calculated the total
GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in that
regard, if the database size limitation is 4 GB and my calculations are
correct.
The activity should be spread throughout the entire shift, so that should be
okay.
What would you suggest for a minimum in the way of CPU, RAM and high-speed
hard drives? Suggested OS?
--
Sandy
"Joe from WI" wrote:
> P.S. And it depends on the application too. In a typical day, how many
> records will the average user insert into the database? And more
> importantly, over what time period? For example, if all of the activity h
its
> at the end of a shift, you might have a problem. If it's spread over the
> entire shift, it may work fine.
> Based on 300 users creating 2 million rows in a year, thats 2,000,000 rows
/
> 300 users = 6667 rows per user per year. 6667 / 235 work days = 28 rows p
er
> day. (To account for vacation and holidays, I assumed 47 work w
s * 5 d
ays
> per w
= 235 work days.) So, 300 users * 28 rows = 8,400 rows per day.|||SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
2003. I'd recommend Windows Server 2003 - Standard Edition. If that doesn'
t
fit your budget, Windows XP Pro could be used but it's not as robust.
You can only have 1 physical CPU so I'd recommend the fastest you can
afford. Keep in mind that Microsoft licenses by the physical CPU so you can
purchase 1 dual core CPU and still be compliant with licensing. A dual-core
runs a little slower but I believe it will give overall better performance
when other services are competing for the CPU.
Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
Athlon 64 4000+.
Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
Pentium 4 Extreme Edition, and XEON EM64T.
CPU choice is heavily dependent on what motherboard you have/will have. My
personal favorite is the AMD Athlon 64 X2 4800+.
If the computer is only used for SQL Server and there are no extensive
query's, etc., then I'd recommend 2 GB of RAM. If you will be querying thos
e
3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
(assuming the motherboard will support them) and hopefully, you'll have open
slots to add more memory later.
The speed of the memory is determined by what the motherboard will support.
If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster bu
t
Intel CPU's use an external memory controller. AMD's have the memory
controller integrated into the CPU for very fast memory access.
Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM are
the fastest, primarily due to the access time of 4.5ms. (Other SATA drives
like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
cannot afford SCSI, I'd highly recommend the WD Raptors.
An PCI/PCI-e RAID controller that has the RAID processor built into it is
the fastest solution. Controllers integrated into the motherboard utilize
the CPU for processing and are a bottleneck. One solution is to use the
motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) array
as the O/S drive doesn't get heavy pounding. Then, use a separate controlle
r
for the database drives.
If you don't want to loose data, you'll need to protect, at a minumum, the
database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calculate
parity. The log file is written to serially so if you can dedicate a
separate pair of drives, there will be minimum hard drive head movement and
will give you the best throughput.
If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
much overhead to calculate and re-calculate (when data changes) parity
information.
Sorry for the long post. Hope that helps,
Joe
"Sandy" wrote:
> Hi Joe -
> Thanks for your reply. The row size is 567 bytes. I calculated the total
> GB to be 1.6, assuming 3,000,000 rows. I should be relatively safe in tha
t
> regard, if the database size limitation is 4 GB and my calculations are
> correct.
> The activity should be spread throughout the entire shift, so that should
be
> okay.
> What would you suggest for a minimum in the way of CPU, RAM and high-speed
> hard drives? Suggested OS?
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Note that Express is 32 bit only so a 64 bit processor isn't of much use to
an Express installation.
I think you biggest issue with that many simultaneous users will be the 1GB
limit on memory. Each user requires some memory so a lot of users will
stretch the 1GB limit. If the load from each user is fairly light, you
might want to close the connection after each insert. Otherwise, I doubt
that you will get adequate performance from Express.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that
> doesn't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you
> can
> purchase 1 dual core CPU and still be compliant with licensing. A
> dual-core
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have.
> My
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying
> those
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have
> open
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will
> support.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
> but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM
> are
> the fastest, primarily due to the access time of 4.5ms. (Other SATA
> drives
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring)
> array
> as the O/S drive doesn't get heavy pounding. Then, use a separate
> controller
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to
> calculate
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement
> and
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thank you very much, Joe!
--
Sandy
"Joe from WI" wrote:
> SQL Server Express supports Windows 2000 SP4, Windows XP SP1, and Windows
> 2003. I'd recommend Windows Server 2003 - Standard Edition. If that does
n't
> fit your budget, Windows XP Pro could be used but it's not as robust.
> You can only have 1 physical CPU so I'd recommend the fastest you can
> afford. Keep in mind that Microsoft licenses by the physical CPU so you c
an
> purchase 1 dual core CPU and still be compliant with licensing. A dual-co
re
> runs a little slower but I believe it will give overall better performance
> when other services are competing for the CPU.
> Top-end AMD's include Athlon 64 X2 4800+, Opteron 280 (dual core), and
> Athlon 64 4000+.
> Top-end Intel's include Pentium D 840 (dual core), Pentium 4 670, and
> Pentium 4 Extreme Edition, and XEON EM64T.
> CPU choice is heavily dependent on what motherboard you have/will have. M
y
> personal favorite is the AMD Athlon 64 X2 4800+.
> If the computer is only used for SQL Server and there are no extensive
> query's, etc., then I'd recommend 2 GB of RAM. If you will be querying th
ose
> 3 million rows, aggregates (SUM, AVG, etc.), processing data, etc., I'd
> recommend 4 GB of RAM. If you can only afford 1 or 2 GB, buy 1 GB sticks
> (assuming the motherboard will support them) and hopefully, you'll have op
en
> slots to add more memory later.
> The speed of the memory is determined by what the motherboard will support
.
> If it's AMD, you'll be looking at DDR400 memory. If it's Intel, DDR400,
> perhaps DDR2, and perhaps higher clock speeds. Intel often sounds faster
but
> Intel CPU's use an external memory controller. AMD's have the memory
> controller integrated into the CPU for very fast memory access.
> Hard drives are dependent on budget. Fastest is Ultra320 SCSI 15,000 RPM
> drives. In the SATA world, Western Digital Raptor 74GB SATA 10,000 RPM ar
e
> the fastest, primarily due to the access time of 4.5ms. (Other SATA drive
s
> like ATA/IDE run at 7200 RPM and access data around 8.5 to 9ms.) If you
> cannot afford SCSI, I'd highly recommend the WD Raptors.
> An PCI/PCI-e RAID controller that has the RAID processor built into it is
> the fastest solution. Controllers integrated into the motherboard utilize
> the CPU for processing and are a bottleneck. One solution is to use the
> motherboard HDD controller for the O/S drives in a RAID-1 (mirroring) arra
y
> as the O/S drive doesn't get heavy pounding. Then, use a separate control
ler
> for the database drives.
> If you don't want to loose data, you'll need to protect, at a minumum, the
> database log file (a.k.a. the transaction log) with RAID-1 (mirroring) or
> RAID-1+0. Do NOT use RAID-5. In my opinion, too much overhead to calcula
te
> parity. The log file is written to serially so if you can dedicate a
> separate pair of drives, there will be minimum hard drive head movement an
d
> will give you the best throughput.
> If you can't afford for the box to be down, you'll need RAID-1 or RAID-1+0
> for the O/S and for the database. Do NOT use RAID-5. In my opinion, too
> much overhead to calculate and re-calculate (when data changes) parity
> information.
> Sorry for the long post. Hope that helps,
> Joe
> "Sandy" wrote:
>|||Thanks Roger!
--
Sandy
"Roger Wolter[MSFT]" wrote:
> Note that Express is 32 bit only so a 64 bit processor isn't of much use t
o
> an Express installation.
> I think you biggest issue with that many simultaneous users will be the 1G
B
> limit on memory. Each user requires some memory so a lot of users will
> stretch the 1GB limit. If the load from each user is fairly light, you
> might want to close the connection after each insert. Otherwise, I doubt
> that you will get adequate performance from Express.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
> news:0FC7ADE0-75A2-4D38-B826-A1BE395F31B5@.microsoft.com...
>
>|||oops...I got carried away in my memory statement.
I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
Express and the other 1 GB can be used by Windows, free space, etc. With
Express, there is no need to have more memory than 2 GB. (Unless of course,
you have other memory hungry applications on the same computer.)
The 4 GB of RAM recommendation would require Workgroup or Standard in order
to make use of it.
Sorry for the error, confusion, misstatement.
Joe
"Sandy" wrote:
> Thank you very much, Joe!
> --
> Sandy
>
> "Joe from WI" wrote:
>|||Also, Express is limited by 1 scheduler. A dual-core won't help you on the
SQL Server side of things.
"Joe from WI" wrote:
> oops...I got carried away in my memory statement.
> I recommended 2 GB of RAM because 1 GB can be utilized by SQL Server 2005
> Express and the other 1 GB can be used by Windows, free space, etc. With
> Express, there is no need to have more memory than 2 GB. (Unless of cours
e,
> you have other memory hungry applications on the same computer.)
> The 4 GB of RAM recommendation would require Workgroup or Standard in orde
r
> to make use of it.
> Sorry for the error, confusion, misstatement.
> Joe
> "Sandy" wrote:
>
gibbo1715 wrote: