In Part 3, we completed the setup of the standalone instance of SQL Server at the DR data center.

In our final post in the series, we are now going to setup our Availability Group (AG). Before we start, we will prepare the SQL Server instances by restoring our availability databases from previous backups or by creating new databases for the lab. In this case, we will be restoring from previous backups. Once the databases exist on the Primary replica, which is our FCI in this case, we don’t have to restore the databases to the Secondary replica (the stand alone DR node). We can allow the AG creation process to perform the task for us, but this may not be practical depending on the size of the databases. The AG process will take a backup from the primary replica and restore to the secondary replica with the NORECOVERY option. In this lab, we will perform the restore task manually. Note that the backups include database and transaction log backups.

Okay, let’s get going.

Restore selected databases onto the FCI. For the purposes of this lab, we have four databases – Credit, Deal, Sales and Retail. These databases will be our availability databases and we will select them when creating the AG. Ensure that the databases have their recovery model set to FULL

For DR, restore all four databases with the NORECOVERY option on the standalone SQL Server instance (Node 3).

P422b

 

 

From SQL Server Management studio (SSMS), the SQL Server instances should look like the following after the restore.

P415_before

 

The databases are ready, we can now Create an AG on top of the FCI and standalone instance.

We will create the AG from the FCI. From SSMS, right click on the ‘AlwaysON High Availability’ folder and select ‘New Availability Group Wizard’. Click Next to proceed.

 

P41

 

 

Give the AG a name. We will use SQL2012AG, but the name can be anything you want. It has to be unique on the WSFC cluster and the maximum length  is 128 characters.

P42

 

All the databases that meet the requirement for the AG will be shown, we will select the four databases as shown below.

P43

 

On the ‘Specify Replicas’ page, we will configure the tabs as shown in the following screens.

P45

 

 

P46

 

P47

 

The final part is to configure the ‘Listener’. This is the ultimate client access point with which users and applications will connect to the availability databases. In reality, the name of the listener will follow the company server naming convention. We will use the default port for SQL Server (1433), we would need to specify the port number for every connection if we didn’t.

P48

 

Click ‘Add’ on the page to include IP addresses for both subnets. This will enable the listener to keep accepting connections in the case of a disaster.

P49

 

 

P410

 

As we have already restored our databases we can simply join them to the AG. SO we choose ‘Join only’. Click Next.

P411

 

Allow the validation to run and Finish the setup on the final screen

P412

P413

 

P414

 

The SQL Server instances should look like the following after the AG has been setup successfully.

P416_after

 

P417_FCI

 

P420_Flex3

 

 

You can also connect directly to the listener, it implicitly connects to the active node. This is the node that is the current owner of the AG resource (SQL2012AG)

 

 

P422_listener

 

Verify the setup, roles, resources and ownership from the Failover Cluster Manager

P421

 

 

 

So there we have it. Our solution is complete. There many post-installation configuration that we will need to do if we were in production, perhaps I will add that as a final post to the series if there is a demand for it. However, a few of those considerations are listed below.

  • Check the resource dependencies
  •  Check that you have configured Kerberos authentication (if used).
  • Set port to 1433, if you use any other port number, you will have to include this port number in the client connection string whenever you connect to the listener.
  • Understand options available to assist with issues around multi-subnet failover, such as ‘RegisterAllProvidersIP’ and ‘MultiSubnetFailover’.

 

Leave a Reply

Your email address will not be published. Required fields are marked *