Make your servers work harder: Consolidate with SQL Server 2008 R2!
SQL Server 2008 R2 introduces several new features which significantly expand upon existing support for working in, managing, and leveraging a multi-instance SQL Server environment. This blog post will focus on three of the new areas to improve your consolidation experience by using SQL Server 2008 R2.
First, we have dramatically increased the number of logical processors we support. Gone is the 64 processor limitation; SQL Server 2008 R2 can now scale with Windows Server 2008 R2 up to 256 logical processors! This means you can run more workloads, more queries, and have more users accessing your SQL Server instances simply by moving to newer/larger hardware.
Second, Microsoft’s investment in application and multi-server management will help SQL Server administrators manage database environments more efficiently at scale. A core concept to application and multi-server management is the addition of the Utility Control Point, which enables a centralized view of SQL Server instances and database applications and their utilization across the designated managed server group. And for administrators who use System Center to monitor and manage their SQL Server instances, we are introducing a new SQL Management Pack which removes the DMO dependency, improves the health model (particularly around storage monitoring), and reduces noise and false alerts.
Last but not least, SQL Server 2008 R2 fully leverages the new Live Migration feature delivered as part of the Windows Server 2008 R2 Hyper-V release. This provides even greater benefits to virtualizing and running your SQL Server instance in a virtual machine by providing zero planned downtime. You can also leverage System Center Virtual Machine Manager Performance and Resource Optimization to dynamically load balance your SQL Server workloads in a multi-machine/multi-instance environment. A great video explaining this is more detail is available here: http://newed.any0.dpdns.org/sqlserver/tour/en/videos/business-continuity-windows-server-2008-r2-hyper-v-live-migration.aspx
Putting it all together, we ran a test simulating the consolidation of multiple applications onto a new 32 core server running Windows Server 2008 R2 and SQL Server 2008 R2. We used three of the most common consolidation methods: virtualization (one machine with multiple VM’s each with one SQL Server instance), instance (one machine with multiple SQL Server instances running natively), and database (one machine with one SQL Server instance and multiple independent databases). As this chart shows, we were able to achieve consistent results across each consolidation method even up to 40 applications running simultaneously (and yes, this does mean 40 VM’s running simultaneously)!
|
Consolidation method
|
Number of applications
|
Throughput
|
Response time
|
Host system CPU utilization
|
|
Baseline (old hardware)
|
1
|
100%
|
100%
|
6%
|
|
|
||||
|
Virtualization
|
24
|
+0.8%
|
80%
|
24%
|
|
Instance
|
24
|
+0.6%
|
58%
|
20%
|
|
Database
|
24
|
+0.9%
|
53%
|
16%
|
|
|
||||
|
Virtualization
|
40
|
+0.6%
|
95%
|
45%
|
|
Instance
|
40
|
+1.1%
|
73%
|
37%
|
|
Database
|
40
|
+1.3%
|
55%
|
34%
|
For more details on this experiment including how we put it together and learn more about how to effectively consolidation your database workload and choose between different consolidation options, please refer to our newly published whitepaper: http://msdn.microsoft.com/en-us/library/ee819082.aspx
