Configuring SQL Server AlwaysOn Availability Groups

If you run multiple SQL Server Enterprise instances on Compute Engine, you can configure those instances to use Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups to support high availability and disaster recovery.

This tutorial shows you how to create a cluster of instances with SQL Server Enterprise with the necessary network settings and configure those instances to use Windows Server Failover Clustering and SQL Server AlwaysOn Availability Groups. You can use SQL Server Enterprise instances created from either the SQL Server public images or Microsoft License Mobility.

Prerequisites

This tutorial assumes that you understand the following items:

You are familiar with Compute Engine VPC networks and firewall rules.
You are familiar with creating Windows instances on Compute Engine.
You are familiar with setting up Active Directory on Compute Engine.

Setting up the VPC network

Create a custom mode VPC network with three subnets. Then, set the firewall rules to allow traffic between internal virtual machines (VMs). You can use an existing network for this task if necessary, but we recommend you isolate systems into different networks and subnets. This tutorial expects that you create the following VPC network and subnet configuration:

Create a custom mode VPC network.

gcloud compute networks create wsfcnet --subnet-mode custom

Add three subnets to the VPC network.

gcloud compute networks subnets create wsfcsubnet1 --network wsfcnet \
  --region us-central1 --range 10.0.0.0/24

gcloud compute networks subnets create wsfcsubnet2 --network wsfcnet \
  --region us-central1 --range 10.1.0.0/24

gcloud compute networks subnets create wsfcsubnet3 --network wsfcnet \
  --region us-central1 --range 10.2.0.0/24

Create a firewall rule to allow traffic between the instances on internal IP addresses on the new VPC network.

gcloud compute firewall-rules create allow-internal-ports \
  --network wsfcnet --allow tcp:1-65535,udp:1-65535,icmp \
  --source-ranges 10.0.0.0/24,10.1.0.0/24,10.2.0.0/24

Create a firewall rule to allow RDP on port 3389 on the VPC network.

gcloud compute firewall-rules create allow-rdp --network wsfcnet \
  --allow tcp:3389 --source-ranges 0.0.0.0/0

Creating a Windows domain controller

Create a Windows domain controller. For this tutorial, the domain is dbeng.com and the name of the domain controller is dc-windows at IP address 10.2.0.100. The domain controller is using the wsfcsubnet3 subnet.

Create an instance to use as a domain controller. For this tutorial, specify a small n1-standard-2 machine type and the latest image from the windows-2016 image family.

gcloud compute instances create dc-windows --machine-type n1-standard-2 \
  --boot-disk-type pd-ssd --image-project windows-cloud \
  --image-family windows-2016 --boot-disk-size 200GB \
  --zone us-central1-f --subnet wsfcsubnet3 --private-network-ip=10.2.0.100

Generate a password so that you can connect to the domain controller VM by using a local account. Note the username and password for future use.
Using RDP, connect to the domain controller VM with your local account username and password.
On the instance, run PowerShell as an administrator to open the PowerShell terminal.
Set up an Administrator user.
1. Run the following command, then enter a password for use with the Administrator account.
```
PS C:\> $Password = Read-Host -AsSecureString
```
  Note the password that you entered.
2. Set the Administrator account password.
```
PS C:\> Set-LocalUser -Name Administrator -Password $Password
```
3. Enable the Administrator account.
```
PS C:\> Enable-LocalUser -Name Administrator
```

Set the following variables:

PS C:\> $DomainName = "dbeng.com";

PS C:\> $DomainMode = "Win2012R2";

PS C:\> $ForestMode = "Win2012R2";

PS C:\> $DatabasePath = "C:\Windows\NTDS";

PS C:\> $LogPath = "C:\Windows\NTDS";

PS C:\> $SysvolPath = "C:\Windows\SYSVOL";

Install the following Active Directory tools:

PS C:\> Install-WindowsFeature -Name AD-Domain-Services -IncludeManagementTools

PS C:\> Install-ADDSForest -CreateDnsDelegation:$false -DatabasePath $DatabasePath -LogPath $LogPath -SysvolPath $SysvolPath -DomainName $DomainName -DomainMode $DomainMode -ForestMode $ForestMode -InstallDNS:$true -NoRebootOnCompletion:$false -SafeModeAdministratorPassword ((Get-Credential).Password) -Force:$true

If the instance does not restart automatically after it creates the domain, restart the instance manually.
After the instance restarts, reconnect by using the Administrator account with the newly created domain name (dbeng.com in this tutorial).
Create a domain account, called sql_service, on which to run SQL Server:
1. Securely enter a password for use with the SQL Server service account.
```
 PS C:> $Password = Read-Host -AsSecureString
 
```
  Note the password that you entered. Use this password for the SQL Server service account.
2. Create the SQL Server service account and set its password.
```
PS C:\> New-LocalUser -Name "sql_service" -Description "SQL Agent and SQL Admin account."  -Password $Password
```

The domain controller is configured, and you can disconnect from the dc-windows instance. Next, create the instances for the availability group.

Creating instances for your availability group

Create SQL Server instances for an availability group. There are length limitations for the Availability Group name.

These instances use the following IP address configurations with alias IPs:

Instance name: cluster-sql1
- Internal IP address: 10.0.0.4
- Windows Failover Cluster name: cluster-ag
- Failover Cluster IP address: 10.0.0.5 (alias IP)
- Availability Group name: cl-listener
- Availability Group Listener address: 10.0.0.6 (alias IP)
Instance name: cluster-sql2
- Internal IP address: 10.1.0.4
- Windows Failover Cluster name: cluster-ag
- Failover Cluster IP address: 10.1.0.5 (alias IP)
- Availability Group name: cl-listener
- Availability Group Listener address: 10.1.0.6 (alias IP)

Create the SQL Server instances using one of the following methods:

Create a SQL Server instance using a public image
Bring your existing licenses to Compute Engine using license mobility through Software Assurance and apply those licenses on top of Windows Server public images.

SQL Server public images

Create two instances from public SQL Server images. For this example, name the instances cluster-sql1 and cluster-sql2. Specify a 200GB boot disk size and an n1-highmem-4 machine type. SQL Server instances usually require more compute resources than the domain controller instance. If you need additional compute resources later, you can change the machine type for these instances. If you need additional storage space, add a disk or resize a persistent boot disk. In larger availability groups, you can create several instances.

Additionally, include the --metadata sysprep-specialize-script-ps1 flag to run a PowerShell command during instance creation that installs the Failover-Clustering feature.

For this example, create two instances using the following commands:

gcloud compute instances create cluster-sql1 --machine-type n1-highmem-4 \
  --boot-disk-type pd-ssd --boot-disk-size 200GB \
  --image-project windows-sql-cloud --image-family sql-ent-2016-win-2016 \
  --zone us-central1-a \
  --network-interface "subnet=wsfcsubnet1,private-network-ip=10.0.0.4,aliases=10.0.0.5;10.0.0.6" \
  --can-ip-forward --metadata sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"

gcloud compute instances create cluster-sql2 --machine-type n1-highmem-4 \
  --boot-disk-type pd-ssd --boot-disk-size 200GB \
  --image-project windows-sql-cloud --image-family sql-ent-2016-win-2016 \
  --zone us-central1-f \
  --network-interface "subnet=wsfcsubnet2,private-network-ip=10.1.0.4,aliases=10.1.0.5;10.1.0.6" \
  --can-ip-forward --metadata sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"

For larger availability groups, you can create additional instances with the appropriate IP addresses.

Existing licenses

If you want to bring over your own licenses for SQL Server Enterprise based on Microsoft License Mobility through software assurance, create a Windows image with --guestOSFeatures enabled. Then, install SQL Server Enterprise manually later.

Note: This cluster configuration method requires the --guestOSFeatures option, which is a beta feature in the gcloud beta compute images create command.

Create a Windows Server instance.
On that instance, update all the components to the latest versions.
Set the auto-delete state to false for the boot disk for that instance.
Delete the instance.
Create a temporary disk with a standard Windows Server image on it.

gcloud compute disks create windows-2012-disk-new --size 200 \
  --zone us-central1-f --type pd-ssd --image-family windows-2012-r2 \
  --image-project windows-cloud

From the new disk, create a custom Windows Server image with MULTI_IP_SUBNET enabled in the guestOSFeatures parameter.

gcloud beta compute images create windows-server-2012-r2-dc-new \
  --source-disk windows-2012-disk-new --source-disk-zone us-central1-f \
  --guest-os-features MULTI_IP_SUBNET

After the image is created, delete the temporary disk.

gcloud compute disks delete windows-2012-disk-new --zone us-central1-f

Use the custom image to create the new SQL Server instances for your availability group. SQL Server instances usually require more compute resources than the domain controller instance, so specify at least an n1-highmem-4 machine type. If you need additional compute resources later, you can change the machine type for these instances. If you need additional storage space, add a disk or resize persistent boot disk. In larger availability groups, you can create several instances. For this example, create two instances.

gcloud compute instances create cluster-sql1 --machine-type n1-highmem-4 \
  --boot-disk-type pd-ssd --boot-disk-size 200GB \
  --image windows-server-2012-r2-dc-new --zone us-central1-a \
  --subnet wsfcsubnet1 --private-network-ip=10.0.0.4 --can-ip-forward \
  --metadata sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"

gcloud compute instances create cluster-sql2 --machine-type n1-highmem-4 \
  --boot-disk-type pd-ssd --boot-disk-size 200GB \
  --image windows-server-2012-r2-dc-new --zone us-central1-f \
  --subnet wsfcsubnet2 --private-network-ip=10.1.0.4 --can-ip-forward \
  --metadata sysprep-specialize-script-ps1="Install-WindowsFeature Failover-Clustering -IncludeManagementTools;"

On each of the instances, install SQL Server Enterprise manually.

After you create the instances, configure them so that they can function as an availability group.

Connect to both instances using RDP connections.

Change both instances to use static IP addresses and set the netmask to 255.255.0.0. Open a PowerShell terminal as an administrator and set the static IP addresses to static. These commands might end your remote desktop connection and if so, you will have to connect again:

Instance 1:

PS C:\> netsh interface ip set address name=Ethernet static 10.0.0.4 255.255.0.0 10.0.0.1 1

PS C:\> netsh interface ip set dns Ethernet static 10.2.0.100

PS C:\> netsh advfirewall firewall add rule name="Open Port 5022 for Availability Groups" dir=in action=allow protocol=TCP localport=5022

PS C:\> netsh advfirewall firewall add rule name="Open Port 1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433

Instance 2:

PS C:\> netsh interface ip set address name=Ethernet static 10.1.0.4 255.255.0.0 10.1.0.1 1

PS C:\> netsh interface ip set dns Ethernet static 10.2.0.100

PS C:\> netsh advfirewall firewall add rule name="Open Port 5022 for Availability Groups" dir=in action=allow protocol=TCP localport=5022

PS C:\> netsh advfirewall firewall add rule name="Open Port 1433 for SQL Server" dir=in action=allow protocol=TCP localport=1433

Add both instances to the Windows domain. Open a PowerShell as an administrator and run the following Add-Computer command on both instances:
```
PS C:\> Add-Computer -DomainName "dbeng.com" -Credential "dbeng.com\Administrator" -Restart -Force
```
The command prompts you for your credentials. When the command finishes running, the instance restarts.
Reconnect to your instances using RDP, to set the SQL Server service accounts:
1. Open SQL Server Configuration Manager.
2. Select the SQL Server services tab, right-click SQL Server, and then click Properties.
3. Set the account and password for sql_service.

The instances are now created for the availability group. Next, configure the Failover Cluster Manager.

Configuring the Failover Cluster Manager

Enable failover clustering on the instances in your availability group, and configure one instance to act as the Failover Cluster Manager. Enable AlwaysOn High Availability on all instances in the group.

Reconnect to your instances using RDP, but use the Domain Administrator credentials. For this example, the domain is dbeng and the administrator account is Administrator. If you are using Chrome RDP for Google Cloud, in the Options menu, under the Certificates list, delete the existing RDP certificates for these addresses.

Select one of your instances and configure it to run as the Failover Cluster Manager.

Open PowerShell as an administrator and set variables that reflect your cluster environment. For this example, set the following variables:

PS C:\> $node1 = "cluster-sql1"

PS C:\> $node2 = "cluster-sql2"

PS C:\> $nameWSFC = "cluster-dbclus" #Name of cluster

PS C:\> $ipWSFC1 = "10.0.0.5" #IP address of cluster in subnet 1

PS C:\> $ipWSFC2 = "10.1.0.5" #IP address of cluster in subnet 2

Create the failover cluster:

PS C:\> New-Cluster -Name $nameWSFC -Node $node1, $node2 -NoStorage -StaticAddress $ipWSFC1, $ipWSFC2

Enable AlwaysOn High Availability for both nodes in the cluster:

PS C:\> Enable-SqlAlwaysOn -ServerInstance $node1 -Force

PS C:\> Enable-SqlAlwaysOn -ServerInstance $node2 -Force

On the secondary instance where there is no Cluster Manager, create a backup folder at C:\SQLBackup and share the folder as \\cluster-sql2\SQLBackup to the sql_service account for both read and write.
On both instances, create two folders at C:\SQLData and C:\SQLLog. Use these folders for the database data and log files.

The Failover Cluster Manager is ready. Next, create the availability group.

Creating the availability group

Create a test database and configure it to work with a new availability group. Alternatively, you can instead specify an existing database for the availability group.

If you do not have a database configured already, create a test database. On the Cluster Manager instance, run the SQL Server Management Studio and create a test database with the following SQL command:

CREATE DATABASE TestDB
ON PRIMARY (NAME = 'TestDB_Data', FILENAME='C:\SQLData\TestDB_Data.mdf', SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB )
LOG ON (NAME = 'TestDB_Log', FILENAME='C:\SQLLog\TestDB_Log.ldf', SIZE = 256MB, MAXSIZE = UNLIMITED, FILEGROWTH = 256MB )
GO
USE [TestDB]
Exec dbo.sp_changedbowner @loginame = 'sa', @map = false;
  ALTER DATABASE [TestDB] SET RECOVERY FULL;
  GO
  BACKUP DATABASE TestDB to disk = '\\cluster-sql2\SQLBackup\TestDB.bak' WITH INIT
GO

On the Cluster Manager instance, run SQL Server Management Studio.
Right-click AlwaysOn High Availability and select New Availability Group Wizard.
On the Specify Name page, set an availability group name. For this example, specify cluster-ag.
On the Select Databases page, specify which database you want to replicate. For this example, specify the TestDB database.
On the Specify Replicas page, set both instances as replicas with automatic failover and synchronous commit.
On the Select Data Synchronization page, specify the network share to keep the backup of the database for initial synchronization. For this example, specify \\cluster-sql2\SQLBackup.
The Validation page generates a warning because there is no listener, but you can ignore this warning.
After the wizard finishes, right-click the new availability group and select Add Listener.
Specify the parameters for this listener:
- Listener DNS Name: cluster-listener.
- Network Port: 1433.
- Network Mode: Static IP.
Add two subnet and IP address fields. For this example, use the following subnet and IP address pairs:
- 10.0.0.0/16 and 10.0.0.6
- 10.1.0.0/16 and 10.1.0.6

Now you can connect to SQL Server using cluster-listener as the name of the SQL Server database instead of the name of the instances. This connection points to the instance that is currently active.

What's next

If your instances require more compute resources, change the machine type of the instance.
If you require more storage space, add a disk or resize persistent boot disk.