SQL Server 2012 – Introducing Highly Availability using Availability Groups

Case of multi-subnet Windows Failover Cluster

What is Highly Available?

A high-availability solution masks the effects of a hardware or software failure, outage, planned/unplanned maintenance and maintains the availability of applications so that the perceived downtime for users is minimized. For example: Database Mirroring

What is Disaster Recovery?

Disaster recovery (DR) is the process, policies and procedures that are related to preparing for recovery or continuation of technology infrastructure which are vital to an organization after a natural or human-induced disaster. Disaster recovery is a subset of business continuity. For example: Database Replication

The Old Hand – Database Mirroring?

Database mirroring maintains two copies of a single database that must reside on different instances of SQL Server Database Engine (server instances). Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server), while the other server instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session.

The feature of Database Mirroring was first introduced with Microsoft SQL Server 2005.

Why not Database Mirroring?

The feature “Database Mirroring” now has been marked for deprecation which means that this feature will be removed from future releases of SQL Server.

• The mirroring database is in a constantly in a restoring state (almost real time), a database snapshot will be required to use the database for reporting requirements. The only possibility is to use Database Snapshots to get a consistent view of your database at a given point in time and refresh the Database Snapshot on a regularly basis.
• The Failover is done on the Mirroring session level, which means you can only failover 1 database at a given time. Again – if you have more than 1 database, you need different Mirroring sessions and you have to coordinate a multi-database failover at your own.
• A DBM can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database.

The new ROCKSTAR – Availability Groups

Introduced with SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set (instances where an application is using more than one database) of user database. An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together as one single unit.

What is an Availability Database?

An availability database is any user database which is online, read write enabled database which you would want to remain highly available. This database will be a part of the Availability Group.

What is an Availability Replica?

Availability replicas are components of the availability group. Each availability replica hosts a copy of the availability databases in the availability group. For a given availability group, the availability replicas must be hosted by separate instances of SQL Server residing on different nodes of a WSFC cluster. Each of these server instances must be enabled for AlwaysOn.

Every availability replica is assigned an initial role—either the primary role or the secondary role, which is inherited by the availability databases of that replica. The role of a given replica determines whether it hosts read-write databases or read-only databases.

What is an Availability Group Listener?

An availability group listener provides a set of resources that is attached to a given availability group to direct inbound client connections to the appropriate availability replica. An availability group listener is associated with a unique DNS name that serves as a virtual network name (VNN), one or more virtual IP addresses (VIPs), and a TCP port number.

What is Readable Secondary Replicas?

In case of DBM where the mirroring server remained in the continuous restoring mode. The DBA had to ensure that regular database snapshots are in place to make data available for reporting. To add more, the database snapshots can only be created in the same instance of the SQL Server as they share data pages and all of this has to be scheduled periodically!

What’s new in AG?  You can have not just one but multiple readable secondary replicas. All the availability replicas in secondary role (up to 4) can be configured to allow read only inbound connections.  Not only this, one can come up with a scalable solution even in case of one of the secondary fails using the request routing order which is defined in the due course of building an AG.

What is Request Routing?

A very specific design pattern to re-direct inbound client request to readable secondary replicas can be proposed by modifying the existing AG and specifying the request routing order.

There is a very useful DMV (Dynamic Management View)

SELECT * FROM sys.availability_read_only_routing_lists

The result of this DMV is a table. This table has information of the routing order (priority) which was defined in the process of building an AG. If you run this immediately after you create a new AG using the wizard, this table will be empty. The wizard does not support defining the routing request for each availability replica. You can modify the list by altering the AG using powershell or T-SQL.

ALTER AVAILABILITY GROUP [Your AG Name]
MODIFY REPLICA ON N'REPLICA 1' WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY))
 
	  
ALTER AVAILABILITY GROUP [Your AG Name]
MODIFY REPLICA ON N'REPLICA 1' WITH
(PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE))
 

