Create an Availability Group
In this tutorial we will learn how to create an Always On availability group for SQL Server on Azure Virtual Machines (VMs).
Create the cluster
After the prerequisites are completed, the first step is to create a Windows Server Failover Cluster that includes two SQL Severs and a witness server.
- Firstly, use Remote Desktop Protocol (RDP) to connect to the first SQL Server. Use a domain account that is an administrator on both SQL Servers and the witness server.
- Secondly, in the Server Manager dashboard, select Tools, and then select Failover Cluster Manager.
- Then, in the left pane, right-click Failover Cluster Manager, and then select Create a Cluster.
- After that, in the Create Cluster Wizard, create a one-node cluster by stepping through the pages with the settings in the following table:
Set the Windows server failover cluster IP address
- Firstly, in Failover Cluster Manager, scroll down to Cluster Core Resources and expand the cluster details. You should see both the Name and the IP Address resources in the Failed state.
- Secondly, right-click the failed IP Address resource, and then select Properties.
- Thirdly, select Static IP Address and specify an available address from the same subnet as your virtual machines.
- Lastly, in the Cluster Core Resources section, right-click cluster name and select Bring Online. Wait until both resources are online. When the cluster name resource comes online, it updates the domain controller (DC) server with a new Active Directory (AD) computer account.
Add the other SQL Server to cluster
Add the other SQL Server to the cluster.
- Firstly, in the browser tree, right-click the cluster and select Add Node.
- Secondly, in the Add Node Wizard, select Next. In the Select Servers page, add the second SQL Server. Type the server name in Enter server name and then select Add. When you are done, select Next.
- Thirdly, in the Validation Warning page, select No (in a production scenario you should perform the validation tests). Then, select Next.
- Then, in the Confirmation page if you are using Storage Spaces, clear the checkbox labeled Add all eligible storage to the cluster.
- After that, select Next.
- Then, select Finish.
- Failover Cluster Manager shows that your cluster has a new node and lists it in the Nodes container.
- Lastly, log out of the remote desktop session.
Enable availability groups
Next, enable the AlwaysOn availability groups feature. Do these steps on both SQL Servers.
- Firstly, from the Start screen, launch SQL Server Configuration Manager.
- Secondly, in the browser tree, select SQL Server Services, then right-click the SQL Server (MSSQLSERVER) service and select Properties.
- Thirdly, select the AlwaysOn High Availability tab, then select Enable AlwaysOn availability groups, as follows:
- Then, select Apply. Select OK in the pop-up dialog.
- Lastly, restart the SQL Server service.
Create a backup share
- Firstly, on the first SQL Server in Server Manager, select Tools. Open Computer Management.
- Secondly, select Shared Folders.
- Then, right-click Shares, and select New Share….. Use Create a Shared Folder Wizard to create a share.
- After that, on Folder Path, select Browse and locate or create a path for the database backup shared folder. Select Next.
- Next, in Name, Description, and Settings verify the share name and path. Select Next.
- Now, on Shared Folder Permissions set Customize permissions. Select Custom….
- On Customize Permissions, select Add….
- Here, make sure that the SQL Server and SQL Server Agent service accounts for both servers have full control.
- Then, select OK.
- Lastly, in Shared Folder Permissions, select Finish. Select Finish again.
Create the availability group:
- Firstly, on remote desktop session to the first SQL Server. In Object Explorer in SSMS, right-click AlwaysOn High Availability and select New availability group Wizard.
- Secondly, in the Introduction page, select Next. In the Specify availability group Name page, type a name for the availability group in Availability group name. For example, AG1. Select Next.
- Thirdly, in the Select Databases page, select your database, and then select Next.
- Then, in the Specify Replicas page, select Add Replica.
- The Connect to Server dialog pops up. Type the name of the second server in Server name. Select Connect.
- After that, select Endpoints to see the database mirroring endpoint for this availability group. Use the same port that you used when you set the firewall rule for database mirroring endpoints.
- Then, in the Select Initial Data Synchronization page, select Full and specify a shared network location. For the location, use the backup share that you created. In the example it was, \\Backup. Select Next.
- Next, in the Validation page, select Next. This page should look similar to the following image:
- After that, in the Summary page, select Finish, then wait while the wizard configures the new availability group. In the Progress page, you can select More details to view the detailed progress. Once the wizard is finished, inspect the Results page to verify that the availability group is successfully created.
- Lastly, select Close to exit the wizard.
Configure the listener
The next thing to do is to configure an availability group listener on the failover cluster.
The availability group listener is an IP address and network name that the SQL Server availability group listens on. To create the availability group listener, do the following:
- Firstly, get the name of the cluster network resource.
a. Use RDP to connect to the Azure virtual machine that hosts the primary replica.
b. Open Failover Cluster Manager.
c. Select the Networks node, and note the cluster network name. Use this name in the $ClusterNetworkName variable in the PowerShell script. In the following image the cluster network name is Cluster Network 1:
- Secondly, add the client access point.
- The client access point is the network name that applications use to connect to the databases in an availability group. Create the client access point in Failover Cluster Manager.
a. Expand the cluster name, and then click Roles.
b. In the Roles pane, right-click the availability group name, and then select Add Resource > Client Access Point.
c. In the Name box, create a name for this new listener. The name for the new listener is the network name that applications use to connect to databases in the SQL Server availability group.
d. To finish creating the listener, click Next twice, and then click Finish. Do not bring the listener or resource online at this point.
- Thirdly, take the availability group cluster role offline. In Failover Cluster Manager under Roles, right-click the role, and select Stop Role.
- Then, configure the IP resource for the availability group.
a. Click the Resources tab, and then expand the client access point you created.
The client access point is offline.
b. Right-click the IP resource, and then click properties. Note the name of the IP address, and use it in the $IPResourceName variable in the PowerShell script.
c. Under IP Address, click Static IP Address. Set the IP address as the same address that you used when you set the load balancer address on the Azure portal.
- Next, make the SQL Server availability group resource dependent on the client access point.
a. In Failover Cluster Manager, click Roles, and then click your availability group.
b. On the Resources tab, under Other Resources, right-click the availability resource group, and then click Properties.
c. On the dependencies tab, add the name of the client access point (the listener) resource.
d. Click OK.
- After that, set the cluster parameters in PowerShell.
a. Copy the following PowerShell script to one of your SQL Server instances. Update the variables for your environment.
$ListenerILBIP is the IP address that you created on the Azure load balancer for the availability group listener.
$ListenerProbePort is the port you configured on the Azure load balancer for the availability group listener.
- Lastly, bring the availability group cluster role online. In Failover Cluster Manager under Roles, right click the role, and select Start Role.
Reference: Microsoft Documentation