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 discuss using availability groups to scale out read-only workloads in Chapter 16.

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 13-1 creates these databases.

CREATE DATABASE Chapter13App1Customers ;
GO

ALTER DATABASE Chapter13App1Customers SET RECOVERY FULL ;
GO

USE Chapter13App1Customers
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 Chapter13App1Sales ;
GO

ALTER DATABASE Chapter13App1Sales SET RECOVERY FULL ;
GO

USE Chapter13App1Sales
GO

CREATE TABLE [dbo].[Orders](
         [OrderNumber] [int] IDENTITY(1,1) NOT NULL 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 Chapter13App2Customers ;
GO

ALTER DATABASE Chapter13App2Customers SET RECOVERY FULL ;
GO

USE Chapter13App2Customers
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 13-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 13-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 Chapter13App1Customers and Chapter13App1Sales databases. We do this by running the script in Listing 13-3.

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

BACKUP DATABASE Chapter13App1Sales
TO  DISK = N’C:BackupsChapter13App1Sales.bak’
WITH NAME = N’Chapter13App1Sales-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 13-2, now displays, giving us an overview of the steps that we need to undertake.

On the Specify Name page (see Figure 13-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 13-4. On this screen, notice that we cannot select the Chapter13App2Customers 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 13-5.

On the Endpoints tab of the Specify Replicas page, illustrated in Figure 13-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 13-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. The Backup Preferences tab is shown in Figure 13-7.

On the Listener tab, shown in Figure 13-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 13-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 13-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. This page is shown in Figure 13-11.

As setup progresses, the results of each configuration task display on the Results page, as shown in Figure 13-12. 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 13-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 [Chapter13App1Customers], [Chapter13App1Sales]
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 Chapter13App1Customers
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Customers.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

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

--Restore Database and Log (First database)

:Connect CLUSTERNODE2SYNCHA

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

RESTORE LOG Chapter13App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Customers.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 [Chapter13App1Customers] SET HADR AVAILABILITY GROUP = [App1];

GO

--Back Up Database and Log (Second database)

:Connect CLUSTERNODE1PRIMARYREPLICA

BACKUP DATABASE [Chapter13App1Sales]
TO  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Sales.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

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

--Restore Database and Log (Second database)

:Connect CLUSTERNODE2SYNCHA

RESTORE DATABASE [Chapter13App1Sales]
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Sales.bak’
WITH  NORECOVERY, STATS = 5 ;
GO

RESTORE LOG Chapter13App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Sales.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 [Chapter13App1Sales] SET HADR AVAILABILITY GROUP = [App1];
GO

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

The FAILOVER_CONDITION_LEVEL argument determines the group’s sensitivity to failover. Table 13-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 Chapter13App2Customers 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 13-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 [Chapter13App2Customers] TO  DISK = N’\CLUSTERNODE1AOAGShareChapter13App2Customers.bak’ WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

BACKUP LOG [Chapter13App2Customers] TO  DISK = N’\CLUSTERNODE1AOAGShareChapter13App2Customers.trn’ WITH NOSKIP, REWIND, COMPRESSION,  STATS = 5 ;
GO

--Restore Database and Log

:Connect CLUSTERNODE2SYNCHA

RESTORE DATABASE [Chapter13App2Customers] FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App2Customers.bak’ WITH  NORECOVERY, STATS = 5 ;
GO

RESTORE LOG [Chapter13App2Customers] FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App2Customers.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 13-13. 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 13-14. 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 13-15.

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 13-6 runs some write-intensive tests against the Chapter13App2Customers database (which is in Asynchronous Commit mode) and then against the Chapter13App1Customers 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 Chapter13App2Customers
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 Chapter13App1Customers
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 13-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 Chapter13App1Customers and Chapter13App1Sales 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 13-16. 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 12. Running the Cluster Validation wizard in our scenario is likely to throw up some warnings, which are detailed in Table 13-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 13-17.

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 13-18. 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 13-19.

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

On the Configure File Share Witness page, which is illustrated in Figure 13-21, 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 13-22.

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 13-23.

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 13-24.

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 13-25. 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 13-26, 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 13-27, 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 13-28.

On the Listener tab, illustrated in Figure 13-29, 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 13-30.

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

On the Summary page, displayed in Figure 13-32, 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 13-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 Chapter13App1Customers TO  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Customers.bak’
WITH  COPY_ONLY, FORMAT, INIT, REWIND, COMPRESSION,  STATS = 5 ;
GO

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

:Connect CLUSTERNODE3ASYNCDR

ALTER AVAILABILITY GROUP [App1] JOIN;
GO

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

RESTORE LOG Chapter13App1Customers
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Customers.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 Chapter13App1Customers SET HADR AVAILABILITY GROUP = [App1];
GO

--Back up and restore the second database and log

:Connect CLUSTERNODE1PRIMARYREPLICA

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

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

:Connect CLUSTERNODE3ASYNCDR

ALTER AVAILABILITY GROUP [App1] JOIN;
GO

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

RESTORE LOG Chapter13App1Sales
FROM  DISK = N’\CLUSTERNODE1AOAGShareChapter13App1Sales.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 Chapter13App1Sales 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 13-33. Here, we add the listener’s second IP address.

We can also achieve this through T-SQL by running the script in Listing 13-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 13-34.

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 13-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.

Managing AlwaysOn Availability Groups

Once the initial setup of your availability group is complete, you still need to perform administrative tasks. These include failing over the availability group, monitoring, and on rare occasions, adding additional listeners. These topics are discussed in the following sections.

Failover

If a replica is in Synchronous Commit mode and is configured for automatic failover, then the availability group automatically moves to a redundant replica in the event of an error condition being met on the primary replica. There are occasions, however, when you will want to manually fail over an availability group. This could be because of DR testing, proactive maintenance, or because you need to bring up an asynchronous replica following a failure of the primary replica or the primary data center.

Synchronous Failover

If you wish to fail over a replica that is in Synchronous Commit mode, launch the Failover Availability Group wizard by selecting Failover from the context menu of your availability group in Object Explorer. After moving past the Introduction page, you find the Select New Primary Replica page (see Figure 13-35). On this page, check the box of the replica to which you want to fail over. Before doing so, however, review the Failover Readiness column to ensure that the replicas are synchronized and that no data loss will occur.

On the Connect To Replica page, illustrated in Figure 13-36, use the Connect button to establish a connection to the new primary replica.

On the Summary page, you are given details of the task to be performed, followed by a progress indicator on the Results page. Once the failover completes, check that all tasks were successful, and investigate any errors or warnings that you receive.

We can also used T-SQL to fail over the availability group. The command in Listing 13-11 achieves the same results. Make sure to run this script from the replica that will be the new primary replica. If you run it from the current primary replica, use SQLCMD mode and connect to the new primary within the script.

ALTER AVAILABILITY GROUP App1 FAILOVER ;
GO

Asynchronous Failover

If your availability group is in Asynchronous Commit mode, then from a technical standpoint, you can fail over in a similar way to the way you can for a replica running in Synchronous Commit mode, except for the fact that you need to force the failover, thereby accepting the risk of data loss. You can force failover by using the command in Listing 13-12. You should run this script on the instance that will be the new primary. For it to work, the cluster must have quorum. If it doesn’t, then you need to force the cluster online before you force the availability group online.

ALTER AVAILABILITY GROUP App1 FORCE_FAILOVER_ALLOW_DATA_LOSS ;

From a process perspective, you should only ever do this if your primary site is completely unavailable. If this is not the case, first put the application into a safe state. This avoids any possibility of data loss. The way that I normally achieve this in a production environment is by performing the following steps:

1. Disable logins.
2. Change the mode of the replica to Synchronous Commit mode.
3. Fail over.
4. Change the replica back to Asynchronous Commit mode.
5. Enable the logins.

You can perform these steps with the script in Listing 13-13. When run from the DR instance, this script places the databases in App1 into a safe state before failing over, and then it reconfigures the application to work under normal operations.

--DISABLE LOGINS

DECLARE @AOAGDBs TABLE
(
DBName NVARCHAR(128)
) ;

INSERT INTO @AOAGDBs
SELECT database_name
FROM sys.availability_groups AG
INNER JOIN sys.availability_databases_cluster ADC
        ON AG.group_id = ADC.group_id
WHERE AG.name = ’App1’ ;

DECLARE @Mappings TABLE
(
        LoginName NVARCHAR(128),
    DBname NVARCHAR(128),
    Username NVARCHAR(128),
    AliasName NVARCHAR(128)
) ;

INSERT INTO @Mappings
EXEC sp_msloginmappings ;

DECLARE @SQL NVARCHAR(MAX)

SELECT DISTINCT @SQL =
(
        SELECT ’ALTER LOGIN [’ + LoginName + ’] DISABLE; ’ AS [data()]
        FROM @Mappings M
        INNER JOIN @AOAGDBs A
                ON M.DBname = A.DBName
        WHERE LoginName <> SUSER_NAME()
        FOR XML PATH (’’)
)

EXEC(@SQL)
GO

--SWITCH TO SYNCHRONOUS COMMIT MODE

ALTER AVAILABILITY GROUP App1
MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT) ;
GO

--FAIL OVER

ALTER AVAILABILITY GROUP App1 FAILOVER
GO

--SWITCH BACK TO ASYNCHRONOUS COMMIT MODE

ALTER AVAILABILITY GROUP App1
MODIFY REPLICA ON N’CLUSTERNODE3ASYNCDR’ WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT) ;
GO

--ENABLE LOGINS

DECLARE @AOAGDBs TABLE
(
DBName NVARCHAR(128)
) ;

INSERT INTO @AOAGDBs
SELECT database_name
FROM sys.availability_groups AG
INNER JOIN sys.availability_databases_cluster ADC
        ON AG.group_id = ADC.group_id
WHERE AG.name = ’App1’ ;

DECLARE @Mappings TABLE
(
        LoginName NVARCHAR(128),
    DBname NVARCHAR(128),
    Username NVARCHAR(128),
    AliasName NVARCHAR(128)
) ;

INSERT INTO @Mappings
EXEC sp_msloginmappings

DECLARE @SQL NVARCHAR(MAX)

SELECT DISTINCT @SQL =
(
        SELECT ’ALTER LOGIN [’ + LoginName + ’] ENABLE; ’ AS [data()]
        FROM @Mappings M
        INNER JOIN @AOAGDBs A
                ON M.DBname = A.DBName
        WHERE LoginName <> SUSER_NAME()
        FOR XML PATH (’’)
) ;

EXEC(@SQL)

Synchronizing Uncontained Objects

Regardless of the method you use to fail over, assuming that all of the databases within the availability group are not contained, then you need to ensure that instance-level objects are synchronized. The most straightforward way to keep your instance-level objects synchronized is by implementing an SSIS package, which is scheduled to run on a periodic basis.

