Configuring SQL Server AlwaysOn availability groups with synchronous commit


Microsoft SQL Server AlwaysOn availability groups (AG) let you replicate databases across multiple SQL Server Enterprise instances.

Similar to SQL Server Failover Cluster Instances, AlwaysOn availability groups use Windows Server Failover Clustering (WSFC) to implement high availability. But the two features differ in multiple ways, including:

AlwaysOn availability groups Failover cluster instances
Scope of fail-over Group of databases Instance
Storage Not shared Shared

For a more detailed comparison, see Comparison of Failover Cluster Instances and Availability Groups.

AlwaysOn availability groups support multiple availability modes. This tutorial shows how you can deploy AlwaysOn availability groups in synchronous commit mode to implement high availability for one or more databases.

In the setup, you will create three VM instances. Two VM instances, node-1 and node-2, serve as WSFC nodes and run SQL Server. A third VM instance, witness, is used to achieve a quorum in a failover scenario. The three VM instances are distributed over three zones and share a common subnet.

Using a SQL Server AlwaysOn availability group, an example database, bookshelf, is synchronously replicated across the two SQL Server instances.

In an on-premises environment, you can let WSFC perform ARP announcements if a failover occurs to notify network equipment about an IP address change. Google Cloud, however, disregards ARP announcements. Consequently, you must implement one of the following two options:

Architecture

Architecture

The article assumes that you have already deployed Active Directory on Google Cloud and that you have basic knowledge of SQL Server, Active Directory, and Compute Engine.

Objectives

Costs

This tutorial uses billable components of Google Cloud, including:

Use the pricing calculator to generate a cost estimate based on your projected usage.

Before you begin

To complete this guide, you need the following:

  • An Active Directory domain with at least one domain controller. You can create an Active Directory domain by using Managed Microsoft AD. Alternatively, you can deploy a custom Active Directory environment on Compute Engine and set up a private DNS forwarding zone that forwards DNS queries to your domain controllers.
  • An Active Directory user that has permission to join computers to the domain and can log in by using RDP. If you're using Managed Microsoft AD, you can use the setupadmin user.
  • A Google Cloud project and VPC with connectivity to your Active Directory domain controllers.
  • A subnet to use for the WSFC VM instances.

To complete the guide, you also need a Google Cloud project:

  1. Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get $300 in free credits to run, test, and deploy workloads.
  2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  3. Make sure that billing is enabled for your Google Cloud project.

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Go to project selector

  5. Make sure that billing is enabled for your Google Cloud project.

When you finish this tutorial, you can avoid continued billing by deleting the resources you created. For more information, see Cleaning up.

Preparing the project and network

To prepare your Google Cloud project and VPC for the deployment of SQL Server AlwaysOn availability groups, do the following:

  1. In the Google Cloud console, open Cloud Shell by clicking the Activate Cloud Shell Activate Cloud Shell. button.

    Go to the Google Cloud console

  2. Initialize the following variables:

    VPC_NAME=VPC_NAME
    SUBNET_NAME=SUBNET_NAME
    

    Where:

    • VPC_NAME: name of your VPC
    • SUBNET_NAME: name of your subnet
  3. Set your default project ID:

    gcloud config set project PROJECT_ID
    

    Replace PROJECT_ID with the ID of your Google Cloud project.

  4. Set your default region:

    gcloud config set compute/region REGION
    

    Replace REGION with the ID of the region you want to deploy in.

Create firewall rules

To allow clients to connect to SQL Server, allow communication between the WSFC nodes, and to enable the load balancer to perform health checks, you need to create several firewall rules. To simplify the creation of these firewall rules, you use network tags:

  • The 2 WSFC nodes are annotated with the wsfc-node tag.
  • All servers (including the witness) are annotated with the wsfc tag.

Create firewall rules that use these network tags:

  1. Return to your existing Cloud Shell session.
  2. Create firewall rules for the WSFC nodes:

    SUBNET_CIDR=$(gcloud compute networks subnets describe $SUBNET_NAME --format=value\('ipCidrRange'\))
    
    gcloud compute firewall-rules create allow-all-between-wsfc-nodes \
    --direction=INGRESS \
    --action=allow \
    --rules=tcp,udp,icmp \
    --enable-logging \
    --source-tags=wsfc \
    --target-tags=wsfc \
    --network=$VPC_NAME \
    --priority 10000
    
    gcloud compute firewall-rules create allow-sql-to-wsfc-nodes \
    --direction=INGRESS \
    --action=allow \
    --rules=tcp:1433 \
    --enable-logging \
    --source-ranges=$SUBNET_CIDR \
    --target-tags=wsfc-node \
    --network=$VPC_NAME \
    --priority 10000
    
  3. Create a firewall rule that allows health checks from the IP ranges of the Google Cloud probers.

    gcloud compute firewall-rules create allow-health-check-to-wsfc-nodes \
       --direction=INGRESS \
       --action=allow \
       --rules=tcp \
       --source-ranges=130.211.0.0/22,35.191.0.0/16 \
       --target-tags=wsfc-node \
       --network=$VPC_NAME \
       --priority 10000
    

Create VM instances

You now deploy two VM instances for the failover cluster. At any point in time, one of these VMs hosts the primary replica of the SQL Server database while the other node hosts the secondary replica. The two VM instances must:

  • be located in the same region so that they can be accessed by an internal passthrough Network Load Balancer (not applicable for DNN).
  • have WSFC and SQL Server installed.
  • have Compute Engine WSFC support enabled.

You use a SQL Server premium image which has SQL Server 2022 preinstalled.

