Tag: Cluster

Configuring DTC Security In A Cluster With PowerShell

The other day Allan Hirt (blog|twitter) write a fantastic post around “How to properly configure DTC for Clustered Instances of SQL Server with Windows Server 2008 R2”. He included a PowerShell script to handle all the funky setup stuff. The only downside was that after that you had to manually go through and configure Network Access and security for the clustered DTC using the GUI.

“There has to be a better way” I thought. I recalled how security back in 2003 could be set in the registry so I went digging. Eventually I was able to find the requisite keys under the Distribute Transaction Coordinator resource. From there it was just a case of tying things back so that those keys could be easily found from the SQL Resource Name and then updating them so that they matched what Allan demonstrated on his page.

I’ve tested it on a couple of my clusters and it’s worked exactly as designed, you just need to change the ServiceName variable so that it matches your clustered service name and then decided what authentication method you want to use (it defaults to the most secure level: Mutual Authentication Required).

Get the code below or download ConfigureMSDTC.ps1

<#

.SYNOPSIS

   Configures MSDTC on a cluster

.DESCRIPTION

   This configures a basic networked config for MSDTC for clustered SQL Server instances.

   Uncomment the transaction manager security setting and enter the correct ServiceName that can be found in FC manager

.PARAMETER <paramName>

   NONE

.EXAMPLE

   NONE

#>

 

$ServiceName = "SQL Server (SOX2)"

 

#--What MSDTC Transaction Manager Security setting is requested? (uncomment one)

$TranManSec = "Mutual" #Mutual Authentication Required

#$TranManSec = "Incoming" #Incoming Called Authentication Required

#$TranManSec = "None" #No Authentication Required

 

 

 

 

#Grab a list of cluster groups

$GroupPath = "HKLM:ClusterGroups"

$GroupList = dir $GroupPath

 

#Iterate through the groups to find the one matching the service name

foreach ($Group in $GroupList)

{

    $GroupChildPath = $Group.PSPath

    if ((Get-ItemProperty -path $GroupChildPath -name Name).Name -eq $ServiceName)

    {

        #we got a match! Now grab a list of the groups in this service

        $ReferencedResources = (Get-ItemProperty -path $GroupChildPath -name Contains).Contains

        foreach ($Resource in $ReferencedResources)

        {

            #Query each of the resources for their type and work with MSDTC

            $ResourcePath = "HKLM:ClusterResources$Resource"

            if ((Get-ItemProperty -path $ResourcePath).Type -eq "Distributed Transaction Coordinator")

            {

                #We found MSDTC resource for that service group, let's configure it

                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTCSecurity"

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccess" -value 1

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessClients" -value 0

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessTransactions" -value 0

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessInbound" -value 1

                Set-ItemProperty -path $SecurityPath -name "NetworkDtcAccessOutbound" -value 1

                Set-ItemProperty -path $SecurityPath -name "LuTransactions" -value 1

 

                #Now configure the authentication method for MSDTC (defaulting to Mutual Auth as it's most secure)

                $SecurityPath = "$ResourcePathMSDTCPRIVATEMSDTC"

                if ($TranManSec -eq "None")

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 1

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0

                }

 

                elseif ($TranManSec -eq "Incoming")

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 1

                }

 

                else 

                {

                    Set-ItemProperty -path $MSDTCPath -name "TurnOffRpcSecurity" -value 0

                    Set-ItemProperty -path $MSDTCPath -name "AllowOnlySecureRpcCalls" -value 1

                    Set-ItemProperty -path $MSDTCPath -name "FallbackToUnsecureRPCIfNecessary" -value 0

                } 

 

            }

        }

    }

}

 

Creating A Clustered SQL Install ini File Using PowerShell–Part 3

In parts 1 and 2 the cluster was made ready for the SQL install and the Active Directory objects were created. In the final part of the series we will finally get to creating the ini file and installing SQL.

As an FYI this script is designed to install the following components along with the Database Engine:

  • SQL Server Replication
  • Full-Text Search
  • Business Intelligence Development Studio
  • Client Tools Connectivity
  • Integration Services
  • Client Tools Backwards Compatibility
  • Client Tools SDK
  • SQL Server Books Online
  • Management Tools – Basic
    • Management Tools – Complete
  • SQL Client Connectivity SDK
  • Microsoft Sync Framework

The ini can be quickly modified to remove certain of these components if they are not required (I’m looking to add selections for these items to a future version).

 

Copy the script to the server

Download the CreateClusteredSQLInstallFile.ps1 and copy it over to the server. From there it can either be opened directly in PowerShell ISE or called from within the PowerShell shell.

If calling the script from the shell you might get an error:

 

If so you’ll need to reopen the shell as an administrator and change the execution policy to RemoteSigned

set-executionpolicy Remotesigned

 

This time it should run without any errors.

 

Working through the script

The first thing to come up is an informational box letting you know that you’ll be creating ini files:

 

Next, depending upon whether you started the script through the shell or the ISE you’ll see different things…in the ISE you’ll see a pop-up window asking if you want to “Install a new clustered instance of add node to an existing cluster?”, this is in case you want to generate an ini file for a cluster you already have in place. By default this will create files for a new clustered install of SQL Server:

In the shell you will just get a prompt”:

 

A new window will appear asking where you want to put the ini files when they are created. I’ve chosen to put them in C:SQL Install ini (this is the same if you use the shell or the ISE)

 

Next you’ll be prompted to enter the SQL virtual name (going forward I’ll be showing the prompts through the ISE, keep an eye on the shell if that’s what you are using). This is the disabled computer account we created in Active Directory:

 

The next prompt will ask for an instance name, here I’m going with PROD (which will give us the SQL instance of GROOPROD):

 

Follow this up by entering the IP address:

 

The next two prompts are for the SQL Service and SQL Agent accounts to be used:

 

Now choose a root folder for the SQL system databases (master/model/msdb). A subdirectory of SQLSystem will automatically be created, as such I am using the root of G:

 

A similar window will pop-up and ask for the location of TempDB, the data and log files and backups. This is just another case of choosing the appropriate locations. As a note from the location you choose the following subfolders will be created and used:

  • TempDB – <root>MSSQLData
  • SQL Data Files – <root>MSSQLData
  • SQL Log Files – <root>MSSQLLogs
  • Backups – <root>MSSQLBackup

 

 

The final step is a prompt that will appear asking what failover clustered disks should be used. These will be listed under available storage in the Failover Cluster Manager. Only the disks to actually be used should be added. This is a comma separated list of those disks (the screenshot shows the disks next to the prompt for easy identification):

 

A completion pop-up appears indicating what files were created and how to use them:

 

The filenames are generated based upon the user creating the script and the time that the script started executing (this helps to prevent accidental duplicates).

 

If you’d used the shell to execute the script you’d expect to see the following:

 

I’ve got files, so now what?

Now that we have generated the ini files it’s time to put them to use. The first thing I like to do is rename them to something more appropriate (like the SQL virtual name and their purpose) so

become

 

I also like to store these files away in a repository, this way it’s a very quick and easy operation to add a node to a cluster or find out what installation settings were used.

 

Can we do the SQL install already?

We sure can, it’s go time! But first, take a quick look at the ini file that was created:

;File created by: AWESOMESAUCENic
;File creation date: 2011-07-17 16:47
;Script to install new SQL clustered instance
;SQLSERVER2008 Configuration File
[SQLSERVER2008]
IACCEPTSQLSERVERLICENSETERMS=”TRUE”
FAILOVERCLUSTERNETWORKNAME=”GROO”
INSTANCEID=”PROD”
INSTANCENAME=”PROD”
FAILOVERCLUSTERGROUP=”SQL Server (PROD)”
FAILOVERCLUSTERIPADDRESSES=”IPv4;192.168.137.150;Public Network;255.255.255.0″
INSTALLSHAREDDIR=”C:Program FilesMicrosoft SQL Server”
INSTALLSHAREDWOWDIR=”C:Program Files (x86)Microsoft SQL Server”
INSTANCEDIR=”C:Program FilesMicrosoft SQL Server”
ACTION=”InstallFailoverCluster”
FEATURES=SQLENGINE,REPLICATION,FULLTEXT,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,OCS
HELP=”False”
INDICATEPROGRESS=”True”
QUIET=”True”
QUIETSIMPLE=”False”
X86=”False”
ENU=”True”
ERRORREPORTING=”False”
SQMREPORTING=”False”
FILESTREAMLEVEL=”0″
ISSVCSTARTUPTYPE=”Automatic”
ISSVCACCOUNT=”NT AUTHORITYNetworkService”
SQLCOLLATION=”SQL_Latin1_General_CP1_CI_AS”
FTSVCACCOUNT=”NT AUTHORITYLOCAL SERVICE”
SQLSYSADMINACCOUNTS=”awesomesaucenic”
SQLSVCACCOUNT=”awesomesauceGroo_SQLEngine”
AGTSVCACCOUNT=”awesomesauceGroo_SQLAgent”
INSTALLSQLDATADIR=”G:SQLSystem”
SQLTEMPDBDIR=”G:TempDBMSSQLData”
SQLUSERDBDIR=”G:SQLDataMSSQLData”
SQLUSERDBLOGDIR=”G:SQLDataMSSQLLogs”
SQLBACKUPDIR=”H:MSSQLBackup”
FAILOVERCLUSTERDISKS=”SQL Backup Disk” “SQL Data Disk”

 

 

That’s all the information needed to perform the install.

Now open up an elevated command prompt and go to the location of the install media. Now just call setup.exe and pass along the location and name of the SQLInstall.ini file along with the SQL and Agent passwords, then hit enter:

 

Now go and make yourself a beverage of your choice, the install will take a little while….after an indeterminate amount of time you should see the following nondescript message in the command prompt window:

 

Not exactly a whole bunch of bells and whistles there, but “Setup result: 0” tells us that the install was successful (even though it’s somewhat cryptic).

We can test this by cranking up SSMS and connecting:

 

Add another node

A single node cluster while nice isn’t particularly highly available, so we need to have this SQL instance deployed on the other node(s). This is a quick and painless task. Just copy over the SQL Install ini folder to the other node(s), login and run the setup.exe, this time calling the AddNode.ini file:

 

Again, go for a walk around the block, talk to your colleagues and eventually come back to:

 

 

Rinse and repeat for as many nodes as you need.

 

Summary

Setup your cluster, ensure Active Directory is good to go and use CreateClusteredSQLInstallFile.ps1 to quickly build your ini file to install SQL Server on to your cluster. It’s quick and easy. It allowed me to deploy 4 SQL Server instances to 5 nodes in under 4 1/2 hours.

Give it a try, let me know what you think. I’ll be looking to enhance it in the future.

 

Read Part 1

Read Part 2

Creating A Clustered SQL Install ini File Using PowerShell–Part 2

In Part 1 I showed how the cluster was readied for the SQL install. In this short part 2 I’ll go over the Active Directory configuration components so that everything is ready for us to start performing the install.

 

Create the computer account

The first thing is to create a new Computer object in Active Directory. This will be the virtual name used in the cluster.

 

Once added we need to go back in and edit the properties of the Computer (you’ll need to use Advanced Features for this bit, look under the view menu item).

Go to the security tab of the Computer properties and hit the Add button. In the Select Users, Computers, Service Accounts or Groups window hit Object Types and ensure that Computers is checked. The cluster account will be used to manage the Computer resource (in this case CLUSTER1) so we add CLUSTER1$

 

And then give CLUSTER1$ Full Control over the computer object.

 

Finally the computer object needs to be disabled (if it’s not disabled your cluster install will not work correctly and it will fail to come online)

 

 

Create the service accounts

In the interest of security I’ll be using one account for the SQL Engine and another for the SQL Agent. Quickly create a couple of accounts with nice secure passwords (that of course you will keep somewhere secure, like in a KeePass file).

 

I like to give the accounts good descriptive names. It makes them easier to find later (actually the best way is to have a separate OU for the SQL accounts and for the SQL computer objects as it makes it a lot easier to manage and create Group Policies for them).

 

The boring stuff is done

The setup items are complete, we’re now ready to create the ini file….in part 3

Creating A Clustered SQL Install ini File Using PowerShell–Part 1

I’ve written a few posts recently around Clustering. Most of thse have been inspired by working on building out 4 new clusters consisting of 16 nodes and 14 new SQL instances. Each of the servers in the clusters are identical and each one of the SQL installs needed to be exactly the same.

Ensuring that all the installs are the same can be challenging when using the GUI, especially when you have you are installing a lot of instances. In this case:

  • Cluster A – 5 nodes x 4 SQL instances = 20 installs
  • Cluster B  – 4 nodes x 3 SQL instances = 12 installs
  • Cluster C – 4 nodes x 4 SQL instances = 16 installs
  • Cluster D – 3 nodes x 3 SQL instances = 9 installs
  • Total installs: 57

Given how slow it is to use the SQL installer GUI I figured it would take me about 2 1/2 lifetimes to perform the installs I needed.

 

A better and more consistent way to install SQL

So what would be a better way to perform these installs? It makes sense to perform automated installs using ini files that hold all the configuration information for the instance.

There’s a good MSDN article on “How to: Install SQL Server 2008 R2 Using a Configuration File”.

Using the SQL Server instance prep tool is again a major undertaking using the GUI. I figured that there had to be a better and faster way to build an ini file to perform the installs I needed. From previous cluster installs I had a couple of configuration.ini files laying around, it’s a pretty easy task to manipulate those to reference new clusters/disks and IP addresses. Thing is that it’s not particularly quick and also very prone to error.

So what would be a better way?

 

PowerShell to the rescue

Yet again, PowerShell is the savior in this situation.

I put together a script that is quick and lightweight, asks the relevant questions and provides you with the ini files that you need to install a new clustered SQL Server instance and the ini files that you need to perform the installs on the additional cluster nodes.

 

First things first

Before using the script we need to ensure the cluster is setup and ready to go. Here I’ll be using my home machine to install a new clustered SQL Server instance.

I’ve created a new cluster called CLUSTER1 consisting of two nodes CLUSTERNODEA and CLUSTERNODEB which consists of a private and public network (named as such).

 

The cluster had node and disk majority. Storage wise (as this is a demo) I’ve only got a couple of disks. SQL Data Disk to hold my SQL system, data and log files plus SQL Backup Disk to hold the backups. This is obviously not how I would setup a normal system, this is just iSCSI disk running on my laptop, don’t judge me too harshly.

 

 

In part 2 I’ll go over the configured Active Directory components so that everything is in place ready for the install to happen.

Using PowerShell To Set Resource Owners In A Cluster

Following up on my post about Using PowerShell To Add Owners For Cluster Disks here’s another quick post on how PowerShell can help you with your clusters.

In my new cluster build out I needed to quickly set it so that one of the SQL instances could only potentially live on two of the 5 nodes. This could have been quickly done using the GUI however it’s just as fast to do so using PowerShell.

Load up the Windows PowerShell Modules and you’re ready to go.

 

In this example we have a two node cluster made up of SERVER1 and SERVER2 and a single installed SQL instance called INST1.

In PowerShell the following would provide us a list of possible owners of INST1:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}
         

 

Now to change this so that only SERVER1 can potentially own the SQL instance is a very quick task:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1

 

Sadly you don’t get any feedback that the change has been made, but if you run Get-ClusterOwnerNode again:

Get-ClusterOwnerNode -Group "SQL Server (INST1)"

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1}
       

 

Adding SERVER2 back is as quick as running Set-ClusterOwnerNode again and providing a comma delimited list of servers:

Set-ClusterOwnerNode -Group "SQL Server (INST1)" -Owners SERVER1,SERVER2

ClusterObject                          OwnerNodes                                 
————-                                 ———-                                 
SQL SERVER (INST1)           {server1, server2}
     

 

You have to love the simplicity of working with clusters in PowerShell.

Using PowerShell To Add Owners For Cluster Disks

Here’s a quick post about how great PowerShell can be for your clusters.

I’m in the middle of configuring a few machines in clusters and recently added a new node to a cluster that was already built (but not configured). I needed to have that final node be a possible owner for all 33 presented disks on that cluster.  To do that through the cluster GUI would require a heck of a lot of mouse clicks….PowerShell to the rescue!

The Windows PowerShell Modules (under Administrative Tools) allow you to do all kinds of wonderful things with clusters.

Allowing a single disk to be owned by the extra server is as simple as:

CLUSTER RESOURCE "Cluster Disk 1" /addowner:DBSERVER1

 

 

If I want to do that for 33 disks a quick PowerShell one-liner takes care of it:

$i=1;do {cluster resource `"Cluster Disk $i`" /addowner:DBSERVER1;$i = $i +1} while ($i -lt 33)

 

 

Here’s a version that’s a little more readable:

$DiskNo = 1

DO

    { CLUSTER RESOURCE `"Cluster Disk $DiskNo`" /addowner:DBSERVER1

        $DiskNo = $DiskNo+1

    }

    

WHILE ($DiskNo -lt 33)

 

Quick and easy. Saved my clicky finger lots of work.

 

More PowerShell and cluster fun coming up in the next few weeks.

MSDTC Failing In A Cluster

I’m merrily working away on installing SQL 2008R2 on a bunch of new clusters. The SQL installs have gone fine and I’m getting set to install MSDTC, one for each SQL instance (read the awesome post by Cindy Gross on this).

The install of MSDTC went smoothly and it seemed very happy. Then I failed over the cluster…

MSDTC failed.

It wouldn’t restart.

I failed back to the original node and it wouldn’t start there either.

 

What’s the error?

I dumped the last few minutes of the cluster log by opening a dos box command prompt and running…

cluster log /gen /span:5

 

This dumps the last five minutes of cluster logs into C:WindowsClustercluster.log

I scrolled through to get to the MSDTC error piece:

INFO  [RES] Physical Disk: Failed to get vol MP root for path ?, status 123
ERR   [RHS] Error 123 from ResourceControl for resource <instance>_MSDTC.
WARN  [RCM] ResourceControl(STORAGE_IS_PATH_VALID) to <instance>_MSDTC returned 123.

I checked the disk resource for MSDTC and it was online. Looking at the filesystem on that disk and there was an MSDTC directory, so I knew there were no access problems. It didn’t make any sense.

 

So what’s going on?

The key error here is the failure to get MP root for path ?

Apparently MSDTC is not supported does not work with Mount Points, which is what I had set the dependency to. There were no warnings on this when setting MSDTC up and I’d not seen or heard of any documentation that speaks to this.

I was finally pointed to a Connect item opened by someone who’d had the same issue https://connect.microsoft.com/SQLServer/feedback/details/576545/msdtc-fails-to-restart-in-sql-server-2008-r2-clustered-group

Side note: I love it when Connect items such as this are closed as by design. Why is this by design? Can someone explain to me why MSDTC shouldn’t be supported on Mount Points?

 

I deleted the MSDTC resource and added it again, this time using a regular drive as a dependency and everything worked perfectly. I was able to failover and have SQL perform distributed transactions.