AlwaysOn Availability Groups provide a flexible option for achieving high availability, recovering from disasters, and scaling out read-only workloads. The technology synchronizes data at the database level, but health monitoring and quorum are provided by a Windows cluster.

This chapter demonstrates how to build and configure availability groups for both high availability (HA) and disaster recovery (DR). We also discuss aspects such as performance considerations and maintenance. We also discuss using availability groups to scale out read-only workloads.

Implementing High Availability with AlwaysOn Availability Groups

Before implementing AlwaysOn Availability Groups, we first create three databases, which we will use during the demonstrations in this chapter. Two of the databases relate to the fictional application, App1, and the third database relates to the fictional application, App2. Each contains a single table, which we populate with data. Each database is configured with Recovery mode set to FULL. This is a hard requirement for a database to use AlwaysOn Availability Groups because data is synchronized via a log stream. The script in Listing 5-1 creates these databases.

CREATE DATABASE Chapter5App1Customers ;
GO

ALTER DATABASE Chapter5App1Customers SET RECOVERY FULL ;
GO

USE Chapter5App1Customers
GO

CREATE TABLE App1Customers
(
ID                INT                PRIMARY KEY        IDENTITY,
FirstName         NVARCHAR(30),
LastName          NVARCHAR(30),
CreditCardNumber  VARBINARY(8000)
) ;
GO

--Populate the table

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE

DECLARE @Names TABLE
(
        FirstName       VARCHAR(30),
        LastName        VARCHAR(30)
) ;

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’),
                 (’Margaret’, ’Jones’)

INSERT INTO App1Customers(Firstname, LastName, CreditCardNumber)
SELECT  FirstName, LastName, CreditCardNumber FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName
                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName
                ,(SELECT CONVERT(VARBINARY(8000)
                ,(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                  FROM @Numbers
                  WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                         FROM @Numbers
                         WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c
) d ;

CREATE DATABASE Chapter5App1Sales ;
GO

ALTER DATABASE Chapter5App1Sales SET RECOVERY FULL ;
GO

USE Chapter5App1Sales
GO

CREATE TABLE dbo.Orders(
         OrderNumber          int      NOT NULL  IDENTITY(1,1) PRIMARY KEY CLUSTERED,
         OrderDate            date     NOT NULL,
         CustomerID           int      NOT NULL,
         ProductID            int      NOT NULL,
         Quantity             int      NOT NULL,
         NetAmount            money    NOT NULL,
         TaxAmount            money    NOT NULL,
         InvoiceAddressID     int      NOT NULL,
         DeliveryAddressID    int      NOT NULL,
         DeliveryDate         date     NULL,
) ;

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE

--Populate ExistingOrders with data

INSERT INTO Orders
SELECT
         (SELECT CAST(DATEADD(dd,(SELECT TOP 1 Number
                                  FROM @Numbers
                                  ORDER BY NEWID()),getdate())as DATE)),
         (SELECT TOP 1 Number -10 FROM @Numbers ORDER BY NEWID()),
         (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()),
         (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()),
        500,
        100,
         (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()),
         (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()),
         (SELECT CAST(DATEADD(dd,(SELECT TOP 1 Number - 10
          FROM @Numbers
          ORDER BY NEWID()),getdate()) as DATE))
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c ;

CREATE DATABASE Chapter5App2Customers ;
GO

ALTER DATABASE Chapter5App2Customers SET RECOVERY FULL ;
GO

USE Chapter5App2Customers
GO

CREATE TABLE App2Customers
(
ID                INT                PRIMARY KEY        IDENTITY,
FirstName         NVARCHAR(30),
LastName          NVARCHAR(30),
CreditCardNumber  VARBINARY(8000)
) ;
GO

--Populate the table

DECLARE @Numbers TABLE
(
        Number        INT
) ;

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE ;

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
) ;

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’),
                 (’Margaret’, ’Jones’)

INSERT INTO App2Customers(Firstname, LastName, CreditCardNumber)
SELECT  FirstName, LastName, CreditCardNumber FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName
                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName
                ,(SELECT CONVERT(VARBINARY(8000)
                ,(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                  FROM @Numbers
                  WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                         FROM @Numbers
                         WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c
) d ;

Configuring SQL Server

The first step in configuring AlwaysOn Availability Groups is enabling this feature on the SQL Server service. To enable the feature from the GUI, we open SQL Server Configuration Manager, drill through SQL Server Services and select Properties from the context menu of the SQL Server service. When we do this, the service properties display and we navigate to the AlwaysOn High Availability tab, shown in Figure 5-1.

On this tab, we check the Enable AlwaysOn Availability Groups box and ensure that the cluster name displayed in the Windows Failover Cluster Name box is correct. We then need to restart the SQL Server service. Because AlwaysOn Availability Groups uses stand-alone instances, which are installed locally on each cluster node, as opposed to a failover clustered instance, which spans multiple nodes, we need to repeat these steps for each stand-alone instance hosted on the cluster.

We can also use PowerShell to enable AlwaysOn Availability Groups. To do this, we use the PowerShell command in Listing 5-2. The script assumes that CLUSTERNODE1 is the name of the server and that PRIMARYREPLICA is the name of the SQL Server instance.

Enable-SqlAlwaysOn -Path SQLSERVER:SQLCLUSTERNODE1PRIMARYREPLICA

The next step is to take a full backup of all databases that will be part of the availability group. We create separate availability groups for App1 and App2, respectively, so to create an availability group for App1, we need to back up the Chapter5App1Customers and Chapter5App1Sales databases. We do this by running the script in Listing 5-3.

BACKUP DATABASE Chapter5App1Customers
TO  DISK = N’C:BackupsChapter5App1Customers.bak’
WITH NAME = N’Chapter5App1Customers-Full Database Backup’ ;
GO

BACKUP DATABASE Chapter5App1Sales
TO  DISK = N’C:BackupsChapter5App1Sales.bak’
WITH NAME = N’Chapter5App1Sales-Full Database Backup’ ;
GO

Creating the Availability Group

You can create an availability group topology in SQL Server in several ways. It can be created manually, predominantly through dialog boxes, via T-SQL, or through a wizard. The following sections explore each of these options.

Using the New Availability Group Wizard

When the backups complete successfully, we invoke the New Availability Group wizard by drilling through AlwaysOn High Availability in Object Explorer and selecting the New Availability Group wizard from the context menu of the Availability Groups folder. The Introduction page of the wizard, displayed in Figure 5-2, now displays, giving us an overview of the steps that we need to undertake.

On the Specify Name page (see Figure 5-3), we are prompted to enter a name for our availability group.

On the Select Databases page, we are prompted to select the database(s) that we wish to participate in the availability group, as illustrated in Figure 5-4. On this screen, notice that we cannot select the Chapter5App2Customers database, because we have not yet taken a full backup of the database.

The Specify Replicas page consists of four tabs. We use the first tab, Replicas, to add the secondary replicas to the topology. Checking the Synchronous Commit option causes data to be committed on the secondary replica before it is committed on the primary replica. (This is also referred to as hardening the log on the secondary before the primary.) This means that, in the event of a failover, data loss is not possible, meaning that we can meet an SLA (service level agreement) with an RPO (recovery point objective) of 0 (zero). It also means that there is a performance impediment, however. If we choose not to check the option for Synchronous Commit, then the replica operates in Asynchronous Commit mode. This means that data is committed on the primary replica before being committed on the secondary replica. This stops us from suffering a performance impediment, but it also means that, in the event of failover, the RPO is nondeterministic. Performance considerations for synchronous replicas are discussed later in this chapter.

When we check the Automatic Failover option, the Synchronous Commit option is also selected automatically if we have not already selected it. This is because automatic failover is only possible in Synchronous Commit mode. We can set the Readable Secondary drop-down to No, Yes, or Read-intent. When we set it to No, the database is not accessible on replicas that are in a secondary role. When we set it to read-intent, the Availability Group Listener is able to redirect read-only workloads to this secondary replica, but only if the application has specified Application Intent=Read-only in the connection string. Setting it to Yes enables the listener to redirect read-only traffic, regardless of whether the Application Intent parameter is present in the application’s connection string. Although we can change the value of Readable Secondary through the GUI while at the same time configuring a replica for automatic failover without error, this is simply a quirk of the wizard. In fact, the replica is not accessible, since active secondaries are not supported when configured for automatic failover. The Replicas tab is illustrated in Figure 5-5.

On the Endpoints tab of the Specify Replicas page, illustrated in Figure 5-6, we specify the port number for each endpoint. The default port is 5022, but we can specify a different port if we need to. On this tab, we also specify if data should be encrypted when it is sent between the endpoints. It is usually a good idea to check this option, and if we do, then AES (Advanced Encryption Standard) is used as the encryption algorithm.

Optionally, you can also change the name of the endpoint that is created. Because only one database mirroring endpoint is allowed per instance, however, and because the default name is fairly descriptive, there is not always a reason to change it. Some DBAs choose to rename it to include the name of the instance, since this can simplify the management of multiple servers. This is a good idea if your enterprise has many availability group clusters.

The service account each instance uses is displayed for informational purposes. It simplifies security administration if you ensure that the same service account is used by both instances. If you fail to do this, you will need to grant each instance permissions to each service account. This means that instead of reducing the security footprint of each service account by using it for one instance only, you simply push the footprint up to the SQL Server level instead of the Operating System level.

The endpoint URL specifies the URL of the endpoint that availability groups will use to communicate. The format of the URL is [Transport Protocol]://[Path]:[Port]. The transport protocol for a database mirroring endpoint is always TCP (Transmission Control Protocol). The path can either be the fully qualified domain name (FQDN) of the server, the server name on its own, or an IP address, which is unique across the network. I recommend using the FQDN of the server, because this is always guaranteed to work. It is also the default value populated. The port should match the port number that you specify for the endpoint.

On the Backup Preferences tab (see Figure 5-7), we can specify the replica on which automated backups will be taken. One of the big advantages of AlwaysOn Availability Groups is that when you use them, you can scale out maintenance tasks, such as backups, to secondary servers. Therefore, automated backups can seamlessly be directed to active secondaries. The possible options are Prefer Secondary, Secondary Only, Primary, or Any Replica. It is also possible to set priorities for each replica. When determining which replica to run the backup job against, SQL Server evaluates the backup priorities of each node and is more likely to choose the replica with the highest priority.

Although the advantages of reducing IO on the primary replica are obvious, I, somewhat controversially, recommend against scaling automated backups to secondary replicas in many cases. This is especially the case when RTO (recovery time objective) is a priority for the application because of operational supportability issues. Imagine a scenario in which backups are being taken against a secondary replica and a user calls to say that they have accidently deleted all data from a critical table. You now need to restore a copy of the database and repopulate the table. The backup files, however, sit on the secondary replica. As a result, you need to copy the backup files over to the primary replica before you can begin to restore the database (or perform the restore over the network). This instantly increases your RTO.

Also, when configured to allow backups against multiple servers, SQL Server still only maintains the backup history on the instance where the backup was taken. This means that you may be scrambling between servers, trying to retrieve all of your backup files, not knowing where each one resides. This becomes even worse if one of the servers has a complete system outage. You can find yourself in a scenario in which you have a broken log chain.

The workaround for most of the issues that I just mentioned is to use a share on a file server and configure each instance to back up to the same share. The problem with this, however, is that by setting things up in this manner, you are now sending all of your backups across the network rather than backing them up locally. This can increase the duration of your backups as well as increase network traffic.

On the Listener tab, shown in Figure 5-8, we choose if we want to create an availability group listener or if we want to defer this task until later. If we choose to create the listener, then we need to specify the listener’s name, the port that it should listen on, and the IP address(es) that it should use. We specify one address for each subnet, in multi-subnet clusters. The details provided here are used to create the client access point resource in the availability group’s cluster role. You may notice that we have specified port 1433 for the listener, although our instance is also running on port 1433. This is a valid configuration, because the listener is configured on a different IP address than the SQL Server instance. It is also not mandatory to use the same port number, but it can be beneficial, if you are implementing AlwaysOn Availability Groups on an existing instance because applications that specify the port number to connect may need fewer application changes. Remember that the server name will still be different, however, because applications will be connecting to the virtual name of the listener, as opposed to the name of the physical serverinstance. In our example, applications connect to APP1LISTENPRIMARYREPLICA instead of CLUSTERNODE1PRIMARYREPLICA. Although connections via CLUSTERNODE1 are still permitted, they do not benefit from high availability or scale our reporting.

On the Select Initial Data Synchronization screen, shown in Figure 5-9, we choose how the initial data synchronization of the replicas is performed. If you choose Full, then each database that participates in the availability group is subject to a full backup, followed by a log backup. The backup files are backed up to a share, which you specify, before they are restored to the secondary servers. After the restore is complete, data synchronization, via log stream, commences.

If you have already backed up your databases and restored them onto the secondaries, then you can select the Join Only option. This starts the data synchronization, via log stream, on the databases within the availability group. Selecting Skip Initial Data Synchronization allows you to back up and restore the databases yourself after you complete the setup.

On the Validation page, rules that may cause the setup to fail are checked, as illustrated in Figure 5-10. If any of the results come back as Failed, then you need to resolve them before you attempt to continue.

Once validation tests are complete and we move to the Summary page, we are presented with a list of the tasks that are to be carried out during the setup. As setup progresses, the results of each configuration task display on the Results page. If any errors occur on this page, be sure to investigated them, but this does not necessarily mean that the entire availability group needs to be reconfigured. For example, if the creation of the availability group listener fails because the VCO had not been prestaged in AD, then you can re-create the listener without needing to re-create the entire availability group.

As an alternative to using the New Availability Group wizard, you can perform the configuration of the availability group using the New Availability Group dialog box, followed by the Add Listener dialog box. This method of creating an availability group is examined later in this chapter.

Scripting the Availability Group

We can also script the activity by using the script in Listing 5-4. This script connects to both instances within the cluster, meaning that it can only be run in SQLCMD mode. First, the script creates a login for the service account on each instance. It then creates the TCP endpoint, assigns the connect permission to the service account, and starts the health trace for AlwaysOn Availability Groups (which we discuss later in this chapter). The script then creates the availability group on the primary and joins the secondary to the group. Next, we perform a full and log backup and a restore of each database that will participate in the availability group before we add the databases to the group. Note that the databases are backed up, restored, and added to the group in a serial manner. If you have many databases, then you may want to parallelize this process.

--Create Logins for the Service Account,
--create Endpoints and assign Service Account permissions
--to the Endpoint on Primary Replica

:Connect CLUSTERNODE1PRIMARYREPLICA

USE master
GO

CREATE LOGIN [prosqladminclusteradmin] FROM WINDOWS ;
GO

CREATE ENDPOINT [Hadr_endpoint]
        AS TCP (LISTENER_PORT = 5022)
        FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) ;
GO

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED ;
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [prosqladminclusteradmin] ;
GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE=START;
END
GO

--Create Logins for the Service Account,
--create Endpoints and assign Service Account permissions
--to the Endpoint on Secondary Replica

:Connect CLUSTERNODE2SYNCHA

USE master
GO

CREATE LOGIN [prosqladminClusterAdmin] FROM WINDOWS ;
GO

CREATE ENDPOINT [Hadr_endpoint]
        AS TCP (LISTENER_PORT = 5022)
        FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) ;
GO

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED ;
GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [prosqladminClusterAdmin] ;
GO

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE=START;
END
GO

--Create Avaiability Group

:Connect CLUSTERNODE1PRIMARYREPLICA

USE master
GO

CREATE AVAILABILITY GROUP App1
WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY)
FOR DATABASE Chapter5App1Customers, Chapter5App1Sales
REPLICA ON N’CLUSTERNODE1PRIMARYREPLICA’
WITH (ENDPOINT_URL = N’TCP://ClusterNode1.PROSQLADMIN.COM:5022’,
FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50,
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
        N’CLUSTERNODE2SYNCHA’
          WITH (ENDPOINT_URL = N’TCP://ClusterNode2.PROSQLADMIN.COM:5022’,
          FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
          BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

--Create the Listener (Use an IP Address applicable to your environment)

ALTER AVAILABILITY GROUP App1
ADD LISTENER N’App1Listen’ (
WITH IP
((N’192.168.0.4’, N’255.255.255.0’)
)
, PORT=1433);
GO

--Join the Secondary Replica

:Connect CLUSTERNODE2SYNCHA

ALTER AVAILABILITY GROUP App1 JOIN;
GO

--Back Up Database and Log (First database)

:Connect CLUSTERNODE1PRIMARYREPLICA

BACKUP DATABASE Chapter5App1Customers
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG Chapter5App1Customers
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.trn’
WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

--Restore Database and Log (First database)

:Connect CLUSTERNODE2SYNCHA

RESTORE DATABASE Chapter5App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.bak’
WITH  NORECOVERY,  STATS = 5 ;
GO

RESTORE LOG Chapter5App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.trn’
WITH  NORECOVERY, STATS = 5 ;
GO

--Wait for replica to start communicating

DECLARE @connection BIT

DECLARE @replica_id UNIQUEIDENTIFIER
DECLARE @group_id UNIQUEIDENTIFIER

SET @connection = 0

WHILE @Connection = 0
BEGIN
        SET @group_id = (SELECT group_id
                         FROM Master.sys.availability_groups
                         WHERE name = N’App1’)
        SET @replica_id = (SELECT replica_id
                           FROM Master.sys.availability_replicas
                           WHERE UPPER(replica_server_name COLLATE Latin1_General_CI_AS) =
                                   UPPER(@@SERVERNAME COLLATE Latin1_General_CI_AS)
                           AND group_id = @group_id)

        SET @connection = ISNULL((SELECT connected_state
                                  FROM Master.sys.dm_hadr_availability_replica_states
                                  WHERE replica_id = @replica_id), 1)

        WAITFOR DELAY ’00:00:10’
END

--Add first Database to the Availability Group

ALTER DATABASE Chapter5App1Customers SET HADR AVAILABILITY GROUP = App1;

GO

--Back Up Database and Log (Second database)

:Connect CLUSTERNODE1PRIMARYREPLICA

BACKUP DATABASE Chapter5App1Sales
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG Chapter5App1Sales
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.trn’
WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

--Restore Database and Log (Second database)

:Connect CLUSTERNODE2SYNCHA

RESTORE DATABASE Chapter5App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.bak’
WITH  NORECOVERY, STATS = 5 ;
GO

RESTORE LOG Chapter5App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.trn’
WITH  NORECOVERY,  STATS = 5 ;
GO

--Wait for replica to start communicating
DECLARE @connection BIT

DECLARE @replica_id UNIQUEIDENTIFIER
DECLARE @group_id UNIQUEIDENTIFIER

SET @connection = 0

WHILE @Connection = 0
BEGIN
        SET @group_id = (SELECT group_id
                         FROM Master.sys.availability_groups
                         WHERE name = N’App1’)
        SET @replica_id = (SELECT replica_id
                           FROM Master.sys.availability_replicas
                           WHERE UPPER(replica_server_name COLLATE Latin1_General_CI_AS) =
                                     UPPER(@@SERVERNAME COLLATE Latin1_General_CI_AS)
                                 AND group_id = @group_id)

        SET @connection = ISNULL((SELECT connected_state
                                  FROM Master.sys.dm_hadr_availability_replica_states
                                  WHERE replica_id = @replica_id), 1)

        WAITFOR DELAY ’00:00:10’
END

--Add Second database to the Availaility Group

ALTER DATABASE Chapter5App1Sales SET HADR AVAILABILITY GROUP = App1;
GO

Creating the availability group via T-SQL gives you the most flexibility in terms of configuration. Table 5-1 contains a complete list of arguments, along with their explanation.

The FAILOVER_CONDITION_LEVEL argument determines the group’s sensitivity to failover. Table 5-2 provides a description of each of the five levels.

Using the New Availability Group Dialog Box

Now that we have successfully created our first availability group, let’s create a second availability group for App2. This time, we use the New Availability Group and Add Listener Dialog boxes. We begin this process by backing up the Chapter5App2Customers database. Just like when we created the App1 availability group, the databases are not selectable until we perform the backup. Unlike when we used the wizard, however, we have no way to make SQL Server perform the initial database synchronization for us. Therefore, we back up the database to the share that we created during the previous demonstration and then restore the backup, along with a transaction log backup, to the secondary instance. We do this by using the script in Listing 5-5, which must be run in SQLCMD mode for it to work. This is because it connects to both instances.

--Back Up Database and Log

:Connect CLUSTERNODE1PRIMARYREPLICA

BACKUP DATABASE Chapter5App2Customers TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App2Customers.bak’ WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG Chapter5App2Customers TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App2Customers.trn’ WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

--Restore Database and Log

:Connect CLUSTERNODE2SYNCHA

RESTORE DATABASE Chapter5App2Customers FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App2Customers.bak’ WITH  NORECOVERY, STATS = 5 ;
GO

RESTORE LOG Chapter5App2Customers FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App2Customers.trn’ WITH  NORECOVERY,  STATS = 5 ;
GO

If we had not already created an availability group, then our next job would be to create a TCP endpoint so the instances could communicate. We would then need to create a login for the service account on each instance and grant it the connect permissions on the endpoints. Because we can only ever have one database mirroring endpoint per instance, however, we are not required to create a new one, and obviously we have no reason to grant the service account additional privileges. Therefore, we continue by creating the availability group. To do this, we drill through AlwaysOn High Availability in Object Explorer and select New Availability Group from the context menu of availability groups.

This causes the General tab of the New Availability Group dialog box to display, as illustrated in Figure 5-11. On this screen, we type the name of the availability group in the first field. Then we click the Add button under the Availability Databases window before we type the name of the database that we wish to add to the group. We then need to click the Add button under the Availability Replicas window before we type the serverinstance name of the secondary replica in the new row.

Now we can begin to set the replica properties. We discussed the Role, Availability Mode, Failover Mode, Readable Secondary, and Endpoint URL properties when we created the App1 availability group. The Connection In Primary Role property defines what connections can be made to the replica if the replica is in the primary role. You can configure this as either Allow All Connections, or allow Read/Write connections. When Read/Write is specified, applications using the Application Intent = Read only parameter in their connection string will not be able to connect to the replica.

The Session Timeout property sets how long the replicas can go without receiving a ping from one another before they enter the DISCONNECTED state and the session ends. Although it is possible to set this value to as low as 5 seconds, it is usually a good idea to keep the setting at or above 10 seconds, otherwise you run the risk of a false positive response, resulting in unnecessary failover. If a replica times out, it needs to be resynchronized, since transactions on the primary will no longer wait for the secondary, even if the secondary is running in Synchronous Commit mode.

On the Backup Preferences tab of the dialog box, we define the preferred replica to use for automated backup jobs, as shown in Figure 5-12. Just like when using the wizard, we can specify Primary, or we can choose between enforcing and preferring backups to occur on a secondary replica. We can also configure a weight, between 0 and 100 for each replica, and use the Exclude Replica check box to avoid backups being taken on a specific node.

Once we have created the availability group, we need to create the availability group listener. To do this, we select New Listener from the context menu of the App2 availability group, which should now be visible in Object Explorer. This invokes the New Availability Group Listener dialog box, which can be seen in Figure 5-13.

In this dialog box, we start by entering the virtual name for the listener. We then define the port that it will listen on and the IP address that will be assigned to it. We are able to use the same port for both of the listeners, as well as the SQL Server instance, because all three use different IP addresses.

Performance Considerations for Synchronous Commit Mode

Unlike traditional clustering, Availability Group topology does not have any shared disk resources. Therefore, data must be replicated on two devices, which of course, has an overhead. This overhead varies depending on various aspects of your environment, such as network latency and disk performance, as well as the application profile. However, the script in Listing 5-6 runs some write-intensive tests against the Chapter5App2Customers database (which is in Asynchronous Commit mode) and then against the Chapter5App1Customers database (which is Synchronous Commit mode). This indicates the overhead that you can expect to witness.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SET STATISTICS TIME ON

PRINT ’Begin asynchronous commit benchmark’

USE Chapter5App2Customers
GO

PRINT ’Build a nonclustered index’

CREATE NONCLUSTERED INDEX NIX_FirstName_LastName ON App2Customers(FirstName, LastName) ;

PRINT ’Delete from table’

DELETE FROM [dbo].[App2Customers] ;

PRINT ’Insert into table’

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE ;

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
) ;

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’),
                 (’Margaret’, ’Jones’)

INSERT INTO App2Customers(Firstname, LastName, CreditCardNumber)
SELECT  FirstName, LastName, CreditCardNumber FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName
                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName
                ,(SELECT CONVERT(VARBINARY(8000)
                  , (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                     FROM @Numbers
                     WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                         FROM @Numbers
                    WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c
) d ;
GO

PRINT ’Begin synchronous commit benchmark’

USE Chapter5App1Customers
GO

PRINT ’Build a nonclustered index’

CREATE NONCLUSTERED INDEX NIX_FirstName_LastName ON App1Customers(FirstName, LastName) ;

PRINT ’Delete from table’

DELETE FROM dbo.App1Customers ;

PRINT ’Insert into table’

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE ;

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
) ;

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’),
                 (’Margaret’, ’Jones’) ;

INSERT INTO App1Customers(Firstname, LastName, CreditCardNumber)
SELECT  FirstName, LastName, CreditCardNumber FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName
                ,(SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName
                ,(SELECT CONVERT(VARBINARY(8000)
                ,(SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                  FROM @Numbers
                  WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                          FROM @Numbers
                          WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()) + ’-’ +
                         (SELECT TOP 1 CAST(Number * 100 AS CHAR(4))
                         FROM @Numbers
                         WHERE Number BETWEEN 10 AND 99 ORDER BY NEWID()))) CreditCardNumber
FROM @Numbers a
CROSS JOIN @Numbers b
CROSS JOIN @Numbers c
) d ;

GO

SET STATISTICS TIME OFF

GO

The relevant parts of the results of this query are displayed in Listing 5-7. You can see that the index rebuild was almost three times slower when the Availability Group was operating in Synchronous Commit mode, the insert was over six times slower, and the delete was also marginally slower. In productions environments in which I have implemented availability groups in Synchronous Commit mode, they have generally been around two times slower than in Asynchronous Commit mode.

Begin asynchronous commit benchmark

Build a nonclustered index
 SQL Server Execution Times:
   CPU time = 1109 ms,  elapsed time = 4316 ms.

Delete from table
 SQL Server Execution Times:
   CPU time = 6938 ms,  elapsed time = 69652 ms.
(1000000 row(s) affected)

Insert into table
 SQL Server Execution Times:
   CPU time = 13656 ms,  elapsed time = 61372 ms.
(1000000 row(s) affected)

Begin synchronous commit benchmark

Build a nonclustered index
 SQL Server Execution Times:
   CPU time = 1516 ms,  elapsed time = 12437 ms.

Delete from table
 SQL Server Execution Times:
   CPU time = 8563 ms,  elapsed time = 77273 ms.
(1000000 row(s) affected)

Insert into table
 SQL Server Execution Times:
   CPU time = 23141 ms,  elapsed time = 372161 ms.
(1000000 row(s) affected)

Because of the performance challenges associated with Synchronous Commit mode, many DBAs decide to implement high availability and disaster recovery by using a three-node cluster, with two nodes in the primary data center and one node in the DR data center. Instead of having two synchronous replicas within the primary data center, however, they stretch the primary replica across a failover clustered instance and configure the cluster to only be able to host the instance on these two nodes, and not on the third node in the DR data center. This is important, because it means that we don’t need to implement SAN replication between the data centers. The DR node is synchronized using availability groups in Asynchronous Commit mode. If you combine an AlwaysOn failover clustered instance with AlwaysOn Availability Groups in this way, then automatic failover is not supported between the clustered instance and the replica. It can only be configured for manual failover. There is also no need for availability groups to fail over between the two nodes hosting the clustered instance, because this failover is managed by the cluster service. This configuration can prove to be a highly powerful and flexible way to achieve your continuity requirements.

Implementing Disaster Recovery with Availability Group

Now that we have successfully implemented high availability for the Chapter5App1Customers and Chapter5App1Sales databases through the App1 availability group, we need to implement disaster recovery for these databases. To do this, we first need to build out a new server in our second site and install a stand-alone instance of SQL Server. Because the cluster now spans two sites, we need to reconfigure it as a multi-subnet cluster. We also need to reconfigure the quorum model to remove its dependency on the shared storage, which we currently have for the quorum. Once this is complete, we are able to add the instance on the new node to our availability group. The following sections assume that you have already built out a third server with a SQL Server instance called CLUSTERNODE3ASYNCDR, and they demonstrate how to reconfigure the cluster as well as the availability group.

Configuring the Cluster

We need to perform several cluster configuration steps before we begin to alter our availability group. These include adding the new node, reconfiguring the quorum, and adding a new IP to the cluster’s client access point.

Adding a Node

The first task in adding DR capability to our availability group is to add the third node to the cluster. To do this, we select Add Node from the context menu of nodes in Failover Cluster Manager. This causes the Add Node Wizard to be invoked. After passing through the Before You Begin page of this wizard, you are presented with the Select Servers page, which is illustrated in Figure 5-14. On this page, you need to enter the server name of the node that you plan to add to the cluster.

On the Validation Warning page, you are invited to run the Cluster Validation Wizard. You should always run this wizard in a production environment when making changes of this nature; otherwise, you will not be able to receive support from Microsoft for the cluster. Details of running the Cluster Validation wizard can be found in Chapter 3. Running the Cluster Validation wizard in our scenario is likely to throw up some warnings, which are detailed in Table 5-3.

On the Confirmation page, we are given a summary of the tasks that will be performed. On this page, we deselect the option to add eligible storage, since one of our aims is to remove the dependency on shared storage. The Confirmation page is displayed in Figure 5-15.

On the Configure The Cluster page, the progress on the tasks displays until it is complete. The Summary page then displays, giving an overview of the actions and their success.

Modifying the Quorum

Our next step in configuring the cluster will be to modify the quorum. As mentioned earlier, we would like to remove our current dependency on shared storage. Therefore we need to make a choice. Since we now have three nodes in the cluster, one possibility is to remove the disk witness and form a node majority quorum. The issue with this is that one of our nodes is in a different location. Therefore, if we lose network connectivity between the two sites for an extended period, then we have no fault tolerance in our primary site. If one of the nodes goes down, we lose quorum and the cluster goes offline. On the other hand, if we have an additional witness in the primary location, then we are not maintaining best practice, since there are an even number of votes. Again, if we lose one voting member, we lose resilience.

Therefore, the approach that we take is to replace the disk witness with a file share witness, thus removing the shared disk dependency. We then remove the vote from the node in the DR site. This means that we have three voting members of the quorum, and all of them are within the same site. This mitigates the risk of an inter-site network issue causing loss of redundancy in our HA solution.

In order to invoke the Configure Cluster Quorum Wizard, we select Configure Cluster Quorum from the More Actions submenu within the context menu of our cluster in Failover Cluster Manager. After moving through the Before You Begin page of this wizard, you are asked to select the configuration that you wish to make on the Select Quorum Configuration Option page, which is shown in Figure 5-16. We select the Advanced Quorum Configuration option.

On the Select Voting Configuration page, we choose to select nodes and remove the vote from CLUSTERNODE3. This is demonstrated in Figure 5-17.

On the Select Quorum Witness page, we choose the Configure A File Share Witness option, as shown in Figure 5-18.

On the Configure File Share Witness page, which is illustrated in Figure 5-19, we enter the UNC of the share that we will use for the quorum. This file share must reside outside of the cluster and must be an SMB file share on a machine running Windows Server.

On the Confirmation page of the wizard, you are given a summary of the configuration changes that will be made, as shown in Figure 5-20.

On the Configure Cluster Quorum Settings page, a progress bar displays. Once the configuration is complete, the Summary page appears. This page provides a summary of the configuration changes and a link to the report.

Adding an IP Address

Our next task is to add a second IP address to the cluster’s client access point. We do not add an extra IP address for the Availability Group Listener yet. We perform this task in the “Configuring the Availability Group” section later in this chapter.

In order to add the second IP address, we select Properties from the context menu of Server Name in the Core Cluster Resources window of Failover Cluster Manager. On the General tab of the Cluster Properties dialog box, we add the IP address for administrative clients, following failover to DR, as illustrated in Figure 5-21.

When we apply the change, we receive a warning saying that administrative clients will temporarily be disconnected from the cluster. This does not include any clients connected to our availability group. If we choose to proceed, we then navigate to the Dependencies tab of the dialog box and ensure that an OR dependency has been created between our two IP addresses, as shown in Figure 5-22.

After this process is complete, the second IP address resource in the Cluster Core Resources group shows up as offline. This is normal. In the event of failover to the server in the second subnet, this IP address comes online, and the IP address of the subnet in the primary site goes offline. This is why the OR dependency (as opposed to an AND dependency) is critical. Without it, the Server Name resource could never be online.

Configuring the Availability Group

To configure the availability group, we first have to add the new node as a replica and configure its properties. We then add a new IP address to our listener for the second subnet. Finally, we look at improving the connection times for clients.

Adding and Configuring a Replica

In SQL Server Management Studio (SSMS), on the primary replica, we drill through Availability Groups | App1 and select Add Replica from the context menu of the Availability Replicas node. This causes the Add Replica To Availability Group wizard to be displayed. After passing through the Introduction page of the wizard, you see the Connect To Replicas page, as displayed in Figure 5-23. On this page, you are invited to connect to the other replicas in the availability group.

On the Replicas tab of the Specify Replicas page, shown in Figure 5-24, we first use the Add Replica button to connect to the DR instance. After we have connected to the new replica, we specify the properties for that replica. In this case, we leave them as-is because it will be a DR replica. Therefore, we want it to be asynchronous and we do not want it to be readable.

On the Endpoints tab, shown in Figure 5-25, we ensure that the default settings are correct and acceptable.

On the Backup Preferences tab, the option for specifying the preferred backup replica is read-only. We are, however, able to specifically exclude our new replica as a candidate for backups or change its backup priority. This page is displayed in Figure 5-26.

On the Listener tab, illustrated in Figure 5-27, we can decide if we will create a new listener. This is a strange option, since SQL Server only allows us to create a single listener for an availability group, and we already have one. Therefore, we leave the default choice of Do Not Create An Availability Group Listener selected. It is possible to create a second listener, directly from Failover Cluster Manager, but you would only want a second listener for the same availability group in very rare, special cases, which we discuss later in this chapter.

On the Select Data Synchronization page, we choose how we want to perform the initial synchronization of the replica. The options are the same as they were when we created the availability group, except that the file share will be prepopulated, assuming that we choose the Full synchronization when creating the availability group. This screen is shown in Figure 5-28.

On the Validation page, which is illustrated in Figure 5-29, we should review any warnings or errors and resolve them before continuing.

On the Summary page, displayed in Figure 5-30, we are presented with a summary of the configurations that will be carried out.

After the reconfiguration completes, our new replica is added to the cluster. We should then review the results and respond to any warnings or errors. We could also have used T-SQL to add the replica to the availability group. The script in Listing 5-8 performs the same actions just demonstrated. You must run this script in SQLCMD Mode since it connects to multiple instances.

:Connect CLUSTERNODE3ASYNCDR

--Create Login for Service Account

USE master
GO

CREATE LOGIN [prosqladminClusterAdmin] FROM WINDOWS ;
GO

--Create the Endpoint

CREATE ENDPOINT Hadr_endpoint
        AS TCP (LISTENER_PORT = 5022)
        FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES) ;
GO

ALTER ENDPOINT Hadr_endpoint STATE = STARTED ;
GO

--Grant the Service Account permissions to the Endpoint

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [prosqladminClusterAdmin] ;
GO

--Start the AOAG Health Trace

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=’AlwaysOn_health’)
BEGIN
  ALTER EVENT SESSION AlwaysOn_health ON SERVER STATE=START;
END
GO

:Connect CLUSTERNODE1PRIMARYREPLICA

USE master
GO

--Add the replica to the Avaialability Group

ALTER AVAILABILITY GROUP App1
ADD REPLICA ON N’CLUSTERNODE3ASYNCDR’
WITH (ENDPOINT_URL = N’TCP://CLUSTERNODE3.PROSQLADMIN.COM:5022’,
      FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50,
      SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

--Back up and restore the first database and log

BACKUP DATABASE Chapter5App1Customers TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG Chapter5App1Customers
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.trn’
WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

:Connect CLUSTERNODE3ASYNCDR

ALTER AVAILABILITY GROUP App1 JOIN;
GO

RESTORE DATABASE Chapter5App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.bak’
WITH  NORECOVERY,  STATS = 5 ;
GO

RESTORE LOG Chapter5App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Customers.trn’
WITH  NORECOVERY, STATS = 5 ;
GO

-- Wait for the replica to start communicating
DECLARE @connection BIT

DECLARE @replica_id UNIQUEIDENTIFIER
DECLARE @group_id UNIQUEIDENTIFIER

SET @connection = 0

WHILE @Connection = 0
BEGIN
        SET @group_id = (SELECT group_id
                         FROM Master.sys.availability_groups
                         WHERE name = N’App1’)
        SET @replica_id = (SELECT replica_id
                           FROM Master.sys.availability_replicas
                           WHERE UPPER(replica_server_name COLLATE Latin1_General_CI_AS) =
                                   UPPER(@@SERVERNAME COLLATE Latin1_General_CI_AS)
                               AND group_id = @group_id)

        SET @connection = ISNULL((SELECT connected_state
                                  FROM Master.sys.dm_hadr_availability_replica_states
                                  WHERE replica_id = @replica_id), 1)

        WAITFOR DELAY ’00:00:10’
END

--Add the first Database to the Availability Group on the new replica

ALTER DATABASE Chapter5App1Customers SET HADR AVAILABILITY GROUP = [App1];
GO

--Back up and restore the second database and log

:Connect CLUSTERNODE1PRIMARYREPLICA

BACKUP DATABASE Chapter5App1Sales
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG Chapter5App1Sales
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.trn’
WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

:Connect CLUSTERNODE3ASYNCDR

ALTER AVAILABILITY GROUP [App1] JOIN;
GO

RESTORE DATABASE Chapter5App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.bak’
WITH  NORECOVERY,  STATS = 5 ;
GO

RESTORE LOG Chapter5App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter5App1Sales.trn’
WITH  NORECOVERY, STATS = 5 ;
GO

-- Wait for the replica to start communicating
DECLARE @connection BIT

DECLARE @replica_id UNIQUEIDENTIFIER
DECLARE @group_id UNIQUEIDENTIFIER

SET @connection = 0

WHILE @Connection = 0
BEGIN
        SET @group_id = (SELECT group_id
                         FROM Master.sys.availability_groups
                         WHERE name = N’App1’)
        SET @replica_id = (SELECT replica_id
                           FROM Master.sys.availability_replicas
                           WHERE UPPER(replica_server_name COLLATE Latin1_General_CI_AS) =
                                   UPPER(@@SERVERNAME COLLATE Latin1_General_CI_AS)
                               AND group_id = @group_id)

        SET @connection = ISNULL((SELECT connected_state
                                  FROM Master.sys.dm_hadr_availability_replica_states
                                  WHERE replica_id = @replica_id), 1)

        WAITFOR DELAY ’00:00:10’
END

--Add the second database to the Avaiability Group on the new replica

ALTER DATABASE Chapter5App1Sales SET HADR AVAILABILITY GROUP = App1;
GO

Add an IP Address

Even though the replica has been added to the availability group and we are able to fail over to this replica, our clients are still not able to connect to it in the DR site using the Availability Group Listener. This is because we need to add an IP address resource, which resides in the second subnet. To do this, we can select Properties from the context menu of App1Listen in Object Explorer, which causes the Availability Group Listener Properties dialog box to be displayed, as in Figure 5-31. Here, we add the listener’s second IP address.

We can also achieve this through T-SQL by running the script in Listing 5-9.

ALTER AVAILABILITY GROUP App1
MODIFY LISTENER ’App1Listen’
(ADD IP (N’192.168.2.10’, N’255.255.255.0’)) ;

SQL Server now adds the IP address as a resource in the App1 role, and also configures the OR dependency on the Name resource. You can view this by running the dependency report against the Name resource in Failover Cluster Manager, as illustrated in Figure 5-32.

Improving Connection Times

Clients using .NET 4 or higher are able to specify the new MultiSubnetFailover=True property in their connecting strings when connecting to an AlwaysOn Availability Group. This improves connection times by retrying TCP connections more aggressively. If clients are using older versions of .NET, however, then there is a high risk of their connections timing out.

There are two workarounds for this issue. The first is to set the RegisterAllProvidersIP property to 0. This is the recommended approach, but the problem with it is that failover to the DR site can take up to 15 minutes. This is because the IP address resource for the second subnet is offline until failover occurs. It can then take up to 15 minutes for the PTR record to be published. In order to reduce this risk, it is recommended that you also lower the HostRecordTTL. This property defines how often the resource records for the cluster name are published.

The script in Listing 5-10 demonstrates how to disable RegisterAllProvidersIP and then reduce the HostRecordTTL to 300 seconds.

Get-ClusterResource "App1_App1Listen" | Set-ClusterParameter RegisterAllProvidersIP 0

Get-ClusterResource "App1_App1Listen" | Set-ClusterParameter HostRecordTTL 300

The alternative workaround is to simply increase the time-out value for connections to 30 seconds. However, this solution accepts that a large volume of connections will take up to 30 seconds. This may not be acceptable to the business.

Adding AlwaysOn Readable Secondary Replicas

It can be very useful to add readable secondary replicas to an AlwaysOn Availability Group topology in order to implement vertically scaled reporting. When you use this strategy, the databases are kept synchronized, with variable, but typically low latency, using log streaming. The additional advantage of readable secondary replicas from SQL Server 2014 onward is that they stay online, even if the primary replica is offline. The limitation here, however, is that users must connect directly to the instance, as opposed to the Availability Group Listener.

You can further improve read performance in readable secondary replicas by using temporary statistics, which you can also use to optimize read-only workloads. Also, snapshot isolation is also used exclusively on readable secondary replicas, even if other isolation levels or locking hints are explicitly requested. This helps avoid contention, but it also means that TempDB should be suitably scaled and on a fast disk array.

The main risk of using readable secondary replicas is that implementing snapshot isolation on the secondary replica can actually cause deleted records not to be cleaned up on the primary replica. This is because the ghost record cleanup task only remove rows from the primary once they are no longer required at the secondary. In this scenario, log truncation is also delayed on the primary replica. This means that you potentially risk having to kill long-running queries that are being satisfied against the readable secondary. This issue can also occur if the secondary replica becomes disconnected from the primary. Therefore, there is a risk that you may need to remove the secondary replica from the Availability Group and subsequently re-add it.

To make a secondary replica readable, you need to perform three tasks. First configure the secondary replica to allow read-only connections. Second, specify a read-only URL for reporting. The Availability Group Listener then directs appropriate traffic to this URL. The final task is to update the read-only routing list on the primary replica. These tasks are performed by the script in Listing 5-11.

--Configure the ASYNCDR Replica to allow read-only connections

ALTER AVAILABILITY GROUP App1
 MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)) ;

--Configure the read-only URL for the ASYNCDR Replica

ALTER AVAILABILITY GROUP App1
 MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N’TCP://CLUSTERNODE3.PROSQLADMIN.com:1433’)) ;

--Configure the read-only routing list on the Primary Replica

ALTER AVAILABILITY GROUP App1
MODIFY REPLICA ON N’CLUSTERNODE1PRIMARYREPLICA’ WITH
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(’CLUSTERNODE3ASYNCDR’))) ;

Summary

AlwaysOn Availability Groups can be implemented with up to eight secondary replicas, combining both Synchronous and Asynchronous Commit modes. When implementing high availability with availability groups, you always use Synchronous Commit mode, because Asynchronous Commit mode does not support automatic failover. When implementing Synchronous Commit mode, however, you must be aware of the associated performance penalty caused by committing the transaction on the secondary replica before it is committed on the primary replica. For disaster recovery, you will normally choose to implement Asynchronous Commit mode.

The availability group can be created via the New Availability Group Wizard, though dialog boxes, through T-SQL, or even through PowerShell. If you create an availability group using dialog boxes, then some aspects, such as the endpoint and associated permissions, must be scripted using T-SQL or PowerShell.

If you implement disaster recovery with availability groups, then you need to configure a multi-subnet cluster. This does not mean that you must have SAN replication between the sites, however, since availability groups do not rely on shared storage. What you do need to do is add additional IP addresses for the administrative cluster access point and also for the Availability Group Listener. You also need to pay attention to the properties of the cluster that support client reconnection to ensure that clients do not experience a high number of timeouts.