Search This Blog

MS SQL Server 2012 failover cluster installation on Windows Server 2012 R2 (NEW)


How to install a clustered SQL Server 2012 Instance (step-by-step guide)

In this series, I will demonstrate how to install a SQL Server 2012 clustered instance in a cluster of two nodes. In general, the installation will be done in two parts:
  1. New instance installation in one of the nodes.
  2. Add the other node to the existing clustered instance.
For a cluster with more than two nodes, we would need to perform the first step in one of the nodes, and repeat the second step on all other nodes.
What is a clustered instance?Basically, a clustered instance is a SQL Server instance installed over a Windows Failover Cluster (WFC) service. The main purpose of a WFC solution is protect our systems from hardware failures.
In a scenario of a cluster with two nodes, we are talking about two servers, with similar hardware configuration, connected by a Failover Cluster service. Having one SQL Server instance installed over this solution, we can call this instance as a clustered instance. That clustered instance must be active in only one of the available nodes, and this means that the other nodes will be in IDLE mode, with no active functions.
Another important point is that the WFC accepts shared storage, which means that we need a SAN to store the database files (logs and data). However, the SQL Server binaries generated by the installation should be in a local disk.
Other than shared storage, we also have an option to store our database files into a SMB Fileshare, which is cheaper, but not as good as a solution using SAN. From SQL Server 2012 we have an option to store the TempDB isolated in a local disk, which brings lots of benefits.
This way, the WFC is a high availability solution and not a load balancing or a disaster recovery solution. We can reach this by having an AlwaysOn configuration, available from SQL Server 2012.
AssumptionI’m assuming that at this point we already have a built cluster solution with two or more nodes. Normally, the DBA receives the environment ready to install the clustered instance. The WFC build is usually made by the System Administrators. However, I’m planning on doing another article explaining how to configure a WFC solution. Stay tuned!
Prerequisites
Before we start the installation, we need to assure that we have the following items ready to be used:
  • A virtual hostname. In our example we will use “SQL04″.
  • A virtual IP, a.k.a vIP. We will use: 192.168.123.124.
  • Available shared storage. The best practice is have, at least, one for Data files (mdf and ldf), one for Log files (ldf) and one for Tempdb files. On this guide I will use one disk for everything, to simplify, but this is a bad approach!
  • Service Accounts: One for SQL Server Engine and another for SQL Server Agent (this is the best practice). We will use the following accounts: SSLABSVCSQLSRVENG and SSLABSVCSQLAGT.
    • Notice that the service accounts are domain accounts. We have no other choice, to build a cluster we need to be part of a domain!
Our Environment
On this step-by-step guide, we will use the following environment – based in virtual machines:
  • Windows Server 2012 R2 nodes:
    • W2012SRV03 – 192.168.123.205
    • W2012SRV04 – 192.168.123.206
  • The both nodes are part of the following cluster:
    • W2012CLT02 – 192.168.123.111
  • Storage:
    • As this is a lab, I’m using a Synology Diskstation as my SAN. Just for information, the IP is: 192.168.123.103.
  • For SQL Server:
    • vHostname – SQL04
    • vIP – 192.168.123.124
    • Version: Microsoft SQL Server 2012 (SP1) – 11.0.3128.0 (X64)  -  Enterprise Edition
    Screen Shot 2013-12-12 at 12.03.53
Installation Permissions for the used loginTo install the SQL Server I’m using the domain login called “SSLABdba”, which is part of the Administrators group on W2012SRV03 and W2012SRV04. The login “SSLABdba” is a simple user into the domain, without special permissions.

-----------------------------------------------------------------------------------

Part 2:
In continuation of our series on how to install a SQL Server 2012 clustered instance, let´s start the actual installation… Starting from the first node.
The objective of this second article is show how to install the first node of a clustered SQL Server 2012 instance and how to basically manage it from the Failover Cluster Manager tool, on Windows Server 2012 R2.
Just to refresh our memory, this is our infrastructure:
Screen Shot 2013-12-12 at 12.03.53
For more details about the prerequisites and a general explanation, check out Part 1 of this series.
The installation:  Starting from the very first node
So let’s start the installation. If you remember, I mentioned that we have two steps to complete the installation on both nodes. For now we will start doing the new instance installation in one of the nodes. This node will be W2012SRV03.
  1. With the SQL Server installation binaries available, click “Setup”:Screen Shot 2013-12-09 at 12.38.54
  2. The SQL Server Installation Center will open. Click “Installation” in the left menu, then select “New SQL Server Failover Cluster Installation”:Screen Shot 2013-12-09 at 12.39.28
  3. A check will run in order to find possible constraints when installing the SQL Server Setup support files. At the end of the check, click “OK”:Screen Shot 2013-12-09 at 16.22.39
  4. Now the installation will check for available updates, I recommend that you include those updates into the installation. Click “Next”:Screen Shot 2013-12-09 at 16.23.21
  5. At this step, the setup support files will be extracted and installed, click  “Install”:Screen Shot 2013-12-09 at 16.25.48
  6. Finally, we have all the setup files installed. Another check will run in order to validate if problems might occur when SQL Server files is installed.Screen Shot 2013-12-09 at 16.26.54
    Best Practice:
    It’s recommended to have a clustered MS DTC resource, as well as a dedicated MS DTC resource dedicated to each SQL Server group.
    Here is a link to a good resource about this theme:http://blogs.msdn.com/b/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx
  7. At this step, just insert the product key and click “Next”:Screen Shot 2013-12-09 at 16.29.32
  8. Select “I accept the license terms” and click “Next”:Screen Shot 2013-12-09 at 16.30.08
  9. To install a SQL Server clustered instance, choose the first option “SQL Server Feature Installation”. Click “Next”.Screen Shot 2013-12-09 at 16.31.27
  10. Here we have few options to install on “Instance Features” and “Shared Features” sections.
    For “Instance Features” pick the “Database Engine Services” item.
    For “Shared Features” pick “Management Studio (complete)”.Another important point here is the directory that the shared tools will be installed. A good practice is have a dedicated local disk to install SQL Server related files. In the image I’m using the drive C, the same that I have my OS. This is my lab, so I advise that this is not a good practice for a productive system. Click “Next”.Screen Shot 2013-12-09 at 16.38.20
  11. At this step another check will run, this time to identify problems that might  block the setup, based on our choices of the last step. Just wait for its completion and click “Next”.Screen Shot 2013-12-09 at 16.39.02
  12. This is an important step: Here we will define the instance network name (one of our prerequisites), whether we will use a default or name instance, the Instance ID and the instance root directory.A few things to consider here:
    - On “Detected SQL Server instances and Features on this computer” section, we already have one instance installed. So I had to use a named instance and change the Instance ID to do not conflict with the existing one.
    - Based on the information above, to connect to our instance we will need to use: SQL04\DB.
    - Another important point is the “Instance root directory”: It’s recommended to use a dedicated local disk to install the SQL Server binaries. Avoid using the system drive, a.k.a. “drive C”.Fill and verify all the points an click “Next”:    Screen Shot 2013-12-09 at 16.46.40
  13. This step confirms if the disk space requirements are being met. Click “Next”:Screen Shot 2013-12-09 at 16.47.02
  14. Now this is related to the Cluster Resource Group name to be used. The installation suggests a name, but you can change it.
    This window also shows the reserved and already used Resource Group names. Change the Resource Group name if you are not satisfied with the suggestion and click “Next”:Screen Shot 2013-12-09 at 16.47.37
  15. Now the disks! Another piece of the prerequisites has been shown. At this step, we will have the information of all available storage to be used on our new clustered instance.
    As mentioned before, I have only on available disk for this guide, so let’s use this. Just select the desirable disks and click “Next”:Screen Shot 2013-12-09 at 16.50.23
  16. Do you remember the requisite of an IP? It’s time to use it! Just pick the network that you will use, unmark the DHCP column and fill the address column with the value of the IP. And… “Next”:Screen Shot 2013-12-09 at 16.56.12
  17. Here is our last requisite: Service Accounts. Fill the information about the SQL Server Engine and Agent service account (login and password).
    Note: For a clustered instance, the “Startup Type” for the services should be as “Manual”. The Cluster Service will manage this for us.
    Click “Next”.Screen Shot 2013-12-09 at 17.03.20
  18. At the same step, we have another tab where we can define the collation to be used in our database engine. By default it has “Latin1_General_CI_AS”.
    For more information about collations, click here. This can be a very important choice! If you have an special collation requirement, don’t forget to select the right one during the installation, otherwise you will have a hard work ahead to change this.    Screen Shot 2013-12-09 at 17.05.59
  19. Now we need to choose the authentication mode of our instance.The options are either “Windows Authentication”, which will take benefit from domain and local server logins, or “Mixed Mode”, which accepts Domain/Windows logins as well as logins created and managed by SQL Server.If you pick “Mixed Mode” a login called “sa”, member of the “Sysadmin” role will be enabled. For this reason we need to specify the password for this login.In the box bellow, we need to add all domain/Windows users that have access to the instance and will be part of the “Sysadmin” fixed server role. You need to use the three buttons to add/remove logins from this list:Screen Shot 2013-12-09 at 17.10.25
  20. Now it’s time to define the disk strategy.This step worth an entire article — I’m using only one disk for demonstration purposes, but the recommendation is to use one isolated disk for each one of the points.
    The general rule here is: The most spread, the better!
    We could use a layout like the following:
    Screen Shot 2013-12-16 at 17.02.52
    The only thing that we cannot specify directly is a place to store the non-clustered indexes. This separation of the non-clustered and clustered index is a case aside.
    Points to take attention here:
    • Isolate the TempDB in a fast disk. Remember: From SQL Server 2012 we can store the TempDB in a local disk on clustered installations!
    • Place your data files in one disk and log files in another one.
    • Request the disks with an appropriate RAID level.
    • Pay attention to the partition offset and block size before the installation, even if you are using a Windows 2008+ OS.
    After consider all those points, set carefully the disks for each point and click on “Next”.
    Screen Shot 2013-12-09 at 17.10.25
  21. Here you have an option to send error reports or not. Click “Next”:Screen Shot 2013-12-09 at 17.11.40
  22. Another check will run to verify if the failover cluster installation will be blocked. After the check, click “Next”:Screen Shot 2013-12-09 at 17.12.21
  23. Here we will be able to review all chosen options. Review and click “Install”:Screen Shot 2013-12-09 at 17.12.32
  24. The installation will begin — wait for completion.Screen Shot 2013-12-09 at 17.13.21
  25. In the end you will have a confirmation about the success or not of each feature installation.
    You are done here! Click  “Close”:Screen Shot 2013-12-09 at 17.20.21
On this second part, we passed for all the steps to install the first node of a clustered instance. Of course some points will be slightly different from your environment, but here are pretty much all the steps to follow. After the completion of this installation you will need to add the other nodes to this clustered installation and you will be able to see the SQL Server “Role” created into the WFC Manager.
Dealing with Failover Cluster Manager
As we already have one node of our clustered instance installed, we will need to manage its resources using the Failover Cluster Manager tool.
  • Click on the “Windows Key”+R.
  • Write “cluadmin.msc” and click on “Ok”.
  • The Windows Failover Cluster will be opened.
Screen Shot 2013-12-17 at 12.39.52
On the image you can see two “Roles”, representing two clustered SQL Server instances. The labeled as “SQL Server (DB)” is the one that we installed (Do you remember the choice made on the step 14?).
Selecting this Role will show all the resources that are part of this clustered installation, such as IPs, Disks, etc.
At this point, we have the clustered instance installed into one node only, so we cannot do a failover yet.
To stop the SQL Server, which will stop also the SQL Server Agent, right-click over the SQL Server Engine service and do the following:
Screen Shot 2013-12-17 at 12.49.06
In other hands, to start do the following:
Screen Shot 2013-12-17 at 12.51.49
Note: When you start the SQL Server, the agent service needs to be started also. One way to reduce the number of steps is first start the Agent, this way, the SQL Server Engine will start automatically. The reason is that the SQL Server Agent is dependent of the SQL Server Engine, this way WFC will try to start the Engine service prior to start the Agent.
Another very important item are the dependencies of our SQL Server.
To check that:
  • Right-clicking the SQL Server Engine service.
  • Click on Properties.
  • Select the “Dependencies” tab.
Screen Shot 2013-12-17 at 13.11.50
This way you can see all the resources that the SQL Server Engine is dependent. Looking for our picture, if either the “Cluster Disk 1″ or “SQL Server network name (SQL04)” fails, the SQL Server Engine will shutdown/failover!!
All the clustered resources can be dependent of another resource. For example, the “SQL Server network name (SQL04)” is dependent of the IP. This way, if the IP fails, the resource “SQL Server network name (SQL04)” will be offline and this will fire a SQL Server failover/shutdown.
What to take from here?
Check the dependencies and conditions on this tab, this can be useful to increase you availability rate!
As a practical example:
All the disks are important, but if the backup disk fails, we can continue with the service online, and fix the problem in background. But, if the SQL Server is dependent of this disk, we will have a failover/shutdown.
So, pay attention to this!
What’s next?
After completing the second step, we already have our instance working on a clustered environment, but we have only one available node. We will complete this series with more two parts:
  • Adding another nodes to our WFCI.
  • Configuring a dedicated MS DTC resource for our SQL Server Role.