ALTER AVAILABILITY GROUP [Your AG Name]
MODIFY REPLICA ON N'REPLICA 1' WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP End Point Goes Here'))
 
 
ALTER AVAILABILITY GROUP [Your AG Name]
MODIFY REPLICA ON
N'REPLICA 1' WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST= (' REPLICA 2','REPLICA 3')));

Here we have defined a routing request on REPLICA 1, when this replica is on primary role all the inbound read only request will go first to replica is secondary role here REPLICA 2 and if this fails, then the request is routed to REPLICA 3.

Similarly, you can define the routing partners for all other availability replicas in the then primary role to have a designated read only secondary role.

Alternatively, instead of using the DMV mentioned above, which lists the replica ID’s, you can also create a T-SQL to fetch replica names. This query will return the replica name, the routing priority to secondary replica in read only and name of the replica is secondary role.

SELECT ar.replica_server_name, 
       rl.routing_priority, 
       (SELECT ar2.replica_server_name 
        FROM   sys.availability_read_only_routing_lists rl2 
               JOIN sys.availability_replicas AS ar2 
                 ON rl2.read_only_replica_id = ar2.replica_id 
        WHERE  rl.replica_id = rl2.replica_id 
               AND rl.routing_priority = rl2.routing_priority 
               AND rl.read_only_replica_id = rl2.read_only_replica_id) AS
       'read_only_replica_server_name'
FROM   sys.availability_read_only_routing_lists rl 
       JOIN sys.availability_replicas AS ar 
         ON rl.replica_id = ar.replica_id

Now you can connect to the replica in primary role and secondary role by creating a connection string as below respectively.
Data Source=AG-Listener; Initial Catalog=AdventureWorksDW2012

Data Source=AG-Listener; Initial Catalog=AdventureWorksDW2012; ApplicationIntent=ReadOnly
Depending upon the priorities that were set for each replica the listener will direct in inbound read only request to the associated host and serve the request.

Why should IT go AlwaysOn with Availability Groups?

• With a set of user database now a part of availability groups, multiple databases can failover.
• Unlike DBM, where only 1 mirror can be assigned for 1 principal, here you can have up to 4 secondary availability replicas for every primary replica.
• Unlike DBM, where the mirroring database continues to remains in restoring mode, here the secondary replicas are active i.e. these replicas can be configured to maintain database backups and also permit read only user connections thereby off-loading the  primary replica. How is this important from business standpoint? The inventory cost is justified as the additional servers procured are just not sitting idle and waiting for their turn to be in action.

More Here…

• The Availability Groups (AG) requires a Windows Server Failover Cluster. AGs use Windows failover clustering (WSFC) as an underlying platform for the listener and to provide quorum in the event of a failover.
• AGs can be used to design more dependable (up to four secondary replicas) and offers greater return on investment for the business via readable availability replicas, but that is all about the database. What about other SQL Management Objects like logins at the instance level, SQL Server Agent jobs, or any other object that is not in the database? Using partially contained database, the issue of logins can be resolved, but for others perhaps a mechanism to transfer objects will be an additional requirement. AG’s don’t handle this.
• In the least, you will require Windows Server 2008 Enterprise Edition powering your servers to create a WSFC or Windows Server 2012 – all the editions support.
• You will be required to install SQL Server Enterprise Edition on all the nodes which will be part of the WSFC with AlwaysOn enabled on each of them.
• All the nodes (availability replicas) in the cluster must be a part of same domain.
• An availability database can belong to one and only one AG at a time.
• AG’s supports failover to replicas which are not in same subnet i.e. multi subnet failure is supported.

AG + BI

This is where things really get interesting. Yes you can create an AG for your BI deployments including reporting services and integration services. You can your BI catalogs (Reporting Services catalogs and Integration Services catalog – SSISDB) highly available using AG.

As discussed earlier, you can leverage the replicas in secondary role to enable data reporting. Not only this you can make you SSIS packages along with the IS catalog SSISDB highly available, but this requires certain amount of effort to make both the SSIS packages support failover and also restart capabilities.
Regards

Raunak Jhawar

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook