<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
How many databases on one single instance
12 posts, Page 1 of 2
1
2
»»
How many databases on one single instance
Rate Topic
Display Mode
Topic Options
Author
Message
a.borgeld
a.borgeld
Posted Tuesday, January 10, 2012 3:44 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, August 20, 2012 10:50 AM
Points: 42,
Visits: 87
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
Cadavre
Cadavre
Posted Tuesday, January 10, 2012 3:55 AM
SSCrazy
Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236,
Visits: 6,486
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
spaghettidba
spaghettidba
Posted Tuesday, January 10, 2012 4:54 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804,
Visits: 8,067
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
Get your two-cent-answer quickly
The Spaghetti DBA
Post #1233064
a.borgeld
a.borgeld
Posted Tuesday, January 10, 2012 5:03 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, August 20, 2012 10:50 AM
Points: 42,
Visits: 87
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
george sibbald
george sibbald
Posted Tuesday, January 10, 2012 6:51 AM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 3:23 PM
Points: 5,264,
Visits: 11,191
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
benjamin.reyes
benjamin.reyes
Posted Tuesday, January 10, 2012 9:44 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:34 PM
Points: 198,
Visits: 745
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
a.borgeld
a.borgeld
Posted Tuesday, January 10, 2012 11:33 PM
SSC Rookie
Group: General Forum Members
Last Login: Monday, August 20, 2012 10:50 AM
Points: 42,
Visits: 87
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
crazy4sql
crazy4sql
Posted Tuesday, January 10, 2012 11:55 PM
SSC Eights!
Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:10 PM
Points: 876,
Visits: 3,731
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
Kenneth.Fisher
Kenneth.Fisher
Posted Wednesday, January 11, 2012 9:19 AM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:31 AM
Points: 3,367,
Visits: 1,563
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
a.borgeld
a.borgeld
Posted Wednesday, January 11, 2012 10:01 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, August 20, 2012 10:50 AM
Points: 42,
Visits: 87
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 »
12 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
