In this article I’m going to explain, step-by-step, how to install clustered SQL Server instance. The SQL Server version that we will be using is the 2014, or SQL Server 12, the latest SQL Server version, so far…
In this environment, we have a cluster with two nodes, based on Windows Server 2012 R2 operating systems. This cluster is based in a domain called SQLCAFE.
Regarding the cluster nodes, we have one network – 192.100.100.0/24 – connecting all the nodes and all other servers/computers. This is bases in my local lab, not a real production service. I opted to take advantage of the possibility to maintain a copy of the TempDB in a local disk, in order to have a batter performance, and added a SSD drive to each one of the nodes – local disk not shared. We also have more 4 shared storage, to support the data and log files, as well as the backups.
I tried to better simulate a real environment, during this installation I didn’t use a user without special permission in the domain, just to assure that we are following all the needed steps.
In the picture bellow you can find a schema of what we will have in this step-by-step guide. Even if you have a cluster with more nodes, you can follow this guide, as the step to add the clustered instance to other nodes is the same, so it doesn’t matters if you are dealing with a threes nodes cluster or a 8 nodes one, the procedure is always the same.
Note: I recommend the entire reading of the article before start with the actual installation, this way you can find out something that you are missing, or some constraint that may suspend the installation.
The following prerequisites are needed in order to achieve a successfully clustered SQL Server instance installation:
- SQL Server Engine service account: SQLCAFE\SVCSQLEngine
- SQL Server Agent service account: SQLCAFE\SVCSQLAgent
- Computer Name: SQLSRVCLT01
- Virtual IP (VIP): 192.100.100.50
Note: As a best practice, I create one service account for each SQL Server service, but this is not required. You can create one account to all the services if you want (SQL Server Engine, SQL Server Agent, SQL Server Integration Services, etc…).
You can have more than one VIP, if you have different subnets. For example, if you have a dedicate network to perform backups (great!) you can assign an IP for the backups network and the front-end IP to the same computer name.
The following should be assured for the SQL Server computer name:
- The computer name “SQLSRVCLT01” was previously created in the domain and marked as disabled.
- The cluster computer – W2012R2CLT01 – should have full access to the SQL Server computer account – SQLSRVCLT01.
In order to accomplish this, the following steps are needed in the domain level:
- In the “Active Directory Users and Computers” tool, activate the “Advanced Features”, as shown:
- Now go to the Computers folder, right click the created SQL Server Computer and add the permission in the “Security” tab:
- In the “Active Directory Users and Computers” tool, activate the “Advanced Features”, as shown:
Who is installing SQL Server?
I see people using the SQL Server service account to install the instance, in this case I’m using the domain user SQLCAFE\murilo to install the SQL Server clustered instance. This user is a normal user in the domain – not domain admin and has no special grants. The user SQLCAFE\murilo is part of the Administrators group on in both of the cluster’s node: W2012R2SRV01 and W2012R2SRV02. I didn’t set any permissions to both SQL Server service accounts any nodes of the cluster. We will see what is going to happen in the end of the installation…
Storage
For a clustered instance we need to use shared storage. The disk for the TempDB files and Buffer Pool Extension disk (if used) are exceptions. On both cases we need to have the local disks attached to each node and mapped to the same drive letter/mountpoint path.
For this instance, I’m creating the following disks:
- Assigned to drive letter E: Disk for SQL Server Binaries (local)
- Assigned to drive letter F: Disk for Data Files (shared)
- Assigned to drive letter G: Disk for Log Files (Shared)
- Assigned to drive letter H: Disk for Backups (Shared)
- Assigned to drive letter I: Disk for TempDB (local)
All the shared disks need to be shown as “Available Storage” in the Failover Cluster Manager, as you can see:
I formatted the SQL Server disks using an Allocation Unit of 64K, as the performance best practice states.
The Installation Process
Now that we have all the prerequisites, we can start the installation. As said, I’m using my personal domain account to log into the cluster nodes and start the SQL Server installation.
The approach to install the clustered instance is run the setup in the first node, and pick the option to install a new SQL Server clustered instance. After the initial installation, in the first node, we need to run the setup in all other nodes that we want to install the SQL Server clustered instance, and pick the option to add a node to an existing clustered SQL Server instance. We will walk through all of this in the continuation of this article.
Starting from the first node
I choose the node W2012R2SRV01 to start installing the instance. The reason for that? There’s no reason, only a matter of choice 
I mounted the SQL Server 2014 ISO and this is available for me in my drive D. So, the first step is run the Setup.exe as Administrator.
The “SQL Server Installation Center”, will be opened. And we need to go to the “Installation” option, in the menu on the left side, and click on “New SQL Server failover cluster installation” option (the second one).
The installation wizard will start-up. The first step is insert the product key (normally it comes automatically) and click next.
Now read the agreement and check the box right to “I accept the license terms”, click “Next”.
Now the wizard will check if the server where you are installing the instance has guaranties the Global Rules prerequisites. If you pass on all the tests, SQL Server will just proceed to the next step automatically.
At this stage, you have the option to download the updates for a most updated installation. I prefer to not select this option and apply the patches manually after the successful installation. Just click Next.
If you opted to include the updates, at this step the existence of updates will be checked, the files will be downloaded, extracted and installed. Just click Next (if the wizard don’t do this automatically).
Now the setup wizard will check for the Setup Rules, in order to identify potential problems that might occur while running the setup. Try to resolve all the failed checks. The one with warning can be ignored at this stage and resolved after the instance installation, if you prefer.
For this step, just keep the first option (SQL Server Feature Installation) selected and proceed to the next step.
This step is the most important so far! Here we need to pick all the instance and/or shared features that we want to install. In my case, I just selected the “Database Engine Services” and the “Management Tools – Complete”. This will guaranty that I have the SQL Server Engine, Agent, SQL Server Management Studio, SQL Server Configuration manager… If you need services like the Integration Services or Analysis Services, you have to select the respective options. Just make sure to select only what you need! Please, do not select all if you will not use the feature, as this will use server resources that could be used by the SQL Server.
Another important step here is the instance directory, basically where the binaries will be placed. You can notice that I indicated the drive “E”, the one I reserved for binaries.
Click Next to proceed.
Once again, SQL Server will check for rules, this time for the features tha we selected. In my case you can notice that I was missing the “Microsoft .Net Framework 3.5 Service Pack 1”, as shown:
The .Net framework is required, so I need to install this in order to proceed with the installation. So, open the “Server Manager” and go to “Manage”, followed by “Add roles and features”:
Now select the server where you are going to install, and jump to the “Features” page (select the option in the left menu). Now Select the first option “.NET Framework 3.5 features” and proceed with the installation.
This feature should be installed on all the nodes of the cluster where you are going to install the clustered instance.
After the successful .Net Framework 3.5 installation we can go back to the SL Server Setup Wizard and click in the “Re-Run” button. This time no problems were found. Click Next (if SQL Server Setup don’t do this for you – that thing is becoming smarter
Now we need to fill the SQL Server Network Name. Do you remember that computer name that I referred in the beginning of this article? I’m talking about “SQLSRVCLT01”. It’s time to use it! Fill the network name and proceed to the next step.
Note: If you have other instances installed, you might need to change the “Instance ID” value.
Now we need to set the cluster resource group name related to this instance. I usually change it by reflect the instance computer name, instead of the ID. Click Next.
At this step, you have a list of all the available disks. If you compare this list with the Failover Cluster Manager, you will notice that this is the same. Only the available storage will be available here. Sometime you have more storage in the available storage list, so you won’t need to select everything, so pay attention here. In my case, I had only the storage that I need to install my clustered instance, so I’m good to select all and proceed.
Here is another very important step. Do you remember the Virtual IP (VIP) that we defined in the beginning of this article? Now is the time to use it. Select the checkbox in the first column, make sure that the DHCP box is unchecked and insert the IP in the Address field. Notice that depending of the number of networks that you have available, you may need to have more than one IP!
Another prerequisite will be used. Now the service accounts. On this page you need to insert the respective domain accounts and passwords for the SQL Server Engine and SQL Server agent. Make sure to type the correct password, is common to lock out accounts at this step, and not always easy to ask to the domain admin to unlock it Another important point, do not change the “Startup Type” to automatic, as a clustered instance, the cluster manages where the instance will be active and all the operations need to be centralized to the Failover Cluster Manager.
It’s also important to choose the proper Collation, on most of the cases the preselected one is ok, but make sure about the requirements. Change the instance collation after the installation is not an easy task…
Now we need to define if the instance will work in “Windows Authentication Mode” or “Mixed Mode”. I usually choose “Mixed Mode” because there’s always an application that don’t work with domain accounts. By choosing this option you need to specify the password for the famous “sa” login. Notice that you can change the authentication mode anytime! You also need to add accounts to receive the Sysadmin privilege, this is very important… Even more if you choose the “Windows Authentication Mode”.
Let’s jump to the “Data Directories” tab. Here we need to define the disks/path of the root/system databases directory, user databases, log files, backup and TempDB. If you click in the “…” button, you will have all the available disks to pick. Attention that only the TempDB supports a local disk, the other items need to be placed in a shared disk.
If you want to configure the “filestream” feature, you can do in the respective tab.
After all the needed configuration, go ahead and click on “Next”.
As we choose a local disk to place the TempDB, this confirmation message will appear. Basically we need to make sure that the path referred in the “Backup Directory” exists in all the other nodes, so in case of a failover the instance won’t fail to create the TempDB, which obviously can compromise the instance startup.
Now one more check will run, this time to check if the failover cluster installation will be blocked. Click “Next”, if this wasn’t done automatically.
Now we are ready to rock! Just review all the configurations that you did during the process and click on “Install”!
Now it’s time to get one more coffee and watch a nice progress bar for some minutes… The installation is running!
We are done with the main installation! Verify that all the items were successfully installed. And click close.
You can also look to the Failover Cluster Manager and check the SQL Server role, and its components, reflecting the options taken during the installation wizard, like the IP address, Computer Name and disks.
Even with SQL Server installed on only one node, we can use the instance. We can use the SQL Server Management Studio to perform that, as we just installed this tool.
Ok, we are done with the first node. Now we need to proceed to the all other nodes. In our case, we are installing the instance of SQL Server in a two nodes cluster. Sometimes I hear people naming this configuration an “active-active” cluster, but this is not right. To complete our clustered instance installation, we just need to add another node to the installation. The following steps are showing how to perform this. If you need to install a clustered instance with more nodes, just follow the same steps.
Adding a node to a SQL Server clustered instance
Now that we have the first node installed, we need to add the other node to our clustered instance, this way we will be able to successfully perform a failover to the other node.
To start the installation, run the SQL Server setup as Administrator, as did in the first node installation.
Go to the “Installation” panel, by clicking in the respective menu and, this time, choose the option “Add node to a SQL Server failover cluster”, as shown in the image:
One more time, confirm your product key and click on “Next”.
Now check the box to accept the license terms and procced.
As done before, leave the “check for update” box unchecked and proceed.
Check if all the rules passed the test and resolve if something wrong was found.
Now select the instance that you want to add the node. Notice that you have a “Nodes” column with all the current installed nodes for the instance. In this example, we have only one instance installed, so there’s no room to fail the right one. In case of have more than one clustered instance installed in the same cluster, pay attention in when choosing the instance.
Select the expected networks.
At this stage, you need to confirm the password for the Engine and Agent service account:
Once again, more rules verification will be checked. Just proceed.
Finally, just confirm the settings and click on “Install”.
The SQL Server wizard will start the installation, just wait for some minutes…
Now the installation is complete! The node was added and is ready to be used.
So, let’s test it by doing a failover…
Success! Everything is online on node 02!
When adding this other node to the instance we didn’t select any features, anyway the shared tool were brought to this server, and SQL Server Management Studio is also available in this node.
MHITIPS 