Scaling for large number of users

  • 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

  • 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

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • 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

    steve@dkranch.net

  • 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

  • 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

  • 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"

  • Don't forget about your backups and how you manage them.

    Steve Jones

    steve@dkranch.net

  • 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