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
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 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
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22070 Visits: 8519
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
SSC Guru
SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)SSC Guru (57K reputation)

Group: General Forum Members
Points: 57001 Visits: 13367
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
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 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
SSC Guru
SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)SSC Guru (58K reputation)

Group: General Forum Members
Points: 58816 Visits: 13706
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
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2883 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
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 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
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: 10754 Visits: 4556
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12409 Visits: 2121
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 was once offered a wizards hat but it got in the way of my dunce cap.
--------------------------------------------------------------------------------
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
Say Hey Kid
Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)Say Hey Kid (671 reputation)

Group: General Forum Members
Points: 671 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