Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How many databases on one single instance Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 3:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 3:04 AM
Points: 45, 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?
Post #1233023
Posted Tuesday, January 10, 2012 3:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:22 AM
Points: 2,422, Visits: 7,436
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1233025
Posted Tuesday, January 10, 2012 4:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:31 AM
Points: 5,014, Visits: 10,514
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

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1233064
Posted Tuesday, January 10, 2012 5:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 3:04 AM
Points: 45, 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é
Post #1233071
Posted Tuesday, January 10, 2012 6:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:27 PM
Points: 5,974, Visits: 12,875
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


---------------------------------------------------------------------

Post #1233159
Posted Tuesday, January 10, 2012 9:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 9:08 AM
Points: 252, Visits: 1,350
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.
Post #1233354
Posted Tuesday, January 10, 2012 11:33 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 3:04 AM
Points: 45, 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.
Post #1233735
Posted Tuesday, January 10, 2012 11:55 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Sunday, July 20, 2014 10:36 PM
Points: 880, Visits: 4,081
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
Post #1233744
Posted Wednesday, January 11, 2012 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 1:31 PM
Points: 3,461, Visits: 1,784
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
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
Post #1234101
Posted Wednesday, January 11, 2012 10:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 3:04 AM
Points: 45, 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é
Post #1234161
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse