Configure Always On Availability Group in Azure VM manually

This tutorial shows how to create a SQL Server Always On Availability Group on Azure Virtual Machines. The complete tutorial creates an Availability Group with a database replica on two SQL Servers.

Time estimate: Takes about 30 minutes to complete once the prerequisites are met.

The diagram illustrates what you build in the tutorial.

Availability Group

Prerequisites

The tutorial assumes you have a basic understanding of SQL Server Always On Availability Groups. If you need more information, see Overview of Always On Availability Groups (SQL Server).

The following table lists the prerequisites that you need to complete before starting this tutorial:

Requirement Description
Square Two SQL Servers - In an Azure availability set
- In a single domain
- With Failover Clustering feature installed
Square Windows Server File share for cluster witness
Square SQL Server service account Domain account
Square SQL Server Agent service account Domain account
Square Firewall ports open - SQL Server: 1433 for default instance
- Database mirroring endpoint: 5022 or any available port
- Availability group load balancer IP address health probe: 59999 or any available port
- Cluster core load balancer IP address health probe: 58888 or any available port
Square Add Failover Clustering Feature Both SQL Servers require this feature
Square Installation domain account - Local administrator on each SQL Server
- Member of SQL Server sysadmin fixed server role for each instance of SQL Server

Before you begin the tutorial, you need to Complete prerequisites for creating Always On Availability Groups in Azure Virtual Machines. If these prerequisites are completed already, you can jump to Create Cluster.

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.

  1. RDP to the first SQL Server using a domain account that is an administrator on both SQL Servers and the witness server.

    Tip

    If you followed the prerequisites document, you created an account called CORP\Install. Use this account.

  2. In the Server Manager dashboard, select Tools, and then click Failover Cluster Manager.

  3. In the left pane, right-click Failover Cluster Manager, and then click Create a Cluster. Create Cluster
  4. In the Create Cluster Wizard, create a one-node cluster by stepping through the pages with the settings in the following table:

    Page Settings
    Before You Begin Use defaults
    Select Servers Type the first SQL Server name in Enter server name and click Add.
    Validation Warning Select No. I do not require support from Microsoft for this cluster, and therefore do not want to run the validation tests. When I click Next, continue Creating the cluster.
    Access Point for Administering the Cluster Type a cluster name, for example SQLAGCluster1 in Cluster Name.
    Confirmation Use defaults unless you are using Storage Spaces. See the note following this table.

Set the Windows server failover cluster IP address

  1. 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. The IP address resource cannot be brought online because the cluster is assigned the same IP address as the machine itself, therefore it is a duplicate address.

  2. Right-click the failed IP Address resource, and then click Properties.

    Cluster Properties

  3. Select Static IP Address and specify an available address from the same subnet as your virtual machines.

  4. In the Cluster Core Resources section, right-click cluster name and click Bring Online. Then, wait until both resources are online. When the cluster name resource comes online, it updates the DC server with a new AD computer account. Use this AD account to run the Availability Group clustered service later.

Add the other SQL Server to cluster

Add the other SQL Server to the cluster.

  1. In the browser tree, right-click the cluster and click Add Node.

    Add Node to the Cluster

  2. In the Add Node Wizard, click Next. In the Select Servers page, add the second SQL Server. Type the server name in Enter server name and then click Add. When you are done, click Next.

  3. In the Validation Warning page, click No (in a production scenario you should perform the validation tests). Then, click Next.

  4. In the Confirmation page if you are using Storage Spaces, clear the checkbox labeled Add all eligible storage to the cluster.

    Add Node Confirmation

    Warning

    If you are using Storage Spaces and do not uncheck Add all eligible storage to the cluster, Windows detaches the virtual disks during the clustering process. As a result, they do not appear in Disk Manager or Explorer until the storage spaces are removed from the cluster and reattached using PowerShell. Storage Spaces groups multiple disks in to storage pools. For more information, see Storage Spaces.

  5. Click Next.

  6. Click Finish.

    Failover Cluster Manager shows that your cluster has a new node and lists it in the Nodes container.

  7. Log out of the remote desktop session.

Add a cluster quorum file share

In this example the Windows cluster uses a file share to create a cluster quorum. This tutorial uses a Node and File Share Majority quorum. For more information, see Understanding Quorum Configurations in a Failover Cluster.

  1. Connect to the file share witness member server with a remote desktop session.

  2. On Server Manager, click Tools. Open Computer Management.

  3. Click Shared Folders.

  4. Right-click Shares, and click New Share....

    New Share

    Use Create a Shared Folder Wizard to create a share.

  5. On Folder Path, click Browse and locate or create a path for the shared folder. Click Next.

  6. In Name, Description, and Settings verify the share name and path. Click Next.

  7. On Shared Folder Permissions set Customize permissions. Click Custom....

  8. On Customize Permissions, click Add....

  9. Make sure that the account used to create the cluster has full control.

    New Share

  10. Click OK.

  11. In Shared Folder Permissions, click Finish. Click Finish again.

  12. Log out of the server

Configure cluster quorum

Next, set the cluster quorum.

  1. Connect to the first cluster node with remote desktop.

  2. In Failover Cluster Manager, right-click the cluster, point to More Actions, and click Configure Cluster Quorum Settings....

    New Share

  3. In Configure Cluster Quorum Wizard, click Next.

  4. In Select Quorum Configuration Option, choose Select the quorum witness, and click Next.

  5. On Select Quorum Witness, click Configure a file share witness.

    Tip

    Windows Server 2016 supports a cloud witness. If you choose this type of witness, you do not need a file share witness. For more information, see Deploy a cloud witness for a Failover Cluster. This tutorial uses a file share witness, which is supported by previous operating systems.

  6. On Configure File Share Witness, type the path for the share you created. Click Next.

  7. Verify the settings on Confirmation. Click Next.

  8. Click Finish.

The cluster core resources are configured with a file share witness.

Enable Availability Groups

Next, enable the AlwaysOn Availability Groups feature. Do these steps on both SQL Servers.

  1. From the Start screen, launch SQL Server Configuration Manager.
  2. In the browser tree, click SQL Server Services, then right-click the SQL Server (MSSQLSERVER) service and click Properties.
  3. Click the AlwaysOn High Availability tab, then select Enable AlwaysOn Availability Groups, as follows:

    Enable AlwaysOn Availability Groups

  4. Click Apply. Click OK in the pop-up dialog.

  5. Restart the SQL Server service.

Repeat these steps on the other SQL Server.

Create a database on the first SQL Server

  1. Launch the RDP file to the first SQL Server with a domain account that is a member of sysadmin fixed server role.
  2. Open SQL Server Management Studio and connect to the first SQL Server.
  3. In Object Explorer, right-click Databases and click New Database.
  4. In Database name, type MyDB1, then click OK.

Create a backup share

  1. On the first SQL Server in Server Manager, click Tools. Open Computer Management.

  2. Click Shared Folders.

  3. Right-click Shares, and click New Share....

    New Share

    Use Create a Shared Folder Wizard to create a share.

  4. On Folder Path, click Browse and locate or create a path for the database backup shared folder. Click Next.

  5. In Name, Description, and Settings verify the share name and path. Click Next.

  6. On Shared Folder Permissions set Customize permissions. Click Custom....

  7. On Customize Permissions, click Add....

  8. Make sure that the SQL Server and SQL Server Agent service accounts for both servers have full control.

    New Share

  9. Click OK.

  10. In Shared Folder Permissions, click Finish. Click Finish again.

Take a full backup of the database

You need to back up the new database to initialize the log chain. If you do not take a backup of the new database, it cannot be included in an Availability Group.

  1. In Object Explorer, right-click the database, point to Tasks..., click Back Up.

  2. Click OK to take a full backup to the default backup location.

Create the Availability Group

You are now ready to configure an Availability Group using the following steps:

  • Create a database on the first SQL Server.
  • Take both a full backup and a transaction log backup of the database
  • Restore the full and log backups to the second SQL Server with the NORECOVERY option
  • Create the Availability Group (AG1) with synchronous commit, automatic failover, and readable secondary replicas

Create the Availability Group:

  1. On remote desktop session to the first SQL Server. In Object Explorer in SSMS, right-click AlwaysOn High Availability and click New Availability Group Wizard.

    Launch New Availability Group Wizard

  2. In the Introduction page, click Next. In the Specify Availability Group Name page, type a name for the Availability Group, for example AG1, in Availability group name. Click Next.

    New AG Wizard, Specify AG Name

  3. In the Select Databases page, select your database and click Next.

    Note

    The database meets the prerequisites for an Availability Group because you have taken at least one full backup on the intended primary replica.

    New AG Wizard, Select Databases

  4. In the Specify Replicas page, click Add Replica.

    New AG Wizard, Specify Replicas

  5. The Connect to Server dialog pops up. Type the name of the second server in Server name. Click Connect.

    Back in the Specify Replicas page, you should now see the second server listed in Availability Replicas. Configure the replicas as follows.

    New AG Wizard, Specify Replicas (Complete)

  6. Click 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.

    New AG Wizard, Select Initial Data Synchronization

  7. 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, \\<First SQL Server>\Backup\. Click Next.

    Note

    Full synchronization takes a full backup of the database on the first instance of SQL Server and restores it to the second instance. For large databases, full synchronization is not recommended because it may take a long time. You can reduce this time by manually taking a backup of the database and restoring it with NO RECOVERY. If the database is already restored with NO RECOVERY on the second SQL Server before configuring the Availability Group, choose Join only. If you want to take the backup after configuring the Availability Group, choose Skip initial data synchronization.

    New AG Wizard, Select Initial Data Synchronization

  8. In the Validation page, click Next. This page should look similar to the following image:

    New AG Wizard, Validation

    Note

    There is a warning for the listener configuration because you have not configured an Availability Group listener. You can ignore this warning because on Azure virtual machines you create the listener after creating the Azure load balancer.

  9. In the Summary page, click Finish, then wait while the wizard configures the new Availability Group. In the Progress page, you can click 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.

    New AG Wizard, Results

  10. Click Close to exit the wizard.

Check the Availability Group

  1. In Object Explorer, expand AlwaysOn High Availability, then expand Availability Groups. You should now see the new Availability Group in this container. Right-click the Availability Group and click Show Dashboard.

    Show AG Dashboard

    Your AlwaysOn Dashboard should look similar to this.

    AG Dashboard

    You can see the replicas, the failover mode of each replica and the synchronization state.

  2. In Failover Cluster Manager, click your cluster. Select Roles. The Availability Group name you used is a role on the cluster. That Availability Group does not have an IP address for client connections, because you did not configure a listener. You will configure the listener after you create an Azure load balancer.

    AG in Failover Cluster Manager

    Warning

    Do not try to fail over the Availability Group from the Failover Cluster Manager. All failover operations should be performed from within AlwaysOn Dashboard in SSMS. For more information, see Restrictions on Using The Failover Cluster Manager with Availability Groups.

At this point, you have an Availability Group with replicas on two instances of SQL Server. You can move the Availability Group between instances. You cannot connect to the Availability Group yet because you do not have a listener. In Azure virtual machines, the listener requires a load balancer. The next step is to create the load balancer in Azure.

Create an Azure load balancer

On Azure virtual machines, a SQL Server Availability Group requires a load balancer. The load balancer holds the IP addresses for the Availability Group listeners and the Windows Server Failover Cluster. This section summarizes how to create the load balancer in the Azure portal.

An Azure Load Balancer can be either a Standard Load Balancer or a Basic Load Balancer. Standard Load Balancer has more features than the Basic Load Balancer. For an availability group, the Standard Load Balancer is required if you use an Availability Zone (instead of an Availability Set). For details on the difference between the load balancer types, see Load Balancer SKU comparison.

  1. In the Azure portal, go to the resource group where your SQL Servers are and click + Add.
  2. Search for Load Balancer. Choose the load balancer published by Microsoft.

    AG in Failover Cluster Manager

  3. Click Create.

  4. Configure the following parameters for the load balancer.

    Setting Field
    Name Use a text name for the load balancer, for example sqlLB.
    Type Internal
    Virtual network Use the name of the Azure virtual network.
    Subnet Use the name of the subnet that the virtual machine is in.
    IP address assignment Static
    IP address Use an available address from subnet. Use this address for your availability group listener. Note that this is different from your cluster IP address.
    Subscription Use the same subscription as the virtual machine.
    Location Use the same location as the virtual machine.

    The Azure portal blade should look like this:

    Create Load Balancer

  5. Click Create, to create the load balancer.

To configure the load balancer, you need to create a backend pool, a probe, and set the load balancing rules. Do these in the Azure portal.

Add backend pool for the availability group listener

  1. In the Azure portal, go to your availability group. You might need to refresh the view to see the newly created load balancer.

    Find Load Balancer in Resource Group

  2. Click the load balancer, click Backend pools, and click +Add.

  3. Type a name for the backend pool.

  4. Associate the backend pool with the availability set that contains the VMs.

  5. Under Target network IP configurations, check VIRTUAL MACHINE and choose both of the virtual machines that will host availability group replicas. Do not include the file share witness server.

    Note

    If both virtual machines are not specified, connections will only succeed to the primary replica.

  6. Click OK to create the backend pool.

Set the probe

  1. Click the load balancer, click Health probes, and click +Add.

  2. Set the listener health probe as follows:

    Setting Description Example
    Name Text SQLAlwaysOnEndPointProbe
    Protocol Choose TCP TCP
    Port Any unused port 59999
    Interval The amount of time between probe attempts in seconds 5
    Unhealthy threshold The number of consecutive probe failures that must occur for a virtual machine to be considered unhealthy 2
  3. Click OK to set the health probe.

Set the load balancing rules

  1. Click the load balancer, click Load balancing rules, and click +Add.

  2. Set the listener load balancing rules as follows.

    Setting Description Example
    Name Text SQLAlwaysOnEndPointListener
    Frontend IP address Choose an address Use the address that you created when you created the load balancer.
    Protocol Choose TCP TCP
    Port Use the port for the availability group listener 1433
    Backend Port This field is not used when Floating IP is set for direct server return 1433
    Probe The name you specified for the probe SQLAlwaysOnEndPointProbe
    Session Persistence Drop down list None
    Idle Timeout Minutes to keep a TCP connection open 4
    Floating IP (direct server return) Enabled

    Warning

    Direct server return is set during creation. It cannot be changed.

  3. Click OK to set the listener load balancing rules.

Add the cluster core IP address for the Windows Server Failover Cluster (WSFC)

The WSFC IP address also needs to be on the load balancer.

  1. In the portal, on the same Azure load balancer, click Frontend IP configuration and click +Add. Use the IP Address you configured for the WSFC in the cluster core resources. Set the IP address as static.

  2. On the load balancer, click Health probes, and click +Add.

  3. Set the WSFC cluster core IP address health probe as follows:

    Setting Description Example
    Name Text WSFCEndPointProbe
    Protocol Choose TCP TCP
    Port Any unused port 58888
    Interval The amount of time between probe attempts in seconds 5
    Unhealthy threshold The number of consecutive probe failures that must occur for a virtual machine to be considered unhealthy 2
  4. Click OK to set the health probe.

  5. Set the load balancing rules. Click Load balancing rules, and click +Add.

  6. Set the cluster core IP address load balancing rules as follows.

    Setting Description Example
    Name Text WSFCEndPoint
    Frontend IP address Choose an address Use the address that you created when you configured the WSFC IP address. This is different from the listener IP address
    Protocol Choose TCP TCP
    Port Use the port for the cluster IP address. This is an available port that is not used for the listener probe port. 58888
    Backend Port This field is not used when Floating IP is set for direct server return 58888
    Probe The name you specified for the probe WSFCEndPointProbe
    Session Persistence Drop down list None
    Idle Timeout Minutes to keep a TCP connection open 4
    Floating IP (direct server return) Enabled

    Warning

    Direct server return is set during creation. It cannot be changed.

  7. Click OK to set the load balancing rules.

Configure the listener

The next thing to do is to configure an Availability Group listener on the failover cluster.

Note

This tutorial shows how to create a single listener - with one ILB IP address. To create one or more listeners using one or more IP addresses, see Create Availability Group listener and load balancer | Azure.

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:

  1. 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:

    Cluster Network Name

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

    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.

  3. Take the availability group cluster role offline. In Failover Cluster Manager under Roles, right-click the role, and select Stop Role.

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

    Client Access Point

    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.

    IP Resource

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

    IP Resource

    d. Click OK.

  6. Make the client access point resource dependent on the IP address.

    a. In Failover Cluster Manager, click Roles, and then click your availability group.

    b. On the Resources tab, right-click the client access point resource under Server Name, and then click Properties.

    IP Resource

    c. Click the Dependencies tab. Verify that the IP address is a dependency. If it is not, set a dependency on the IP address. If there are multiple resources listed, verify that the IP addresses have OR, not AND, dependencies. Click OK.

    IP Resource

    Tip

    You can validate that the dependencies are correctly configured. In Failover Cluster Manager, go to Roles, right-click the availability group, click More Actions, and then click Show Dependency Report. When the dependencies are correctly configured, the availability group is dependent on the network name, and the network name is dependent on the IP address.

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

    $ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
    $IPResourceName = "<IPResourceName>" # the IP Address resource name
    $ListenerILBIP = "<n.n.n.n>" # the IP Address of the Internal Load Balancer (ILB). This is the static IP address for the load balancer you configured in the Azure portal.
    [int]$ListenerProbePort = <nnnnn>
    
    Import-Module FailoverClusters
    
    Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ListenerILBIP";"ProbePort"=$ListenerProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
    

    b. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

    Note

    If your SQL Server instances are in separate regions, you need to run the PowerShell script twice. The first time, use the $ListenerILBIP and $ListenerProbePort from the first region. The second time, use the $ListenerILBIP and $ListenerProbePort from the second region. The cluster network name and the cluster IP resource name are also different for each region.

  8. Bring the availability group cluster role online. In Failover Cluster Manager under Roles, right click the role, and select Start Role.

If necessary, repeat the steps above to set the cluster parameters for the WSFC cluster IP address.

  1. Get the IP address name of the WSFC Cluster IP address. In Failover Cluster Manager under Cluster Core Resources, locate Server Name.

  2. Right-click IP Address, and select Properties.

  3. Copy the Name of the IP address. It may be Cluster IP Address.

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

    • $ClusterCoreIP is the IP address that you created on the Azure load balancer for the WSFC core cluster resource. It is different from the IP address for the availability group listener.

    • $ClusterProbePort is the port you configured on the Azure load balancer for the WSFC health probe. It is different from the probe for the availability group listener.

    $ClusterNetworkName = "<MyClusterNetworkName>" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
    $IPResourceName = "<ClusterIPResourceName>" # the IP Address resource name
    $ClusterCoreIP = "<n.n.n.n>" # the IP Address of the Cluster IP resource. This is the static IP address for the load balancer you configured in the Azure portal.
    [int]$ClusterProbePort = <nnnnn> # The probe port from the WSFCEndPointprobe in the Azure portal. This port must be different from the probe port for the availability grouop listener probe port.
    
    Import-Module FailoverClusters
    
    Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ClusterCoreIP";"ProbePort"=$ClusterProbePort;"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
    

    b. Set the cluster parameters by running the PowerShell script on one of the cluster nodes.

Warning

The availability group listener health probe port has to be different from the cluster core IP address health probe port. In these examples, the listener port is 59999 and the cluster core IP address is 58888. Both ports require an allow inbound firewall rule.

Set listener port

In SQL Server Management Studio, set the listener port.

  1. Launch SQL Server Management Studio and connect to the primary replica.

  2. Navigate to AlwaysOn High Availability | Availability Groups | Availability Group Listeners.

  3. You should now see the listener name that you created in Failover Cluster Manager. Right-click the listener name and click Properties.

  4. In the Port box, specify the port number for the Availability Group listener. 1433 is the default, then click OK.

You now have a SQL Server Availability Group in Azure virtual machines running in Resource Manager mode.

Test connection to listener

To test the connection:

  1. RDP to a SQL Server that is in the same virtual network, but does not own the replica. You can use the other SQL Server in the cluster.

  2. Use sqlcmd utility to test the connection. For example, the following script establishes a sqlcmd connection to the primary replica through the listener with Windows authentication:

    sqlcmd -S <listenerName> -E
    

    If the listener is using a port other than the default port (1433), specify the port in the connection string. For example, the following sqlcmd command connects to a listener at port 1435:

    sqlcmd -S <listenerName>,1435 -E
    

The SQLCMD connection automatically connects to whichever instance of SQL Server hosts the primary replica.

Tip

Make sure that the port you specify is open on the firewall of both SQL Servers. Both servers require an inbound rule for the TCP port that you use. For more information, see Add or Edit Firewall Rule.

Next steps