Number of databases on one server

Question

Number of databases on one server

S Hodkinson

SSCertifiable

Points: 7529
More actions
May 17, 2007 at 3:03 am

#117148

I know this is a bit like asking how long is a piece of string but .......how many databases should sit on one server?
I realise that this will depend on size of server, processing power, number of concurrent users accessing each databases, number of users with possible access etc but I wonder if there is a 'rule of thumb' calculation?

Madame Artois

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Nope, you pretty much just answered your own question. I'm sure that there are other factors to consider in the equation but it looks like you got the key ones at the moment.

jezemine SSCrazy Eights Points: 8059 More actions · Answer 2

there's always room for one more...

---------------------------------------
elsasoft.org

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

Standard answer incomming:

It depends!

I have 55 databases on my main production server, ranging in size from 50 mb to 85 GB. It handles about 5 million stored procedure calls per day, including reports. Now if I could just get rid of the Access and Excel connections....

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 4

Don't forget to consider multiple instances. If you have Applications A,B,C, and D on one instance of SQL Server, and maybe Application A bloats up the mem to leave cache by calling COM objects via extended sp, you will have to restart the SQL Service and shutdown production on all applications that use this server. If you have each application on a separate instance (I strongly recommend this if your licensing allows it) then you can restart the instance for Application A and not affect production on the other 3 applications.

Can you tell this has burned me before? =)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Be careful of multiple instances. It can separate out tempdb, which can be a point of contention, but it can also require more memory that you need to set for each instance.

I've seen hundreds of dbs on servers and it worked if things are well designed, a relatively low, spread load, and enough resources.

Ed Hammar Ten Centuries Points: 1084 More actions · Answer 6

Personally I don't like any of the answers given so I'll give my own answer.

SQL Server can host as many databases on a single server as YOU are capable of allowing it to host. The servers ability to host the databases depends on you (the DBA) more than it does on anything else. I manage a server that currently hosts over 450 databases and that number is growing. Each day 1500-2000 users who make 2500-3000 concurrent connections to the server. No it's not a million dollar server and no the application connecting to the server and queries being submitted to it are not optimized. Heck, I inherited the server from the previous DBA and he wasn't even considerate enough to make sure the hardware was being efficiently utilized. I'm still working to correct that problem.

You might be asking yourself what kind of response time the users get from this server. Well I'm happy to report that response times have been going down since I took over and use has been going up. A snapshot of the performance of the server recently revealed our average response time is around 4 milliseconds and our most offensive query took 3.422 seconds to execute with only 17 queries taking more than 1 second to execute. During that period the server executed over 500,000 queries.

Our server has 2 independent disk subsystems, one for the OS and a RAID array for SQL Server. That alone shows you we could further increase the responsiveness of the server. We are writing logs and data and just about everything else to a single RAID array. If the DBA who preceded me was even the least bit considerate he would have made sure there were a few independent disk subsystems so that the TEMPDB and log files could be written to devices other than the device the random access data was being written to. Better still the server had 2GB's of RAM. I have personal PC's at home with 2GB's of RAM, but fortunately we upgraded and now at least have enough RAM at 6GB's for SQL Server to operate somewhat efficiently.

The reality is that you can host thousands of databases on a single server if YOU are capable of doing so and it doesn't require a multi-million dollar server. If you’d like to understand how to go about successfully managing a server which hosts thousands of databases and thousands of users I suggest you consider taking a few days or weeks to absorb some of the information in the scalability white paper Microsoft and Dell were kind enough to whip up.

http://www.dell.com/downloads/global/solutions/ServerConsolidation1.doc

I've seen and will continue to see systems that make the best and most expensive hardware crawl. I've yet to see many people who are thoughtful enough to design systems that make cheap hardware respond like the system is being run on some futuristic supercomputer.

Andrew Stanton Ten Centuries Points: 1115 More actions · Answer 7

IF things are well designed. As a Support Person or DBA, you often have little or no control over the coding practices of the application developers. If they want to use ad-hoc, non-parameterized queries or don't know how or don't want to follow your recommendations for best performance, you have to do what you can to keep production moving.