Configuring SQL Server 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:

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. You can specify any machine type and Windows Server version that you need. For the domain controller, specify a small n1-standard-1 machine type and the latest image from the windows-2012-r2 image family.

    gcloud compute instances create dc-windows --machine-type n1-standard-1 \
    --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. Connect to the domain controller instance using RDP

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

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

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

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

  7. After the instance restarts, reconnect to the instance using RDP. Use your domain credentials, which match your original credentials.

  8. Run "Active Directory Users and Computers" and reset the password for "Administrator". Be sure to uncheck the "User must change password at next logon" option.

  9. Disconnect from the instance and reconnect using the Administrator account.

  10. Create a domain account called "sql.service", which will be used to run SQL Server. Uncheck the "User must change password at next logon" option as well as the "Password never expires" option.

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:

  • Instance name: cluster-sql1
    • Internal IP address: 10.0.0.4
    • Windows Failover Cluster name: cluster-ag
    • Failover Cluster IP address: 10.0.1.4
    • Availability Group name: cluster-listener
    • Availability Group Listener address: 10.0.1.5
  • Instance name: cluster-sql2
    • Internal IP address: 10.1.0.4
    • Windows Failover Cluster name: cluster-ag
    • Failover Cluster IP address: 10.1.1.4
    • Availability Group name: cluster-listener
    • Availability Group Listener address: 10.1.1.5

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

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.

Addionally, 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 --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-project windows-sql-cloud --image-family sql-ent-2016-win-2016 \
--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;"

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.

  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:

    • 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. On your local workstation use the gcloud routes create command to add routes for the Windows cluster and availability groups.

    gcloud compute routes create cluster-sql1-route --network wsfcnet \
    --destination-range 10.0.1.4/32 --next-hop-instance cluster-sql1 \
    --next-hop-instance-zone us-central1-f --priority 1
    

    gcloud compute routes create cluster-sql2-route --network wsfcnet \
    --destination-range 10.1.1.4/32 --next-hop-instance cluster-sql2 \
    --next-hop-instance-zone us-central1-f --priority 1
    

    gcloud compute routes create cluster-sql1-route-listener --network wsfcnet \
    --destination-range 10.0.1.5/32 --next-hop-instance cluster-sql1 \
    --next-hop-instance-zone us-central1-f --priority 1
    

    gcloud compute routes create cluster-sql2-route-listener --network wsfcnet \
    --destination-range 10.1.1.5/32 --next-hop-instance cluster-sql2 \
    --next-hop-instance-zone us-central1-f --priority 1
    

  5. Check the routes to make sure they are created properly:

    gcloud compute routes list --filter="network:wsfcnet"
    

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.1.4" #IP address of cluster in subnet 1
      
      PS C:> $ipWSFC2 = "10.1.1.4" #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. You can choose another location for a shared folder as long as both instances have access to the shared folder.

  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 on 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.1.5
    • 10.1.0.0/16 and 10.1.1.5

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

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Compute Engine Documentation