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 thewindows-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.
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.
Set the Administrator account password.
PS C:\> Set-LocalUser -Name Administrator -Password $Password
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: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.
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)
- Internal IP address:
- 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)
- Internal IP address:
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.
- From the new disk, create a custom Windows Server image with
MULTI_IP_SUBNET
enabled in theguestOSFeatures
parameter. - After the image is created, delete the temporary disk.
- 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. - On each of the instances, install SQL Server Enterprise manually.
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
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
gcloud compute disks delete windows-2012-disk-new --zone us-central1-f
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;"
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:
- Open SQL Server Configuration Manager.
- Select the SQL Server services tab, right-click SQL Server, and then click Properties.
- 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 isAdministrator
. 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 thesql_service
account for both read and write.On both instances, create two folders at
C:\SQLData
andC:\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
.
- Listener DNS Name:
Add two subnet and IP address fields. For this example, use the following subnet and IP address pairs:
10.0.0.0/16
and10.0.0.6
10.1.0.0/16
and10.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.