Number of Databases
-
What is the practical maximum number of databases that SQL can handle and still maintain a good level of performance? Our vendor is saying we'll need a separate server for each 5 or 6 databases, but I know there are servers out there with many, many more than that.
I'm expecting the DBs to be less than 1 GB, and the use to be moderate, heavy in very small bursts (for reporting or billing runs).
I know this is a wide-open question -- I wish I had more specifics to give you.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
Just to fish out numbers... I've read about servers that handled 100s of dbs. I think it all comes down to how much traffic are the apps sending and how much does the server can take (especially on peak hours).
-
Like Remi said, there are way too many variables (CPU, memory, usage, server load, disk subsystems, etc.) to say something like 5 to 6 is the max.
When dealing with sofware vendors - I always ask for a reference of a client with similar needs as me, so that I can call them and see how they are doing with their implementation. A lot of times the vendor WAY oversizes the solution so that there will be little to no chance that the application doesn't perform well due to hardware.
Here's a favorite story of mine dealing with a non-technical sales rep:
Vendor - You need to buy an eight-way server.
Me - why
Vendor - The first user will get the first CPU, the second user will get the second CPU...
Me - (muffled laughing).
-
Was he selling the server too?
On a more obvious side : did you buy the software??
-
He was only the software vendor, we were a Compaq (HP) shop, and did all of our own purchasing. We bought the software, but did not take his sizing recommendations into account. That was just a funny story demonstrating why I take everything software vendors say with a grain of salt.
-
Maybe you should have said :
But I have 47 users, do I have to buy a 47 way server??
Then record the answer and playback at the Christmas party.
. -
I can just guess the answer - 'In that case, you'll need 6 eight-way servers, and you'll even have room for an additional user!'
-
Yup, then you show up at PASS with that recording... Sure would get you some freebies
. -
The "no chance that the application doesn't perform well due to hardware" sounds like the most reasonable answer.
I'm going to recommend to my CIO that we buy servers when performance problems are encountered, and NOT set some arbitrary number of databases per server for this application.
I'm sure I'll end up in a nightmare scenario of having to move the DB and then figuring out why the application can't find it anymore!
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
We have one server here, that's completely busy with the call center database being the only one on that server, while another server hosts some 30+ less frequently used and still gives good performance.
But another point that hasn't been mentioned yet.
We have here a third server exclusively for the HR department. It might happen that there are three concurrent users in that system, but that would already be peak usage. However, there is no chance to either move this db to another server or that that server will host other db's. It is simply not wanted.
To cut a long speech short (can I say so?):
It depends...
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes you can, but the real expression is : "To cut a long story, short ...."
-
Now that I read this, I remember having used it myself before...Thanks!
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
As has been said earlier in the thread, usage of the DBs will set the practical limits. Another limit that might affect some is that Enterprise Manager will not play nice with a lot of databases, but I guess anyone managing that kind of server would not be using EM anyway. To make it a little more theoretical though, the artificial limit of SQL Server is 32,767 databases per instance. And in Books Online, Memory Used by SQL Server Objects Specifications, you can see how much memory is used by each open database regardless if it is actually in any use at the moment.
-
One thought - if a software vendor is referring to lots of databases, is it his software that is using them all? If so and its bad design, perhaps he's worried about system table / tempdb locking. If so, a good reason to reconsider buying his software!
-
After a teleconference with the vendor, it became obvious they were used to dealing with clients that had moderately large installations (about 20 users per database) as opposed to our anticipated use (2-4 users per database), so I'm leaning more heavily toward sizing the system based on their 100 users per server comment.
It also became obvious that they really didn't know much about SQL Server in the real world, and were repeating an "engineering" recommendation.
That would give us 20-30 databases for the small offices on a server. And, if we see performance problems, we'll buy a server.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply