Getting Started with SQL 2012 Clustering

I’ve been playing with SQL Clustering recently. Why? It’s cool! Here’s how to get it up and running quickly.

Overview

  1. Create an MSCS Failover Cluster with 2 nodes.
  2. Install the SQL Cluster role.
  3. Configure the SQL nodes.
  4. Install the MS DTC cluster role.
  5. Test MSDTC

Create an MSCS Failover Cluster

To do this, see my previous post Windows Server 2012 – Getting Started With Failover Clustering. Your cluster will need the following characteristics:

  • Quorum disk configured.
  • A free cluster disk for DTC use.
  • A free cluster disk for SQL use.

Install the SQL Cluster Role

Here are the steps:

Create the Service Accounts

#read-host must be done manually or it assumes that the lines following it are the input.
$cred = Read-Host "Account Password:" -AsSecureString
$namePrefix = "sqlc1-"
$accounts = @("ssa","ssde","ssrs","ssis")
$accounts | %{$newname = $namePrefix + $_; New-ADUser -name $newname -Enabled $true -AccountPassword $cred}

Install .Net 3.5

  1. Insert windows media.
  2. Run the following PowerShell code:
    Install-WindowsFeature net-framework-code -source D:\windows\sxs

Install SQL Node 1

Steps:

  1. Insert SQL media
  2. Run the install wizard.
  3. Choose Installation -> New SQL Server Failover Cluster Installation

Here are my install notes:

  • On the screen ‘Setup Support Rules’, you will likely see warnings for MSDTC, MSCS, and Windows Firewall. This is OK.
  • On the screen ‘Instance Configuration’, enter the desired netbios name of your cluster under ‘SQL Server Network Name’. Don’t worry about the instance root directory being on a non-cluster disk. The instance root that you see under ‘Instance Root Directory’ is not where the databases will actually be stored.
  • On the screen ‘Cluster Resource Group’, you will see a sheet with two red circles in the ‘qualified’ column. This is normal, just hit ‘next’.
  • On the screen ‘Cluster Disk Selection’, check the box next to the cluster disk that you’d like to use for the SQL database. We will need to leave at least 1 disk free for MSDTC, so don’t check the boxes next to all of the cluster disks. Leave one available.
  • On the screen ‘Cluster Network Configuration’, check the box for the cluster network you’d like to use. I recommend entering a static address. The cluster will auto-register its name with DNS, but if you want to use TCP/IP you will need to enter specific IP’s into SQL Server Configuration Manager on each node.
  • on the screen ‘Server Configuration’, enter the username\passwords for the service accounts created in a previous step. Leave the ‘startup type’ as the default for all services (most should be set to ‘manual’).

Verify that the SQL Role is online using Failover Cluster Manager. Then, verify that you can connect to the SQL Cluster Network Name using SQL Server Management Studio (from the node itself since we haven’t configured TCP). If it connects, then your first node is up. Congrats!

Install SQL Node 2

There is nothing substantially noteworthy about adding the second node. The installation wizard is streamlined and straightforward.

  1. Insert SQL media.
  2. Run the install wizard.
  3. Choose Installation -> Add Node to a SQL Server Failover Cluster

Configure TCP and Firewalls

On both nodes, do the following:

  1. Launch SQL Server Configuration Manager
  2. Navigate to SQL Server Network Configuration -> Protocols for MSSQLSERVER
  3. Right click TCP/IP and choose ‘Properties’.
  4. On the ‘IP Addresses’ tab, make sure that the IP addresses for the cluster nodes and the SQL Cluster network resource are entered and enabled.
  5. Close SSCM.
  6. Open Powershell and run the following commands to open the firewall. I recommend choosing a firewall rule more specific than the one I’m presenting here, but it will get you started.
    New-NetFirewallRule -DisplayName "MSSQL BROWSER UDP" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow -Profile Domain
    New-NetFirewallRule -DisplayName "MSSQL ENGINE TCP" -Direction Inbound -LocalPort 1433-1434 -Protocol TCP -Action Allow -Profile Domain
    

You should now be able to connect to the SQL Server Cluster network name from any domain computer. Pretty awesome.

Install the MS DTC Cluster

I didn’t know anything about the Microsoft Distributed Transaction Coordinator service until this SQL clustering adventure. Here’s some background information.

  1. (wikipedia) Microsoft Distributed Transaction Coordinator
  2. What exactly is MSDTC, any when do I need it?
  3. How to install a clustered SQL Server 2012 Instance – step-by-step – Part 4

Basically, it’s a service that handles transactions across multiple nodes. Some applications as well as SQL services require MSDTC. I’m choosing to cluster it even though I’m not absolutely sure it’s necessary. Here’s how to set it up.

First, copy the following PowerShell code. Scan through and replace any strings with the ones you’d like to use.

#Add DTC Role
Add-ClusterResource "sqlc1-dtc" -ResourceType "Distributed Transaction Coordinator" -Cluster cluster1 -Group "SQL Server (MSSQLSERVER)"

#DTC Depends on Network Name
##find the name of the network name resource with Get-ClusterResource
##example: $sqlNetworkName = "SQL Network Name (sqlc1)"
$sqlNetworkName = "Insert SQL Cluster Network Resource Name Here"
Add-ClusterResourceDependency "sqlc1-dtc" $sqlNetworkName -Cluster cluster1

#Add DTC Disk to DTC Group
##chose a cluster disk. 1GB freespace is fine. It just stores some logs.
Move-ClusterResource "Cluster Disk 2" -Cluster Cluster1 -Group "SQL Server (MSSQLSERVER)"

#DTC Depends on Disk
Add-ClusterResourceDependency "sqlc1-dtc" "Cluster Disk 2" -Cluster cluster1

#SQL Server Depends on DTC
Add-ClusterResourceDependency "SQL Server" "sqlc1-dtc" -Cluster cluster1

In order to get DTC working between nodes, I needed to open all firewall ports between the DTC nodes themselves, and between the DTC nodes and the application server. I’m sure there’s a better way to do this. Again, this will get you started.

In powershell, on each node:

#$IPAddresses = @("node1-IP","node2-IP","Test-Client-IP")
$IPAddresses = @("10.10.10.10","10.10.10.50","10.10.10.54")
New-NetFirewallRule -name clustering-rpc-ports-to-partners -remoteaddress $IPAddresses -displayname "Clustering - Allow RPC to Partners"

Finally, we need to enable network access for the DTC service. To do this, complete the following procedure:

  1. Logon to the node actively running the DTC role.
  2. Run the ‘Component Services’ snap-in from ‘Control Panel’ -> ‘Administrative Tools’.
  3. Navigate to ‘Component Services’ -> ‘Computers’ -> ‘My Computer’ -> ‘Distributed Transaction Coordinator’ -> ‘Clustered DTCs’.
  4. Right-click the DTC cluster and choose ‘Properties’.
  5. On the ‘Security’ Tab, choose the following checkboxes:
    [X] Network DTC Access
    [X] Allow Remote Clients
    [X] Allow Inbound
    [X] Allow Outbound
  6. Click ‘OK’, then restart the SQL failover cluster via the Failover Cluster Management tool.

Assuming all went well, you now have DTC configured. Let’s test it!

Testing the DTC Cluster

Download the following tools from Microsoft. Extract them to a temp directory of your choice on both cluster nodes, and on a test client.

  • DTCPing
  • DTCTester

Testing Basic Connectivity

Perform the following procedure to test DTC basic connectivity:

  1. Launch DTCPing on both DTC nodes.
  2. On node1, type the netbios name of node2 into the DTC ping utility and press ‘Start’.
  3. You should see a successful response.
  4. Try this from the test client also, after relaunching DTCping on the nodes.

If DTC ping fails, it’s probably a name resolution or network issue.

Testing Actual Transactions

Perform the following procedure to test actual DTC transactions:

  1. On node1, naviagte to Control Panel -> Administrative Tools -> ODBC Connectors (32-bit).
  2. Create a new System DSN pointing at the netbios name of the SQL cluster.
  3. Enable mixed-mode authentication on the SQL cluster via SQL Server Management Studio.
  4. Create a new SQL account on the SQL cluster named ‘test’. Give it full permissions on the databases and cluster via SQL Server Management Studio.
  5. Run DTCTester.exe with the following syntax:
    dtctester.exe <dsn-name> <user> <password>
    example: dtctester.exe sqlc1-dsn test myPassword

You can perform the same procedure from a test client instead of a node, however there are additional steps required.

On a test client, perform the following steps before running DTCTester.exe

  1. Install DTC with the following powershell command:
    DTCInstall
  2. Navigate to Control Panel -> Administrative Tools -> Component Services.
  3. Navigate to ‘Component Services’ -> ‘Computers’ -> ‘My Computer’ -> ‘Distributed Transaction Coordinator’ -> ‘Clustered DTCs’.
  4. Right-click the DTC cluster and choose ‘Properties’.
  5. On the ‘Security’ Tab, choose the following checkboxes:
    [X] Network DTC Access
    [X] Allow Inbound
    [X] Allow Outbound

If dtctester.exe fails, follow the chart at this page: Troubleshooting MSDTC Communication Checklist.

Now, you can create the DSN on the test client and run dtctester.exe

Whew, and now you’ve got a working SQL cluster with DTC. Not the easiest thing ever. Easier than getting vPro running at least ;).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s