To provide a tie-breaking vote and achieve a quorum for the failover scenario, you deploy a third VM that serves as a file share witness.

  1. Return to your existing Cloud Shell session.
  2. Create a specialize script for the WSFC nodes. The script installs the necessary Windows feature and creates firewall rules for WSFC and SQL Server:

    cat << "EOF" > specialize-node.ps1
    
    $ErrorActionPreference = "stop"
    
    # Install required Windows features
    Install-WindowsFeature Failover-Clustering -IncludeManagementTools
    Install-WindowsFeature RSAT-AD-PowerShell
    
    # Open firewall for WSFC
    netsh advfirewall firewall add rule name="Allow SQL Server health check" dir=in action=allow protocol=TCP localport=59997
    
    # Open firewall for SQL Server
    netsh advfirewall firewall add rule name="Allow SQL Server" dir=in action=allow protocol=TCP localport=1433
    
    # Open firewall for SQL Server replication
    netsh advfirewall firewall add rule name="Allow SQL Server replication" dir=in action=allow protocol=TCP localport=5022
    
    # Format data disk
    Get-Disk |
     Where partitionstyle -eq 'RAW' |
     Initialize-Disk -PartitionStyle MBR -PassThru |
     New-Partition -AssignDriveLetter -UseMaximumSize |
     Format-Volume -FileSystem NTFS -NewFileSystemLabel 'Data' -Confirm:$false
    
    # Create data and log folders for SQL Server
    md d:\Data
    md d:\Logs
    EOF
    
  3. Create the VM instances. On the two VMs that serve as WSFC nodes, attach an additional data disk and enable the Windows Server Failover Clustering by setting the metadata key enable-wsfc to true:

    REGION=$(gcloud config get-value compute/region)
    PD_SIZE=200
    MACHINE_TYPE=n2-standard-8
    
    gcloud compute instances create node-1 \
      --zone $REGION-a \
      --machine-type $MACHINE_TYPE \
      --subnet $SUBNET_NAME \
      --image-family sql-ent-2022-win-2022 \
      --image-project windows-sql-cloud \
      --tags wsfc,wsfc-node \
      --boot-disk-size 50 \
      --boot-disk-type pd-ssd \
      --boot-disk-device-name "node-1" \
      --create-disk=name=node-1-datadisk,size=$PD_SIZE,type=pd-ssd,auto-delete=no \
      --metadata enable-wsfc=true \
      --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1
    
    gcloud compute instances create node-2 \
      --zone $REGION-b \
      --machine-type $MACHINE_TYPE \
      --subnet $SUBNET_NAME \
      --image-family sql-ent-2022-win-2022 \
      --image-project windows-sql-cloud \
      --tags wsfc,wsfc-node \
      --boot-disk-size 50 \
      --boot-disk-type pd-ssd \
      --boot-disk-device-name "node-2" \
      --create-disk=name=node-2-datadisk,size=$PD_SIZE,type=pd-ssd,auto-delete=no \
      --metadata enable-wsfc=true \
      --metadata-from-file=sysprep-specialize-script-ps1=specialize-node.ps1
    
    gcloud compute instances create "witness" \
      --zone $REGION-c \
      --machine-type n2-standard-2 \
      --subnet $SUBNET_NAME \
      --image-family=windows-2022 \
      --image-project=windows-cloud \
      --tags wsfc \
      --boot-disk-size 50 \
      --boot-disk-type pd-ssd \
      --metadata sysprep-specialize-script-ps1="add-windowsfeature FS-FileServer"
    
  4. To join the 3 VM instances to Active Directory, do the following for each of the 3 VM instances:

    1. Monitor the initialization process of the VM by viewing its serial port output:

      gcloud compute instances tail-serial-port-output NAME
      

      Replace NAME with the name of the VM instance.

      Wait about 3 minutes until you see the output Instance setup finished, then press Ctrl+C. At this point, the VM instance is ready to be used.

    2. Create a username and password for the VM instance

    3. Connect to the VM by using Remote Desktop and sign in using the username and password created in the previous step.

    4. Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).

    5. Confirm the elevation prompt by clicking Yes.

    6. Join the computer to your Active Directory domain and restart:

      Add-Computer -Domain DOMAIN -Restart
      

      Replace DOMAIN with the DNS name of your Active Directory domain.

      Wait for approximately 1 minute for the restart to complete.

Reserve cluster IP addresses

You now reserve two static IP addresses in your VPC. One IP address is used as the WSFC cluster IP address, the other is used by the internal load balancer.

  1. Reserve a static IP address that you use as cluster IP:

    gcloud compute addresses create wsfc-cluster \
      --subnet $SUBNET_NAME \
      --region $(gcloud config get-value compute/region) && \
    CLUSTER_ADDRESS=$(gcloud compute addresses describe wsfc-cluster \
        --region $(gcloud config get-value compute/region) \
        --format=value\(address\)) && \
    echo "Cluster IP: $CLUSTER_ADDRESS"
    

    Note the IP address, you need it later.

  2. Reserve another static IP for the internal load balancer and capture the address in a new environment variable named LOADBALANCER_ADDRESS:

    gcloud compute addresses create wsfc \
      --subnet $SUBNET_NAME \
      --region $(gcloud config get-value compute/region)
    
    LOADBALANCER_ADDRESS=$(gcloud compute addresses describe wsfc \
      --region $(gcloud config get-value compute/region) \
      --format=value\(address\)) && \
    echo "Load Balancer IP: $LOADBALANCER_ADDRESS"
    

    Note the IP address, you need it later.

Your project and VPC are now ready for the deployment of the WSFC and SQL Server.

Deploying the failover cluster

You now use the VM instances to deploy a WSFC and SQL Server.

Preparing SQL Server

Create a new user account in Active Directory for SQL Server:

  1. Connect to node-1 by using Remote Desktop. Sign in with your domain user account.
  2. Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).
  3. Confirm the elevation prompt by clicking Yes.
  4. Create a domain user account for SQL server and the SQL agent and assign a password:

    $Credential = Get-Credential -UserName sql_server -Message 'Enter password'
    New-ADUser `
      -Name "sql_server" `
      -Description "SQL Admin account." `
      -AccountPassword $Credential.Password `
      -Enabled $true -PasswordNeverExpires $true
    

To configure SQL Server, perform the following steps on both node-1 and node-2:

  1. Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).

    Rename the SQL server instance so that its name matches the hostname:

    $OLD_NAME = Invoke-Sqlcmd -Query "
      select @@SERVERNAME;
      GO" | ConvertTo-Csv | SELECT -Skip 2
    $OLD_NAME = $OLD_NAME.Replace('"', '')
    Invoke-Sqlcmd -Query "
      sp_dropserver '$OLD_NAME';
      GO
      sp_addserver '$env:computername', local;
      GO"
    Restart-Service -Name MSSQLSERVER
    
  2. Open SQL Server Configuration Manager.

  3. In the navigation pane, select SQL Server Services

  4. In the list of services, right-click SQL Server (MSSQLSERVER) and select Properties.

  5. Under Log on as, change the account:

    • Account name: DOMAIN\sql_server where DOMAIN is the NetBIOS name of your Active Directory domain.
    • Password: Enter the password you chose previously.
  6. Click OK.

  7. When prompted to restart SQL Server, select Yes.

SQL Server now runs under a domain user account.

Creating file shares

Create two file shares on witness so that the VM instance can store SQL Server backups and act as a file share witness:

  1. Connect to witness by using Remote Desktop. Sign in with your domain user account.
  2. Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).
  3. Confirm the elevation prompt by clicking Yes.
  4. Create a witness file share and grant yourself and the two WSFC nodes access to the file share:

    New-Item "C:\QWitness" –type directory
    
    icacls C:\QWitness\ /grant 'node-1$:(OI)(CI)(M)'
    icacls C:\QWitness\ /grant 'node-2$:(OI)(CI)(M)'
    
    New-SmbShare `
      -Name QWitness `
      -Path "C:\QWitness" `
      -Description "SQL File Share Witness" `
      -FullAccess $env:username,node-1$,node-2$
    
  5. Create another file share to store backups and grant SQL Server full access:

    New-Item "C:\Backup" –type directory
    New-SmbShare `
      -Name Backup `
      -Path "C:\Backup" `
      -Description "SQL Backup" `
      -FullAccess  $env:USERDOMAIN\sql_server
    

Deploy WSFC

You are now ready to create the failover cluster:

  1. Return to the Remote Desktop session on node-1.
  2. Right-click the Start button (or press Win+X) and click Windows PowerShell (Admin).
  3. Confirm the elevation prompt by clicking Yes.
  4. Create a new cluster:

    • For load balancer configuration
    New-Cluster `
      -Name sql-cluster `
      -Node node-1,node-2 `
      -NoStorage `
      -StaticAddress CLUSTER_ADDRESS
    

    Replace CLUSTER_ADDRESS with the cluster IP address that you created earlier.

    • For DNN configuration
    New-Cluster `
      -Name sql-cluster `
      -Node node-1,node-2 `
      -NoStorage `
      -ManagementPointNetworkType Distributed
    
  5. Return to the PowerShell session on witness and grant the virtual computer object of the cluster permission to access the file share:

    icacls C:\QWitness\ /grant 'sql-cluster$:(OI)(CI)(M)'
    Grant-SmbShareAccess `
      -Name QWitness `
      -AccountName 'sql-cluster$' `
      -AccessRight Full `
      -Force
    
  6. Return to the PowerShell session on node-1 and configure the cluster to use the file share on witness as a cluster quorum:

    Set-ClusterQuorum -FileShareWitness \\witness\QWitness
    
  7. Verify that the cluster was created successfully:

    Test-Cluster
    

    You might see some warnings that can be safely ignored:

    WARNING: System Configuration - Validate All Drivers Signed: The test reported some warnings..
    WARNING: Network - Validate Network Communication: The test reported some warnings..
    WARNING:
    Test Result:
    HadUnselectedTests, ClusterConditionallyApproved
    Testing has completed for the tests you selected. You should review the warnings in the Report.  A cluster solution is
    supported by Microsoft only if you run all cluster validation tests, and all tests succeed (with or without warnings).
    

    You can also launch the Failover Cluster Manager MMC snap-in to review the cluster's health by running cluadmin.msc.

  8. If you're using Managed AD, add the computer account used by WSFC to the Cloud Service Domain Join Accounts group so that it can join computers to the domain:

    Add-ADGroupMember `
      -Identity "Cloud Service Domain Join Accounts" `
      -Members sql-cluster$
    
  9. Enable AlwaysOn availability groups on both nodes:

    Enable-SqlAlwaysOn -ServerInstance node-1 -Force
    Enable-SqlAlwaysOn -ServerInstance node-2 -Force
    

Creating an availability group

You now create a sample database bookshelf, include it in a new availability group named bookshelf-ag and configure high availability.

Creating a database

Create a new database. For the purpose of this tutorial, the database doesn't need to contain any data.

  1. Return to the Remote Desktop session on node-1.
  2. Open the SQL Server Management Studio.
  3. In the Connect to server dialog, verify the server name is set to NODE-1 and select Connect.
  4. In the menu, select File > New > Query with current connection.
  5. Paste the following SQL script into the editor:

    -- Create a sample database
    CREATE DATABASE bookshelf ON PRIMARY (
      NAME = 'bookshelf',
      FILENAME='d:\Data\bookshelf.mdf',
      SIZE = 256MB,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 256MB)
    LOG ON (
      NAME = 'bookshelf_log',
      FILENAME='d:\Logs\bookshelf.ldf',
      SIZE = 256MB,
      MAXSIZE = UNLIMITED,
      FILEGROWTH = 256MB)
    GO
    
    USE [bookshelf]
    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- Create sample table
    CREATE TABLE [dbo].[Books] (
      [Id] [bigint] IDENTITY(1,1) NOT NULL,
      [Title] [nvarchar](max) NOT NULL,
      [Author] [nvarchar](max) NULL,
      [PublishedDate] [datetime] NULL,
      [ImageUrl] [nvarchar](max) NULL,
      [Description] [nvarchar](max) NULL,
      [CreatedById] [nvarchar](max) NULL,
      CONSTRAINT [PK_dbo.Books] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    
    -- Create a backup
    EXEC dbo.sp_changedbowner @loginame = 'sa', @map = false;
      ALTER DATABASE [bookshelf] SET RECOVERY FULL;
      GO
      BACKUP DATABASE bookshelf to disk = '\\witness\Backup\bookshelf.bak' WITH INIT
    GO
    

    The script creates a new database with a single table and performs an initial backup to witness.

  6. Select Execute to run the SQL script.

Configuring high availability

  1. In the Object Explorer window, right-click AlwaysOn High Availability and then select New Availability Group Wizard.
  2. On the Specify Options page, set the availability group name to bookshelf-ag, then select Next.
  3. On the Select Databases page, select the bookshelf database, then select Next.
  4. On the Specify Replicas page, select the Replicas tab:

    1. Select Add replica.
    2. In the Connect to server dialog, enter the server name node-2 and select Connect.

      The list of availability replicas now contains SQL Server instances, node-1 and node-2.

    3. Set the Availability mode to Synchronous commit for both instances.

    4. Set Automatic failover to Enabled for both instances.

    5. Select the Listener tab **

      1. Select Create an availability group listener
      2. Enter the following settings:

        • Listener DNS name: bookshelf
        • Port: 1433
        • Network mode: Static IP
      3. Select Add and enter the load balancer IP address that you created earlier. Then select OK.

    6. Select Next.

  5. On the Select Data Synchronization page, select Automatic Seeding.

  6. On the Validation page, verify that all checks are successful.

  7. On the Summary page, select Finish.

  8. On the Results page, select Close.

Configure health checks

As a final step, configure the cluster to expose a health check endpoint that can be used by an internal load balancer:

  1. Return to the PowerShell session on node-1.
  2. Initialize a variable with the IP address of the load balancer.

    $LoadBalancerIP = 'IP_ADDRESS'
    

    Replace IP_ADDRESS with the IP address of the wsfc address that you reserved earlier.

  3. Configure the Failover Cluster to respond to the health check service:

    $SqlIpAddress = Get-ClusterResource |
      Where-Object {$_.ResourceType -eq "IP Address"} |
      Where-Object {$_.Name.StartsWith("bookshelf")}
    
    $SqlIpAddress | Set-ClusterParameter -Multiple @{
     'Address'= $LoadBalancerIP;
     'ProbePort'= 59997;
     'SubnetMask'='255.255.255.255';
     'Network'= (Get-ClusterNetwork).Name;
     'EnableDhcp'=0; }
    
  4. Restart the cluster resource:

    $SqlIpAddress | Stop-ClusterResource
    $SqlIpAddress | Start-ClusterResource
    

Create an internal load balancer

To provide a single endpoint for SQL Server clients, you now deploy an internal load balancer. The load balancer uses a health check which ensures that traffic is directed to the active node of the WSFC.

  1. Return to your existing Cloud Shell session.
  2. Create two unmanaged instance groups, one per zone, and add the two nodes to the groups:

    REGION=$(gcloud config get-value compute/region)
    
    gcloud compute instance-groups unmanaged create wsfc-group-1 --zone $REGION-a
    gcloud compute instance-groups unmanaged add-instances wsfc-group-1 --zone $REGION-a \
      --instances node-1
    
    gcloud compute instance-groups unmanaged create wsfc-group-2 --zone $REGION-b
    gcloud compute instance-groups unmanaged add-instances wsfc-group-2 --zone $REGION-b \
      --instances node-2
    
  3. Create a health check that the load balancer can use to determine which is the active node.

    gcloud compute health-checks create tcp wsfc-healthcheck \
      --check-interval="2s" \
      --healthy-threshold=1 \
      --unhealthy-threshold=2 \
      --port=59997 \
      --timeout="1s"
    

    The health check probes port 59997, which is the port you previously configured as ProbePort for the availability group listener.

  4. Create a backend service and add the two instance groups:

    gcloud compute backend-services create wsfc-backend \
      --load-balancing-scheme internal \
      --region $(gcloud config get-value compute/region) \
      --health-checks wsfc-healthcheck \
      --protocol tcp
    
    gcloud compute backend-services add-backend wsfc-backend \
      --instance-group wsfc-group-1 \
      --instance-group-zone $REGION-a \
      --region $REGION
    
    gcloud compute backend-services add-backend wsfc-backend \
      --instance-group wsfc-group-2 \
      --instance-group-zone $REGION-b \
      --region $REGION
    
  5. Create the internal load balancer:

    gcloud compute forwarding-rules create wsfc-sql \
      --load-balancing-scheme internal \
      --address $LOADBALANCER_ADDRESS \
      --ports 1433 \
      --network $VPC_NAME \
      --subnet $SUBNET_NAME \
      --region $REGION \
      --backend-service wsfc-backend
    

You can now connect to SQL Server availability group listener by using the internal load balancer and the DNS name bookshelf.

To create multiple availability groups on a single failover cluster, you must use a separate load balancer with its own healthcheck per availability group. This means that for each availability group, you need:

  1. A reserved internal IP address (one for each availability group) to be used for the listener and the internal load balancer.

  2. A separate load balancer (one for each availability group) with its own IP address reserved in the previous step.

  3. A separate probe port (such as 59997, 59998, and 59999 for each availability group) and firewall rule to allow traffic on those probe ports.

  4. A separate health check rule for each availability group using the respective probe ports.

Configure a DNN listener for an availability group

Similar to the internal load balancing configuration, DNN listener serves as a single endpoint for SQL Server clients.

  1. Return to the PowerShell session on node-1.
  2. Execute the following script to create a DNN listener.

      $Ag='bookshelf-ag'
      $Port='DNN_PORT'
      $Dns='DNN_NAME'
    
      # create the DNN resource with the port as the resource name
      Add-ClusterResource -Name $Port -ResourceType "Distributed Network Name" -Group $Ag
    
      # set the DNS name of the DNN resource
      Get-ClusterResource -Name $Port | Set-ClusterParameter -Name DnsName -Value $Dns
    
      # start the DNN resource
      Start-ClusterResource -Name $Port
    
      # add the Dependency from availability group resource to the DNN resource
      Set-ClusterResourceDependency -Resource $Ag -Dependency "[$Port]"
    
      #bounce the AG resource
      Stop-ClusterResource -Name $Ag
      Start-ClusterResource -Name $Ag
    

    Replace DNN_PORT with the DNN listener port. The DNN listener port must be configured with a unique port. For more information, see Port considerations.

    Replace DNN_NAME with the DNN listener name.

  3. Create firewall rules for DNN listener port on both node-1 and node-2.

      netsh advfirewall firewall add rule name="Allow DNN listener" dir=in action=allow protocol=TCP localport=DNN_PORT
    

Testing failover

You are now ready to test if the failover works as expected:

  1. Return to the PowerShell session on witness.
  2. Run the following script:

    while ($True){
      $Conn = New-Object System.Data.SqlClient.SqlConnection
      $Conn.ConnectionString = "Server=LISTENER;Integrated Security=true;Initial Catalog=master"
      $Conn.Open()
    
      $Cmd = New-Object System.Data.SqlClient.SqlCommand
      $Cmd.Connection = $Conn
      $Cmd.CommandText = "SELECT @@SERVERNAME"
    
      $Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Cmd
      $Data = New-Object System.Data.DataSet
      $Adapter.Fill($Data) | Out-Null
      $Data.Tables[0] + (Get-Date -Format "MM/dd/yyyy HH:mm:ss")
    
      Start-Sleep -Seconds 2
    }
    

    Replace LISTENER with the listener DNS name or the DNN listener, and port of the availability group.

    Every 2 seconds, the script connects to SQL Server by using the availability group listener or DNN listener, and queries the server name.

    Leave the script running.

  3. Return to the Remote Desktop session on node-1 to trigger a failover:

    1. In SQL Server Management Studio, navigate to AlwaysOn High Availability > Availability Groups > bookshelf-ag (Primary) and right-click the node.
    2. Select Failover.
    3. On the Select new primary replica page, verify that node-2 is selected as new primary replica and that the Failover readiness column indicates No data loss. Then select Next.
    4. On the Connect to replica page, select Connect.
    5. In the Connect to server dialog, verify that the server name is node-2 and click Connect.
    6. Select Next and then Finish.
    7. On the Results page, verify that the failover was successful.
  4. Return to the PowerShell session on witness.

  5. Observe the output of the running script and notice that the server name changes from node-1 to node-2 as a result of the failover.

  6. Stop the script by pressing Ctrl+C.

Clean up

After you finish the tutorial, you can clean up the resources that you created so that they stop using quota and incurring charges. The following sections describe how to delete or turn off these resources.

Deleting the project

The easiest way to eliminate billing is to delete the project that you created for the tutorial.

To delete the project:

  1. In the Google Cloud console, go to the Manage resources page.

    Go to Manage resources

  2. In the project list, select the project that you want to delete, and then click Delete.
  3. In the dialog, type the project ID, and then click Shut down to delete the project.

What's next