Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 4


By:   |   Updated: 2020-11-11   |   Comments   |   Related: 1 | 2 | 3 | 4 | More > Clustering


Problem

We are migrating to a SQL Server 2019 failover cluster running on Windows Server 2019. How do I go about installing and configuring a SQL Server 2019 failover cluster on Windows Server 2019?  Check out part 4 of this series.

Solution

To continue this series on Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster, we will look at adding a node to an existing SQL Server 2019 failover clustered instance (FCI). In Part 3, you completed the installation of a single-node SQL Server 2019 FCI. This tip will cover installing a secondary node to an existing SQL Server 2019 FCI.

SQL Server 2019 Failover Cluster Instance Installation - Install Secondary (Failover) Cluster Node

The SQL Server 2019 FCI you installed in Part 3 is not highly available even though it is a fully functioning instance. To make it highly available, you need to add nodes to it. To add a node to an existing SQL Server 2019 FCI, perform the following:

  1. Run setup.exe from the SQL Server 2019 installation media to launch SQL Server Installation Center. Click on the Installation link on the left-hand side.
  2. Click the Add node to a SQL Server failover cluster link. This will run the SQL Server 2019 Setup wizard.
sql server 2019 cluster setup
  1. In the Product Key dialog box, enter the product key that came with your installation media and click Next.
sql server 2019 cluster setup
  1. In the License Terms dialog box, click the I accept the license terms check box and click Next.
sql server 2019 cluster setup
  1. In the Global Rules dialog box, validate that the checks return successful results and click Next.
sql server 2019 cluster setup
  1. In the Microsoft Update dialog box, click Next. Adding a node to an existing SQL Server FCI while slipstreaming the latest cumulative update will be covered in the section Adding a node to an existing SQL Server 2019 Failover Clustered Instance (FCI) with Slipstreamed Updates.
sql server 2019 cluster setup
  1. In the Add Node Rules dialog box, validate that the checks return successful results. If the checks returned a few warnings, make sure you fix them before proceeding with the installation. Click Next. Just like when you were installing the SQL Server 2019 FCI in Part 3, be aware that you will get a warning result for Microsoft Cluster Service (MSCS) cluster verification warnings as a side effect of the storage spaces direct (S2D) checks described in this tip.
sql server 2019 cluster setup
  1. In the Cluster Node Configuration dialog box, validate that the information for the existing SQL Server 2019 FCI that you installed and configured in Part 3 is correct. Click Next.
sql server 2019 cluster setup
  1. In the Cluster Network Configuration dialog box, validate that the IP address information is the same as the one you provided in the Part 3. Click Next.
sql server 2019 cluster setup
  1. In the Service Accounts dialog box, verify that the information is the same as what was used to configure the first node. Provide the appropriate credentials for the corresponding SQL Server service accounts.

Pay close attention to the order of the SQL Server services especially when you use different service accounts. In the Part 3, you see the SQL Server Agent service listed first before the SQL Server Database Engine service. Here, it's the reverse - the SQL Server Database Engine service is listed first before the SQL Server Agent service. Be sure not to mix those two up.

Select the Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service checkbox to enable Instant File Initialization for SQL Server as highlighted in this tip. Because this is a local permission assigned to an account, you need to explicitly do this on all the nodes in the SQL Server FCI.

Click Next.

sql server 2019 cluster setup
  1. In the Feature Rules dialog box, verify that all checks are successful. Click Next.
sql server 2019 cluster setup
  1. In the Ready to Add Node dialog box, verify that all configuration settings are correct. Click Install to proceed with the installation.
sql server 2019 cluster setup
  1. In the Complete dialog box, click Close. This concludes adding a node to an existing SQL Server 2017 FCI.
sql server 2019 cluster setup

To add more nodes to the SQL Server 2019 FCI, simply repeat steps #1 to #13.

At the completion of a successful installation and configuration of the node, you need to validate whether the SQL Server 2019 FCI will failover - either automatically or manually - to all the available nodes.

Adding a node to an existing SQL Server 2019 Failover Clustered Instance (FCI) with Slipstreamed Updates

Slipstreaming updates when adding a node to an existing SQL Server 2019 FCI installation is similar to the steps outlined in the Part 3 and also requires installing SQL Server from the command line. However, the /Action=AddNode parameter will be used. The example command below references the \\CentralFileServer\SQLServer2019CUs\CU5 shared folder to look for SQL Server 2019 updates.

setup.exe /Action=AddNode /UpdateEnabled=True /UpdateSource="\\CentralFileServer\SQLServer2019CUs\CU5"
command line sql install

The Product Updates section of the Add a Failover Cluster Node process will confirm whether the updates have been detected.

sql server 2019 cluster setup

Proceed with adding a node to an existing SQL Server FCI as outlined in the previous section SQL Server 2019 Failover Cluster Instance Installation - Install Secondary (Failover) Cluster Node. The Ready to Add Node dialog box of the installation process will also confirm the SQL Server version number after the installation. In this example, the installation media is using the RTM version of SQL Server 2019 (15.00.2000.05) while the update contains SQL Server 2019 CU 5 (15.0.4043.0) as shown in the Update Version: field.

sql server 2019 cluster setup

Testing SQL Server 2019 FCI Manual Failover with Application Connectivity

A simple way to test whether the SQL Server 2019 FCI works is to perform a manual failover. This process involves moving the SQL Server cluster resource group/role from one node to another. For this test, a simple query using SQL Server Management Studio as the client application can be used. It is recommended to perform this test with application connectivity to observe how the application behaves during the failover process. Refer to the query below and connect to the SQL Server 2019 FCI.

SELECT @@VERSION
SELECT @@SERVERNAME AS InstanceName, SERVERPROPERTY ('ComputerNamePhysicalNetBIOS') AS NodeName
SELECT * FROM sys.dm_os_cluster_nodes
query results

To test the failover process using the Failover Cluster Manager console as follows:

  1. Expand Roles and select SQL Server (<NAME>).
  2. Right-click the SQL Server (<NAME>) role, select Move and click Select Node...
failover cluster manager
  1. In the Move Clustered Role dialog box, select the node where you want the SQL Server FCI to move into. Click OK.
move clustered role
  1. After the failover process completes, re-run the query above to verify that the SQL Server FCI is now running on the other node.
query results

Another way of performing a manual failover test is by using the Move-ClusterGroup PowerShell cmdlet as described in this previous tip.

Move-ClusterGroup "SQL Server (TDPRDSQLCLS77)" -Node "TDPRD072" 
Next Steps


Last Updated: 2020-11-11


get scripts

next tip button



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips





Comments For This Article





download





Recommended Reading

Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 1

Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 2

Step-by-step Installation of SQL Server 2019 on a Windows Server 2019 Failover Cluster - Part 3

Getting started with SQL Server clustering

Force Start a Windows Server Failover Cluster without a Quorum to bring a SQL Server Failover Clustered Instance Online














get free sql tips
agree to terms