December 28, 2010 at 10:44 am
I would like to know how many number of databases on each instance and how many instances are recommended on each server by Microsoft in for sql 2008. I know the max number of db's and instances a server can have. I would appreciate if anyone can send me an official MSFT KB article on this.Mentioned below are my requirements.
i) each db is not going to be more than 500mb
ii) There are going to be about 400 dbs.
Thanks
December 28, 2010 at 10:58 am
It's not the size of the database that's important, it's the activity of the DB. 400 mostly idle databases are totally different from 400 very heavily used databases. The hardware also makes a difference. A low powered, low spec server will be able to host far fewer databases than a high-spec machine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 28, 2010 at 1:17 pm
Gails answer is best, but if you want a specific article please review the following:
http://msdn.microsoft.com/en-us/library/ms143432.aspx
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
December 29, 2010 at 7:44 am
Thanks Gail.I should have mentioned this before. These dbs are not heavily transactional. Lets say i have 64bit sql 2008 with OS 2008 64 bit on a 64 gb Dell server with 4 quadcore's , how many dbs and instances you think is suitable?Thanks
December 29, 2010 at 8:14 am
It depends.
Seriously, there's no absolute number that I can pull out that's correct. Depends on the usage of the databases, depends on the way they're accessed, depends on the other hardware specs that you haven't posted.
The only reason for multiple instances is if you need to prioritise resources among databases, or for security reasons, or for more tempDBs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 10:42 am
Gail mentioned below is my h/w config. Lets say i have 100 dbs, i would want to split into two instances with 50 dbs with max of 100 dbs on each instance for better memory management and better use of tempdb. Please let me know your thoughts. We will be using sql 2008 r2 standard edition. Thanks for response.
ItemValue
OS NameMicrosoft Windows Server 2008 R2 Standard
Version6.1.7600 Build 7600
Other OS Description Not Available
OS ManufacturerMicrosoft Corporation
System NameABC- Server
System ManufacturerDell Inc.
System ModelPowerEdge R610
System Typex64-based PC
ProcessorIntel(R) Xeon(R) CPU E5540 @ 2.53GHz, 2528 Mhz, 4 Core(s), 8 Logical Processor(s)
ProcessorIntel(R) Xeon(R) CPU E5540 @ 2.53GHz, 2528 Mhz, 4 Core(s), 8 Logical Processor(s)
ProcessorIntel(R) Xeon(R) CPU E5540 @ 2.53GHz, 2528 Mhz, 4 Core(s), 8 Logical Processor(s)
ProcessorIntel(R) Xeon(R) CPU E5540 @ 2.53GHz, 2528 Mhz, 4 Core(s), 8 Logical Processor(s)
BIOS Version/DateDell Inc. 2.0.11, 2/26/2010
SMBIOS Version2.6
Windows DirectoryC:\Windows
System DirectoryC:\Windows\system32
Boot Device\Device\HarddiskVolume1
Installed Physical Memory (RAM)64.0 GB
Total Physical Memory64.0 GB
Available Physical Memory58.12 GB
Total Virtual Memory24.0 GB
Available Virtual Memory14.3 GB
Page File Space12.0 GB
Page FileC:\pagefile.sys
December 29, 2010 at 10:59 am
What do you want from me? A stamp of approval? A guarantee that your plans are optimal?
I can't give you either. As I said before, it depends. The 100 databases might be fine. They might not. The only way you're going to answer that question is to test under production-level workload.
It depends on the workload placed on the databases.
It depends on the way the queries are written.
It depends on your storage subsystem (which you've said nothing about)
In short, I cannot give you a number or a yes/no answer. You're the only one who can, through testing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2010 at 11:22 am
As Gail has been saying, you need to test your specific loads and databases to know what the right proportions are. I'd suggest starting with one instance and several "typical" databases and then run load testing against them. Simulate the largest number of users and the largest amount of traffic and see how it performs. There's software out there that'll help you do this.
If that runs well, add more databases and keep testing, when you start to hit performance problems, split some off into another instance and see if that helps. You might want to make a chart of your databases grouping them by size, # of users, frequency of requests and size of requests. When you start separating into instances, group them by how much they're being used. Try different combinations.
You can get all the expert opinions in the world, but you won't really know what's best until you experiment with your specific requirements.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 29, 2010 at 11:51 am
As others have said, there is no hard and fast rule.
My rule of thumb is start with fewer db's, monitor load and add or remove databases as needed based on load. My experience has been with mostly smaller, low to medium volume databases, and I've rarely, if ever seen, the database server maxed out with 10-20 databases on it.
Another thing to consider in this situation is, what are you going to do if the server with 100 databases fails? Which databases come up first? Do you have another SQL Server that you can move them to and if so, can it handle the extra load?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 29, 2010 at 12:00 pm
Do the databases exist now? Are they in use?
I dislike multiple instances because I have to make a hard decision about how to split memory among the instances, but you might have security or other requirements that dictate this.
As mentioned, you have to test this. There is no way I can say that xx databases work on a server. One database can overload that system, or it might support 1000 databases.
If the databases exist, and you do not think they are heavily transactional (which isn't a term that can be defined well), then perhaps you want to move 20 databases over and compare the load they generate to the load they used to generate. Then move 20 more, and retest.
Ultimately you need the hardware you need, depending on the performance you want. The way that your databases are accessed, read and write, will impact caches, buffer pools, the I/O system, etc. No easy way to predict what your particular workload(s) will do to a specific set of hardware.
December 29, 2010 at 12:01 pm
Jack has a really good point. You said you're going to have 500 databases, that is a lot of databases to restore/bring back on-line if the server fails. You're going to need to consider backup plans for all of these and have a good recovery strategy that should also be thoroughly tested.
You might want to consider multiple servers or at least virtual machines to try and spread some of the risk.
Also remember that if you have multiple instances, you're going to want backup plans for each instance's system databases.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 29, 2010 at 12:07 pm
Ditto, ditto, ditto to Gail, Stefan, and Jack.
But let's see if breaking down your assumptions will help you understand the issue.
sqldba_icon (12/29/2010)
Lets say i have 100 dbs, i would want to split into two instances with 50 dbs with max of 100 dbs on each instance for better memory management and better use of tempdb.
Here is your first mistake. Assuming breaking your databases into two instances on one server will actually assist you. It may. It may not.
Let's start with Memory. While it can be configured per instance, you still only have a finite amount as your maximum. That amount will be split between your OS and your two instances. But it will also be split between your databases, in uneven amounts which depend on database activity.
You could have the same exact query running on two different databases, and one will be a CPU-Memory hog because of the database design while the other runs in two seconds flat because of the database design.
Regarding TempDB, you will have two tempdbs to play with, one for each instance. But each time there is activity on any of those databases, it will be accessing, in some small part, tempdb. Especially when you run out of memory and SQL Server needs a place to put the data while it's processing all its requests.
The more activity you have, the more likely tempdb will be used. If all databases are seeing activity at the same time, they'll still compete for tempdb space which brings me back to the finite maximum. TempDB lives on the hard drive. All it takes is one rogue query to make one of your two tempdbs chew up all the extra hd space and you're in trouble.
All of this goes back to stuff we cannot see: Your database design. Your query design. Your workflow process. All of this effects how SQL Server runs. So, in order to know what you need, you need to do the deed and test the setup.
You could even get an SSC article out of it, detailing your experiences, what you did, what you found, what you didn't do and why. I know a lot of people would be interested in hearing about it. Remember, hardware is a finite resource. Just because you can scale large doesn't mean you should. The only way to know the tolerance levels are to test them yourself.
December 29, 2010 at 12:22 pm
Brandie Tarvin (12/29/2010)
Ditto, ditto, ditto to Gail, Stefan, and Jack.
And ditto Brandie and Steve. It is going to depend.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2010 at 1:09 pm
I usually avoid multiple instances like the plague. However, the last server I built I split the server up to 2 instances.
Instance 1 has a lot of miscelaneaous databases that are used internally for our company but have very low usage.
Instance 2 has 40 to 50 small databases for a timeclock application. Each location we have has it's own timeclock database. This application should have been designed as a single database but that is out of my control. Of these 40 to 50 databases only 4 or 5 have significant use. Most of them are very small, the 4 to 5 that are heavily used are about a gig each.
The reasons I broke apart the instances was to have better control for security, to allocate memory different for each instance and also because there were other instance level options that needed to be set for different needs. Also to have my tempdb's on separate spindles between the instances.
Both instances have completely different disaster recovery needs.
As others have said resources are finite. In my scenario the server I moved my databases has at least 10 times the resources as both the previous 2 or 3 servers I moved the databases from had. As I moved each database I only moved a few databases at a time and watched my resources like a hawk during peak usage periods.
So far I'm happy with it but I've only had it in production for a short period.
EDIT: Also I ditto what everyone else has said. It really DEPENDS.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
December 30, 2010 at 6:40 am
It depends on the load and the type of load.
My view on instances also goes along the lines of managing security / isolation to a great degree.
I have seen couple db's require a cluster, and also 200 on one machine.
Very different workload patterns.
Along with activity - cpu usage, transactions, queries, etc. you need to think about disk IO.
And then what is the plan for growth? Is the hardware / architecture scalable?
Like the others, testing would be your best indication.
There are a lot of variables. I'd assume that a lot of the db's exist and are running on some hardware that you could monitor.
My server only runs about a dozen dbs. Handles it easily until I need to reload a large fact table to add a new dimension.
At that point, the 1 db uses everything. So some of the other applications slow to a crawl.
So it just depends.....
Greg E
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply