Captions are on by default; turn them off from the player menu. The narration uses a synthetic voice.
Transcript
Deploying a SQL Server Failover Cluster Instance
Welcome to HubDat AI. In this video we build a SQL Server failover cluster instance, step by step. We will cover the prerequisites, validate and create the Windows cluster, configure quorum, install SQL Server on both nodes, and then test failover and patching.
What a failover cluster instance gives you
A failover cluster instance is one SQL Server instance that can run on any node in a Windows cluster. The databases sit on shared storage that every node can reach, and only one node owns the instance at a time. If that node fails, the cluster restarts the instance on another node. Clients connect to a virtual network name, so the connection string never changes. Remember that the shared storage is a single point of failure, so an FCI protects you from server failure, not from storage failure.
The design we are building
Here is the design. Two Windows Server nodes, SQLN1 and SQLN2, form a cluster called SQLCLU01. Both nodes see the same shared disks for data and log files. TempDB can live on a fast local disk on each node. The SQL Server instance is published as the virtual name SQLFCI01 with its own IP address, and a cloud or file share witness gives the cluster a tie-breaking vote.
Prerequisites checklist
Before you start, check the prerequisites. Both nodes must run the same Windows Server version and patch level, be joined to the domain, and use static IP addresses. Present the shared disks to both nodes, and format them with a 64 KB allocation unit. In Active Directory, the cluster computer object needs permission to create computer objects in its organisational unit. Prepare domain service accounts or group managed service accounts for the engine and agent. Standard edition supports two nodes, Enterprise supports more.
Install the Failover Clustering feature
Step one. Install the failover clustering feature and its management tools on both nodes. You can do this on each server, or remotely from one place with Invoke-Command, as shown. A restart may be required.
Validate the configuration
Step two. Run Test-Cluster against both nodes. It checks inventory, networking, storage and system configuration, and writes an HTML report. Fix every failure and make sure you understand every warning. Keep the report, because Microsoft support expects a cluster that passes validation.
Create the cluster and add the disks
Step three. Create the cluster with New-Cluster, giving it a name and a static address. We use the -NoStorage switch so we can add disks deliberately. Then add the available disks, and rename them to something meaningful, such as SQL Data and SQL Log. Clear names make the SQL Server setup and later troubleshooting much easier.
Configure the quorum witness
Step four. Configure a quorum witness. A two-node cluster always needs one, otherwise losing a single node can take the whole cluster down. Use a cloud witness in an Azure storage account, or a file share witness on a third server. Then check the result with Get-ClusterQuorum.
Install SQL Server on the first node
Step five. On the first node, run SQL Server setup with the InstallFailoverCluster action. You can use the wizard, or the command line shown here. Give it the SQL network name, its IP address and cluster network, and the cluster disks for data and logs. TempDB can go to a local path, as long as that folder exists on every node. Use your service accounts and an Active Directory group for sysadmin. Parameter names can vary by version, so check the documentation for yours.
Add the second node
Step six. On the second node, run setup with the AddNode action. It installs the binaries and registers the node as a possible owner of the instance. Every node must have exactly the same version, edition and cumulative update. If TempDB is on a local disk, create that folder before you fail over.
Post-install configuration
Step seven. Configure the instance as you would any production server. Set max server memory so the operating system has room, set max degree of parallelism and the cost threshold, and create equal sized TempDB data files. Then schedule backups, integrity checks and index maintenance, and add monitoring for both the cluster and SQL Server.
Test failover
Step eight. Test failover before you go live. Move the SQL Server cluster group to the second node, and confirm from a client that the virtual name still answers, while the physical computer name has changed. A failover restarts the instance, so expect a short outage, and make sure applications retry their connections.
Rolling patching with no surprises
For patching, use a rolling approach. Patch the passive node first and reboot it. Fail the instance over to it during a maintenance window, then patch the other node. Fail back if you want a fixed home node, and finally confirm the version on both nodes. The only downtime is the failover itself.
Common problems and fixes
Finally, the problems we see most often. If the SQL network name will not come online, the cluster object usually lacks permission to create computer objects in Active Directory. If setup cannot see the disks, make sure they are clustered and online on that node. If the instance will not start after failover, check the local TempDB folder and the service account. And for unexpected failovers, generate the cluster log and review networking and antivirus exclusions.
Full guides on HubDatAI
That is the complete deployment. For the detailed guides, including FCI versus availability groups and running an FCI on Amazon EC2, visit HubDat AI. Thanks for watching.