Whether you choose to schedule a SSIS package to execute, or you choose a different approach, such as a SQL Server Agent job that scripts and re-creates the objects on the secondary servers, these are the objects that you should consider synchronizing:

• Logins
• Credentials
• SQL Server Agent jobs
• Custom error messages
• Linked servers
• Server-level event notifications
• Stored procedures in Master
• Server-level triggers
• Encryption keys and certificates

Monitoring

Once you have implemented availability groups, you need to monitor them and respond to any errors or warnings that could affect the availability of your data. If you have many availability groups implemented throughout the enterprise, then the only way to monitor them effectively and holistically is by using an enterprise monitoring tool, such as SOC (Systems Operations Center). If you only have a small number of availability groups, however, or if you are troubleshooting a specific issue, then SQL Server provides the AlwaysOn Dashboard and the AlwaysOn Health Trace. The following sections examine these two features.

AlwaysOn Dashboard

The AlwaysOn Dashboard is an interactive report that allows you to view the health of your AlwaysOn environment and drill through, or roll up elements within the topology. You can invoke the report from the context menu of the Availability Groups folder in Object Explorer, or from the context menu of the availability group itself. Figure 13-37 shows the report that is generated from the context menu of the App1 availability group. You can see that currently, synchronization of both replicas is in a healthy state.

The three possible synchronization states that a database can be in are SYNCHRONIZED, SYNCRONIZING, and NOT SYNCHRONIZING. A synchronous replica should be in the SYNCHRONIZED state, and any other state is unhealthy. An asynchronous replica, however, will never be in the SYNCHRONIZED state, and a state of SYNCHRONIZING is considered healthy. Regardless of the mode, NOT SYNCHRONIZING indicates that the replica is not connected.

At the top right of the report, there are links to the failover wizard, which we discussed earlier in this chapter; the AlwaysOn Health events, which we discussed in the next section; and also a link to view cluster quorum information. The Cluster Quorum Information screen, which is invoked by this link, is displayed in Figure 13-38. You can also drill through each replica in the Availability Replicas window to see replica-specific details.

AlwaysOn Health Trace

The AlwaysOn Health Trace is an Extended Events session, which is created when you create you first availability group. It can be located in SQL Server Management Studio, under Extended Events | Sessions, and via its context menu, you can view live data that is being captured, or you can enter the session’s properties to change the configuration of the events that are captured.

Drilling through the session exposes the session’s package, and from the context menu of the package, you can view previously captured events. Figure 13-39 shows that the latest event captured, was Database 5 (which, in our case, is Chapter13App1Customers), was waiting for the log to be hardened on the synchronous replica. Extended Events is discussed in detail in Chapter 19.

Adding Multiple Listeners

Usually, each availability group has a single Availability Group Listener, but there are some rare instances in which you may need to create multiple listeners for the same availability group. One scenario in which this may be required is if you have legacy applications with hard-coded connection strings. Here, you can create an extra listener with a client access point that matches the name of the hard-coded connection string.

As mentioned earlier in this chapter, it is not possible to create a second Availability Group Listener through SQL Server Management Studio, T-SQL, or even PowerShell. Instead, we must use Failover Cluster Manager. Here, we create a new Client Access Point resource within our App1 role. To do this, we select Add Resource from the context menu of the App1 role, and then select Client Access Point. This causes the New Resource Wizard to be invoked. The Client Access Point page of the wizard is illustrated in Figure 13-40. You can see that we have entered the DNS name for the client access point and specified an IP address from each subnet.

On the Confirmation page, we are shown a summary of the configuration that will be performed. On the Configure Client Access Point page, we see a progress indicator, before we are finally shown a completion summary on the Summary page, which is illustrated in Figure 13-41.

Now we need to configure the Availability Group resource to be dependent upon the new client access point. To do this, we select Properties from the context menu of the App1 resource and then navigate to the Dependencies tab. Here, we add the new client access point as a dependency and configure an OR constraint between the two listeners, as illustrated in Figure 13-42. Once we apply this change, clients are able to connect using either of the two listener names.

Other Administrative Considerations

When databases are made highly available with AlwaysOn Availability Groups, several limitations are imposed. One of the most restrictive of these is that databases cannot be placed in single_user mode or be made read only. This can have an impact when you need to safe-state your application for maintenance. This is why, in the Failover section of this chapter, we disabled the logins that have users mapped to the databases. If you must place your database in single-user mode, then you must first remove it from the availability group.

A database can be removed from an availability group by running the command in Listing 13-14. This command removes the Chapter13App1Customers database from the availability group.

ALTER DATABASE Chapter13App1Customers SET HADR OFF ;

There may also be occasions in which you want a database to remain in an availability group, but you wish to suspend data movement to other replicas. This is usually because the availability group is in Synchronous Commit mode and you have a period of high utilization, where you need a performance improvement. You can suspend the data movement to a database by using the command in Listing 13-15, which suspends data movement for the Chapter13App1Sales database and then resumes it.

ALTER DATABASE Chapter13App1Customers SET HADR SUSPEND ;
GO

ALTER DATABASE Chapter13App1Customers SET HADR RESUME ;
GO

Another important consideration is the placement of database and log files. These files must be in the same location on each replica. This means that if you use named instances, it is a hard technical requirement that you change the default file locations for data and logs, because the default location includes the name of the instance. This is assuming, of course, that you do not use the same instance name on each node, which would defy many of the benefits of having a named instance.

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.

Failover to a synchronous replica in the event of a failure of the primary replica is automatic. There are instances, however, in which you will also need to failover manually. This could be because of a disaster that requires failover to the DR site, or it could be for proactive maintenance. Although it is possible to failover to an asynchronous replica with the possibility of data loss, it is good practice to place the databases in a safe-state first. Because you cannot place a database in read only or single_user mode, if it is participating in an availability group, safe-stating usually consists of disabling the logins and then switching to Synchronous Commit mode before failover.

To monitor availability groups throughout the enterprise, you need to use a monitoring tool, such as Systems Operation Center. If you need to monitor a small number of availability groups or troubleshoot a specific issue, however, use one of the tools included with SQL Server, such as a dashboard for monitoring the health of the topology, and an extended events session, called the AlwaysOn Health Trace.

You should also consider other maintenance tasks. These include where to place database and log files, as they must have the same location on each replica, and removing a database from an availability group so that you can place it in single_user mode, for example. Changing to single_user mode may be due to a requirement to run DBCC CHECKDB in a repair mode, and suspend data movement. Suspending data movement allows you to remove the performance overhead during a period of high utilization, but be warned, it also causes the transaction log on the primary replica to grow, without an option to truncate it, until data movement has resumed and the databases are once again synchronized.