Microsoft PFE TechNet Day :: SQL Server 2012 AlwaysOn by admin

Date: 27th March 2013
Topic: SQL Server 2012 AlwaysOn!
Speaker: Harminder Singh Sethi (Microsoft PFE)

Session Synopsis:
SQL Server AlwaysOn is the new comprehensive high availability and disaster recovery solution in SQL Server 2012. Using AlwaysOn, you can increase application availability and get a better return on your hardware investments through a simplified high availability (HA) deployment and management experience. SQL Server AlwaysOn provides availability at either the application database or instance level.

Topics discussed:

1)       HA & DR solutions available in SQL Server 2008 R2 & before

  1. Failover Clustering
  2. Database Mirroring
  3. Log Shipping
  4. Replication

 

 2)       Limitations with existing HA and DR solutions

  1. Solutions are fragmented
  2. Database mirroring does not allow multiple secondary’s
  3. Multiple databases cannot fail over as a group
  4. Log shipping might lose data and does not fail over automatically
  5. Passive servers are mostly running idle
  6. Offloading of reporting and maintenance tasks from the primary server is not easy
  7. SAN is a single point of failure in failover clustering
  8. Peer-to-peer transactional replication does not automatically detect a failure or automatically fail over

 

 3)       Introduction to AlwaysOn

SQL Server 2012 AlwaysOn provides flexible design choices for selecting an appropriate high availability and disaster recovery solution for your application. SQL Server AlwaysOn was developed for applications that require high uptime, need protection against failures within a data center (high availability) and adequate redundancy against data center failures (disaster recovery). http://msdn.microsoft.com/en-us/sqlserver/gg490638 provides an overview of high availability and disaster recovery solutions available in SQL Server 2012 AlwaysOn.

Through working with customers who are evaluating and deploying SQL Server 2012 AlwaysOn currently, we have seen the following design patterns emerge as end-to-end HA+DR solution:

  • Using Availability Groups (AG) for local high availability and disaster recovery solution
  • Using Failover Cluster Instance (FCI) for local high availability, and Availability Groups (AG) for disaster recovery solution
  • Using Multi-site Failover Cluster Instance (FCI) for local high availability and disaster recovery solution

 

4)       SQL Server 2012 AlwaysOn FCI enhancements

  1. Flexible failover policy
  2. Improved diagnostics (Exec SP_Server_Diagnostics)
  3. Support for TempDB on local disk
  4. Better mount-point support
  5. Reduced planned downtime
  6. SMB protocol support
  7. Multi-subnet clustering

 

5)       SQL Server 2012 AlwaysOn AG (Availability Groups) Architecture & Design

New enterprise-level solution for high availability and disaster recovery. Availability groups maximize the availability of one or more user databases.

  1. Automatic Failover via WSFC. SQL Server AlwaysOn uses WSFC to perform health detection and automatic failover to an available secondary copy of the databases. The unit of failover is a WSFC resource group.
  2. Multi-Subnet Clustering. Built on Windows Server 2008 clustering, availability groups can use geographically dispersed clusters and can span Windows Server cluster nodes that are in separate subnets.
  3. Multiple Secondaries (up to four). Availability groups can synchronize up to four secondary SQL Server instances. Each copy of the database is called a replica. You can have a total five replicas (one primary and four secondaries).
  4. Maximum 10 AG Groups with 10 Databases each per SQL Server Instance is supported

Terminologies used:

  1. Availability Replicas (primary Replica & Secondary Replicas): Every copy is called a replica
  2. Availability Group (containers): Unit of high availability
  3. Availability Group Listener: Defines the endpoint where clients can connect to the instance

Data-Synchronization Modes and Failover

  1. Synchronous-commit mode with automatic failover: No data loss
  2. Synchronous-commit mode with manual failover: No data loss
  3. Asynchronous-commit mode: High performance, Possible Data Loss

 

6)       SQL Server 2012 AlwaysOn Setup & Manageability

  1. Follow the Prerequisites,
    Restrictions, and Recommendations for AlwaysOn Availability Groups http://msdn.microsoft.com/en-sg/library/ff878487.aspx
  2. Enable AlwaysOn Availability Groups. The AlwaysOn Availability Groups feature must be enabled on every instance of SQL Server 2012 that is to participate in an availability group.  Enable and Disable AlwaysOn Availability Groups: http://msdn.microsoft.com/en-us/library/ff878259.aspx
  3. Use the New Availability Group Wizard from SQL Server Management Studio to create AlwaysOn AG http://msdn.microsoft.com/en-us/library/hh403415

 

7)       SQL Server 2012 AlwaysOn Benefits & usage of secondary replicas

Secondary Replica DB can be in below different states:

  1. Read-Only Mode: Open for Read Queries
  2. None: Offline, not accessible
  3. Read-Intent: Applications can specify the purpose of the connection through a new property of the connection string called Application Intent. This property specifies whether the connection is directed to a read-write or a read-only version of the availability-group database. The value of the property is stored in the client’s session during login. SQL Server then determines which database to connect to, according to the configuration of the availability group and the current state of the database in the secondary replica.

Other Benefits:

  1.  Backups can be offload to secondary Replicas
  2. Offloading read-only resource-intensive queries to secondary replicas
  3. Reporting can be offloaded to secondary replicas

 

For details on the above listed topics discussed during the session, follow below links:

Email, RSS Follow

Related Posts

Leave a Reply