Transcript DAT303: Architecting and Using Microsoft SQL Server Availability

[email protected]
“What types of workloads have you deployed virtualization technology for 2006 vs. 2008”
79%
Test and Development
74%
74%
Production Application Servers
64%
51%
Disaster Recovery Systems
29%
50%
Production Databases
30%
47%
Data/Storage Mgmt Systems
21%
47%
Production Web Servers
47%
45%
End-User Desktops
5%
2008
2006
41%
Production Middleware Systems
26%
0%
10%
20%
Source: Virtualization and Management: Trends, Forecasts, and Recommendations; Enterprise Management Associates (EMA); April 2008
30%
40%
50%
60%
70%
80%
Save Costs: Improve Resource Utilization
• Reduce server sprawl, save space
• Save on power and cooling costs
• Optimize usage of current hardware resources
Enhanced Business Continuity
• Increase availability of business applications
• Delivers cost effective high availability
• Improve service levels, less downtime
Agile and Efficient Management
• Rapidly provision business applications
• Quickly test applications
• Increase administrative flexibility
“By the time we hit our fifth virtual machine on a host, we’ve
usually paid for the host. Long term, we will be able to reduce our
total data center holdings by 75 percent.”
Robert McSkinsky, Senior Systems Administrator,
Dartmouth-Hitchcock Medical Center
“We can no longer tolerate service interruptions. With
virtualization, we are creating a redundant data center in Normandy
to ensure business continuity”
Amaury Pitrou, Projects Architecture,
Desktops and Mobility Director Bouygues Constructions
“Building a physical server took almost four hours before
virtualization. Hyper-V™ has helped decrease this time to 20
minutes.”
Vito Forte, Chief Information Officer
WorleyParsons
Higher Isolation, Higher Costs
Currently a variety of consolidation strategies exist and are utilized.

Typically, as isolation goes up, density goes down and operation cost goes
up.
IT Managed
Environment
Virtual
Machines
Instances
Sales_1
Databases
MyServer
Marketing_1
Online_Sales
ERP_10
DB_1
DB_2
ERP_10
DB_3
Microsoft Confidential
Schemas
Higher Density, Lower Costs

Configuration:
Results:
• OS: Microsoft® Windows Server® 2008 R2 Hyper-V™
• Hardware:
HP DL585 (16 core) with SLAT
HP EVA 8000 storage
• Virtual Machines: 4 virtual processors and 7 GB RAM per virtual machine; Fixed size
VHD
• Increased throughput with consolidation
• Near linear scale in throughput with no CPU over-commit
• Improved performance with Windows Server 2008 R2 and SLAT
processor architecture
Throughput
(Batch requests/sec)
Virtual Instances Scalability
% CPU
80
Almost Linear Scale
No CPU over-commit
3500
CPU over-commit
70
3000
60
2500
Heavy
Load
50
2000
40
)
1500
30
Moderate
Load
1000
20
500
10
0
0
1VM
2VM
Batch req/sec
3VM
4VM
%CPU
5VM
6VM
Relative Throughput
7VM
8VM
Relative Throughput for Windows Server
2008
Low
Load
Multiple SQL Instances
Multiple Virtual Machines (VM)
Shared Windows instance
Dedicated Windows instance
Number of CPUs visible to Windows
instance
Up to 4 virtual CPUs
CPU over-commit is supported
Memory
Server Limit
Dynamic(max server memory)
Statically allocated to VM (Offline changes only)
64GB limit per VM
2 TB Limit per Host
Storage
SQL Data Files with standard storage
options
SQL Data Files using
Passthrough or Virtual Hard Disks exposed to VM
Windows System Resource
Manager(process level)
SQL Server Resource Governor
Hyper-V guest VM
SQL Server Resource Governor
50
Practical limit determined by physical resources
Clustering, Database Mirroring, Log
Shipping, Replication
Live Migration, Guest Clustering, Database Mirroring, Log Shipping, Replication
Good
Comparable with multiple instances, acceptable overhead
Isolation
CPU Resources
Resource Management
Number of instances
High Availability
Performance
Replication
Database Mirroring
Database
Scale Out
For Queries
Hot
Standby
Production
Database
Log Shipping
Failover Clustering
Log Shipping
With Restore
Delay
Warm
Standby
App/User
Error Recovery
Clustering keeps you from putting all your VM
eggs in 1 basket
Guest
Cluster
Guest
Cluster
2
1
11
Live
Migration
Host cluster
Shared Storage
Redundant
Paths to storage
iSCSI
Shared Storage
iSCSI, SAS, Fibre
22
VM’s move from server to server
SAN
Planned Downtime
SAN
Unplanned Downtime
SAN
any
SAN
VM
Mobility
• If you need to patch the host OS you can move the
VM to another node
Health
monitoring
• If the VM crashes or becomes unresponsive the
VM health monitoring will detect and recover
• If the host has a failure, VMs will automatically be
started on other nodes
Apps move from VM to VM
iSCSI
iSCSI
iSCSI
App
Mobility
• If you need to patch the Guest OS you can move the workload to
another VM
• Host clustering does not enable patching the guest OS but live
migration with host cluster allows no downtime when doing
maintenance on the host.
App health
monitoring
• If the app crashes or becomes unresponsive the app health
monitoring will detect and recover
• Host clustering cannot monitor the health state of apps running
inside of VM’s
• Systems Center OpsMgr may be an alternative
Host Clustering
• VM’s move from server to server
• Zero downtime to move a VM
• Works with any application or guest OS
Guest Clustering
• Apps move from VM to VM
• Downtime when moving applications
• Requires “cluster aware” applications running on Windows Server
• Requires double the resources – 2 VM’s for single workload
Fault
Host Cluster
Guest Cluster
Host hardware failure
P
P
Parent partition failure
P
P
P
P
P
P
P
VM failure
Guest OS failure
Application Failure
Storage
Host Cluster
Fibre Chanel (FC)
P
Serial Attached SCSI (SAS)
P
P
iSCSI
File (SMB / NFS)
Guest Cluster
P
Best of both worlds
Where to configure:
Effect:
http://technet.microsoft.com/en-us/library/dd197562(WS.10).aspx
Where to configure:
Effect:
KB Article: 296799
Where to configure:
Effect:
Where to configure:
Effect:
iSCSI
Scenario Description:
• Help protect from data loss with SQL Server® Database Mirroring.
Automatically, failover from primary to standby using witness.
• Consolidate mirrored database servers on standby site with
virtualization
• Use mirrored databases with database snapshots for reporting
• Ensure there is enough CPU capacity at the standby site to provide
acceptable SLA upon failover
VM
SQL Server Database Mirroring
1
Reporting Server
(DB Snapshot)
2
Virtualization Benefits:
• Better server utilization on standby site due to consolidation
• Cost effective disaster recovery solution without using costly
specialized hardware
• Management efficiency based on SQL Server and System Center
management tools
Click Here For More Information
2
3
SQL Server Database Mirroring
SQL Server Consolidation Guidance
SQL Server and Hyper-V Best Practices
MSIT “Green” Case Study Using Consolidation & Virtualization
Similarities and Differences of SQL Azure and SQL Server
Hyper-V Live Migration with SQL Server Demo Video
www.microsoft.com/teched
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn
Sign up for Tech·Ed 2011 and save $500
starting June 8 – June 31st
http://northamerica.msteched.com/registration
You can also register at the
North America 2011 kiosk located at registration
Join us in Atlanta next year