January 28, 2015 at 1:51 am
We have a big push to do as much consolidation of servers and databases as possible. At the moment, we don't have a huge number (22 servers, 300 databases). Most are SQL 2008 and 2008 R2, a couple of SQL 2012. And we're looking to implement SQL 2014 this year. A couple are physical servers, the others are VM (VMware). The consolidated server(s) will also be VM. When consolidating instances on a single server:
How many instances per server? Databases per instance? Not MS theoretical limits but based on practical experience. IIRC, a MS SQL trainer once told me 5 instances per server and 50 databases per instance (but that seems low by today's standards).
What are the resources that limit a single server's performance, compared to giving each instance it's own server? (Assuming CPU and memory are practically unlimited, and each instance has separate SAN LUNs for data, logs tempdb, etc). What is the first thing "to hit the wall" on a single server?
Would you mix versions of SQL? 2014, 2012, 2008 on the same box?
What other considerations are there when consolidating instances and databases?
thanks for any suggestions.
January 28, 2015 at 2:11 am
JarJar (1/28/2015)
How many instances per server?
In my opinion, 1 unless you have a good reason.
The only advantages of multiple instances over multiple DBs on an instance are memory partitioning, partial security separation and more TempDBs, but you can get that and more my partitioning at the VM level (two VMs with one instance each rather than one VM with two instances)
Databases per instance?
Absolutely no way to answer that question. Depends on the database, the usage, the resource requirements, the server hardware, etc, etc, etc
What is the first thing "to hit the wall" on a single server?
Depends entirely on the database and it's load and usage patterns. No way to say in general.
Would you mix versions of SQL? 2014, 2012, 2008 on the same box?
See first answer.
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
January 28, 2015 at 2:14 am
On thing you should not forget it the effort required by developers to change all the applications to point at the new instance name/database name.
Developers and administrators usually are not part of the same organization structure and have entirely different priorities. The developers may have little incentive to become part of this if it conflicts with their priorities. I was part of a consolidation project that failed for this very reason because the developers would just not get on board.
Another thing to consider is how well applications get along with others. Sometimes vendors write software that requires SA access to the database, and this may be a consideration for other applications on the server. I have also seen applications that have the instance name hard-coded or the user name and password hard-coded, so that can be a problem.
February 11, 2015 at 2:20 am
GilaMonster (1/28/2015)
JarJar (1/28/2015)
What is the first thing "to hit the wall" on a single server?
Depends entirely on the database and it's load and usage patterns. No way to say in general.
i meant outside of SQL, at the windows server level. are there any resource advantages to multiple servers rather than a single server?
February 13, 2015 at 3:16 am
thread bump for help?
March 6, 2015 at 11:34 am
How many software installations of differing versions on one box: 1
How many instances? 1 if it is possible. I have instances that require different collations for example.
How many databases per instance? You will need to check your server performance and resource consumption to make sure the next database doesn't tip it over the edge. I have one database for example that consumes between 40-50GB constantly and others whereby I wonder if they are ever used! You will need to use your best judgement.
The two pieces of advice I would offer are these:
1. If you decide to install more than one instance for whatever reason, ensure you allocate memory appropriately and just as important, ensure that your allocation doesn't encroach on that which the operating system needs. Keep a check on Page Life Expectancy over the instance(s) to ensure you aren't memory starving the system
2. Don't forget to select an appropriate recovery mode for your needs for each database. Does a DWH really need to run in full or would simple suffice? In either case, something that you need to consider is your transaction log maintenance plan on a database for database basis.
3. How many TEMPDB files do you need to reduce allocation contention? Would it suffice to have them on a single LUN or would performance dictate separate LUNs better.
There are a lot more things to consider than just this but it is certainly a start.....
March 9, 2015 at 9:29 am
thanks kevaburg. i'm pretty much clued in to all that. what i don't understand is where the limitations are in Windows Server, if any.
why should I maintain 1 instance per server when I can load up 5 or 10 instances (given that I have enough memory) on a single server?
instead of 10 Windows Servers to maintain and update, now I only have one. assuming that the 5 or 10 instances happily co-habitate on the same server.
we're using VMware, so if push comes to shove we can always increase the memory or cpu without a major server maintenance.
March 9, 2015 at 11:59 am
JarJar (3/9/2015)
thanks kevaburg. i'm pretty much clued in to all that. what i don't understand is where the limitations are in Windows Server, if any.why should I maintain 1 instance per server when I can load up 5 or 10 instances (given that I have enough memory) on a single server?
instead of 10 Windows Servers to maintain and update, now I only have one. assuming that the 5 or 10 instances happily co-habitate on the same server.
we're using VMware, so if push comes to shove we can always increase the memory or cpu without a major server maintenance.
Hi JarJar,
There are no limitations in Windows except you need to calculate the memory left over so that Windows doesn't get starved of resources.
Why should you maintain only one instance on the server? Let me answer a question with a question: Why would you want more than one? Technically you could have 30 (I have a physical server with 13 Instances for testing and development) but ask yourself why you would want to do that? Do you realise how much extra work I have looking after 13 Instances in backups, monitoring etc;
If you are using VMware then be very careful. You will be required to license ALL the hardware cores on the box even if you use only 4! Additionally, you are making yet more work for yourself insofar that now you have to configure your DB Server in such a way that it doesn't negatively affect the other VMs on the physical box. And naturally the other way around: You need to ensure that your other VMs don't affect your database. Don't forget as well, upping CPU and Memory capacity still requires a downtime so there you have won very little.
My advice is and will always remain: 1 Box, 1 Instance
March 10, 2015 at 12:58 am
kevaburg (3/9/2015)
There are no limitations in Windows except you need to calculate the memory left over so that Windows doesn't get starved of resources.
this pretty much is what i was looking for. i just wanted to validate that there was not any "hidden limit" on something like disk throughput, bandwidth, etc within the windows op system.
kevaburg (3/9/2015)
Why should you maintain only one instance on the server? Let me answer a question with a question: Why would you want more than one? Technically you could have 30 (I have a physical server with 13 Instances for testing and development) but ask yourself why you would want to do that? Do you realise how much extra work I have looking after 13 Instances in backups, monitoring etc;
"separation of databases which don't play nice together" is my answer. sharepoint must be on its own instance, bi on it's own instance, application databases on their own, infrastructure monitoring applications on their own, ERP on its own, etc. i don't want the buffer cache to be blown away because some bi report is reading 10GB of data, or our server monitoring software kicks in to rollup performance data. those apps can knock themselves out doing what they do and our sharepoint and ERP will be unaffected. plus we only have one windows server to update. this gets easier with alwayson or clustering but the single server instances must be done off-hours (pita).
kevaburg (3/9/2015)
If you are using VMware then be very careful. You will be required to license ALL the hardware cores on the box even if you use only 4! Additionally, you are making yet more work for yourself insofar that now you have to configure your DB Server in such a way that it doesn't negatively affect the other VMs on the physical box. And naturally the other way around: You need to ensure that your other VMs don't affect your database. Don't forget as well, upping CPU and Memory capacity still requires a downtime so there you have won very little.
are you sure you are not thinking of Oracle? because that is true, we have an ESX with 2 CPU licensed and dedicated for our Oracle apps.
not quite true for SQL. you have that option to license all physical cores but you can also license individual cores allocated to specific VMs.
March 10, 2015 at 1:45 am
JarJar (3/10/2015)
kevaburg (3/9/2015)
There are no limitations in Windows except you need to calculate the memory left over so that Windows doesn't get starved of resources.this pretty much is what i was looking for. i just wanted to validate that there was not any "hidden limit" on something like disk throughput, bandwidth, etc within the windows op system.
kevaburg (3/9/2015)
Why should you maintain only one instance on the server? Let me answer a question with a question: Why would you want more than one? Technically you could have 30 (I have a physical server with 13 Instances for testing and development) but ask yourself why you would want to do that? Do you realise how much extra work I have looking after 13 Instances in backups, monitoring etc;"separation of databases which don't play nice together" is my answer. sharepoint must be on its own instance, bi on it's own instance, application databases on their own, infrastructure monitoring applications on their own, ERP on its own, etc. i don't want the buffer cache to be blown away because some bi report is reading 10GB of data, or our server monitoring software kicks in to rollup performance data. those apps can knock themselves out doing what they do and our sharepoint and ERP will be unaffected. plus we only have one windows server to update. this gets easier with alwayson or clustering but the single server instances must be done off-hours (pita).
kevaburg (3/9/2015)
If you are using VMware then be very careful. You will be required to license ALL the hardware cores on the box even if you use only 4! Additionally, you are making yet more work for yourself insofar that now you have to configure your DB Server in such a way that it doesn't negatively affect the other VMs on the physical box. And naturally the other way around: You need to ensure that your other VMs don't affect your database. Don't forget as well, upping CPU and Memory capacity still requires a downtime so there you have won very little.are you sure you are not thinking of Oracle? because that is true, we have an ESX with 2 CPU licensed and dedicated for our Oracle apps.
not quite true for SQL. you have that option to license all physical cores but you can also license individual cores allocated to specific VMs.
"Separation of databases which don't play nice together". The Problem with this Statement is that when you have databases that don't Play well together you Need to ask yourself why. SharePoint I can understand due to the Maximum Level of Parallelism having to be 0 although in my Environment even that hasn't affected Performance. Having diverse Systems on a single instance requires an understanding of the unlying processes but it is most certainly not a KO Point that speaks against having just a single instance. Here ou Need to provide more Details about why they don't Play well together.
As for the licensing. I am not talking about Oracle. We have Solaris machines with separate Zones configured and the licensing Arrangement for that is considered in the main Zone licensing Agreement.
For SQL Server however it really is as I have said and it is the single biggest Driver for a consolidation plan. Each VM on our ESX Cluster must have ALL available physical cores licensed and that is a Standard. It is the same Problem when we have BI components on machines separate to the database machine. The BI machine must now have a full SQL Server license regardless of whether the database is installed on it or not!
March 10, 2015 at 2:05 am
kevaburg (3/9/2015)
"Separation of databases which don't play nice together". The Problem with this Statement is that when you have databases that don't Play well together you Need to ask yourself why. SharePoint I can understand due to the Maximum Level of Parallelism having to be 0 although in my Environment even that hasn't affected Performance. Having diverse Systems on a single instance requires an understanding of the unlying processes but it is most certainly not a KO Point that speaks against having just a single instance. Here ou Need to provide more Details about why they don't Play well together.
would you really mix OLAP and OLTP? do you work with SAP? i'm not sure they would even support you if you tried to mix databases on their instance.
kevaburg (3/9/2015)
For SQL Server however it really is as I have said and it is the single biggest Driver for a consolidation plan. Each VM on our ESX Cluster must have ALL available physical cores licensed and that is a Standard.
not true. this is an option, but not the only licensing option.
March 10, 2015 at 3:24 am
JarJar (3/10/2015)
kevaburg (3/9/2015)
"Separation of databases which don't play nice together". The Problem with this Statement is that when you have databases that don't Play well together you Need to ask yourself why. SharePoint I can understand due to the Maximum Level of Parallelism having to be 0 although in my Environment even that hasn't affected Performance. Having diverse Systems on a single instance requires an understanding of the unlying processes but it is most certainly not a KO Point that speaks against having just a single instance. Here ou Need to provide more Details about why they don't Play well together.would you really mix OLAP and OLTP? do you work with SAP? i'm not sure they would even support you if you tried to mix databases on their instance.
kevaburg (3/9/2015)
For SQL Server however it really is as I have said and it is the single biggest Driver for a consolidation plan. Each VM on our ESX Cluster must have ALL available physical cores licensed and that is a Standard.not true. this is an option, but not the only licensing option.
OLTP and OLAP? No...I would never mix them. But simply separating other databases simply because you can is not something you should do simply because you can. Our DWH consumes a constant 200GB of RAM and Processor time sits at around 40% so putting that on a shared box (let alone a shared Instance) is not a good idea. I don't work with SAP so I can't comment on that but I have worked with Software houses that Claim their Software has to have their own instance. Most of the time that is utter rubbish and once you throw the "you-won't get-your-own-instance" Card at them they miraculously alter their Claims.
An SQL Server Admin worth his/her salt can host many highly-concurrent databases on a single Server instance with a Little thought. I know because I do it. Databases that don't Play well together must have some sort of dependency that means they CANT Play well together. That should be fixed. Simply installing more and more instances will in the Long-term cause far more headaches than it saves and not only that, you will end up reserving resources per instance that could be better used elsewhere.
If you feel you must separate Systems, consider placing them on other boxes. What do you hope to achieve by separating them into individual instances on the same box? The resources they Claim will not be reduced but rather increased because each instance in ist own right requires its own OS space and not only that, but you will Need to reserve Memory and disk space and this in ist own right can waste resouces if it isnt planned correctly.
1 Instance, 1 Box. I cant shift on that Point.
The link you sent me is interesting because our license Audit has been completed in the last couple of weeks and our Microsft "expert" had a completely different idea. I will come back to that.
March 10, 2015 at 3:33 am
JarJar (3/10/2015)
"separation of databases which don't play nice together" is my answer. sharepoint must be on its own instance, bi on it's own instance, application databases on their own, infrastructure monitoring applications on their own, ERP on its own, etc.
s/instance/server
If they don't play nice in a single instance, I don't want them in separate instance on the same server where they can still not play nice with each other.
IMO, before the widespread use of virtualisation, there was a use for multiple instances, but now I'd much prefer to virtualise and have one instance per server. I get far better isolation and control from a hypervisor than I would from multiple instnaces
i don't want the buffer cache to be blown away because some bi report is reading 10GB of data, or our server monitoring software kicks in to rollup performance data. those apps can knock themselves out doing what they do and our sharepoint and ERP will be unaffected. plus we only have one windows server to update. this gets easier with alwayson or clustering but the single server instances must be done off-hours (pita).
But you don't mind the BI instance chowing the CPU and impacting the other instances or the monitoring software maxing out the drives and impacting the other instances
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
March 10, 2015 at 6:45 am
kevaburg (3/10/2015)
OLTP and OLAP? No...I would never mix them. But simply separating other databases simply because you can is not something you should do simply because you can. Our DWH consumes a constant 200GB of RAM and Processor time sits at around 40% so putting that on a shared box (let alone a shared Instance) is not a good idea. I don't work with SAP so I can't comment on that but I have worked with Software houses that Claim their Software has to have their own instance. Most of the time that is utter rubbish and once you throw the "you-won't get-your-own-instance" Card at them they miraculously alter their Claims.
i'm not suggesting to separate any and all databases. i'm suggesting to separate "like" applications and mission critical applications. if there are no restrictions or limitations at the windows level, i don't see any advantage to build a separate windows server (x2 or 3 for HADR) for each instance.
kevaburg (3/10/2015)
An SQL Server Admin worth his/her salt can host many highly-concurrent databases on a single Server instance with a Little thought. I know because I do it. Databases that don't Play well together must have some sort of dependency that means they CANT Play well together. That should be fixed. Simply installing more and more instances will in the Long-term cause far more headaches than it saves and not only that, you will end up reserving resources per instance that could be better used elsewhere.
well, we've already established that BI and OLTP should be separated. sharepoint and SAP should also be separated. our monitoring software and similar apps should be separated from application databases. can't fix any of that.
kevaburg (3/10/2015)
If you feel you must separate Systems, consider placing them on other boxes. What do you hope to achieve by separating them into individual instances on the same box? The resources they Claim will not be reduced but rather increased because each instance in ist own right requires its own OS space and not only that, but you will Need to reserve Memory and disk space and this in ist own right can waste resouces if it isnt planned correctly.1 Instance, 1 Box. I cant shift on that Point.
fewer windows servers to update, monitor, and backup. each instance gets it's own memory, it's own tempdb, and it's own LUNs. the only thing that is shared between instances is the operating system, the network cards, and processors. i still haven't heard that there is any limitation on the operating system (which was the initial intent of this thread). CPU and disk can be dynamically added if needed. (of course more licenses needed if more cores are added).
kevaburg (3/10/2015)
The link you sent me is interesting because our license Audit has been completed in the last couple of weeks and our Microsft "expert" had a completely different idea. I will come back to that.
March 10, 2015 at 6:58 am
I am curious. You seem to have made up your mind what works for you so why are you pushing so hard against the advice of People whose experience you want to call upon?
In summary:
1 Box, 1 Instance
If databases dont work well together, find out why and fix them.
Unless you have a very good reason for multiple instances, don't use them.
Thats about it really.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply