SQL Server Performance Forum – Threads Archive

Scaling Up My SQL Server Application

Hi All I have recently had a request to work out a Technical Architecture for my software app which will support between 2000 and 3000 users. The most we’ve ever done before is 150!!! What do I need to start thinking about. I’ve read the article posted here http://www.sql-server-performance.com/dk_massive_scalability.asp This seems to suggest a number of options: 1. Federation – which I’m not too keen on.
2. Multiple DBs with Replication – this would be good as we have a proprietary replication system in built. I just wouldn’t want to recommend purchasing of many servers when one big one would do the job and be easier to manage.
3. Multiple DBs with a Connection Pooling method – again, not keen as it would involve alot of recoding.
What are the thoughts on 64 bit technology to scale up for users? From the literature I can find it would seem that 64 bit helps performance but it isn’t clear whether it helps concurrency?? Thanks. Tim

Another possibility is to use more asynchronous processing. See:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch12.asp

I’m told that making changes like that to our app are out of scope 🙁 I guess it’s now a choice between Multiple Servers and Replicated Databases
or
One big powerful server (potentially 64 bit) I would prefer the last approach due to its ease of management…..do you think that would solve my problem?

are we really talking 2-3000 concurrent users? Are they all in the same building (LAN) or are they coming in over WAN? How big are the databases? I presume we’re talking OLTP? Cheers
Twan

Twan 2000 is what we’re aiming for. Users will be connecting via Terminal Services over a WAN.
Database is 10GB – so not too big
OLTP Thanks

I believe one big can handle 2000 users without problems if application is well written. Since this is the simplest solution I would recommend that one. But keep in mind the main drowback of SQL Server architecture: locking. For dbs I support nolock hint on almost any query is acceptable. That can be the problem even with the most powerfull hardware.

that’s a lot of terminal servers, and potentially quite a fat WAN pipe! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> agree with mmarovic, if the app is tuned and properly written, a single server should be fine, keep the log on its own raid set since you’re gonna be hitting it hard. You may want to work out how many ts and how big your WAN connection(s) ought to be… 2000 users if each transmits at say average of 1KB/s then that is still 2,000KB/s = 16Mbps and 1KB/s isn’t that much, it could easily be 10 times that… Cheers Twan

by my reckoning the terminal server count will need to be about 50 based on 4-way 4Gb boxes!!!! your point with regards to bandwidth is very useful. the client was planning on using a 5 Mbps connection to host this – we’d already told them this probably wouldn’t be enough but your calculations help. when you talk about ‘properly written’, are there any specifics we should be aware of over and above making sure that the app is as tuned as possible. recently we have modified the application to use uncommitted reads where possible (mostly on the ad-hoc querying) which I believe will help our concurrency limitations alot. log on its own raid is standard setup for us thanks for all your suggestions

Take a look here:http://www.sql-server-performance.com/application_design.asp
Also there are programming language/technology used specific tips on this site. Satya posted recently links to troubleshooting presentations I found very usefull, I have pdfs downloaded but I can’t remember now where it is posted.

hi ya, ‘basic Twan rules’ for a properly written app… – 3NF design or better (unless specific performance reasons to move away from this)
– be consistent about datatype usage e.g. is a field is an int in the parent table then make sure it is also int in any child table, etc.
– no dynamic sql and no ad hoc access
– very efficient triggers (or better yet no triggers)
– very efficient stored procedures
– appropriate indexes
– regularly maintained stats and indexes
to work out the number of TS and bandwidth, you may want to give a very rough estimate now based on ‘finger in the air’ but with the prerequisite that a roll out to 50 clients is done first off so you can monitor load on TS and the WAN. Like I said depending on how chattery the app is you could find that 100Mbps WAN is not fast enough for 2000 clients… Cheers
Twan

hi twan thanks for that. i think we hit most of those, and where we don’t there’s a good reason. the ts side of things should be rolled out fairly slowly anyway so we’ll have plenty of oppurtunity to monitor bandwidth requirements. cheers
tim

I read some MS documents about Terminal Services on Windows 2003. Each user used around 1100-1400 bytes/second depending on user type in the system. So that’s about 10kbits/s per user. But you could tune this a bit. For example switching from 16-bit color depth to 8-bit color depth would decrease the average bandwidth usage from 1450 bytes/user to 1150 bytes/user. RAM was also a big factor.

]]>