Prepare your project and network

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

Configure your project and region

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

1. In the Google Cloud console, openCloud Shell by clicking theActivate Cloud Shellbutton.

   Go to the Google Cloud console

2. Initialize the following variables.

   VPC_NAME=VPC_NAMESUBNET_NAME=SUBNET_NAME

   Replace the following:

   • VPC_NAME: name of your VPC
   • SUBNET_NAME: name of your subnet

3. Set your defaultproject ID.

   gcloud config set projectPROJECT_ID

   ReplacePROJECT_ID with the ID of your Google Cloud project.

4. Set your default region.

   gcloud config set compute/regionREGION

   ReplaceREGION with the ID of theregion you want to deploy in.

Create firewall rules

To allow clients to connect to the SQL Server and the communication between thecluster nodes you need to create several firewall rules. You can usenetwork tagsto simplify the creation of these firewall rules, as follows:

• The two cluster nodes are annotated with thewsfc-node tag.
• All servers (including thewitness) are annotated with thewsfc 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 10000gcloud 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

Note: Depending on how you've deployed the Active Directory, you might need to createadditional firewall rules to allow servers to join the domain. For moreinformation, seeAccessing Managed Microsoft AD from within your VPC.

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 databasewhile 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.
• have Windows Server Failover Cluster and SQL Server installed.
• haveCompute Engine WSFC support enabled.

You use aSQL Server premium imagewhich has SQL Server 2022 preinstalled.

Note: If you plan to bring your own licenses for SQL Server by using theLicense Mobility program,select Windows Server base images for these nodes and install SQL Serverusing your own product keys.

To provide a tie-breaking vote and achieve a quorum for the failover scenario,deploy a third VM that serves as afile share witness using the following steps:

1. Return to your existing Cloud Shell session.

2. Create aspecialized scriptfor the WSFC nodes. This script installs the necessary Windows features andcreates firewall rules for WSFC and SQL Server.

   cat << "EOF" > specialize-node.ps1$ErrorActionPreference = "stop"# Install required Windows featuresInstall-WindowsFeature Failover-Clustering -IncludeManagementToolsInstall-WindowsFeature RSAT-AD-PowerShell# Open firewall for WSFCnetsh advfirewall firewall add rule name="Allow WSFC health check" dir=in action=allow protocol=TCP localport=59998# Open firewall for SQL Servernetsh advfirewall firewall add rule name="Allow SQL Server" dir=in action=allow protocol=TCP localport=1433# Open firewall for SQL Server replicationnetsh advfirewall firewall add rule name="Allow SQL Server replication" dir=in action=allow protocol=TCP localport=5022# Format data diskGet-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 Servermd d:\Datamd d:\LogsEOF

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 Clusteringby setting the metadata keyenable-wsfc totrue:

   REGION=$(gcloud config get-value compute/region)ZONE1=ZONE1ZONE2=ZONE2ZONE3=ZONE3PD_SIZE=200MACHINE_TYPE=n2-standard-8gcloud 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.ps1gcloud 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.ps1gcloud 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"

   ReplaceZONE1,ZONE2,ZONE3 based onthe zones you are using.

   Note: Depending on yourperformance requirements,consider using a machine type larger thann2-standard-8 for the WSFCnodes. Considerdisabling Simultaneous multithreading (SMT)for potential savings on licensing costs.Note: For the purpose of this tutorial, and to fit within the defaultregional SSD Persistent Disk quota, the size of the disks attached to eachVM is smaller than it would be in a production environment. For betterperformance and to accommodate a larger database, increase thesize of each disk.

4. To join the three VM instances to Active Directory, do the following for each ofthe three VM instances:

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

      gcloud compute instances tail-serial-port-outputNAME

      ReplaceNAME with the name of the VM instance.

      Wait for a few minutes until you see the outputInstance setup finished,then press Ctrl+C. At this point, the VM instance is ready to be used.

   2. Create a username and passwordfor the VM instance.

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

   4. Right-click theStart button (or pressWin+X) and clickWindows PowerShell (Admin).

      Note: In this guide, we used Powershell 5.1.

   5. Confirm the elevation prompt by clickingYes.

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

      Add-Computer -DomainDOMAIN -Restart

      ReplaceDOMAIN with the DNS name of your Active Directory domain.

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

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

Reserve static IP addresses

You now reserve two static IP addresses in your VPC. One IP address is used as thedefault WSFC cluster IP address, the other serves as the static IP for the SQLServer availability group listener.

In a WSFC cluster, the cluster IP address primarily serves administrative purposesand accessing cluster resources. This virtual IP address is assigned to the clusteritself, enabling administrators to manage the cluster and perform tasks such asconfiguring cluster settings, monitoring the health of nodes, and managingfailover processes.

In the context of SQL Server availability group, a listener is a virtual networkname (VNN) and IP address that allows clients to connect to the availability groupwithout needing to know which specific server is the primary node.

An internal load balancer needs an internal IP address to efficiently route internaltraffic and support high availability and load balancing in the context of a WSFCcluster. The internal load balancer ensures that requests are always directed tothe current primary replica of the cluster. During failover events, the loadbalancer detects changes in the primary replica and redirects client connectionsto the new primary without requiring manual intervention, minimizing downtimeand ensuring continuous availability of the database services.

In the context of a WSFC with SQL Server Always On availability groups, bothreserved internal static IP addresses for the default WSFC cluster IP addressand the availability group listenerare also used by associated internal load balancers.

1. To reserve two static IP addresses in your VPC, use the following steps.

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

2. Replace the cluster IP address in theCLUSTER_IP variable, you need it later to specify it as the cluster IP:

   CLUSTER_IP=CLUSTER_IP

3. Reserve another static IP for the availability group listener and capture theaddress in a new environment variable namedLISTENER_IP.

   gcloud compute addresses create wsfc-listener-ip \  --subnet $SUBNET_NAME \  --region $(gcloud config get-value compute/region)LISTENER_IP=$(gcloud compute addresses describe wsfc-listener-ip \  --region $(gcloud config get-value compute/region) \  --format=value\(address\)) && \echo "Listener IP: $LISTENER_IP"

   Note: Make sure that the IP address is in the same region as your SQL Server cluster.

4. Replace the load balancer's reserved IP address as theLISTENER_IP variable, you need it later to configure your availability group.

   LISTENER_IP=LISTENER_IP

Your project and VPC are now ready for the deployment of the Windows ServerFailover Cluster and SQL Server.

Deploying the failover cluster

You can now use the VM instances to deploy a Windows Server Failover Cluster and SQLServer. 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 followingsteps.

1. Connect tonode-1by using Remote Desktop.Sign in with your domain user account.
2. Right-click theStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Create a domain user account for SQL server and the SQL agent and assigna 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

   Note: If you use Managed AD, append-Path "OU=Cloud,DC=example,DC=org"to the command to create the user in theCloud organizational units (OU).Ensure that the value of the path is the output of the following command:Get-ADOrganizationalUnit -Filter "Name -eq 'Cloud'" | Select-Object -ExpandProperty DistinguishedName

To configure SQL Server, perform the following steps on bothnode-1 andnode-2, use the following steps:

1. OpenSQL Server Configuration Manager.
2. In the navigation pane, selectSQL Server Services.
3. In the list of services, right-clickSQL Server (MSSQLSERVER) and selectProperties.

4. UnderLog on as, change the account as follows:

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

5. ClickOK.

6. When prompted to restart SQL Server, selectYes.

SQL Server now runs under a domain user account.

Warning: Make sure you've completed the previous configuration steps on bothVM instances, otherwise setting up the Always On availability group will fail.

Create file shares

Create two file shares on the VM instancewitness so that it canstore SQL Server backups and act as a file share witness:

1. Connect towitness byusing Remote Desktop.Sign in with your domain user account.
2. Right-click theStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Create a witness file share and grant yourself and the two cluster nodesaccess to the file share.

   New-Item "C:\QWitness" –type directoryicacls 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 directoryNew-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 onnode-1.
2. Right-click theStart button (or pressWin+X) and clickWindows PowerShell (Admin).
3. Confirm the elevation prompt by clickingYes.

4. Create a new cluster.

   New-Cluster `  -Name sql-cluster `  -Node node-1,node-2 `  -NoStorage `  -StaticAddressCLUSTER_IP

   ReplaceCLUSTER_IP with the cluster IP addressthat you created earlier.

   Note: Whencomputer objects are created outside the containerComputers (in anOU), the permissionCreate Computer objects must be delegatedto the cluster's computer account in the OU.

5. Return to the PowerShell session onwitness and grant the virtualcomputer 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 onnode-1 and configure the clusterto use the file share onwitness as a cluster quorum.

   Set-ClusterQuorum -FileShareWitness \\witness\QWitness

7. Verify that the cluster was created successfully.

   Test-Cluster

   You might see the following warnings that you can safely ignore.

   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, ClusterConditionallyApprovedTesting has completed for the tests you selected. You should review the warnings in the Report.  A cluster solution issupported 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 thecluster's health by runningcluadmin.msc.

8. If you're using Managed AD, add the computer account used by the Windowscluster to theCloud Service Domain Join Accounts group so that it can joincomputers 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 -ForceEnable-SqlAlwaysOn -ServerInstance node-2 -Force

Creating an availability group

You now create a sample databasebookshelf, include it in a new availabilitygroup namedbookshelf-ag and configure high availability.

Creating a database

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

1. Return to the Remote Desktop session onnode-1.
2. Open theSQL Server Management Studio.
3. In theConnect to server dialog, verify the server name is set tonode-1 and selectConnect.
4. In the menu, selectFile > New > Query with current connection.

5. Paste the following SQL script into the editor:

   -- Create a sample databaseCREATE 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)GOUSE [bookshelf]SET ANSI_NULLS ONSET QUOTED_IDENTIFIER ONGO-- Create sample tableCREATE 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 backupEXEC dbo.sp_changedbowner @loginame = 'sa', @map = false;  ALTER DATABASE [bookshelf] SET RECOVERY FULL;  GO  BACKUP DATABASE bookshelf to disk = '\\witness\Backup\bookshelf.bak' WITH INITGO

   The script creates a new database with a single table and performs aninitial backup towitness.

6. SelectExecute to run the SQL script.

Configure high availability

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

Create internal load balancers and health checks

The cluster IP represents a single endpoint for the Windows failovercluster. You use it for administrative purposes and managing cluster resources.The cluster IP always points to the host (or primary) node of the cluster. Youdeploy aninternal load balancerthat uses a health check that ensures that traffic is directed to the host nodeof the cluster. As WSFC tooling requires multiple protocols to be availablefor forwarding (ICMP, UDP, and TCP), we recommend deploying an internal loadbalancer withmultiple protocols that support all ports.

To deploy an internal load balancer, use the following steps:

1. Return to your existing Cloud Shell session.

2. Create twounmanaged 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 $ZONE1gcloud compute instance-groups unmanaged add-instances wsfc-group-1 --zone $ZONE1 \  --instances node-1gcloud compute instance-groups unmanaged create wsfc-group-2 --zone $ZONE2gcloud compute instance-groups unmanaged add-instances wsfc-group-2 --zone $ZONE2 \  --instances node-2

3. Create health check for the cluster IP that the load balancer can use todetermine which is the active node from the Windows cluster perspective. Thedefault port which theCompute Engine guest agentresponds to health checks is59998. The health check provides thecluster IP address in the request and it expects1 as a response returned fromthe active node.

   gcloud compute health-checks create tcp wsfc-healthcheck \  --request=$CLUSTER_IP \  --response=1 \  --check-interval="2s" \  --healthy-threshold=2 \  --unhealthy-threshold=2 \  --port=59998 \  --timeout="1s"

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

   gcloud compute backend-services create wsfc-backend \  --load-balancing-scheme internal \  --region $REGION \  --health-checks wsfc-healthcheck \  --protocol UNSPECIFIEDgcloud compute backend-services add-backend wsfc-backend \  --instance-group wsfc-group-1 \  --instance-group-zone $ZONE1 \  --region $REGIONgcloud compute backend-services add-backend wsfc-backend \  --instance-group wsfc-group-2 \  --instance-group-zone $ZONE2 \  --region $REGION

5. Create the internal load balancer associated with the cluster IP.

   gcloud compute forwarding-rules create wsfc \  --load-balancing-scheme internal \  --address $CLUSTER_IP \  --ports ALL \  --network $VPC_NAME \  --subnet $SUBNET_NAME \  --region $REGION \  --ip-protocol L3_DEFAULT \  --backend-service wsfc-backend

To provide a single endpoint for SQL Server clients that want to connect to anydatabase in yourbookshelf availability group, deploy a new internalload balancer dedicated to that availability group, use the following steps:

1. Create a health check for the availability group listener that the loadbalancer can use to determine which is the primary node in thebookshelfSQL Server availability group.

   gcloud compute health-checks create tcp wsfc-bookshelf-healthcheck \  --request=$LISTENER_IP \  --response=1 \  --check-interval="2s" \  --healthy-threshold=1 \  --unhealthy-threshold=2 \  --port=59998 \  --timeout="1s"

   The health check uses the same Compute Engine guest agent port, but it provides in itsrequest the listener IP address of thebookshelf availabilitygroup.

2. Create a new backend service and add the two instance groups.

   gcloud compute backend-services create wsfc-bookshelf-backend \  --load-balancing-scheme internal \  --region $REGION \  --health-checks wsfc-bookshelf-healthcheck \  --protocol UNSPECIFIEDgcloud compute backend-services add-backend wsfc-bookshelf-backend \  --instance-group wsfc-group-1 \  --instance-group-zone $ZONE1 \  --region $REGIONgcloud compute backend-services add-backend wsfc-bookshelf-backend \  --instance-group wsfc-group-2 \  --instance-group-zone $ZONE2 \  --region $REGION

3. Create the internal load balancer associated with the SQL Serverbookshelf-agavailability group listener.

   gcloud compute forwarding-rules create wsfc-bookshelf \  --load-balancing-scheme internal \  --address $LISTENER_IP \  --ports ALL \  --network $VPC_NAME \  --subnet $SUBNET_NAME \  --region $REGION \  --ip-protocol L3_DEFAULT \  --backend-service wsfc-bookshelf-backend

You can now connect to SQL Server availability group listener by using the DNSnamebookshelf and its port defined in the bookshelf availability group listener.The internal load balancer directs traffic to the primary node of thebookshelf availability group.

To create multiple availability groups on a single failover cluster,you must use separate backend services and a separate load balancer with its ownhealthcheck per availability group.

Each availability group might have different nodes designated as the primary,and they might be different than the host node of the Windows cluster. Formultiple availability groups, you need the following:

• A reserved static IP address for the availability group listener that theinternal load balancer uses. Reserve one address for each availability group.

• A separate health check rule for each availability group. The request of thehealth check provides the static IP address of the availability group listener (which is also the reserved IP address at the previous step). The health checkprobes for response1 returned by the GCE agent. All health checksuse port59998.

• A separate backend service for each availability group to which you add theexisting two compute instance groups. The backend service uses the health checkdefined at the previous step.

• An internal load balancer for each availability group for the backend servicecreated at the previous step. The load balancer is associated with theavailability group listener static IP address.

Test the failover

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

1. Return to the PowerShell session onwitness.

2. Run the following script.

   while ($True){  $Conn = New-Object System.Data.SqlClient.SqlConnection  $Conn.ConnectionString = "Server=tcp:bookshelf,1433;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}

   In this guide, we used the DNS namebookshelf and the port value1433 for the availability group listener in the server definitiontcp:bookshelf,1433.

   Every 2 seconds, the script connects to SQL Server by using the availabilitygroup listener, and queries the server name.

   Leave the script running.

3. Return to the Remote Desktop session onnode-1 to trigger a failover.

   1. InSQL Server Management Studio, navigate toAlways OnHigh Availability > Availability Groups > bookshelf-ag (Primary) andright-click the node.
   2. SelectFailover.
   3. On theSelect new primary replica page, verify thatnode-2 is selectedas new primary replica and that theFailover readiness columnindicatesNo data loss. Then selectNext.
   4. On theConnect to replica page, selectConnect.
   5. In theConnect to server dialog, verify that the server name isnode-2 andclickConnect.
   6. SelectNext and thenFinish.
   7. On theResults page, verify that the failover was successful.

4. Return to the PowerShell session onwitness.

5. Observe the output of the running script and notice that the server namechanges fromnode-1 tonode-2 as a result of the failover.

6. Stop the script by pressingCtrl+C.