Configure SQL Server Always On availability groups with synchronous commit using a distributed network name

Prepare your project and network

To deploy your SQL Server Always On availability groups, you must prepare your Google Cloud project and VPC for the deployment. The following sections discuss how you can do this in detail.

Configure your project and region

To prepare your Google Cloud project for the deployment of SQL Server Always On availability groups, do the following:

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

   Go to the Google Cloud console

2. Initialize the following variables.

   VPC_NAME=VPC_NAME
   SUBNET_NAME=SUBNET_NAME
   

   Replace the following:

   • 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 the SQL Server and the communication between the cluster nodes you need to create several firewall rules. You can use network tags to simplify the creation of these firewall rules, as follows:

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

To create firewall rules that use these network tags, use the following steps:

1. Return to your existing Cloud Shell session.

2. Create firewall rules to allow traffic between cluster 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
   

Create VM instances

Create and 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:

• have failover clustering 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, deploy a third VM that serves as a file share witness using the following steps:

1. Return to your existing Cloud Shell session.

2. Create a specialized script for the WSFC nodes. This script installs the necessary Windows features 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 WSFC health check" dir=in action=allow protocol=TCP localport=59998
   
   # 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 cluster 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)
   ZONE1=ZONE1
   ZONE2=ZONE2
   ZONE3=ZONE3
   PD_SIZE=200
   MACHINE_TYPE=n2-standard-8
   
   gcloud compute instances create node-1 \
     --zone $ZONE1 \
     --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 $ZONE2 \
     --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 $ZONE3 \
     --machine-type e2-medium \
     --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"
   

   Replace ZONE1, ZONE2, ZONE3 based on the zones you are using.

4. To join the three VM instances to Active Directory, do the following for each of the three 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 for a few 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.

   7. Enter the credentials of an account that has permissions to join a VM to the domain

      Wait for the VM to restart. You have now joined the VM instance to the Active Directory.

Deploying the failover cluster

You can now use the VM instances to deploy a Windows Server Failover Cluster and SQL Server. The following sections discuss how you can do this in detail.

Preparing SQL Server

Create a new user account in Active Directory for SQL Server using the following steps.

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, use the following steps:

1. Open SQL Server Configuration Manager.
2. In the navigation pane, select SQL Server Services.
3. In the list of services, right-click SQL Server (MSSQLSERVER) and select Properties.

4. Under Log on as, change the account as follows:

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

5. Click OK.

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

SQL Server now runs under a domain user account.

Create file shares

Create two file shares on the VM instance witness so that it 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 cluster 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
   

Create the failover cluster

To create the failover cluster, use the following steps:

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.

   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 the Windows cluster 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 Always On 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.

Configure high availability

You can now configure high availability for the availability group using either T-SQL or Server Management Studio.

Configure a DNN listener for the availability group

A DNN listener serves as a single endpoint for SQL Server clients. To configure a DNN listener, use the following steps:

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]"
   
     # restart the availability group 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
   

Test the 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=DNN_NAME,DNN_PORT;Integrated Security=true;Initial Catalog=master"
     $Conn.Open()
   
     $Cmd = New-Object System.Data.SqlClient.SqlCommand
     $Cmd.Connection = $Conn
     $Cmd.CommandText = "SELECT SERVERPROPERTY('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 DNN_NAME with the DNN listener name and DNN_PORT with the DNN listener port.

   Every 2 seconds, the script connects to SQL Server by using the availability group 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 Always On 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.