Configuring SQL Server Failover Cluster Instances

If you need high availability and redundancy for your Microsoft SQL Server on
Compute Engine, you can configure Always On Failover Cluster Instances
(FCI) with Storage Spaces Direct (S2D).

Always On FCI provides a single Microsoft SQL Server instance that is installed
across Windows Server Failover Cluster (WSFC) nodes. Only one node in the WSFC
actively hosts the SQL instance. In the event of a failover, the WSFC service
transfers ownership of the instance's resources to a designated failover node.

S2D provides a software-based virtual SAN that can use Compute Engine VM
data disks to store the SQL database.

This tutorial provides an example set up. At the end of this tutorial, you will
have created an Always On SQL Server FCI with S2D on Compute Engine.

Objectives

Set up a VPC network with a Windows domain controller.

Create two Windows SQL Server VM instances to act as cluster nodes.

Set up the failover cluster, including cluster storage.

Set up an internal load balancer to direct traffic to the active node.

Test the failover operation to verify that the cluster is working.

Costs

This tutorial uses Compute Engine images that include Windows Server
licenses. This means the cost to run this tutorial can be significant if you
leave VMs running. It's a good idea to stop the VMs when you're not using them.

Before you begin

Note: If you don't plan to keep the
resources that you create in this procedure, create a project instead of
selecting an existing project. After you finish these steps, you can
delete the project, removing all resources associated with the project.

Note: When you want to connect to the SQL FCI cluster you should connect to the
SQL FCI listener IP address (10.0.0.200). You should not need to connect
directly to the WSFC.

The load balancer listens for requests. Based on health checks, it knows which
SQL Server node is active, and it directs traffic there. In case the active node
fails, then the FCI failover node activates. The health checker receives the
healthy node's signal, and the load balancer redirects traffic. The database,
stored on S2D volumes, remains available.

Setting up the VPC network

While you can use an existing network for this tutorial, it is a best practice
to isolate systems into different networks and subnets with firewall rules that
limit traffic.

Create a custom mode VPC network, which gives
you complete control over its subnets and IP ranges.

gcloud compute networks create wsfcnet --subnet-mode custom

Add a subnet, wsfc-subnet1, which will contain all of the VMs in this
tutorial.

Create a firewall rule to allow incoming traffic for RDP on port 3389.
Notice that you are opening tcp:3389 to all IP addresses (0.0.0.0/0); in
a production environment you would limit access to approved IPs.

Creating and configuring a Windows domain controller

An Active Directory domain is used for domain name services, Windows Failover
Clustering, and Storage Spaces Direct features that are needed for this
tutorial. Having the AD domain controller in the same VPC is not a requirement
but a simplification for the purpose of this tutorial.

In this tutorial, the domain is gontoso.com. The domain controller VM name is
dc-windows (and, by default, the Windows computer name matches the VM name,
dc-windows). The VM is created in our subnet at 10.0.0.3.

Create a VM to use as the domain controller. While you can specify any
machine type and Windows server that you need, for this example use the
following parameters.

Note: If you plan to bring your own licenses for SQL Server via the license
mobility program then select
Windows Server base images for these nodes. You will need to install the SQL
Server using your own product keys.

From Cloud Shell, create two SQL Server VMs with additional data
disks. The disks will be used as S2D capacity and cache volumes.

Note: For the purpose of this tutorial, and to fit within the default
regional SSD persistent disk quota, the size of the disks attached to each
VM is smaller than it would be in a production environment. For better
performance and to accommodate a larger database, you would increase the
size of each disk. Read
this article
for more information about choosing S2D drives.

Optionally, connect to each node using RDP and, from an elevated PowerShell,
verify that Failover Clustering is enabled.

Configuring the cluster VMs' network

Add a firewall rule to open a port for the health check service. This
tutorial will use tcp:59997. You can change this to a different port,
but it must match the health checker port that you will define later. The
health check process periodically pings the agent on each cluster node to
determine its availability.

You will be prompted for credentials. Use the Administrator username and
password that you set when you configured the domain controller VM in a
previous step.

The machine will reboot.

Creating a file share witness

To provide a tie-breaking vote and achieve a quorum
for the failover scenario, create a file share that will act as a witness. For
the purposes of this tutorial, you will create the file share witness on the
domain controller VM. In a production environment, you would create it
elsewhere.

Using RDP, connect to the domain controller VM, dc-windows, with the
Administrator account.

Optionally, using RDP, connect to either node as gontoso.com\Administrator
and verify that you can access the shared directory.

PS C:\> dir \\dc-windows\QWitness

Creating an internal load balancer

An internal load balancer
provides a single IP for the SQL Server. The load balancer listens for requests
and routes network traffic to the active cluster node. It knows which is the
active node because a health checker is running against each node. Only the
active node responds as healthy. If the active node goes down, then the SQL FCI
failover node activates. The health checker receives the signal, and traffic is
redirected there.

Create two instance groups, and add one SQL Server node to each group. These
instance groups act as backends that the load balancer can direct traffic to.

PS C:\> Test-Cluster
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, ClusterConditionallyApproved
Testing has completed for the tests you selected. You should review the warnings in the Report. A cluster solution is
supported by Microsoft only if you run all cluster validation tests, and all tests succeed (with or without warnings).
Test report file path: C:\Users\Administrator\AppData\Local\Temp\Validation Report 2018.05.01 At 19.25.01.htm
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 5/1/2018 7:26 PM 747166 Validation Report 2018.05.01 At 19.25.01.htm

You can also launch the Cluster Admin GUI to review the cluster's health by
running cluadmin.msc.

Enabling Storage Spaces Direct

Using RDP, connect to either node-1 or node-2 and open an elevated
PowerShell.

Enable Storage Spaces Direct.

PS C:\> Enable-ClusterS2D
Confirm
Are you sure you want to perform this action?
Performing operation 'Enable Cluster Storage Spaces Direct' on Target 'windows-fci'.
[Y] Yes [A] Yes to All [N] No [L] No to All [S] Suspend [?] Help (default is "Y"): Y
WARNING: 2018/05/01-19:36:41.315 Node node-1: No disks found to be used for cache
WARNING: 2018/05/01-19:36:41.330 Node node-2: No disks found to be used for cache
WARNING: 2018/05/01-19:36:41.362 C:\Windows\Cluster\Reports\Enable-ClusterS2D on 2018.05.01-19.36.41.362.htm

Accept the default when prompted to confirm. You will see some warnings that
you can ignore.

Optionally, if you want better disk performance, you can add
local SSDs to your cluster's VMs
in addition to standard SSD persistent disks. The local SSDs can
serve as the S2D caching layer. Make the number of capacity drives
(SSD persistent disks in our case) a multiple of the number of local
SSDs. Run the following command for enabling S2D with caching:

Copy the contents of the fci_install_master_node.ini configuration file
to the text editor on node-1. Line breaks may not be preserved, but it
still works.

;fci_install_master_node.ini
;SQL Server 2016 Configuration File
[OPTIONS]
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="InstallFailoverCluster"
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line.
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use.
IACCEPTROPENLICENSETERMS="True"
IAcceptSQLServerLicenseTerms="True"
; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.
ENU="True"
; Setup will not display any user interface.
QUIET="True"
; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="True"
; If this parameter is provided, then this computer will use Microsoft Update to check for updates.
USEMICROSOFTUPDATE="False"
; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install shared components.
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ
; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="False"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
INSTANCENAME="MSSQLSERVER"
; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed.
INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"
; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed.
INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"
; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance.
INSTANCEID="MSSQLSERVER"
; Specify the installation directory.
INSTANCEDIR="C:\Program Files\Microsoft SQL Server"
; Specifies a cluster shared disk to associate with the SQL Server failover cluster instance.
FAILOVERCLUSTERDISKS="Cluster Virtual Disk (VDisk01)"
; Specifies the name of the cluster group for the SQL Server failover cluster instance.
FAILOVERCLUSTERGROUP="SQL Server (MSSQLSERVER)"
; Specifies an encoded IP address. The encodings are semicolon-delimited (;), and follow the format ;;;. Supported IP types include DHCP, IPV4, and IPV6.
FAILOVERCLUSTERIPADDRESSES="IPv4;10.0.0.200;Cluster Network 1;255.255.255.0"
; Specifies the name of the SQL Server failover cluster instance. This name is the network name that is used to connect to SQL Server services.
FAILOVERCLUSTERNETWORKNAME="SQL2016FCI"
; Agent account name
AGTSVCACCOUNT="gontoso.com\service_account"
AGTSVCPASSWORD="changeme"
; CM brick TCP communication port
COMMFABRICPORT="0"
; How matrix will use private networks
COMMFABRICNETWORKLEVEL="0"
; How inter brick communication will be protected
COMMFABRICENCRYPTION="0"
; TCP port used by the CM brick
MATRIXCMBRICKCOMMPORT="0"
; Level to enable FILESTREAM feature at (0, 1, 2 or 3).
FILESTREAMLEVEL="0"
; Specifies a Windows collation or an SQL collation to use for the Database Engine.
SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
; Account for SQL Server service: Domain\User or system account.
SQLSVCACCOUNT="gontoso.com\service_account"
SQLSVCPASSWORD="changeme"
; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.
SQLSVCINSTANTFILEINIT="True"
; Windows account(s) to provision as SQL Server system administrators.
SQLSYSADMINACCOUNTS="gontoso.com\service_account"
; The number of Database Engine TempDB files.
SQLTEMPDBFILECOUNT="4"
; Specifies the initial size of a Database Engine TempDB data file in MB.
SQLTEMPDBFILESIZE="8"
; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.
SQLTEMPDBFILEGROWTH="64"
; Specifies the initial size of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILESIZE="8"
; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.
SQLTEMPDBLOGFILEGROWTH="64"
; The Database Engine root data directory.
INSTALLSQLDATADIR="C:\ClusterStorage\Volume1"
; Add description of input argument FTSVCACCOUNT
FTSVCACCOUNT="NT Service\MSSQLFDLauncher"

Update the configuration file with your gontoso.com\service_account
password. The password must be updated in two places: AGTSVCPASSWORD
and SQLSVCPASSWORD. Search for changeme and replace it with the
password that you defined. Save the file as a plain text file with
filename, C:\fci_install_master_node.ini.

Open an elevated PowerShell and install the SQL Server using the
configuration file.

Copy the contents of the fci_add_node.ini configuration file to the
text editor on node-1.

;fci_add_node.ini
;SQL Server 2016 Configuration File
[OPTIONS]
; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.
ACTION="AddNode"
; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line.
SUPPRESSPRIVACYSTATEMENTNOTICE="False"
; By specifying this parameter and accepting Microsoft R Open and Microsoft R Server terms, you acknowledge that you have read and understood the terms of use.
IACCEPTROPENLICENSETERMS="True"
IAcceptSQLServerLicenseTerms="True"
; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.
ENU="True"
; Setup will not display any user interface.
QUIET="True"
; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.
UpdateEnabled="True"
; If this parameter is provided, then this computer will use Microsoft Update to check for updates.
USEMICROSOFTUPDATE="False"
; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.
UpdateSource="MU"
; Displays the command line parameters usage
HELP="False"
; Specifies that the detailed Setup log should be piped to the console.
INDICATEPROGRESS="True"
; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.
X86="False"
; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).
INSTANCENAME="MSSQLSERVER"
; Specifies the name of the cluster group for the SQL Server failover cluster instance.
FAILOVERCLUSTERGROUP="SQL Server (MSSQLSERVER)"
; Indicates that the change in IP address resource dependency type for the SQL Server multi-subnet failover cluster is accepted.
CONFIRMIPDEPENDENCYCHANGE="False"
; Specifies an encoded IP address. The encodings are semicolon-delimited (;), and follow the format ;;;. Supported IP types include DHCP, IPV4, and IPV6.
FAILOVERCLUSTERIPADDRESSES="IPv4;10.0.0.200;Cluster Network 1;255.255.255.0"
; Specifies the name of the SQL Server failover cluster instance. This name is the network name that is used to connect to SQL Server services.
FAILOVERCLUSTERNETWORKNAME="SQL2016FCI"
; Agent account name
AGTSVCACCOUNT="gontoso.com\service_account"
AGTSVCPASSWORD="changeme"
; Account for SQL Server service: Domain\User or system account.
SQLSVCACCOUNT="gontoso.com\service_account"
SQLSVCPASSWORD="changeme"
; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.
SQLSVCINSTANTFILEINIT="True"
; Add description of input argument FTSVCACCOUNT
FTSVCACCOUNT="NT Service\MSSQLFDLauncher"

Update the configuration file with your gontoso.com\service_account
password. The password must be updated in two places: AGTSVCPASSWORD
and SQLSVCPASSWORD. Search for changeme and replace it with the
password that you defined. Save the file as a plain text file with
filename, C:\fci_add_node.ini.

Despite the loss of node-1, the query succeeds, and shows that
node-2 is now the current owner of the failover cluster.

Limitations

Storage Spaces Direct(S2D) is only suppored for Windows 2016 and above.

With S2D, each disk only contains a partitial view of the overall data.
So taking a snapshot of a persistent disk won't be enough to back up your
data. Use
native SQL backup
instead.

Cleaning up

To avoid incurring charges to your Google Cloud Platform account for
the resources used in this tutorial:

After you've finished the SQL Server FCI tutorial, you can clean up the
resources that you created on GCP so they won't take up
quota and you won't be billed for them in the future. The following
sections describe how to delete or turn off these resources.

Deleting the project

The easiest way to eliminate billing is to delete the project that you
created for the tutorial.

To delete the project:

Caution: Deleting a project has the following effects:

Everything in the project is deleted. If you used an existing project for
this tutorial, when you delete it, you also delete any other work you've done in the project.

Custom project IDs are lost.
When you created this project, you might have created a custom project ID that you want to use in
the future. To preserve the URLs that use the project ID, such as an appspot.com
URL, delete selected resources inside the project instead of deleting the whole project.

If you plan to explore multiple tutorials and quickstarts, reusing projects can help you avoid
exceeding project quota limits.