December 18, 2015 at 7:34 am
I was wondering what do you all think or have as an average of databases per instance?
At what number are you comfortable with before you want to move to a new instance?
I'm currently in an OLAP environment so all databases are used for reports so I do realize it may vary depending on your environment.
Thank you!
December 18, 2015 at 7:44 am
butcherking13 (12/18/2015)
I was wondering what do you all think or have as an average of databases per instance?At what number are you comfortable with before you want to move to a new instance?
I'm currently in an OLAP environment so all databases are used for reports so I do realize it may vary depending on your environment.
Thank you!
Even if someone comes up with an answer – let's say five, just for fun – what will you do with the information?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 18, 2015 at 10:44 am
I was looking to see if there is a pattern or average by response to see if I might be better off creating another instance and moving some databases to new instance for easier administration.
December 18, 2015 at 10:57 am
butcherking13 (12/18/2015)
I was looking to see if there is a pattern or average by response to see if I might be better off creating another instance and moving some databases to new instance for easier administration.
Additional instances require more administration. I can't see how they would ever make administration easier.
The decision as to whether you require more instances should be based on many factors, most of which will be specific to your environment, including:
1) Performance and predicted future hardware requirements.
2) Separation (eg, test/QA/prod/reporting/SSIS...)
3) Disaster recovery requirements
and on and on and on
The number of databases is not a direct factor in any of this.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 18, 2015 at 11:57 am
Sorry, but average databases per instance really is a nonsensical metric for many reasons, including (but not limited to): hardware capabilities (RAM/CPU/IO/NETWORK/ETC), size of individual databases and overall size of all, concurrent users, transactions per second, SLA(s), HA needs, DR needs. The list goes on and on I think.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 18, 2015 at 4:41 pm
I'll throw my hat into the ring and say "It Depends".
While SQL Server can certainly handle a whole lot of databases, there may be logical or even strategic reasons to put databases on separate machines. For example, you might want to protect your "money maker" databases from possible hacks by having them on a separate machine from any 3rd party software that hasn't gone through penetration testing. You might also have some bone crushing, heavy lifting imports and preprocessing going on that you don't want to detract from OLTP apps. You might also have some very sensitive information (payroll, employee info, etc) that you only want one or two very trusted DBAs to have access to (remember that most DBAs actually DO need "sysadmin" privs on the box to correctly and easily maintain it and there's no way to protect any database on the box from them).
--Jeff Moden
Change is inevitable... Change for the better is not.
December 18, 2015 at 7:40 pm
I appreciate the responses as this subject recently came up and was curious to answers from anyone that would respond. I do realize there are a variety of reasons to make this choice, but was curious to hear them from others and why so.
Thank you for the replies!
December 19, 2015 at 6:55 am
butcherking13 (12/18/2015)
I appreciate the responses as this subject recently came up and was curious to answers from anyone that would respond. I do realize there are a variety of reasons to make this choice, but was curious to hear them from others and why so.Thank you for the replies!
If you're referring to reporting data marts as the main use for those databases, then it really does depend. My environment is similar. I have a number of databases used solely for reporting. The number of databases greatly depends on a number of variables such as the size of the data to how it's being used.
Unfortunately, scaling up is not ideal and it's not something you can ideally plan with the sheer number of databases. For example, you can't go to your boss and say, "As long as we don't go above 20 databases, we will be fine."
Usage of each database to amount of data processed to refresh those databases to amount of data stored in those databases to costs of expanding and even how it's accessed (internal vs. external) as well many others all factor into justifying the business case to scale up.
December 19, 2015 at 8:21 am
I will say that I do have more experience than most at both ends of the extreme as far as numbers of databases per server goes. I have a client that at one point had over 7400 databases on one server. That was fun - in a VERY painful and difficult manner. 🙂 And I have also worked on systems where some of the biggest hardware available at the time provided merely acceptable performance for a single exceptionally large or exceptionally busy database.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
December 19, 2015 at 1:11 pm
Something all databases on a given instance share is the buffer cache (and other memory pools). SQL Server decides on its own which database pages are kept in memory. You can limit the size of the buffer cache, but you cannot divide memory within an instance to specific databases. Most of time this is OK. But if you do need control this, separate instances on the same machine (or by extension different machines) is the only way.
Other reasons to separate are different SQL Server versions or patch levels, SQL Server settings at the instance level, collation (some databases may have different collations but need tempdb to have the same collation), different maintenance windows, SQL Server extensions that may be incompatible with each other or applications, and different security requirements (as mentioned above). Some applications require sysadmin or similar privileges which makes it undesirable to share an instance with other applications.
Most of this is common sense if you have good understanding of the applications and databases. Having many instances make things more flexible which make your work more simple, but it will give you more to work to do. Automation can help to negate this for some tasks. One strategy is to keep the databases of big and critical applications on their own instances, while consolidating smaller ones on a central server. This may save licensing costs in some cases, but not if you're migrating from the free SQL Server Express edition. If you need to combine data from different databases, for example by joining tables, having those databases on the same instance is much faster than using linked server connections.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply