Sql server

SQL Server Failover Clustering Setup Guide in sqlcmd and DbSchema

For administrators and developers planning high availability on SQL Server 2022 for Windows; WSFC, quorum and availability groups are explained where they appear.

by Malik Umair Sajjad · 12-Jul-2023 · Updated 10-Sep-2026

On this page

A server running SQL Server goes down, and the application reconnects to the same name and carries on. A SQL Server failover cluster instance (FCI) gives you that: one SQL Server instance installed across several Windows servers, which the cluster moves to a healthy server when the current one fails. You set one up in this order:

Join every node to the same Active Directory domain, and give every node the same Windows version, patches and drive letters.
Install the Windows Failover Clustering feature on each node, validate the nodes with Test-Cluster, and create the cluster with New-Cluster.
Give the cluster a quorum witness, so that it survives the loss of a node.
On the first node, run SQL Server Setup and choose New SQL Server failover cluster installation.
On every other node, run Setup and choose Add node to a SQL Server failover cluster.
Point applications, sqlcmd and DbSchema at the FCI's virtual network name, never at a node.

What a failover cluster instance is

An Always On failover cluster instance is a single SQL Server instance installed across the nodes of a Windows Server Failover Clustering (WSFC) cluster. On the network it looks like an instance running on one computer. Its redundancy sits at the instance level: when the instance moves to another node, every database moves with it, because the new node starts the same instance from the same disks.

Applications connect to the virtual network name SQLFCI01, which points at the active node SQLNODE1; SQLNODE2 waits with its services stopped, both nodes reach the same shared storage, and each keeps tempdb on a local disk

Only one node owns the instance at a time. That node, the active node, owns the FCI's WSFC resource group: the network name, the IP address, the shared disks, and the SQL Server and SQL Server Agent services. The other nodes have SQL Server installed and its services stopped.

Applications connect to the FCI's virtual network name, not to a node. After a failover, the name is registered to the new active node, so connection strings never change. Where the nodes sit in different subnets, each subnet gets its own virtual IP address, and the DNS record for the name is updated to the address of the subnet that now owns the instance.

Microsoft names two reasons for the instance to move: a failure of the hardware, the operating system, an application or a service, and a planned upgrade, where you move the instance off a node to patch it. An FCI fits any instance that has to survive both without its clients being reconfigured.

What happens during a failover

Whether the move is automatic or planned, Microsoft's documentation describes the same sequence:

Unless the hardware or the system failed, all dirty pages in the buffer cache are written to disk.
The SQL Server services in the resource group stop on the active node.
Ownership of the resource group moves to another node.
The new owner starts its SQL Server services.
Client connection requests go to the new active node, under the same virtual network name.

The first step decides how long a failover takes: the more dirty pages since the last checkpoint, the longer the write. Indirect checkpoints, available since SQL Server 2012, cap those pages and make the failover time more predictable, at some cost in normal running. The cluster decides when to fail over by polling the instance over a dedicated connection, which keeps working under heavy load, so a busy server isn't mistaken for a failing one.

What an FCI gives you, and what it costs

What you get is failover of the whole instance, automatic on hardware, operating system, application and service failures, with no reconfiguration of clients.

The cost starts with the storage. An FCI keeps one copy of its data on storage that all its nodes share, which Microsoft says "has the potential of being the single point of failure". The FCI also stays online only while the WSFC cluster keeps quorum, and it needs nodes configured identically, as the requirements below list.

Adding a node adds a place to fail over to, not capacity, because only one node runs the instance. A larger cluster can instead run several FCIs, each in its own resource group.

An availability group makes the other trade: a copy of each database on every replica, and no shared storage. The edition rows are from the SQL Server 2022 editions page:

	Failover cluster instance	Availability group
What fails over	the whole instance	a set of user databases
Copies of the data	one	one on each replica
Storage	shared by every node	each replica's own
Clients connect to	the virtual network name	the availability group listener
SQL Server 2022 Enterprise	up to 16 nodes	yes
SQL Server 2022 Standard	2 nodes	basic availability groups only

The two combine: an FCI can host a replica of an availability group, which the sqlcmd section below creates.

What the nodes need before you start

Microsoft's Before installing failover clustering and Windows Server's hardware requirements list what to check first:

Two or more nodes on a Windows Server edition that supports failover clustering, with the same operating system, SQL Server version, patch level, components and instance name. Setup can create an FCI on one node, but it has no high availability until a second node joins.
Every node in the same Active Directory domain. Workgroup, multi-domain, and mixed domain and workgroup clusters are not supported, and no node may be a domain controller.
Shared storage that every node reaches, such as cluster disks on iSCSI or Fibre Channel, Storage Spaces Direct, or SMB file shares.
A network with no single point of failure, such as several distinct networks, or teamed adapters with redundant switches.
Static IP addresses for the FCI in production, not DHCP.
A DNS server that lets the nodes register the FCI's name dynamically, or Setup fails and rolls the installation back.
The same COM+ configuration, disk drive letters and administrators group members on every node.
TCP/IP Sockets rather than Named Pipes, as Microsoft recommends for a cluster.

The account that creates the Windows cluster needs administrative rights on every node, plus the Create Computer objects and Read All Properties permissions in the Active Directory container that holds the computer accounts, as Microsoft's guide to cluster accounts describes. The account that installs SQL Server must be a local administrator with the rights to log on as a service and to act as part of the operating system, on every node. During Setup, the cluster's own computer account creates an object for the FCI's network name, so it needs permission to create computer objects too.

On a standalone instance, the SQL Server CREATE DATABASE guide walks through a first sqlcmd session and a first DbSchema connection.

Build the Windows cluster

Run the first command on every node, then the other two on one node:

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools
Test-Cluster -Node SQLNODE1, SQLNODE2
New-Cluster -Name SQLCLUSTER -Node SQLNODE1, SQLNODE2 -StaticAddress 192.168.1.12

Test-Cluster runs every validation test and writes a report, and Microsoft supports a cluster only if the whole configuration passes. New-Cluster creates the cluster with the name and address you give it, adds all eligible shared storage unless you pass -NoStorage, and creates the cluster's computer account in Active Directory.

The cluster runs only while more than half of its votes are present, a state called quorum. Each node has one vote, and a quorum witness, a disk or a file share that every node reaches, adds one more. With an even number of voting nodes, Microsoft recommends a witness, so that the cluster keeps running when half of its nodes go down. On two nodes, check that the quorum configuration New-Cluster picked has a witness, or set one on a file share outside the cluster:

Set-ClusterQuorum -FileShareWitness \\FS01\SQLCLUSTER-witness

Two nodes and a witness give three votes. If SQLNODE1 fails, SQLNODE2 and the witness hold two of three and the cluster stays up. If SQLNODE2 is cut off from both SQLNODE1 and the witness, SQLNODE1 and the witness hold two of three and keep running, while SQLNODE2, alone with one vote, stops

The same majority rule keeps a divided cluster from running the instance twice: a node cut off from the other node and the witness holds one vote of three, and stops. When the cluster loses quorum as a whole, the cluster and the FCI go offline until someone reestablishes quorum by hand.

Install the failover cluster instance

SQL Server Setup creates the FCI, not a T-SQL statement. In the integrated install, New SQL Server failover cluster installation on the first node creates a working one-node FCI, and Add node to a SQL Server failover cluster adds each other node. The advanced install runs Advanced cluster preparation on every node, then Advanced cluster completion on one of them, which suits nodes you prepare ahead of time.

Both also run unattended from a command prompt, with the parameters of Install SQL Server from the command prompt. On the first node:

setup.exe /Q /ACTION=InstallFailoverCluster /IACCEPTSQLSERVERLICENSETERMS ^
  /FEATURES=SQLEngine /INSTANCENAME=MSSQLSERVER ^
  /FAILOVERCLUSTERNETWORKNAME=SQLFCI01 ^
  /FAILOVERCLUSTERIPADDRESSES="IPv4;192.168.1.20;<WSFC cluster network>;255.255.255.0" ^
  /FAILOVERCLUSTERDISKS="<cluster disk name>" /INSTALLSQLDATADIR="S:\MSSQL" ^
  /SQLTEMPDBDIR="D:\tempdb" ^
  /SQLSVCACCOUNT="CONTOSO\sqlsvc" /SQLSVCPASSWORD="<password>" ^
  /AGTSVCACCOUNT="CONTOSO\sqlagent" /AGTSVCPASSWORD="<password>" ^
  /SQLSYSADMINACCOUNTS="CONTOSO\DBAs"

MSSQLSERVER makes this the default instance, so clients reach it by SQLFCI01 alone. The third field of the IP address names the WSFC cluster network that carries the address, not the FCI. On each other node, with the same instance name, service accounts and address:

setup.exe /Q /ACTION=AddNode /IACCEPTSQLSERVERLICENSETERMS /INSTANCENAME=MSSQLSERVER ^
  /FAILOVERCLUSTERIPADDRESSES="IPv4;192.168.1.20;<WSFC cluster network>;255.255.255.0" ^
  /CONFIRMIPDEPENDENCYCHANGE=0 ^
  /SQLSVCACCOUNT="CONTOSO\sqlsvc" /SQLSVCPASSWORD="<password>" ^
  /AGTSVCACCOUNT="CONTOSO\sqlagent" /AGTSVCPASSWORD="<password>"

Setup installs the binaries on each node and sets the cluster-aware services to start manually, because the cluster starts them on whichever node owns the instance. It also sets the disk dependencies of the SQL Server cluster group itself, so don't set them beforehand. The data directory must be on a shared cluster disk, and tempdb is the only part that may sit on a local disk: its path must exist on every node, or the SQL Server resource fails to come online after a failover. Before running Setup, disable the applications and services that might use SQL Server components, but leave the disk resources online.

Connecting and creating an availability group from sqlcmd

sqlcmd reaches the FCI by its virtual network name, like any other server. -S takes the server, with a named instance after a backslash, and -U a SQL Server login:

sqlcmd -S SQLFCI01 -U login_id

Leave -P off and sqlcmd prompts for the password; Microsoft calls a password on the command line insecure. For Windows authentication, use -E instead of -U. Once connected, ask SERVERPROPERTY which node answered:

SELECT SERVERPROPERTY('IsClustered')                 AS IsClustered,
       SERVERPROPERTY('MachineName')                 AS MachineName,
       SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS CurrentNode,
       @@SERVERNAME                                  AS ServerName;
GO

On a standalone SQL Server 2022 instance, run here in a container whose host name is sqlnode1, every name is the same and IsClustered is 0:

IsClustered	MachineName	CurrentNode	ServerName
0	sqlnode1	sqlnode1	sqlnode1

On an FCI, IsClustered is 1, MachineName and ServerName return the FCI's name, and CurrentNode returns the node the instance runs on now, which is the value a failover changes. sys.dm_os_cluster_nodes lists every node that can own the instance, with 1 in is_current_owner on the active node, and returns no rows on a standalone instance:

SELECT NodeName, status_description, is_current_owner
FROM sys.dm_os_cluster_nodes;

To watch CurrentNode change, move the instance's role in PowerShell and run the first query again. Setup named the role SQL Server (MSSQLSERVER), after the instance:

Move-ClusterGroup -Name "SQL Server (MSSQLSERVER)" -Node SQLNODE2

Without -Node, Move-ClusterGroup picks another node itself. This is also how you move the instance off a node before patching it.

From the same session you can add an availability group, which keeps copies of chosen databases on other instances. CREATE AVAILABILITY GROUP runs on the instance you intend as the initial primary replica, on a WSFC node with the availability groups feature enabled:

CREATE AVAILABILITY GROUP group_name
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE database_name
REPLICA ON 'SQLFCI01' WITH (
    ENDPOINT_URL = 'TCP://SQLFCI01.contoso.com:5022',
    FAILOVER_MODE = MANUAL,
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    BACKUP_PRIORITY = 50,
    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
);
GO

What each option does on an FCI:

REPLICA ON takes the FCI's network name, which @@SERVERNAME returns on the FCI.
FAILOVER_MODE = MANUAL is the only mode for a replica hosted by an FCI, because such a replica can't fail over automatically within an availability group.
ENDPOINT_URL is required and points at the instance's database mirroring endpoint.
AUTOMATED_BACKUP_PREFERENCE = SECONDARY is the default, and only a preference that your backup jobs must be scripted to read.
SYNCHRONOUS_COMMIT makes the primary wait until the log is hardened on this replica whenever it is a secondary.
ALLOW_CONNECTIONS = ALL lets every connection read the databases on this replica while it is a secondary.

The statement needs the sysadmin role plus the CREATE AVAILABILITY GROUP, ALTER ANY AVAILABILITY GROUP or CONTROL SERVER permission.

How DbSchema reaches a clustered instance

DbSchema connects to an FCI as it does to any SQL Server, through the virtual network name, so it reads the schema whichever node owns the instance. In DbSchema's Connection Dialog:

Choose Connect to Database, then SQL Server.
Set Server Location to Remote computer or custom port.
Enter the virtual network name, SQLFCI01, as the Server Host, and the instance's port as the Port.
Enter the Database User and Password, click Test Connection, then Connect.

The DbSchema Connection Dialog, shown for a PostgreSQL connection, with Remote computer or custom port selected and the Server Host, Port, Database User and Password fields

The saved connection needs no change after a failover. With Microsoft's JDBC driver, add multiSubnetFailover=true, which Microsoft's driver documentation asks for whenever the target is an FCI: where the name resolves to several addresses, as it does across subnets, the driver tries them all at once, and on a single address it costs nothing. Choose Edit the JDBC URL Manually in the Connection Mode list and append it:

jdbc:sqlserver://SQLFCI01:1433;databaseName=database_name;multiSubnetFailover=true

Connecting reverse-engineers the schema into interactive diagrams, and DbSchema reads table data only when you open a table or run a query. While DbSchema is connected, every schema change you make in the diagram is applied to the database at once, so on a production cluster tick Read Only Connection on the Settings tab, and set Highlight to Production so the connection stands out. To design without touching the cluster, choose Disconnected: your changes then go only to the .dbs model file, and the Synchronization Dialog applies them later, one reviewed difference at a time.

Failover clustering is planning first and typing second: the Windows cluster and its quorum, storage that every node reaches, identical nodes, and one virtual network name your applications use from then on. Once the instance answers on that name, download DbSchema at https://dbschema.com/download.html and connect the free Community Edition to it to reverse-engineer the schema into diagrams. Saving the design to a .dbs model file and synchronizing it with the database are Pro edition features.