Befriending Dragons

Transform Tech with Anti-bullying Cultures


1 Comment

Considerations for installing SQL Server on VMWare

You still need to follow the standard SQL Server best practices, security, and preparation when you install on a VMware VM. Below are some additional considerations with regards to VMware.

 

Key points:

·         You must double check that your combination of VMWare, Windows, and SQL will be supported.

o   By Microsoft: http://windowsservercatalog.com/svvp.aspx?svvppage=svvpwizard.htm, http://support.microsoft.com/?id=956893

o   By VMware: http://pubs.vmware.com/vsp40/upgrade/wwhelp/wwhimpl/common/html/wwhelp.htm#href=c_database_scenarios.html&single=true

·         Just as with a physical system you must provide a well-configured IO subsystem. Follow standard SQL Server IO best practices. Use separate VMware VMFS partitions on independent disk arrays.

·         Again, just like with a physical machine, you must use hardware with sufficient resources.

·         Do not overcommit resources to the virtual machine (VM). For example, if the physical box has 4 cores and is running 4 VMs, do not assign 2 cores to each VM as this would overcommit the cores. The same applies to memory and disk.

·         VMWare recommends using ESX Server for production virtual machines if you want the best performance.

·         ” If SQL Server’s “lock pages in memory” parameter has been set, provide set the VM’s reservations to the amount of memory in the VM. This setting can adversely interfere with ESX Server’s balloon driver. Setting reservations will stop the balloon driver from inflating into the VM’s memory space.”

·         Consider configuring “large pages” for some types of workloads.

 

References from VMware:

·         Best Practices for SQL Server http://communities.vmware.com/docs/DOC-8964

·         Performance and Scalability of Microsoft SQL Server on VMware vSphere 4 http://www.vmware.com/files/pdf/perf_vsphere_sql_scalability.pdf

·         Database Scenarios http://pubs.vmware.com/vsp40/upgrade/wwhelp/wwhimpl/common/html/wwhelp.htm#href=c_database_scenarios.html&single=true

·         VirtualCenter Database Performance for Microsoft SQL Server 2005 http://www.vmware.com/resources/techresources/1050

·         SQL Server Workload Consolidation (VMware ESX 3.5) http://www.vmware.com/pdf/SQL_Server_consolidation.pdf

·         SQL Server Performance in a WMware Infrastructure 3 Environment http://www.vmware.com/files/pdf/SQLServerWorkloads.pdf


Leave a comment

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

http://msdn.microsoft.com/en-us/architecture/dd393309.aspx

Running SQL Server 2008 in a Hyper-V Environment – Best Practices and Performance Recommendations

http://sqlcat.com/whitepapers/archive/2008/10/03/running-sql-server-2008-in-a-hyper-v-environment-best-practices-and-performance-recommendations.aspx

Planning for Consolidation with Microsoft SQL Server 2000

http://www.microsoft.com/technet/prodtechnol/sql/2000/plan/SQL2KCon.mspx

SQL Server Consolidation on the 64-Bit Platform

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx

 


2 Comments

SQL Server 2005/2008 Now Supports Guest Clustering in a Virtual Machine!

Technorati Tags: ,

Guest clustering in a Virtual Machine for SQL Server has been a hotly requested feature for quite some time, and as of today it is officially supported! See the below links from Bob Ward for the exact details. Basically the guest OS has to be Windows 2008 or higher, SQL Server has to be version 2005 or higher, everything must pass the cluster validation tests, and the virtualization software has to be supported for SQL Server (for most of us that means Hyper-V and some versions of VMWare). So test it out and tell us your implementation stories!

SQL Server Support Policy for Failover Clustering and Virtualization gets an update…

Support policy for Microsoft SQL Server products that are running in a hardware virtualization environment