SQL Server with WSFC on AWS
SQL Server with WSFC Quick Start

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 set up permissions for AWS Directory Service (if applicable), configure the WSFC nodes by choosing and backing up a database, and create and configure an availability group.

Log in to a Node as a Domain Administrator

Before you continue, log in to one of the nodes as a domain administrator. (Note that the domain administrator account is different from the local administrator account.) For credentials, use the default user name StackAdmin or the user name you set for the Domain Admin User Name (DomainAdminUser) parameter, and use the password you set for the Domain Admin Password (DomainAdminPassword) parameter in step 2.

If you’re using AWS Directory Service, the delegated domain administrator user name is Admin and the password is the one you set for the Domain Admin Password (DomainAdminPassword) parameter.

Set up Permissions for the Cluster Object

If you’re using the AWS Directory Service (that is, if the AD Scenario Type parameter in step 2 is set to AWS Directory Service for Microsoft AD, which is the default), follow these steps to set up permissions:

  1. Run Windows PowerShell as an administrator, and use the following command to install Active Directory Management Services:

    Add-WindowsFeature RSAT-ADDS-Tools
  2. Open Active Directory Users and Computers on one of your two cluster node instances.

  3. In the navigation bar, choose View, Advanced Features to see the advanced features for Active Directory Users and Computers.

  4. Expand the organizational unit (OU) for your domain name.

  5. Open the context (right-click) menu for the Computers OU within your domain name, and then choose Properties.

  6. On the Security tab, choose Advanced.

  7. In the Advanced Security Settings dialog box, choose Add.

  8. Next to Principal, choose Select a principal.

  9. Choose Object Types, select Computers, and then choose OK.

  10. Type the name of your cluster object (WSFCLUSTER1 for the default name), choose Check Names, and then choose OK.

  11. When your object has been verified, choose OK.

  12. Add the Create Computer objects permission to this principal, and then choose OK.

  13. In the Advanced Security Settings for Computers screen, choose OK.

  14. In the Computer Properties screen, choose OK.

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 an 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:

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

        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: