Sunday, 4 November 2012

AlwaysOn High Availability feature in SQLServer 2012




Table of Contents

1.    Introduction

2.    AlwaysOn Availability Groups

2.1 Step-by-step instructions to configure AlwaysOn availability group

          2.1.1 How to convert this into AlwaysOn single availability group?

          2.1.2 How can we look at the created AlwaysOn availability groups?

       2.1.3 What is the Application connection String format that connects to primary of our availability group?

 2.2 How to test the Manual failover?

 2.3 How to setup for the readable secondary?

 2.4 How to Enabling backup on Secondary – Active Secondary?

3.    AlwyasOn Failover Cluster Instances

4.    What is the improvement in reduction of planned downtime?


1.   Introduction

SQL Server 2012 introduces new features called AlwaysOn which gives integrated high-availability and Disaster recovery solution. It provides data access for all the time and faster performance for less money.

Earlier versions, we have used Database Mirroring for Database level protection, Clustering for Instance level protection and Log Shipping for Disaster Recovery. Sometimes depending upon the business requirement, we use the combination of Database Mirroring for local high availability and Log-Shipping for Disaster Recovery. AlwaysOn provides single unified, integrated, flexible and efficient solution for both High-availability and Disaster Recovery [HADR].

AlwaysOn feature can be categorized into below mentioned two types.

1.    AlwaysOn Availability Groups [For Database level Protection].
2.    AlwaysOn Failover Cluster Instances [For Instance level Protection].

      2.  AlwaysOn Availability Groups

It enhances and combines database mirroring and log shipping capabilities. We can failover multiple databases as single unit.  

It can have maximum of 4 Secondary instances in which two secondary instances can be in synchronous and one can be in automatic failover pair. We can also setup both asynchronous and synchronous secondary instances. These secondary instances are active that are readable (can be used in reporting applications) and backup from secondary instances are possible.

Failover is not only in database but also application needs to be reconnected to new primary database server during failover process. It is achieved through availability group listener or virtual network names.

We can also find much improvement in configuration wizard and Dashboard which is used to identify the state of each secondary instance.

We can also setup built-in compression, encryption, automatic page repair, automatic and manual failover process.

All these setup and configuration can be done through automated power shell solution as well.

2.1 Step-by-step instructions to configure AlwaysOn availability group

Let’s assume that we have below mentioned 4 different servers in our environment.

  • SRV2 and SRV3 for local HA
  • SRV3 for Regional HA
  • SRV4 for DR

2.1.1 How to convert the above environment into AlwaysOn single availability group?

1.    Let us ensure that all these servers are part of AlwaysOn availability groups. In order to do this, we need to use Windows Server Cluster failover Manager.

2.    Go to Cluster failover Manager. Right Click and select create a cluster. And add all of these servers.

3.    After adding all of these servers, we will able to find all the added servers listed under Nodes.

4.    Go to SQLServer Configuration Manager. Right clicks the SQL Server Service and go to properties and we will see a new tab AlwaysOn High Availalbility and click on Enable AlwaysOn availability Groups and restart the Service.

5.    Repeat Step-4 for each one of the SQL Server Service.

6.    Let’s connect to SRV2 which will be acting as a Primary Server and it has 2 databases (Test1 and Test2). We are going to create availability groups for both of these databases. In order to do this, go to AlwaysOn High Availability and Right click on Availability Groups and Select New Availability Group.

7.    Specify the Availability group Name say Group1.

8.    Select the databases (Test1 and Test2).

9.    Then Specify Replicas. [Replica is the term which is used for specifying all the servers in AlwaysOn]. Click on Add Replica and each Server and click on Automatic failover and synchronous commit based on the business requirement.

10. Then Click on Listener Tab and add listener where application is connecting to our primary of our availability group. Click on Create an availability group listener and specify below things.

Listener DNS Name  : Lis1
Port                        : 1433
Network Mode          : DHCP
Subnet                              : <IP Address>

11.Then Click Next. Select Data Synchronization. This is backup\restore of the databases for each one of the secondary from primary. Click on Full and then Next.

12. Then Validation and Summary and Finish.

AlwaysOn uses data storage from its own system and it doesn’t require any shared Disk like in failover clustering.

2.1.2 How can we look at the created AlwaysOn availability groups?

1.    Go to AlwaysOn High Availability and Availability Groups and right click on SRV2 and Select show dashboard.

2.1.3 What is the Application connection String format that connects to primary of our availability group?

Provider=SQLNCLI11.1; Data Source=Lis1<Listener Name>; Integrated Security=SSPI; Initial Catalog=Test1<database Name>; Timeout=1

2.2 How to test the Manual failover?

1.    Go to AlwaysOn High Availability and Availability Groups and right click on SRV2 and Select failover.

2.    Click next and Select the new primary replica for this availability group. Let’s select SRV3 as a new replica.

3.    Connect to the new primary replica and click next.

4.    Failover process begins and application will try to reconnect to Listener for which, it needs to have retry logic setup on it.

5.    Now we can find that application has automatically connected to SRV3 which is a new primary replica after the fail over process.

2.3 How to setup for the readable secondary?

