sql clusters in virtualized environments - cloud services · sql clusters in virtualized...
TRANSCRIPT
This session’s primary objective is to help you gain insight into where
clustering fits in the overall picture of HA/DR as it relates to MS SQL Server,
what it needs to work properly, as well as what considerations should go
into virtualizing MS SQL server clusters. This primarily concentrates around
technologies contained in the Microsoft’s latest version of their hypervisor,
Hyper-V R3.
I will be posting up instructions on how to set up a lab for testing SQL
virtualization and clustering using Hyper-V
Session Overview
� SQL High Availability options
� What are clusters?
� Clustering SQL Server
� Types of SQL clusters
� Resources needed
� Multi-Instance Cluster Considerations
� Virtualizing SQL Clusters
� Benefits
� Pitfalls
� Cluster Configuration
� Backup Considerations
� Sources Used
� Questions
� Contact Information
Session Review
Types of Clusters� Majority Node Cluster
� Each Node has it’s own local storage that it uses to store
and access the data
� Data is stored across multiple disks, synchronization
ensured by the cluster service
� Single Quorum Cluster
� Requires shared storage
� Used for storage-centric applications, such as SQL,
Exchange
What are clusters?A cluster is a group of independent systems working together as a one
to provide a highly available service or application. If a member of the
group fails, the others take over the load.
� Active-Passive Single Instance cluster
� Single instance, running on one node.
� In case of failure, fails over to another node
� Active-Active Multi-Instance Cluster
� Multiple Instances running on multiple nodes
� In case of failure, fails over to another node
� NOT A LOAD BALANCING
SOLUTION!!!
Clustering SQL ServersTypes of SQL Clusters
Clustering SQL Server Resource Needed
� Networking
� 3 networks
� Data
� iSCSI
� Cluster traffic (heartbeat)
� Storage
� Create separate LUNs for SQL
� Use appropriate RAID levels for your
configuration
� If using virtual storage, conduct I/O tests to
ensure proper data transfer rates
Clustering SQL ServersResource provisioning – Multi-Instance Clusters
� Memory
Ensure that sufficient resources are in place
to tolerate failure of at least 1 node
� Each Server must have sufficient resources to run
all clustered instances
� Configure maximum memory usage at the
instance level to ensure performance after
failover
� Ports
Change the port on the second instance to
ensure both can coexist on the same server
Virtualizing SQL Clusters Benefits
� Business Continuity through vMotion/Live Migration
� Easier addition of resources
� Can eliminate storage as a single point of failure
� Eliminates expressed need for a SAN
� Designed for consolidation, not performance
� 10% performance hit
� Memory Overcommit/Dynamic Memory
� Virtual Disks
Virtualizing SQL Clusters Pitfalls
Virtualizing SQL Clusters I/O Performance Virtual Disk vs. Pass-Through Disk
Virtual Disk Pass-Through Disk
IO/S Latency (MS) IO/S Latency (ms)
Min Max Average Min Max Average
Random Reads 1545.04 124 6322 2623 11511.74 25 1167 354
Random Writes 1173.88 4 6616 3469 10149.12 31 1715 402
Sequential Reads 1631.54 120 5278 2496 10926.07 27 1114 373
Sequential Writes 1157.9 125 6766 3519 13376.58 18 1033 305
Virtualizing SQL Clusters Cluster Configuration
� Requires 3 virtual networks
� Data
� Storage
� Cluster Heartbeat
� Cluster VM’s preferred host servers should be set to separate hosts
� Avoid contention
� Avoid using thin provisioning/dynamic disks to avoid first write penalty
� Using memory overcommit/dynamic memory IS NOT RECOMMENDED
� If using memory overcommit/dynamic memory, set the minimum amount of
memory to the minimum needed for the SQL server, and increase the load
preference be the highest of all servers
� Test storage to ensure proper response
� SQLIO - http://www.microsoft.com/en-us/download/details.aspx?id=20163
� IOMETER - http://www.iometer.org/doc/downloads.html
Virtualizing SQL Clusters Backup Considerations
� VM Backup software issue
� Cannot read cluster volumes on the guests
� This means that backup software designed to backup the guests will not backup the SQL data
� Backup Exec Hyper-V agents
� VEEAM
� Use SQL to backup the database and logs to flat files
� Back up to a Network Share
� Ensure that normal SQL backup best practices are followed
� TEST YOUR BACKUPS!!!
� User your regular Hyper-V VSS aware backup software
� This will transfer your backup files to your favorite backup storage media
� TEST YOUR BACKUPS!!!
� Installing a SQL Server 2008 R2 Failover Cluster – TechNet– Microsoft’s official guide on installing a SQL server cluster
� http://www.mssqltips.com – excellent resource for day to day SQL server questions
� http://www.sqlha.com – If you are considering SQL high availability solution, check out this site first.
� http://blogs.msdn.com/b/momalek/archive/2012/04/18/creating-an-active-active-sql-cluster-using-hyper-v-part3-the-active-active-configuration.aspx - A walkthrough on configuring a multi-instance cluster in Hyper-V R3
Sources used/further reading
Contact Information
Thank you!
If you have more questions, I can be reached at:
Email – [email protected]
Phone – 508-983-1962
Linked In - www.linkedin.com/pub/ilya-rubinshteyn/4/7b3/746/