November 11, 2008 at 3:05 pm
Let me start by pointing out Microsoft's max # of databases stated for SQL 2005 (64-bit). 32,767
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx
With that said, has anybody come even close to this? Just curious because we're looking at moving about 4,000 databases currently split between two instances in a clustered environment (4 active nodes) to 4 instances in a clustered environment (6 active nodes). I just want to make sure that we're not going to stretch our resources.
Microsoft says that you can have 32,767 DB's per instance, so after you take off the holy ones (tempdb, master, model, msdb) that means I can cram 32,763 DBs per instance.
That seems excessive.. and given that the number of user connections is also 32,767 in a hosted environment, the number of users will likely be a determining factor as to the actual number of databases (if customers have 50 employees on average then that would lead to about 655 or customer companies with each one using aroudn 4 databases or 2621 databases per instance).
Has anybody done any actual number studies as to a good number of databases to operate on?
November 11, 2008 at 6:52 pm
mtassin (11/11/2008)
Just curious because we're looking at moving about 4,000 databases currently split between two instances in a clustered environment (4 active nodes) to 4 instances in a clustered environment (6 active nodes). I just want to make sure that we're not going to stretch our resources.
:crazy:
Are most of these databases not in use or something? Because this honestly seems waaay over the top. I mean how much memory do you have that you can afford to maintain caches for 1000-2000 databases at a time? Whats the projected IO bandwidth capacity that you will need for this?
My guess is that your resources are going to get stretched. :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 11, 2008 at 6:56 pm
Whats the size of the largest DB of the 4000 that are installed on this instance??
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
November 11, 2008 at 8:05 pm
I don't have a magic number, but it really does depend on where your bottleneck shows up.
Here are a few bottlenecks to watch out for, some are deal-breakers, some are not.
Tempdb contention
Disk utilization %
Full Backups taking more than 24 hours
Any other maintenance routine that slips out of the maintenance windows.
Network traffic so heavy that you start to DOS the server when DOS protection is enabled on the OS
When you go to the gui and you click on Logins and it takes 60+ seconds to enumerate the list 🙂
It's all what you're used to managing and what your engineering skills and budget are.
If you build it for scalability it will scale.
We like to put 500-1000 databases on an instance.
Our biggest bottleneck is tempdb contention and disk utilization, sometims RAM, but usually that's
someone's wild query chewing up the buffer cache and that can happen when you have 2 databases on an instance.
full updatestats is problematic too and timing the tran logs to all complete within the SLA time limit.
~BOT
Craig Outcalt
November 12, 2008 at 8:11 am
rbarryyoung (11/11/2008)
Are most of these databases not in use or something? Because this honestly seems waaay over the top. I mean how much memory do you have that you can afford to maintain caches for 1000-2000 databases at a time? Whats the projected IO bandwidth capacity that you will need for this?My guess is that your resources are going to get stretched. :w00t:
Well presently it's a 4 node all active cluster with 3 GB of Ram given to each SQL instance. Each instance thinks it has 8 processor cores.
I have a total of 4000 databases... presently split across two instances, each on it's own clustered server instance. I've never actually physically seen these things... they're in a data center about 1000 mi from me :).
Largest database is 8 gigs... smallest is 10MB. And no, they're pretty much all in use. 2 per client (production and a copy of production for testing things). Tempdb is minimally used (about 120 MB in size) because the developers in order to avoid tempdb contention avoided temp tables, table variables, etc... even at the cost of performance if necessary. The instances haven't even optimized tempdb (by creating a seperate file per processor). (this is pre-me btw).
The database resources are sitting on SAN attached storage with some pretty hefty IO capabilities.
Takes about 3.5 hours to do full backups nightly... Tlogs take about 15 minutes.
November 12, 2008 at 9:47 am
mtassin (11/12/2008)
Well presently it's a 4 node all active cluster with 3 GB of Ram given to each SQL instance.
if the cluster has only 2 instances then 4 nodes cant be active, how have you configured the cluster (i.e. which nodes are responsible for the 2 sql instances)?
if both sql instances run on one node you have an active\passive cluster!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 12, 2008 at 10:02 am
Well I was wrong... it's got 5 nodes (apparently another node was brought up without telling me)... each running different active instances. The cluster group has 5 small instances of about 10-15 databases each on node 1, one of the 2000 database instances on node 2, another on node 3, node 4 has an instance for data conversions, and node 5 seems to be there, but presently passive.
So it's all active... 🙂
I will never claim to be an expert on clusters... they're new to me... never had an opportunity to utilize them prior to this gig.
November 12, 2008 at 10:15 am
thats more like it although essentially with node 5 inactive you could argue you still have active\passive clustering 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 12, 2008 at 3:08 pm
3GB does not sound like anywhere near enough memory for 1000 databases.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 12, 2008 at 3:13 pm
rbarryyoung (11/12/2008)
3GB does not sound like anywhere near enough memory for 1000 databases.
unless of course they're all copies of Northwind and Pubs 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 12, 2008 at 3:51 pm
Resource wise things seem fine... all my cache hit ratios run around .50 or so...
It's 2000 copies of the same database (with different data in it)... I wonder if a lot of cached plans are literally working cross database? My cache hit ratios are running around .50 per performance monitor...
Back when I had 5 databases on a box with 8 GB of Ram I got not much better results.
When it comes to feeling good about it... no I'm not a huge fan of it... but just on a performance basis... the whole thing hums along quite nicely.
If there's a formula or something out there I can use to more equitably divide my SQL databases amongst my SQL instances, I'd love to use it... but the only documented info on max databases says you can stick 32,000 of them on a server and 10 instances per server means you could theoretically host 320,000 databases on a single server. So it makes it a bit hard to request enough instance to make me comfortable (truthfully I'd be happier at about 300-500 per instance).
Page files, etc hum along at a steady usage... I don't see much in terms of huge increases during busy times or anything. It's sort of scary and amazing at the same time that this thing works... but I've got 2 instances that say it does.
November 12, 2008 at 4:05 pm
There shouldn't be sharing of plans, but I could be wrong.
I know people that have had hundreds of duplicate databases (different data) on an instance, but not 2000. It depends on load. If it works, you can run it. If not, you need another box.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply