Configuration Based SQL Server FCI Action On A Windows Server Failover Cluster

Configuration Based SQL Server FCI Action On A Windows Server Failover Cluster.md

This document targets Windows Server 2022, Windows Server 2025, SQL Server 2022, and SQL Server 2025. This document presumes that you store your SQL Server installation media, service packs, configuration files, and scripts on a UNC path.

Table of Contents

Requirements

Before you install a SQL Server Failover Cluster Instance (FCI) on a Windows Server Failover Cluster (WSFC), review the Active Directory, DNS, and storage requirements. See Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster.md.

Cluster Node First-Time Configuration

Use the steps below to configure a WSFC cluster node for first-time use.

Note: Windows Server 2022 and Windows Server 2025 offer two install options: Server Core and Server with Desktop Experience. If your node runs Server Core, some steps below need an alternate tool. Each step states the alternate tool where it applies.

  1. Log in to the WSFC cluster node through Remote Desktop, with credentials that hold administrative rights. Verify that the SQL Server security account and the security group for the DBA team and the SQL Server team belong to the local Administrators group on the node.

    Example:

    TEST\G-SQLAdmins
TEST\G-SQLServiceAccounts
TEST\S-MYTESTSQLSVC01

  2. Verify that the appropriate account or group (for example, the local Administrators group) holds the following user rights, under Local Security Policy → Local Policies → User Rights Assignment:

    • Act as part of the operating system
    • Lock pages in memory
    • Log on as a batch job
    • Log on as a service
    • Perform volume maintenance tasks
    • Replace a process level token

    Run the Local Group Policy Editor (gpedit.msc) on a node with Desktop Experience. Use it to check and set these rights.

    Note: The Security Settings section of the Local Group Policy Editor does not work on Server Core. This is true even when you load the snap-in through a Microsoft Management Console (MMC) session.

    On a Server Core node, set these rights with one of these methods:

    • The secedit.exe command-line tool.
    • A domain Group Policy Object, linked to the cluster's organizational unit.
    • The Group Policy Management Console (GPMC), from an administrative host.

    See Remote PowerShell.md for how to build a separate administrative host for a Server Core node.

    Caution: Before you grant the Lock pages in memory right to a SQL Server service account, read Windows Server 2025/SQL Server 2025 Notes.

  3. Run the Registry Editor (regedit), or PowerShell, to set one of the two values below. Unless you set one of these values, Windows blocks local NTLM access to your WSFC cluster's virtual network name. This block is the NTLM loopback check.

    Recommended: Add the FCI virtual network name to BackConnectionHostNames. This method exempts only the names you list. The loopback check stays active for every other name.

    HKLM\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
BackConnectionHostNames [Multi-String Value]
<FCI_VIRTUAL_NETWORK_NAME>
<FCI_VIRTUAL_NETWORK_NAME>.<DOMAIN>

    Alternative, not recommended: Set DisableLoopbackCheck to turn off the loopback check for every name on the node. Microsoft does not recommend this method. It opens the node to a man-in-the-middle attack on NTLM authentication.

    HKLM\SYSTEM\CurrentControlSet\Control\Lsa
DisableLoopbackCheck [DWORD (32-bit) Value] = 1

    For either method to take effect, restart the node. See Error message when you try to access a server locally by using its FQDN or its CNAME alias for the full procedure.

  4. Confirm whether your SQL Server FCI needs a clustered Microsoft Distributed Transaction Coordinator (MSDTC) resource. Since Windows Server 2008, a WSFC cluster runs MSDTC locally by default, even with Failover Clustering installed. If your application requires MSDTC to fail over together with the SQL Server instance, create a clustered MSDTC resource.

    Note: SQL Server Availability Groups do not support cross-database or distributed (DTC) transactions. This step applies to an FCI only.

    If your FCI needs a clustered MSDTC resource, run the following PowerShell commands as administrator. You can run these commands from an administrative host, or directly on the node. Replace each placeholder in angle brackets with the actual name for your environment.

    Add-ClusterResource -Name <VirtualSQLServerName> -ResourceType "Distributed Transaction Coordinator" -Group <SqlRoleName>

Set-NetFirewallRule -Name 'RPC Endpoint Mapper' -Enabled True
Set-NetFirewallRule -Name 'DTC incoming connections' -Enabled True
Set-NetFirewallRule -Name 'DTC outgoing connections' -Enabled True

Set-DtcNetworkSetting -AuthenticationLevel Mutual -DtcName "Local" `
  -InboundTransactionsEnabled $True -LUTransactionsEnabled $True `
  -OutboundTransactionsEnabled $True -RemoteAdministrationAccessEnabled $False `
  -RemoteClientAccessEnabled $False -XATransactionsEnabled $True -Verbose

Get-Dtc -Verbose | Sort-Object DtcName

Test-Dtc -LocalComputerName <VirtualSQLServerName> -Verbose

    Run the firewall rule commands and the Set-DtcNetworkSetting command on every node in the cluster. See How to rebuild or move an MSDTC installation for use with a SQL failover cluster for the full procedure.

  5. Identify the UNC path where you will store the SQL Server software, service packs, and configuration files.

    Example:

    \\<UNC_PATH>\Software\<SQL_VERSION>\
\\<UNC_PATH>\Software\Updates\<SQL_VERSION>\
\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\
\\<UNC_PATH>\Configuration Scripts\MYTESTCNO1\

SQL Server FCI Mount Point, Folder, and File Configuration

Use these steps to set up the mount points, folders, and files for a new SQL Server FCI.

  1. Log in to a passive WSFC cluster node through Remote Desktop, with elevated credentials. Start the Failover Cluster Manager (cluadmin.msc) on a node with Desktop Experience. On a Server Core node, use Get-ClusterResource in PowerShell instead. Verify that the drive letter and mount points for the new SQL Server FCI belong to the Available Storage role.

    Example:

    Failover Cluster Manager → MYTESTCNO1 → Storage → Disks
G:\
G:\Admin
G:\Data1
G:\Data2
G:\Logs
G:\TempDB

  2. Start Windows PowerShell as administrator and move the Available Storage role to the WSFC cluster node you are on.

    Example:

    Move-ClusterGroup -Cluster MYTESTCNO1 -Name "Available Storage"

  3. Create the following directories under the root drive letter, for each virtual SQL Server FCI you will install.

    Example:

    G:\Admin\Backups
G:\Data1\MSSQL\Data
G:\Data2\MSSQL\Data
G:\Logs\MSSQL\Log
G:\TempDB\MSSQL\TempDB

  4. Create a placeholder file under the root drive letter, to identify the SQL Server FCI.

    Example:

    G:\MYTESTVSQL01$MYTEST01_DO_NOT_REMOVE.TXT

SQL Server FCI Action: InstallFailoverCluster, AddNode, RemoveNode

Follow these steps to perform a configuration-file-based SQL Server FCI action.

  1. Create a configuration (.ini) file for the SQL Server FCI action you will perform: InstallFailoverCluster, AddNode, or RemoveNode. Store the file on a UNC path.

    Note: The original sample configuration files for this document were personal Google Docs links. Those links no longer work. Use these two Microsoft Learn documents instead, to build a current configuration file for each action:

    Example:

    AddNode:
\\<UNC_PATH>\Configuration Scripts\MYTESTCNO1\MYTESTVSQL01$MYTEST01_AddNode.ini

InstallFailoverCluster:
\\<UNC_PATH>\Configuration Scripts\MYTESTCNO1\MYTESTVSQL01$MYTEST01_InstallFailoverCluster.ini

RemoveNode:
\\<UNC_PATH>\Configuration Scripts\MYTESTCNO1\MYTESTVSQL01$MYTEST01_RemoveNode.ini

  2. Create, or identify an existing, batch script to run the SQL Server FCI InstallFailoverCluster, AddNode, or RemoveNode action.

    Example:

    AddNode:
\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_AddNode.bat

InstallFailoverCluster:
\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_InstallFailoverCluster.bat

RemoveNode:
\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_RemoveNode.bat

  3. Update the batch script to run the appropriate SQL Server FCI action. Replace <SQL_VERSION> with SQL Server 2022 or SQL Server 2025, to match the installation media on your UNC path.

    Example, AddNode:

    "\\<UNC_PATH>\Software\<SQL_VERSION>\setup.exe" /CONFIGURATIONFILE="<SQLServerFCI_AddNode_File_Path>" /SQLSVCPASSWORD="" /AGTSVCPASSWORD="" /IACCEPTSQLSERVERLICENSETERMS /PID=""

    Example, InstallFailoverCluster:

    "\\<UNC_PATH>\Software\<SQL_VERSION>\setup.exe" /CONFIGURATIONFILE="<SQLServerFCI_InstallFailoverCluster_File_Path>" /SQLSVCPASSWORD="" /AGTSVCPASSWORD="" /SAPWD="" /IACCEPTSQLSERVERLICENSETERMS /PID=""

    Example, RemoveNode:

    "\\<UNC_PATH>\Software\<SQL_VERSION>\setup.exe" /CONFIGURATIONFILE="<SQLServerFCI_RemoveNode_File_Path>"

  4. Delete the PendingFileRenameOperations registry value. This step avoids a SQL Server FCI action failure caused by a pending reboot.

    Note: To automate this step, add this command before each SQL Server FCI action in your batch scripts.

    REG DELETE "HKLM\SYSTEM\CurrentControlSet\Control\Session Manager" /v PendingFileRenameOperations /f

  5. Open a remote PowerShell session to the target node, from your administrative host. See Remote PowerShell.md for the current CredSSP and Resource-Based Kerberos Constrained Delegation methods.

  6. Run the batch script through the remote PowerShell session, to perform the SQL Server FCI action.

    Example, AddNode:

    & '\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_AddNode.bat'

    Example, InstallFailoverCluster:

    & '\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_InstallFailoverCluster.bat'

    Example, RemoveNode:

    & '\\<UNC_PATH>\SQL Scripts\MYTESTCNO1\SQLServerFCI_RemoveNode.bat'

  7. Review the on-screen output as the SQL Server FCI action proceeds. Confirm that no errors appear.

    Note: You can find the SQL Server FCI action output on the WSFC cluster node, under the following folder. <nnn> is 160 for SQL Server 2022, and 170 for SQL Server 2025.

    C:\Program Files\Microsoft SQL Server\<nnn>\Setup Bootstrap\Log

Windows Server 2025/SQL Server 2025 Notes

  • Setup Bootstrap Log path. SQL Server 2025 writes setup log files under C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log, not under the 110 folder this document originally referenced for SQL Server 2012.
  • TLS 1.2 requirement. SQL Server 2025 setup, including an FCI action, fails if TLS 1.2 is disabled on the node. Before you run any InstallFailoverCluster or AddNode action, enable TLS 1.2. See the companion Windows Server Failover Clustering Hotfixes for SQL Server AlwaysOn.md document.
  • Lock Pages in Memory (LPIM) caution. Windows Server 2025 can raise an access violation for SQL Server when the LPIM policy is enabled for the SQL Server service account. Microsoft has not shipped a fix (checked August 2026). For full detail, see the companion Windows Server Failover Clustering Hotfixes for SQL Server AlwaysOn.md document. Test the configuration in a QA environment first.

Update Notes

This section lists what changed when this document moved from Windows Server 2012/SQL Server 2012 to Windows Server 2022/2025 and SQL Server 2022/2025.

Item Original Updated Reason
Target versions Windows Server 2012, SQL Server 2012 Windows Server 2022/2025, SQL Server 2022/2025 This document now targets currently supported versions.
Server interface Presumed Minimal Server or Server Graphical Shell interface Presumes Server Core or Server with Desktop Experience, with a Server Core caveat for gpedit.msc Windows Server 2016 and later removed the Minimal Server Interface. Confirmed that the Local Group Policy Editor Security Settings section does not work on Server Core.
MSDTC configuration GUI steps from a 2008-era third-party blog post Current PowerShell steps from Microsoft Learn, with a note that clustered MSDTC is not required unless the FCI needs it Confirmed against How to rebuild or move an MSDTC installation for use with a SQL failover cluster. Since Windows Server 2008, MSDTC runs locally by default. Clustering it is only needed when the FCI requires it to fail over with the instance. Availability Groups do not support DTC or cross-database transactions.
Remote PowerShell link A blog post URL for a CredSSP-only method The companion Remote PowerShell.md document, covering CredSSP and Resource-Based Kerberos Constrained Delegation The verified, current document in this folder supersedes the original blog post.
Sample configuration file links Personal Google Docs share links Official Microsoft Learn configuration-file and command-prompt install documents The original share links no longer work.
Setup Bootstrap Log path C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log (SQL Server 2012) \160\ for SQL Server 2022, \170\ for SQL Server 2025 Confirmed against Microsoft Learn and Microsoft Q&A: the folder number matches the SQL Server major version (16.x, 17.x).
Setup.exe path example Hardcoded to SQL Server 2012 Uses a <SQL_VERSION> placeholder This document now covers two SQL Server versions.
Active Directory/DNS/Storage reference A blog post URL The companion Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster.md document The verified, current document in this folder supersedes the original blog post.
Loopback check guidance Set DisableLoopbackCheck only, with no reason given and no restart step Recommends the scoped BackConnectionHostNames method first. Keeps DisableLoopbackCheck as a not-recommended alternative. Adds the required restart step. Confirmed against Error message when you try to access a server locally by using its FQDN or its CNAME alias. Microsoft recommends BackConnectionHostNames over DisableLoopbackCheck. If you turn off the loopback check entirely, the node becomes open to a man-in-the-middle attack on NTLM authentication.

Comments

Post a Comment

Popular posts from this blog

Windows Server Failover Clustering/SQL Server Firewall Access Rules

This document defines the firewall rules required for a Multi-Subnet Windows Server Failover Cluster (WSFC) environment. It covers WSFC core communication, the WSFC dependency on Active Directory (AD), and SQL Server Availability Group (AG) communication. Use these rules when you request network access for a Multi-Subnet Cluster. This version corrects several rows to agree with current Microsoft documentation. See Verification Notes at the end of this document for the list of changes and sources. Table of Contents Best Practice: IP Specificity Critical Action: Review Ephemeral Port Ranges Section 1: Core WSFC and Active Directory Ports Section 2: SQL Server Database Engine and Availability Group Ports Section 3: SQL Server BI Stack Ports (Conditional) Section 4: Multi-Subnet / Cloud Deployment Ports (Conditional) Section 5: Official References Verification Notes Windows Server 2025 / SQL Server 2025 Notes Best Practice: IP Specificity Specify exact IP ad...
Read more

Windows Server Recommendations For AWS

Windows Server Recommendations for AWS.md Windows Server Recommendations for AWS Overview This document gives a high-level recommendation for building a Windows Server EC2 host in AWS. It also covers support for Windows Server Failover Clustering (WSFC). Table of Contents Table of Contents Overview EC2 Host Recommendations EC2 Network Recommendations EC2 Security Recommendations Security Group 1: Internal Region Traffic Security Group 2: Inbound Windows and SQL Server Access Security Group 3: Outbound Access Security Group 4: Cross-Region WSFC and AG Traffic EC2 O/S Recommendations Operating System Image Network Configuration Drive Configuration Patching and Drivers Backups EC2 Host Recommendations This section gives recommendations for an EC2 host in AWS. It covers instance type and storage. Use the r6idn or i3en instance type for a SQL Server host in AWS. Both instance types come with NVMe SSD storage. Use the NVMe SSD storage for te...
Read more

Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster

Requirements for SQL Server AlwaysOn Feature on a Windows Server Failover Cluster.md This document targets Windows Server 2022 and SQL Server 2022. See Update Notes at the end of this document. That section lists what changed from the original 2012 version, and the reason for each change. Table of Contents Overview Terminology Windows Server Failover Clustering (WSFC) Cluster Name Object (CNO) Virtual Computer Object (VCO) Symmetric / Asymmetric Storage SQL Server Failover Clustered Instance (FCI) SQL Server Failover Clustered Instance CNAME (FCI CNAME) SQL Server Availability Group (AG) SQL Server Availability Group Listener (AGL) SQL Server Availability Group Listener CNAME (AGL CNAME) WSFC Requirements SQL Server FCI Requirements SQL Server Availability Group Requirements Update Notes Windows Server 2025 / SQL Server 2025 Notes Overview This document covers the SQL Server AlwaysOn feature. This includes the Failover Clustered Instance, the A...
Read more