SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How many databases on one single instance


How many databases on one single instance

Author
Message
a.borgeld
a.borgeld
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 92
Specs:
VM-ware
Dual core
8 gig memory
64 bit hard and software (Windows 2008 R2 + SQL server 2008 R2)

How many databases can i run on a single instance?

I read about max on 32 bit, use not more then 10 database, but is there a max on 64 bit servers?
I thaught i heard about 32 databases and then you have to make another instance. Is that the case?
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 8472
Theoretically, you could have 32,767 databases per instance.

Good luck with that. . . Personally, I find it difficult to keep track of more than 10.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Gianluca Sartori
Gianluca Sartori
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9836 Visits: 13350
I don't think there's a baked answer to this question.
"As many as your hardware can handle" would be quite close.
If your server has adequate memory, CPU and disk to serve 100 databases, go ahead.
Be aware that mixing workloads from different applications makes monitoring and tuning a bit complicated. Resource Governor helps, but doesn't take away all the pain.

Hope this helps
Gianluca

--Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
a.borgeld
a.borgeld
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 92
Thanks for the feedback Cadavre, i had read it somewhere and i wanted to be sure.

And ofcourse the oposite is add much more hardware. But dividing into more instances in a normal configuration would be better.

Kind regards,

André
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10292 Visits: 13687
that figure you remember of 10 databases on a 32bit server sounds like how many databases you can MIRROR on one server.

Even that is not a hard and fast figure, it all comes down to how much load the databases impose on the server.

64bit would be able to mirror more, but load is still the delimiting factor

---------------------------------------------------------------------
benjamin.reyes
benjamin.reyes
Mr or Mrs. 500
Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)Mr or Mrs. 500 (525 reputation)

Group: General Forum Members
Points: 525 Visits: 2286
How big are these databases and what kind of work(type and load) do they have?

I've seen way more than 10 per instance in production on 32 and 64 bit machines.
a.borgeld
a.borgeld
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 92
15 gig, but there are databases from 1 gig too. Average 7 gig. Some are for the restservice some for the Internetapplication.
I've seen it too. But ofcourse you need to be carefull you don't lose sight and in 32bit i've seen performance degradation with more then 10.
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
a.borgeld (1/10/2012)
Specs:
VM-ware
Dual core
8 gig memory
64 bit hard and software (Windows 2008 R2 + SQL server 2008 R2)

How many databases can i run on a single instance?



Answer is, it depends :-
1) Size of database
2) Transaction Volume of Database
3) How frequently your database are being refreshed with maintenance plan
4) Number of users connecting.

And on light note, you can have more than 4 database. As you cant limit sql to not create system databases(without replication).

----------
Ashish
Kenneth.Fisher
Kenneth.Fisher
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4280 Visits: 2033
My office has servers that don't match what you are describing and have 40+ databases on them with little to no performance problems. It really comes down to users & transactions. If you have 1 database with a huge number of users, and transactions, then it could chew up all of the hardware. If however you have 100+ databases where they are only used by 1 or 2 people each, with a very low number of transactions each, then it will certainly work.

I would recommend putting your 10 databases on the server (more if you feel comfortable) and see how your performance stands. If you feel like there is more room, then add another database. If at any point you start seeing pressure somewhere that you can't resolve then you might want to call it good enough, or even pull one of the databases back off the instance.

Also you should note that it won't help to add an additional instance on the same server. They are both going to be pulling from the same hardware so it would actually decrease performance since the instance itself has its own overhead.

We have a few servers where we have put multiple instances on the same server, but we did so for organizational and security reasons. IE we have a DEV and WEBDEV instance on the same server. DEV is for our non web application databases and WEBDEV is for our web application databases.

As with most things SQL Server, it depends. So good luck :-D
Kenneth

Kenneth Fisher
I strive to live in a world where a chicken can cross the road without being questioned about its motives.
--------------------------------------------------------------------------------
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Link to my Blog Post --> www.SQLStudies.com
a.borgeld
a.borgeld
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 92
An other instance can use a different CPU and threads. But your right, it really depends on the hardware.
Thanks for your advice guys.

Kind regards,

André
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search