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. You can use an existing network for this task if necessary, but it is a best practice to isolate systems into different networks and subnets. This tutorial expects that you create the following VPC network and subnet configuration:

1. Create a custom mode VPC network.

   gcloud compute networks create wsfcnet --subnet-mode custom
   

2. 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
   

3. 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
   

4. 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.

1. 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
   

2. Generate a password so that you can connect to the domain controller VM using a local account. Note the username and password for future use.

3. Using RDP, connect to the domain controller VM with your local account username and password.

4. On the instance, run PowerShell as an administrator to open the PowerShell terminal.

5. Set up an Administrator user.

   1. Run the following command, then enter a password, which you will use with the Administrator account.

      PS C:\> $Password = Read-Host -AsSecureString
      

      Note the password that you entered. You will use this password for the Administrator account.

   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
      

6. 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";
   

7. 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
   

8. If the instance does not restart automatically after it creates the domain, restart the instance manually.

9. After the instance restarts, reconnect using the Administrator account with the newly created domain name (dbeng.com in this tutorial).

10. Create a domain account called sql_service, which will be used 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. You will 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. 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: cluster-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: cluster-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 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-f \
--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 an beta feature in the gcloud beta compute images create command.

1. Create a Windows Server instance.
2. On that instance, update all the components to the latest versions.
3. Set the auto-delete state to false for the boot disk for that instance.
4. Delete the instance.

5. 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
   

6. 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
   

7. After the image is created, delete the temporary disk.

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

8. 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-f \
   --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;"
   

9. 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.

1. Connect to both instances using RDP connections.

2. 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 terminate your remote desktop connection:

   • 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
     

3. 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.

4. 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 the "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.

1. 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 Platform, delete the existing RDP certificates for these addresses under the Certificates list in the Options menu.

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

   1. Open a 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
      

   2. Create the failover cluster:

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

   3. Enable AlwaysOn High Availability for both nodes in the cluster:

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

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

3. 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.

4. 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 specify an existing database for the availability group instead.

1. 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
   

2. On the Cluster Manager instance, run SQL Server Management Studio.

3. Right-click on AlwaysOn High Availability and select New Availability Group Wizard.

4. On the Specify Name page, set an availability group name. For this example, specify cluster-ag.

5. On the Select Databases page, specify which database you want to replicate. For this example, specify the TestDB database.

6. On the Specify Replicas page, set both instances as replicas with automatic failover and synchronous commit.

7. 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.

8. The Validation page generates a warning because there is no listener, but you can ignore this warning.

9. After the wizard finishes, right click the new availability group and select Add Listener.

10. Specify the parameters for this listener:

    • Listener DNS Name: cluster-listener.
    • Network Port: 1433.
    • Network Mode: Static IP.

11. 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.