Transcript DBI316 Assumed Pre-requisites for this presentation: Basic knowledge of AlwaysOn Failover Cluster Instances (FCI) AlwaysOn Availability Groups (AG) Definition: For the purpose of.

DBI316
Assumed Pre-requisites for this presentation: Basic knowledge of
AlwaysOn Failover Cluster Instances (FCI)
AlwaysOn Availability Groups (AG)
Definition: For the purpose of this presentation
High Availability (Local HA): Availability within a data center
Disaster Recovery (DR): Availability across data centers
AlwaysOn ≠ Availability Groups
AlwaysOn = { SQL Server Failover Cluster Instances, Availability Groups }
Availability Groups ≠ Database Mirroring
SQL Server 2012 AlwaysOn
HA+DR Design Pattern
Multi-site Failover Cluster Instance
1
(FCI) for HA and DR
2 Availability Group for HA and DR
Failover Cluster Instance for local HA
3 +
Availability Group for DR
Solution Characteristics
•
1
•
•
Shared Storage solution 1
Masked by storage replication
Corresponding
Pre-SQL Server 2012 Solution
Multi-site FCI using stretch VLAN
Non-Shared Storage solution
Database Mirroring for Local HA
and
Log Shipping for DR
Combined Shared Storage and Non-Shared Storage
Failover Cluster Instance for Local HA
and
Database Mirroring for DR
Primary Site
Node 1
Active
DR Site
Windows Server Failover Cluster
Node 3
Node 2
Passive
SQL-FCI
Passive
Node 4
Passive
Storage Replication
SQL Server 2012 AlwaysOn
HA+DR Solution
Multi-site Failover Cluster Instance
1 (FCI) for HA and DR
(http://sqlcat.com/sqlcat/b/whitepaper
s/archive/2011/12/22/sql-server-2012alwayson_3a00_-multisite-failovercluster-instance.aspx)
Solution Characteristics
•
•
•
•
1
2
Shared Storage solution 1
Instance Level HA (automatic)
Instance Level DR (automatic 2)
Uses storage replication
Masked by storage replication
Consider 3rd data center
Corresponding
Pre-SQL Server 2012 Solution
Multi-site FCI using stretch VLAN
Multi-site Failover Cluster Instance
Key Elements
A single SQL Server failover cluster instance (FCI) providing HA as well as DR
spanning across multiple sites (usually multiple subnets as well)
Key components:
Storage
Storage level replication
Cluster Enabler
Provided by the storage vendor
Work with your storage vendor to get the appropriate software and best practices
Network
Multi-subnet support in SQL Server configuration and engine
Key improvement in SQL Server 2012
IP address OR dependency set within SQL Server setup
SQL Engine skips binding to any IP’s which are not online at start-up
RegisterAllProvidersIP for Network Name improves application failover time
Multi-site Failover Cluster Instance
Deployment Considerations
http://support.microsoft.com/kb/943984
Node and Disk Majority
Multi-site Failover Cluster Instance
Deployment Considerations
Availability Groups for HA and DR
Disaster Recovery
Data Center
Primary Data Center
Windows Server Failover Cluster (single WSFC crossing two data centers)
SQL Server
SQL Server
Primary
SQL Server
Secondary
Secondary
Synchronous
Asynchronous
Availability Group
SQL Server 2012 AlwaysOn
HA+DR Solution
2 Availability Group for HA and DR
Solution Characteristics
•
•
•
•
Non-Shared Storage solution
(Group of) Database Level HA (automatic)
(Group of) Database Level DR (manual 3)
DR replica can be Active Secondary
DR is manual, if HA is chosen automatic. Consider 3rd
data center, if need automatic DR.
3
Corresponding
Pre-SQL Server 2012 Solution
Database Mirroring for Local HA and
Log Shipping for DR
Availability Groups for HA and DR
Deployment Considerations
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)
Availability Groups for HA and DR
Quorum Considerations
Availability Groups for HA and DR
Quorum Considerations
http://support.microsoft.com/kb/2494036
http://msdn.microsoft.com/enus/library/hh270280.aspx#RecommendedAdjustmentstoQuorumVoting
Quorum Model and Node Votes
Node and Fileshare Majority
Use the “Node and File Share Majority” quorum model with a protected file share witness.
Disaster Recovery
Data Center
Primary Data Center
Windows Server Failover Cluster (single WSFC crossing two data centers)
SQL Server
SQL Server
Secondary
SQL Server
Secondary
Primary
Synchronous
Asynchronous
Availability Group
File Share
Note: The Fileshare Witness always has 1 vote.
Quorum Model and Node Votes
Node Majority
Add an additional voting node to the WSFC in the primary data center, and then use the “Node Majority” quorum model.
Disaster Recovery
Data Center
Primary Data Center
Windows Server Failover Cluster (single WSFC crossing two data centers)
SQL Server
SQL Server
Secondary
SQL Server
Secondary
Primary
Synchronous
Asynchronous
Availability Group
Additional Server for
Node Majority
Quorum Model
Quorum Model and Node Votes
How to set / view
Recovering from a Disaster
Migration: From DBM+LS to AG
Planning and Key Considerations
Use of 3rd Data Center
3rd Data Center
Primary Data Center
File Share
Windows Server Failover Cluster
SQL Server
Primary
Synchronous
Availability Group
Disaster Recovery
Data Center
SQL Server
Secondary
Primary Data Center
Disaster Recovery Data Center
SQL_FCI
SQL_FCI
Database
Mirroring
Principal
Database
Mirror
Database
FCI for HA + AG for DR
Primary Data Center
Disaster Recovery Data Center
Windows Server Failover Cluster
SQLFCIPRIMARY
SQLFCIDR
Availability
Group
Primary
Database(s)
SQL Server 2012 AlwaysOn
HA+DR Solution
3
Failover Cluster Instance for local HA +
Availability Group for DR
Solution Characteristics
•
•
•
•
•
Combined Shared Storage and Non-Shared Storage
Instance Level HA (automatic)
(Group of) Database Level DR (manual)
DR replica can be Active Secondary
Asymmetric storage is the key to this solution
Secondary
Database(s)
Corresponding
Pre-SQL Server 2012 Solution
Failover Cluster Instance for Local HA
and
Database Mirroring for DR
FCI for HA + AG for DR
Deployment Considerations
http://support.microsoft.com/kb/976097
http://support.microsoft.com/kb/2494036
http://support.microsoft.com/kb/2531907
Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)
FCI for HA + AG for DR
Deployment Considerations
FCI for HA + AG for DR
Quorum Considerations
FCI for HA + AG for DR
Quorum Considerations
http://support.microsoft.com/kb/2494036
http://msdn.microsoft.com/enus/library/hh270280.aspx#RecommendedAdjustmentstoQuorumVoting
Quorum Model and Node Votes
Example: Node and Fileshare Majority
Primary Data Center
Disaster Recovery Data Center
Windows Server Failover Cluster
SQLFCIDR
SQLFCIPRIMARY
Availability
Group
Primary
Database(s)
Fileshare
Note: The Fileshare Witness always has 1 vote.
Secondary
Database(s)
Quorum Model and Node Votes
How to set / view
Note: Only cluster.exe can be used to set quorum model to “Node and (asymmetric) Disk Majority” or “(asymmetric) Disk Only”
Recovering from a Disaster
Migration: From FCI+DBM to FCI+AG
Planning and Key Considerations
Summary
SQL Server 2012 AlwaysOn
HA+DR Design Pattern
1
Multi-site Failover Cluster Instance
(FCI) for HA and DR
Solution Characteristics
•
•
•
•
•
1
2
2 Availability Group for HA and DR
•
•
•
•
•
Shared Storage solution 1
Instance Level HA (automatic)
Instance Level DR (automatic 2)
Uses storage replication
Doesn’t require database to be in FULL recovery model
Corresponding
Pre-SQL Server 2012 Solution
Multi-site FCI using stretch VLAN
Masked by storage replication
Consider 3rd data center
Non-Shared Storage solution
(Group of) Database Level HA (automatic)
(Group of) Database Level DR (manual 3)
DR replica can be Active Secondary
Requires database to be in FULL recovery model
Database Mirroring for Local HA
and
Log Shipping for DR
DR is manual, if HA is chosen automatic. Consider 3rd data
center, if need automatic DR.
3
Failover Cluster Instance for local HA
3 +
Availability Group for DR
•
•
•
•
•
•
Combined Shared Storage and Non-Shared Storage
Instance Level HA (automatic)
(Group of) Database Level DR (manual)
DR replica can be Active Secondary
Requires database to be in FULL recovery model
Asymmetric storage is the key to this solution
Failover Cluster Instance for Local HA
and
Database Mirroring for DR
mva
[email protected]
www.sqlcat.com
Sanjay
Mishra
@sqlcat
http://northamerica.msteched.com
www.microsoft.com/learning
http://microsoft.com/technet
http://microsoft.com/msdn