Confirm compatibility of your environnement before deploying a SQL failover cluster.
https://docs.microsoft.com/en-us/windows-server/failover-clustering/clustering-requirements
Disks configurations
Disk 1 – Quorum: Is the quorum of the cluster. A cluster quorum disk is the storage medium on which the configuration database is stored for a cluster computing network. The cluster configuration database, also called the quorum, tells the cluster which physical server(s) should be active at any given time.
Disk 2 – Data: This drive must be formatted using 64K. This is the one hosting the databases of the SQL instances
Disk 3 – Log: This drive must be formatted using 64K. This drive is reserved for the log of the SQL instances
Disk 4 – TempDB: This drive must be formatted using 64K. this drive is used for the temdb of the SQL instances
Disk 5 : A final disk could be added for backup purpose.
Define naming convention for the SQL/SSAS environment
Prepare the names for your environnement. This will obviously depends on the naming convention of your org.
InstanceX : Should be replaced with the application name.
SQL Servers name: SQL01/SQL02.domain.com
SQL Cluster name: SQLPROD01.domain.com – Require a dedicated IP and once created it will create a computer objets in AD.
SQL Instance name: InstanceX
SQL Server Network Name: SQLInstanceX – Require a dedicated IP
Service account for Database engine: SRVC_SQLInstanceX
Service account for Agent : SRVC_SQLAGInstanceX
SSAS Servers name: SSAS01/SSAS02.domain.com
SSAS Cluster name: SSASPROD01.domain.com – Require a dedicated IP and once created it will create a computer objets in AD.
SQL Instance name: InstanceX
SQL Server Network Name: SSASInstanceX – Require a dedicated IP
Service Account for SSAS : SRVC_SSASInstanceX
Failover Cluster Installation – Node 1 & 2
Installing the Failover Clustering Feature
Start with the installation server should first have the Failover Clustering feature.
In the Server Manager > Dashboard, click Add roles and features and then click Next.
In the Installation Type step, choose Role-based or feature-based installation, and the click Next.
In the Server Selection step, click Next.
In the Server Roles step, click Next.
In the Features step, select Failover Clustering and then click Add Features.
Click Next, and then Install. Click Close when the installation is done.
After adding the failover cluster feature, navigate to the Server Manager Tools and select Failover Manager.
In the Server Manager, click Tools and then select Failover Cluster Manager.
Select Create Cluster to create a cluster with new instances.
In the Select Servers step, click Add and browse the computer names to add instances to the list of clusters.
For the SQLPROD01 cluster, add the node SQL01.domain.com & SQL02.domain.com
For the SSASPROD01 cluster, add the node SSAS01.domain.com & SSAS02.domain.com
Click Next.
In the Access Point for Administering the Cluster step, fill in the cluster name. Click Next.
Cluster name SQL : SQLPROD01.domain.com
Cluster name SSAS : SSASPROD01.domain.com
In the Confirmation step, click Next.
You can click View Report to check any warning that may have occurred during the cluster creation. Click Finish.
Now that the cluster is created, click it’s name in the Failover Cluster Manager. Launch the Quorum Wizard by right-clicking the cluster name, and selecting More Actions > Configure Cluster Quorum Settings.
In the Select Quorum Configuration step, choose the Select the quorum witness option. Click Next.
In the Select Quorum Witness step, choose Configure a disk witness. Click Next.
Select the Quorum disk. Click Next.
Here the two nodes present in the cluster SQLPROD01.domain.com
Here the two nodes present in the cluster SSASPROD01.domain.com
Finish the configuration with the Validation of the failover clustering by clicking Validate Configuration.
The Validate a Configuration wizard opens. Click Next.
In the Select Servers or a Cluster step, enter the name of the cluster.
In the Testing Options step, select Run only tests I select, and click Next.
In the Test Selection step, clear the Storage checkbox and then click Next.
Provide access to the Cluster account in AD
Once the cluster is created, you will need to modify the OU where the computer object is stored.
Modify the security under the OU called SQL so the account sqlprod01$ can Read and Create Computer Objects
Installation of the first clustered node [Including SQL & SSAS]
SQL standard is required or a full version of enterprise.
Select New SQL Server failover cluster installation
In the Global Rules dialog box, validate that the checks return successful results and click Next.
In the Microsoft Update dialog box, you have the option to include SQL Server product updates like service packs and cumulative updates in the installation process. By default, it searches for product updates thru the Microsoft Updates service online, assuming that the server has access to the Internet. In cases where your servers do not have access to the internet, you can manually download the updates and store them on a network shared folder. You can, then, point the installation media to search the network shared folder instead. For this option, you will need to run setup.exe from the command-line, passing the /UpdateSource parameter. Click Next.
In the Install Failover Cluster 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.
In the Feature Selection dialog box, select the following components Database Engine Services and Client Tools Connectivity. Click Next.
For the SQL node (this could vary with your environment)
For the SSAS node
In the Feature Rules dialog box, verify that all the rules have passed. If the rules returned a few warnings, make sure you fix them before proceeding with the installation. Click Next.
In the Instance Configuration dialog box, enter the SQL Server Network Name. This is the name that the client applications will use to connect to this server. A couple of things need highlighting in this section. By default, the instance name is used as the Instance ID. This is used to identify installation directories and registry keys for this specific instance of SQL Server – very helpful when you want to run multiple instances in a WSFC. It applies to both the default instance and named instances. For a default instance, the instance name and instance ID would be MSSQLSERVER. To use a non-default instance ID, you should specify a value in the Instance ID box. Click Next.
SQL Server Network Name: SQLInstanceX
Named Instance: InstanceX
Instance ID: InstanceX
SQL Server Network Name : SSASInstanceX
Name Instance : InstanceX
Instance ID : InstanceX
In the Cluster Resource Group dialog box, check the resources available on your WSFC. This tells you that a new Resource Group will be created on your WSFC for the SQL Server FCI. To specify the SQL Server cluster resource group name, you can either use the drop-down box to specify an existing group to use or type the name of a new group to create it. Accept all the defaults and click Next.
In the Cluster Disk Selection dialog box, select the available disk groups that are on the WSFC for the SQL Server FCI to use. Click Next. This is where you’ll see the value of renaming the shared disk resources as done in Part 2 of this tip series.
In the Cluster Network Configuration, assign the IP you have selected for the new instance called SQLInstanceX. Click Next.
In the section Server Configuration, you will assign the service accounts for the Agent and for the instance.
SRVC_SQLAGInstanceX for the SQL Agent on SQLPROD01 and the SRVC_SQLInstanceX for the instanceX
For the Analysis, the service account Srvc_SSASInstanceX
Change the services startup type to be automatic or you can do it later as well.
For the Collation Table, it can be either be “Latin_1_General_CI_AS” or “SQL_Latin1_General_CP1_CI_AS”.
For the SQL node :
For the SSAS node :
In the Database Engine Configuration section, select the Windows authentication. Add yourself at the section Specify SQL Server Administrators.
In the Data Directories tab, select the path of your drive for the Data, log and tempdb.
Complete the last steps to finish the installation.
The SQL Cluster.
The SSAS Cluster.
Installation of the second node
Start the SQL installation on node 2.
In the SQL Server Installation Center, click Installation and Add node to a SQL Server failover cluster
In the cluster Node Configuration, select the CS instance already present on node 1 and click Next.
SQL Server instance name : SQLInstanceX for SQL Cluster
SQL Server instance name : SSASInstanceX for the SSAS Cluster
In the following steps, re-enter the services account previously added in the configuration of the node 1. Complete the steps and the second node will now be fully part of the SQL/SSAS Cluster.
Modify the Local Policies on the nodes – Users rights assignment
You have to do this modification on both nodes.
Open the local security policy mmc and add the services accounts to:
- Adjust memory quotas for a process
- Bypass traverse checking
- Replace a process level token
- Log on as a service
Modify the firewall for the SQL/SSAS instance
Simply add the sqlserv.exe and msmdsrv.exe application.
Provide access to the network instances
Open SQL Server Management Configuration
Enable the TCP/IP protocol
Change the dynamic ports for something else. If you plan on adding many SQL instances you will need to add different ports for each instances.
Install SSMS
Connect using the instance name to SQL.
