SQL Server Consolidation
Many companies are now looking to consolidate their SQL Server instances. The old strategy of one instance per server can be wasteful of resources. Often large chunks of physical resources sit idle and a lot of money is spent on electricity to power all those machines. If you can consolidate even some of your instances onto fewer machines, you can save both physical and human resources. Note that any one of the below strategies will work on either a standalone or clustered system.
Some of the pros/cons of each common type of consolidation strategy:
Hyper-V virtualization
· Great when you have to use a version of the OS, drivers, or applications that aren’t used anywhere else or that don’t ‘play well with others’.
· Can be more flexible and easier to move to another host machine than a physical instance.
· Has great performance for SQL Server if configured per best practices, you can get the same throughput at a slight cost in increased CPU usage.
· Network intensive applications may have a higher network and CPU cost on a VM.
· For now, any Hyper-V virtual machine (VM) is limited to only 4 CPUs assigned per VM (for Windows 2008, 2 CPUs for Windows 2003 guest OS).
· For now, any Hyper-V virtual machine is limited to 64GB of RAM per VM.
· Requires x64 chips with Intel VT or VMD virtual with DEP enabled
· Allows total isolation of the entire environment.
Multiple instances
· Very good for isolating security (assuming each SQL Server service starts with a different account).
· Allows each instance to be managed and configured to meet the needs of a single group of users. This often makes downtime for SQL Server patches easier to arrange.
· There is some additional overhead, mainly memory, required compared to a single instance because each instance has some allocations that occur at startup regardless of actual usage. However, this is usually low, especially on today’s high RAM systems.
· Allows multiple versions of SQL Server to be installed at once with each application on its preferred version. The different version could mean 2000 vs. 2005 vs. 2008, or it could mean different service pack and hotfix levels.
Single instance with multiple databases/applications
· This method can be very cost effective and is often easy to manage as long as the applications/databases don’t have performance problems or cause conflicts with one another.
· If a SQL Server patch has to be applied for one database, all the databases share the downtime. The administration is easier because the patch only has to be applied once, but it can be more difficult to arrange downtime that is acceptable to all users.
· This method can cause security problems. If any application has permissions, such as sysadmin role membership, which extends outside of its own database it can affect other databases/applications on the server.
o One example of a problem is an application with sysadmin permissions that changes configuration settings, TempDB settings, or other things that affect other databases/applications either directly or indirectly.
o Another example is that if an application doesn’t protect against SQL injection, it can allow a hacker into the database. If that database is on an instance with other databases and the id used by the hacked application has elevated permissions such as sysadmin then the hacker now has access to all the other databases.
o This could include accidental or intentional changes by internal employees or contractors, so the danger is not limited to databases accessible through the internet.
· Applications share TempDB which can sometimes be a bottleneck depending on the way the applications access the databases. Often this can be managed with proper TempDB sizing and number of data files.
· Depending on how you license your software, this could possibly save you some money.
I hope you find this information helpful, and I have included some references below with more details on your options.
Green IT in Practice: SQL Server Consolidation in Microsoft IT
https://msdn.microsoft.com/en-us/architecture/dd393309.aspx
Running SQL Server 2008 in a Hyper-V Environment - Best Practices and Performance Recommendations
Planning for Consolidation with Microsoft SQL Server 2000
https://www.microsoft.com/technet/prodtechnol/sql/2000/plan/SQL2KCon.mspx
SQL Server Consolidation on the 64-Bit Platform
https://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx