Menu
SQL Server with WSFC on AWS
Quick Start Reference Deployment Guide

Step 3. Configure a SQL Server Always On Availability Group

Note

If you’re using a third Availability Zone as a full SQL Server cluster node (that is, if you set the Third AZ parameter to full), take that into consideration when following the steps in this section.

After you have successfully deployed the Quick Start, you can configure the WSFC nodes by choosing and backing up a database, and creating and configuring an availability group.

Create a Test Database or Attach an Existing Database

  1. Open the Desktop Connection application (mstsc.exe) and connect to the first cluster node (e.g., WSFCNode1).

  2. Open SQL Server Management Studio (SSMS).

  3. In the Connect to Server dialog box, connect to the first cluster node (e.g., WSFCNode1).

  4. Create a new database or attach a test database.

  5. Make sure that the Recovery model on the database is set to Full.

    
       Creating a new database in SQL Management Studio

    Figure 8: Creating a new database in SQL Management Studio

  6. Open the context (right-click) menu for the database in SSMS, and then choose Tasks, Backup.

Create an availability group

  1. In Object Explorer, open the context (right-click) menu for AlwaysOn High Availability and launch the New Availability Group wizard.

    
       Creating a new availability group in SSMS

    Figure 9: Creating a new availability group in SSMS

  2. Complete the New Availability Group wizard:

    1. On the Introduction page, choose Next.

    2. On the Specify Availability Group Name page, type SQLAG1, and then choose Next.

    3. On the Select Databases page, choose the database you created or attached in the previous section, and then choose Next.

    4. On the Specify Replicas page, add the second cluster node (e.g., WSFCNode2), and then choose Automatic Failover.

      
            Specifying replicas

      Figure 10: Specifying replicas

    5. On the Listener tab, choose Create an availability group listener, provide a Listener DNS Name (e.g., AG1-Listener), and then specify the TPC port used by this listener (e.g., 1433). Add the two private subnets the cluster nodes were deployed into and a corresponding IPv4 address.

      Note

      We will use the secondary private IP addresses we assigned earlier to the nodes (e.g., 10.0.0.102 and 10.0.32.102). If you’re using the third Availability Zone with a SQL node, you’ll also want to add 10.0.64.102.

      
            Creating an availability group listener

      Figure 11: Creating an availability group listener

    6. On the Select Initial Data Synchronization page, choose Full. In the shared network location box, type \\WSFCFileServer\replica if you’re using a file share witness, or \\WSFCNode1\replica if you have three SQL nodes (that is, if you set the Third AZ parameter to full). Then choose Next.

    7. On the Validation page, choose Next.

    8. On the Summary page, choose Finish, and then close the wizard.

  3. Run Windows PowerShell as Administrator and change the availability group Listener Host Record TTL to 300.

    
       Modifying the TTL in Windows PowerShell

    Figure 12: Modifying the TTL in Windows PowerShell

  4. Open DNS Manager:

    • If you’re using AWS Directory Service:

      1. On the Remote Desktop Gateway instance, install Remote Server Administration Tools for DNS Server, by running this command in a PowerShell window with administrator privileges:

        Copy
        Install-WindowsFeature RSAT-DNS-Server
      2. In the same PowerShell window, run the command:

        Copy
        Get-NetIPConfiguration
      3. Select one of the DNS server addresses from the command output.

      4. Use that address to connect to the DNS server when you open DNS Manager.

    • If you’re not using AWS Directory Service:

      1. Open the Desktop Connection application (mstsc.exe) and connect to the primary domain controller in Availability Zone 1 using its NetBIOS name (e.g., DC1).

      2. Use the credentials of the domain admin user and domain admin password to log into the instance.

      3. Open DNS Manager.

        
           Verifying DNS configuration

        Figure 13: Verifying DNS Configuration

Note

Client connectivity to an availability group database can be established via the availability group listener. The availability group listener (in this case, AG1-Listener) is a virtual network name that clients can connect to. This configuration allows clients to connect to a database without knowing the name of an individual server in the WSFC cluster. The availability group listener can share TCP port 1433 with an individual SQL Server instance. However, when running multiple side-by-side SQL Server instances, you will need to use a non-standard port to avoid a port conflict.

The security groups and ingress rules created by the AWS CloudFormation template permit all required traffic between WSFC nodes and client connections to TCP port 1433 from the remaining server tiers within the VPC. See the Security section of this guide for a detailed list of port mappings.

When you complete the steps in this section, you will have a WSFC cluster and SQL Server Always On Availability Group successfully deployed in the AWS Cloud, as illustrated previously in Figure 1.

On this page: