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:

• have failover clustering 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.

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 `  -ManagementPointNetworkType Distributed

   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 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, 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 Windows clusterto 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

Note: To get a single cluster endpoint for administrative purposes and managingcluster resources, you can use the DNS Name of the cluster returned by runningtheResolve-DnsName -Name sql-cluster Powershell command.

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 Server Management Studio.

Configure a DNN listener for the availability group

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

1. Return to the PowerShell session onnode-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

   ReplaceDNN_PORT with the DNN listener port. The DNN listener port must be configured with a unique port. For more information, seePort considerations.

   ReplaceDNN_NAME with the DNN listener name.

3. Create firewall rules for DNN listener port on bothnode-1 andnode-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 onwitness.

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}

   ReplaceDNN_NAME with the DNN listener name andDNN_PORT with the DNN listener port.

   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.