sql server 2012 ha dr
DESCRIPTION
As presented to the Philadelphia SQL Server Users Group on 12/07/2012TRANSCRIPT
![Page 1: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/1.jpg)
SQL Server 2012High Availability and DRPhiladelphia SQL Server User’s Group07-December-2011
![Page 2: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/2.jpg)
About Me @jdanton on Twitter Joedantoni.wordpress.com Videos and Blogs at SSWUG.org Vice President of the Philadelphia SQL
Server UG
![Page 3: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/3.jpg)
Speaker Rate This is a new talk for me—I’d love
feedback
http://spkr8.com/s/19509
![Page 4: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/4.jpg)
Agenda SQL Server 2008 to 2012—What’s
Changed in HA and DR Geo-Clustering All about Availability Groups
![Page 5: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/5.jpg)
Learning Objectives SQL Server HA and DR What involved in SQL Clustering How it works What’s new in 2012 HA/DR
This presentation is geared towards DBAs—so feel free to stop at any time with questions
![Page 6: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/6.jpg)
Licensing (What’s changed) The Availability Group features will require
the Enterprise Edition of SQL Server The licensing model for SQL Enterprise
Edition has changed. Consult your friendly Microsoft sales representative for more details
Mirroring is listed as being deprecated from Standard Edition. Will still be there in 2012
![Page 7: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/7.jpg)
Windows Core Support No GUI version of Windows Allows for fewer patches Uses PowerShell and MMCs for support
![Page 8: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/8.jpg)
SQL Server 2012 Extended Events are used much more
heavily Slipstream Install no longer required—
SQL will check for updates from your Windows Update source Can use internet Windows Update or
internal source
![Page 9: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/9.jpg)
High Availability and DR Options in SQL 2008
SQL Server Clustering SQL Server Mirroring Peer to Peer Replication SQL Server Log Shipping*
![Page 10: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/10.jpg)
HA and DR Options in SQL Server 2012 Backup and Recovery Mirroring Availability Groups (2012) Log Shipping Replication SAN Replication* Virtualization*
![Page 11: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/11.jpg)
Clustering--2008 SQL Clustering required 1 subnet to be
used across the whole cluster
10.10.100.10 Cluster failover is controlled by
isAlive/looksAlive processes, which check the SQL service and run @@servername
![Page 12: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/12.jpg)
What’s new in SQL Server 2012 HA/DR New Term: Witness Disk
Can be physical (SAN) disk, or cluster file share
Multi-subnet clustering is supported Requires SAN replication
Flexible Failover The BIG one—Always On Availability
Groups
![Page 13: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/13.jpg)
Clustering 2012 Full support for geo-distributed clusters Flexible failover model TempDB on Non-shared Disk Resource
Makes PCI-based Solid State Drive an option
No check for this as of CTP3—instance won’t start if TempDB drive location not available
![Page 14: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/14.jpg)
Geo-Distributed Clustering Requires SAN replication ($$$$) Two of everything Requires really fast network connection Requires some trickery at the
network/DNS level for connectivity
![Page 15: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/15.jpg)
Multi-Subnet Cluster
![Page 16: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/16.jpg)
Flexible Failover Replaces looksAlive/isAlive functionality
in SQL Clusters (and is used for Availability Groups)
Now runs sp_server_diagnostics Two new parameters
HealthCheckTimeout (Default 60 sec/Minimum 15 sec)
Failover Condition Level
![Page 17: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/17.jpg)
Flexible Failover Policies for Clusters
Level Condition Description
0No automatic failover or restart
• Indicates that no failover or restart will be triggered automatically on any failure conditions.
1Failover or restart on server down
• SQL Server service is down.
2Failover or restart on server unresponsive
• SQL Server instance is not responsive (Resource DLL cannot receive data from sp_server_diagnostics within the HealthCheckTimeout settings).
3 (Default)Failover or restart on critical server errors
• System stored procedure sp_server_diagnostics returns ‘system error’. (Critical errors > 20)
4Failover or restart on moderate server errors
• System stored procedure sp_server_diagnostics returns ‘resource error’. (Moderate errors > 17)
5Failover or restart on any qualified failure conditions
• System stored procedure sp_server_diagnostics returns ‘query_processing error’. (Deadlock)
![Page 18: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/18.jpg)
Understanding Quorum There are a few slides on this topic, it’s
a little confusing In a nutshell, you cluster has to be able to
talk to itself to keep the cluster service up in running
This applies to both SQL Server Failover Cluster Instances and AlwaysOn Availability Groups
![Page 19: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/19.jpg)
Quorum Quorum is critical—contains master
copy of the cluster’s configuration Serves as a tiebreaker if network
communications between cluster nodes fail
If Quorum fails—cluster is shut down until it’s restored
![Page 20: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/20.jpg)
Quorum Models Node and Disk Majority (Default) Node Majority No Majority (Quorum Disk Only) Node and File Share Majority (Good for
Geo Clusters)
![Page 21: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/21.jpg)
Quorum Failure Tolerance
Number of Nodes 2 3 4 5 6 7
Node Majority 0 1 1 2 2 3
Node and Disk/File Share Majority
1 2 2 3 3 4
• Assuming Disk is Up Calculation is: Cluster Up = RoundUp(Total # of Nodes/2)
• Assuming Disk is Down Calculation is: ClusterUp = RoundUp (Total # of Nodes/2)-1
![Page 22: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/22.jpg)
DR in SQL 2008 Mirroring
Allowed automatic failover, but only one target
Mirror target is unreadable Log Shipping
Allowed multiple targets, but failover a manual process, requiring a connection string change
Replication
![Page 23: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/23.jpg)
AlwaysOn Availability Groups
![Page 24: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/24.jpg)
![Page 25: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/25.jpg)
AlwaysOn Requirements Windows Enterprise (Clustering is a
requirement) SQL Server Enterprise Edition Windows Cluster No shared storage is required Quorum Disk Preferred
![Page 26: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/26.jpg)
Flexible AG Failover Similar to how a failover clustered
instance fails over Connects to instance every 30 seconds
to perform health check Also, similar quorum model to Windows
Failover Clustering
![Page 27: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/27.jpg)
Allows for SAN Less HA/DR This isn’t a huge thing for SQL Server at
big shops It may allow us to incorporate a level of
DR into a virtual environment
![Page 28: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/28.jpg)
Failover Modes Automatic failover Planned manual failover (without data
loss) Forced manual failover (with possible
data loss)
![Page 29: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/29.jpg)
Failover
Asynchronous-commit mode
Synchronous-commit mode with manual-failover mode
Synchronous-commit mode with automatic-failover mode
Automatic failover No No Yes
Manual failover No Yes Yes
Forced failover Yes Yes No
![Page 30: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/30.jpg)
Client Connections in This Model Availability Group Listener (Yes, SQL
Server now has a listener) Works just like a failover clustering
instance (single instance, single IP) Creates a VCO (AD Virtual Computer
Object)
![Page 31: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/31.jpg)
Contained Databases Isolate Database from Instance
Currently only fully supported with SQL Logins
No numbered procedures Eases database movement Allows for ease of migration to Azure
Not quite baked out as of RC0
![Page 32: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/32.jpg)
Read Only Replicas Can have up to 3 SQL Client 2012 will allow for this
routing specifically Can take backups from read-only copys*
Copy Only Backups (only full copy, does not affect primary log)
Indexing must be same on replicas Bad queries can affect status of replica
![Page 33: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/33.jpg)
Considerations for Availability Groups All SQL servers (including the secondary in the DR site) in the
same Windows domain All the databases must be in FULL recovery model The unit of failover (for local HA, as well as DR) is at the AG
level, i.e., group of databases – not the instance Consider using Contained Database for containing logins for failover For jobs and other objects outside the database, simple
customization needed No delayed apply on the secondary Removing log shipping means the regular log backup job is
removed Need to re-establish periodic log backup (essential for truncating
the log) New tools for monitoring and alerting
AlwaysOn Dashboard System Center Operations Manager
![Page 34: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/34.jpg)
Availability Groups Demo
![Page 35: Sql server 2012 ha dr](https://reader036.vdocuments.us/reader036/viewer/2022062319/557d571fd8b42ad14f8b45d7/html5/thumbnails/35.jpg)
Summary Lots of Change in the HA/DR Space Licensing also changes—talk to your MS
rep SQL Server Failover Clusters still a good
HA option AlwaysOn Availability Groups add a lot
more flexibility to DR