---------------------------------------------------------------------------------

Part 3:
In continuation of our series on how to install a SQL Server 2012 clustered instance, let’s discuss how to add a node into an existing SQL Server clustered instance. The following steps are performed either to add one more node to some already installed clustered instance, or to continue the installation of a brand new clustered instance — It all boils down to the same thing. To perform this phase, you will need to have at least one node installed. In this case, we installed a new SQL Server failover instance in the Part 2 of this series.
So connect to the next node, in this case W2012SRV04, and perform the following steps:
1. Make sure that you have the same SQL Server 2012 media used to install in the other node available and execute the “Setup” binary.
Screen Shot 2013-12-09 at 12.38.542. The “SQL Server Installation Center” will be opened.
Screen Shot 2013-12-09 at 12.39.28
3. Still on “SQL Server Installation Center”, click “Installation” and select “Add node to a SQL Server failover cluster”.
Screen Shot 2013-12-09 at 22.38.41
4. A check will run in order to verify the setup support rules. Click “OK”.
Screen Shot 2013-12-18 at 14.46.07
5. Now the setup will check and install the latest updates. Keep the “Include SQL Server product updates” checked and click “Next”.
Screen Shot 2013-12-09 at 22.39.51
6. Another check will run in order to identify problems within the installation process. Click “Next”.
Screen Shot 2013-12-09 at 22.42.01
7. Insert the product key and click “Next”.
Screen Shot 2013-12-09 at 22.42.17
8. Accept the terms and click “Next”.Screen Shot 2013-12-09 at 22.42.28
9.  On this step you need to pick the instance where this installation will be related. Notice that you have a list of installed instances, as well as the nodes that the instances are already installed.
In our case, the Instance Name “DB” is installed in only one node, and we need to choose this instance in the list box in the top to proceed to the node addition.
Screen Shot 2013-12-09 at 22.44.13
10. Now. confirm the IP settings as you did in the first node installation. Click “Next”.
Screen Shot 2013-12-09 at 22.45.02
11. Fill the passwords for the Engine and Agent service account, and click  “Next”.
Screen Shot 2013-12-09 at 22.45.36
12. Like in the other (first) node, you have the option to send error reports to Microsoft. Click on “Next”.
Screen Shot 2013-12-09 at 22.46.14
13. Now the setup will verify if the installation process can be blocked. In the end, click “Next”.
Screen Shot 2013-12-09 at 22.46.26
14. Review the options and click on “Install”.
Screen Shot 2013-12-09 at 22.46.47
15 Now you can watch the installation progress. Click “Next” when it is done.
Screen Shot 2013-12-09 at 22.47.00
16. Now the node addition is complete! Just verify if all of the features have succeeded. Click “Close” and you are done!
Screen Shot 2013-12-09 at 22.56.08
At the end of this installation, you will have one more node available to run out our instance. This means that we can now perform a failover from W2012SRV03 to W2012SRV04, so we have now a high availability (HA) solution. In case of a hardware failure on the active node, we will have a failover action.
For some reason, such as a test or for maintenance purposes, you can do a failover manually. To perform this, open the Failover Cluster Manager tool (the same used on step 2), right-click over the role name (in our case “SQL Server (DB)” and then select the “Move” option. Two options will be shown — the first “Best Possible Node” and the second one “Select Node…” Both are valid, but the second gives you the chance to choose the node to move the Role, which is useful when you have more than two nodes.
Screen Shot 2013-12-18 at 16.38.27


Part 4:

We have reached the last article of this series. To close this series out, we will talk about Distributed Transaction Coordinator, or simply DTC. I’ll try to do some simple explanation. After that, I’ll demonstrate how to prepare the DTC for a clustered instance.
What is DTC (MS DTC)?
The MS DTC is a OS level service, which comes automatically installed and running under the Network Service account. Its role is to ensure that a distributed transaction is consistent, even with failures.
Those transactions might be initiated when a transaction is dealing with data on multiple computers via network or when the transaction is dealing with multiple processes in a single computer.
All the participants of a distributed transaction works in sync with the other participants (computers) involved in a transaction, looking for the right moment to commit or abort its work. For this reason, we need to make sure that the computers can reach each other.
Do I need to configure MS DTC on my environment?The answer for this question is the standard for almost everything involved with SQL Server; It depends. You need to understand whether or not you will perform distributed transactions. If you have more than one instance in the same computer, you won’t need the DTC. On the other hand, if you have a two nodes cluster with two clustered instances communicating with each other, you will need the DTC – the instances could be in different nodes. Another possible scenario is when you have the database engine and SSIS installed, in this case you will need to configure the DTC.
For more information, check this link: http://msdn.microsoft.com/en-us/library/ms189910#MSDTC
How to create a clustered MS DTC?
Since Windows 2008, we are allowed to have more than one instance of MS DTC in a server/cluster. So, for clustered SQL Server installations is a best practice to have a Role exclusively for the DTC and a dedicated DTC for each SQL Server Role.
As documented per Microsoft, the SQL Server follow this path to choose the MS DTC instance to use:
  • Use MS DTC installed to the local group, else
    • Use the mapped instance of MS DTC, else
      • Use the cluster’s default instance of MS DTC, else
        • Use the local machine’s installed instance of MS DTC
To configure a DTC in cluster, we will need a disk and a hostname.
To configure a Role exclusively for the DTC, follow the steps:
  1. Right-click on Roles and pick the “Configure Role” option.
    Screen Shot 2014-01-02 at 14.18.43
  2. A new window will open. Click “next”.Screen Shot 2014-01-02 at 14.18.52
  3. Choose the option “Distributed Transaction Coordinator (DTC)” from the list. Click  “Next”.Screen Shot 2014-01-02 at 14.19.11
  4. Fill the hostname in the “Name” field and the IP in the “Network” section. Click “Next”.Screen Shot 2014-01-02 at 14.20.33
  5. Pick up the disk to be used. Click “Next”.Screen Shot 2014-01-02 at 15.14.42
  6. Review the configurations and click “Next”.Screen Shot 2014-01-02 at 15.14.57
  7. The installation will run and in the last step you will see a report. Click “Finish”.Screen Shot 2014-01-02 at 15.15.11
  8. Now you will be able to see a new Role created in the cluster, with all the indicated resources.
Screen Shot 2014-01-02 at 15.16.51
To add a DTC resource into the SQL Server Role, follow the steps:
  1. Right-click the Role, go to “Add Resource”->”More Resources” -> “Distributed Transaction Coordinator”.Screen Shot 2014-01-02 at 15.30.50
  2. The resource will be created in the selected Role, now we need to configure it. Right-click the “New Distributed Transaction Coordinator” and click on “Properties”.Screen Shot 2014-01-02 at 15.31.20
  3. As referred early on this article, the DTC needs a hostname and a disk to work. On dependencies you can pick up those items as shown, and click “ok”.Screen Shot 2014-01-02 at 15.32.44
  4. Now, let’s bring it online.Screen Shot 2014-01-02 at 15.32.55
How to configure the network for distributed transactions?
Note: On clustered environments, you just need to perform the following steps one time.
  1. On “Server Manager” go to “Tools”->”Component Services” or run the command “dcomcnfg”.Screen Shot 2014-01-02 at 15.33.55
  2. Expand the tree, right-click the desired DTC and choose “Properties”.Screen Shot 2014-01-03 at 11.46.28
  3. Go to the “Security” tab and check “Network DTC Acess” as well as “Allow Inbound” and “Allow Outbound”, as shown bellow. Click Ok.Screen Shot 2014-01-03 at 11.49.02
  • Let’s briefly describe the some of the options on this window:
    • “Network DTC Access“: Enable/Disable the network access.
    • “Allow inbound“:  Permit a distributed transaction originated from another computer to run on the current computer.
    • “Allow outbound“:  Permit a distributed transaction initiated in the current computer to run on a remote computer.
    • “Enable XA transactions” and “Enable SNA LU 6.2 Transactions“: Enables/Disable those particular specifications for distributed transactions.
Troubleshooting DTC
There’s a tool called DTC Ping which can help us to verify if the DTC is working correctly on all the computers that should be involved in a transaction.
I recommend the reading of this article, to learn hos to use this tool, as well as troubleshoot the possible errors: Troubleshooting MSDTC issues with the DTCPing tool.
Another great tool is the DTC Tester. You can simulate a distributed transaction on SQL Server:
Screen Shot 2014-01-03 at 14.15.25
To download and get more info about this tool, check this link: http://support.microsoft.com/kb/293799 .
The End
This way we finish this series about how to install a clustered instance. We still have too many details to cover and I will try to create separated articles with best practices, configuration alternatives, etc.
I hope you enjoyed this series and as always, if you have any doubts, contact me!