We help IT Professionals succeed at work.

number of databases per server

ykchakri
ykchakri asked
on
Hi Experts,

What are the guidelines for deciding, how many databases (with what size?) can be placed in a single server ?
For ex: I have PentiumIII Xeon 733 Mhz server (Dual processor) with 2.5GB RAM and 200GB Hard disk. Currently I have a 10GB database. How many such databases I can place in this server, without degrading performance and what is the recommended size of each database ?

Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
The maximum number of database that you can have for SQL Server is 32767, and the max size per database can be over 1'000'000 TB.

Now that's maximum. Of course, every database will use resources of your server:
* CPU
* memory
* network

For performance you need to know that not necessarily the size of the database is the determining factor, but the number of users, the complexity of the queries, the design of the data and of the application. The hardware will eventually be a bottleneck, but design is by magnitudes more important. Hardware should rather play the role of security and be reliable: Disk mirroring & striping, redundant power supply, redundant network cards aso.

The best you can do is to simulate the workload with sample robot software, and measure this for 1, 2, 3, ... n databases. You will see soon what will be the limits of your current configuration. If the limits are too low, you must find the bottleneck.

With 1 database of 10GB, 1 processor might be enough. But if you want to place several databases of that size, you should have a multiprocessor machine in place.
2.5 GB RAM should be enough, 200 GB Hard disk might not be enough.
For HD you must think of the following items:
* Database growing
* Transaction log
* Backup files
* other files
If you have a huge system, a multi-disk configuration should be best:
* one disk for the system files (NT)
* one/more disks for the swap files
* one/more disks for the data files
* one/more disks for the index files (=data file with only indexes in them)
* one/more disks for the transaction log files
* one/more disks for the backup files

all these disks should be dedicated to their job, and not contain other things. The backup disk should be backed up to tape regulary, the backups of the transaction logs as fast as possible.

Don't forget that performance is not Everything (for the DBA), even if the users/customers normally only see the performance. Shut down the database for 2 hours, and tell the users/bosses the database has been lost because no security had been build... You will see the results.

Check security
Check reliablitiy
Check performance
Check bottlenecks
Check your checks

CHeers
ykchakri:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
Top Expert 2004

Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Accept angelIII's comment

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Arbert
EE Cleanup Volunteer

Explore More ContentExplore courses, solutions, and other research materials related to this topic.