If we would like to offload some of the reporting activities to secondary instances, as primary server is getting busy due to business growth. To setup readable secondary, we would need to follow below steps.

1.    Go to AlwaysOn High Availability and Availability Groups and right click on SRV3 (It is new primary replica now) and Select properties.

2.    Change to Yes for the Readable secondary option.

3.    Then use the below query to setup the Read only routing.

-- This Script sets the routing URL on the two replicas enabled for
-- read-access.
-- It is required that the routing URL be set on each replica before
-- you create the list using those replicas.

ALTER Availability Group [Group1]
MODIFY REPLICA ON
'SRV2'
WITH
(
  SECONDARY_ROLE
      (
            READ_ONLY_ROUTING_URL='TCP://SRV2.Testmachine.com:1433'
      )
)

ALTER Availability Group [Group1]
MODIFY REPLICA ON
'SRV3'
WITH
(
  SECONDARY_ROLE
      (
            READ_ONLY_ROUTING_URL='TCP://SRV3.Testmachine.com:1433'
      )
)


ALTER Availability Group [Group1]
MODIFY REPLICA ON
'SRV4'
WITH
(
  SECONDARY_ROLE
      (
            READ_ONLY_ROUTING_URL='TCP://SRV4.Testmachine.com:1433'
      )
)


ALTER Availability Group [Group1]
MODIFY REPLICA ON
'SRV5'
WITH
(
  SECONDARY_ROLE
      (
            READ_ONLY_ROUTING_URL='TCP://SRV5.Testmachine.com:1433'
      )
)

-- Setting Routing List
-- When the primary replica is 'SRV3', then it trys connecting to 'SRV2' as      -- secondary replica.
-- If 'SRV2' is down, then it trys connecting to 'SRV4'

ALTER AVAILABILITy GROUP [Group1]
MODIFY REPLICA ON
'SRV3'
WITH
(
      PRIMARY_ROLE
      (
      -- ALLOW_CONNECTIONS = READ_WRITE,
      READ_ONLY_ROUTING_LIST = ('SRV2,SRV4')
      )
)

-- When the primary replica is 'SRV2', then it trys connecting to 'SRV3' as      -- secondary replica.
-- If 'SRV3' is down, then it trys connecting to 'SRV4'

ALTER AVAILABILITy GROUP [Group1]
MODIFY REPLICA ON
'SRV2'
WITH
(
      PRIMARY_ROLE
      (
      -- ALLOW_CONNECTIONS = READ_WRITE,
      READ_ONLY_ROUTING_LIST = ('SRV3,SRV4')
      )
)

4.    Then include the new parameter [Application Intent] in the application connectivity string to connect to read-only secondary replica.

Provider=SQLNCLI11.1; Data Source=Lis1<Listener Name>; Integrated Security=SSPI; Initial Catalog=Test1<database Name>; Timeout=1; Application Intent = ReadOnly;

5.    Now you will see that reporting application will automatically reconnect to SRV2 which is secondary replica by considering SRV3 as primary replica.

2.4 How to Enabling backup on Secondary – Active Secondary?

1.    Backup can be taken from primary and secondary replicas.

2.    We can perform copy-only full back up or log backup from the secondary replicas.

3.    Log backups which are taken on all replicas will form a single log chain. So it is recommended to keep the backups on single location.

4.    It is recommended to use Database Recovery Adviser to make database restore process as simple.


3.   AlwyasOn Failover Cluster Instances

It is an enhancement for current SQL Server failover clustering.

Major enhancements are listed below.

1.   Faster instance failover.

2.   Native support for multisite clustering.

AlwaysOn failover cluster instance work across two subnets. And it is referencing primary using the virtual network name. If failover happens from one subnet to another, application does not require doing any changes. We can reference primary irrespective of the subnet by using the virtual network name.

3.   It supports for SMB (It is a Consolidation of 26 drive letters).

4.   It also supports to have tempdb on our local drive. So no need to have expensive drives.

5.   Flexible failover policy:

                    It provides administrators control over the conditions when an automatic failover should be initiated.

In earlier versions of SQL Server failover cluster, it identifies the alive state of SQL Server by attacking through some SQL server calls (DLL). For some reason, if SQL Server is under more pressure or memory leaks, the server may not be able to answer back to this call. So cluster might assume that Service is down and will initiate the failover process. This can cause false failover at rare situation.

It is improved in SQL Server 2012 by executing sp_server_diagnostics which is always running in SQL Server which sends SQL Server health information (unresolvable deadlocks, memory issue etc.) to the cluster. As a customer\admin, we can setup the process for what error situation, it should raise the failover.

So Cluster compares each time our defined settings with the values which comes from SQL Server. If it matches then it raises failover.

All these diagnostics information is saved in diagnostics log. So we can decide for which condition, failover should occur.

4.   What is the improvement in reduction of planned downtime?

1. It supports for Windows Server core.
  -- Reduce OS Patching by as much as 50-60%.

2. It supports for rolling upgrade and patching of SQL Server for both Availability Groups and Failover Cluster Instance.

3. New Online operations supported for LOB Index and Adding of Column with default.