I have built a Domain controller (SHERBAZ.COM) on Windows 2012 Datacenter edition evaluation version with DNS role enabled. Also I use a trial version Starwind virtual SAN to provision shared storage for the cluster. Two windows 2012 standard evaluation edition is also installed to setup failover clustering. Setting up the environment is beyond the scope of this session. The windows failover cluster is also considered pre-built before this demo.
Here I am showing a demo on how to setup a SQL Server failover cluster using Managed Service Account instead of normal service accounts. Managed service account(MSA) will have it’s password automatically managed by the Active directory. Admins and users will have no control on the passwords and hence more secure.
1. SQL Virtual Network Name: Lets start by adding DNS record for the new SQL Server instance virtual network name sqlclus.sherbaz.com
2. Create AD computer object for SQL virtual network name: Open Start > Run > dsa.msc on DC.
2.1. Give read/write permissions to DBA installer user account on the SQL computer object sqlclus:
2.2 Give Full Control permission to Windows Cluster Computer object account “winclus1” on SQL object “sqlclus“
If this permission is not granted, we would get below error in the end of SQL Server failover cluster installation wizard.
3. Create MSA: On Domain control server (DC), open powershell as “Run as Administrator” and execute below to create Managed Service Account named sqlclumsa. I thought DNSHostName can be SQL Cluster Virtual Network Name logically and gave it. It didn’t give any problem.
Import-Module ActiveDirectory New-ADServiceAccount -Name sqlclumsa -Enabled $true -Description "SQL Cluster MSA" -DisplayName "SQL MSA" -DNSHostName sqlclus
3.1. Grant permissions to MSA: Grant permissions to all computer objects on the newly created MSA.
> node1 – node1$ – First node of failover cluster
> node2 – node2$ – Second node of failover cluster
> winclus1 – winclus1$ – Windows failover cluster virtual network name computer object in AD
> sqlclus – sqlclus$ – SQL virtual network name computer object in AD
Set-ADServiceAccount -Identity sqlclumsa -PrincipalsAllowedToRetrieveManagedPassword node1$ node2$ winclus1$ sqlclus$
If you forget to create SQL computer object before this step, you will see below error
4. Add Storage: Provision a shared storage for both nodes node1 and node2 for SQL installation. Here I used trial version Starwind virtual SAN software.
4.1 Add storage to individual nodes: Login to any node and Start > Run > Compmgmt.msc to open Computer Management. Expand Storage and select Disk management. This has to be done only on one node since the disk is same on both and shared.
4.2. Add Disk to Cluster: Open Start > Run > cluadmin.msc to open Failover Cluster Administrator
5. Install MSA on each node: Through powershell, the MSA sqlclumsa we created has to be installed on both the nodes for successful authentication.
Execute below command to install windows feature RSAT-AD-PowerShell
Test if the node has access to MSA with below command
Test-ADServiceAccount -Identity sqlclumsa
Execute below command to install service account.
6. SQL Server Installation: Mount the SQL Server Installation DVD ISO and fire Setup.exe. As a pre-requisite, ensure the DBA installer account has local administrator rights on both the nodes.
6.1 Add second node: Add node 2 to SQL cluster we installed above. Mount the install DVD ISO on node 2 and fire Setup.exe
After the installation review the cluster events for any errors. If you see below error in cluster events saying “Cluster network name resource ‘SQL Network Name (sqlclus)’ failed registration of one or more associated DNS names(s) for following reason: DNS operation refused”
Error will be gone. You can continue testing failover to both nodes
We are done with the SQL Server Failover cluster installation using an Active Directory Managed Service Account