January 29, 2002 at 9:22 am
Hi,
I have just been asked to come up with information about running SQL2000 for 1000 users. I have been able to find lots of info on large databases and scaling them, but what about large numbers of concurrent users? Are there any good references for how to setup/configure SQL2000 for this number of users? The database will not be huge a couple of Gb at most, but there will be 1000 users who all log in at 9:00am and log out at 5:00pm.
I am looking for information about such things as
Hardware requirements
Hardware Config (clustering etc)
SQL config.
Any pointers will be greatfully received.
thanks
January 29, 2002 at 12:21 pm
You might give a look at Compaq's Active Answers site at:
http://activeanswers.compaq.com
They have sizers on the site for the Proliant systems, both for SQL Server 7 and 2000, and for OLTP and OLAP environments. Of course, it's geared towards Compaq, but if you aren't using Compaq servers, it should give you a reasonable base-line hardware wise.
You will have to register, but there's no cost.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
January 29, 2002 at 12:51 pm
I've seen this often and been asked the questions myself, but 1000 concurrent users can be extremely taxing or no issue at all. You really have to dig into metrics about #s of transactions, #s of reads v writes, etc. This site handles quite a few concurrent users, but as a web site that has people connect nad disconnect often, we rarely get above 30 users, though we get over a 1000 users a day.
I assume you have some type of application and what you need to do is really get some metrics on how much use you will get, not raw numbers like concurrnet users or db size.
Some very rough guidelines though:
1. Buy a multi proc box. Be best to buy a 4 or 8 way even if you only populate 2. Populate at least 2 as the upgrade from single to multi CPU can be tricky.
2. Buy enought physical drives to separate your production db from its logs at a minimum. Be nice to have at least one more physical drive/array for tempdb/master/msdb.
3. Get lots of RAM. No excuse for < 1GB these days.
4.Clustering if you need it, but be sure that you have test systems that can simulate the live system. So if you want to do clustering, it'd be nice to have two smaller servers that you can test on as clusters as well.
Steve Jones
January 29, 2002 at 1:08 pm
Should have mentioned that the server will be running a CRM package on SQL Server. All 1000 users will be logged in from 9:ooam to 5pm. Will be about 400000 Records, with a couple of transactions per record
February 2, 2002 at 6:03 pm
Agree with Steves recommendations, except I'd raise the bar far higher on RAM - given the cost these days, I'd recommend a min of 2g and buy a machine that will use a lot more.
A lot depends on the app. If the majority of the work in done via stored procs and you have a decent index plan you should have no problem supporting 1000 users on a 4-8 proc box. The key is to tune for the app and keep adhoc reporting off of the box unless you really really have the capacity to handle it.
Andy
February 2, 2002 at 7:04 pm
Hi there
It really comes down to the architecture of the application, from there, you have a better opportunity to scale the application and ask questions from the group on specific components. I was perf tuning a clients app the other day with approx 1000 odd users. But no connection pooling, sql in loops of 15 taking 100ms each cycle for 1 operation, single cpu machines, poor indexing etc, all added up to a database server that was cpu bound and a webserver that hardly moved.
I would recommend (as a minimum)...
a) Seperate DB and Webserver (or even another for a middle tier)
b) 2xCPU's for each server, min 1Gb ram
c) COM+
d) cached data on the webserver for lookup tables that change rarely
e) index all fk, revise clustered indexes, run index wizard on some workloads
f) pin tables as required
g) check existing server performance stats for IO and cpu queue lengths
Take care with IO, this is a tough one to manage later on.
Also, clustering is a HA option, it wont help to scale the app. Look at federated servers for scalability.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
February 4, 2002 at 11:26 am
February 5, 2002 at 5:09 am
Also look at the CRM packages requirements and if they are higher than the suggestions here, go with those. Sometimes they have stuff you don't expect going on in the background. ALso in addition to seperating your SQL Server from any thing else make sure:
1) NT/2000/XP is set for Optimization for Background Services
2) In SQL's properties that under Processor you have all the Processors checked, Boost SQL Server Priority on Windows is checked, and Use all available processors is checked in the parallelism section is checked.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply