Configure Cluster MSDTC with Powershell

Today, I configured a new SQL Server Failover Cluster with command line. The goal is to have a fully automatic installation of a SQL Server Failover Cluster.  By using Powershell, I was already able to add the MSDTC resource.  My next challenge was to configure it automatically Knipogende emoticon
I figured out that the MSDTC settings were in the registry under HKEY_LOCAL_MACHINEClusterResources. The problem was that the folders below this registry key are having an ID, so I had to find a way to know which one is used for MSDTC.
Well, I managed to configure it with the following script.
Import-Module FailoverClusters;
$InstanceName=$args[0]
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Let’s have a detailed look
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
First, I get all the child items of the registry HKEY_LOCAL_MACHINEClusterResources.  In each of the child items, I’m searching for a name that matches “MSDTC-SQL Server (<MyinstanceName>)”
Of course, you should change the “match” parameter when you have used another name for your MSDTC. Note: The parameter $InstanceName should be the name of your SQL Server Instance
As a result, I get the correct key!
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
I have used that key to set the correct registry values, according to my customer needs.
More information can also be found on
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”
Finally, you need to stop and start the cluster resource in order to take the changes into effect.
Note: The parameter $InstanceName should be the name of your SQL Server Instance
Have fun with it!

Confusion about Transparent Client Redirection with Database Mirroring

Yesterday I was preparing an info session for my customer on database mirroring.
I came to the part where I had to explain how the developers can use database mirroring in their application code, which is called Transparent Client Redirection.  As there is a lot of confusion about the use of the “Failover Partner”, I want to clarify this in this blog.

Implicit mode

When you want to use this mode, actually, you don’t need to change anything in your connection string.
I thought, that you also need to specify the “Failover Partner” parameter, but that’s not the case.
Here is how it works.  When you setup a connection to the SQL Server that is hosting a mirrored database, the SQL Server provider will cache the name of the mirror server into the memory.  While your connection is up and your primary server should not be reachable anymore, the SQL Server provider will try to connect to the mirror server.

Explicit mode

With this mode, you need to specify the server name for the mirror in your connection string.
You should add the following to your connection string “;Failover Partner=<servername>” (where <servername> is the name of your mirror server).

.NET Example

Data Source=SQLA;Failover Partner=SQLB;Initial Catalog=AdventureWorks2008R2;Integrated Security=True

That failover partner option will be used for the initial connection towards your principal server.  When you startup your application and it cannot connect to the principal server, the SQL Server provider will check the failover partner and will try to connect to that server.  If you didn’t specify the failover partner option, your application will not start up. 

Remark: Keep in mind that the failover partner will be overwritten by the SQL Server provider, with the name it received from the SQL Server for the mirror server.

Conclusion

Always add the failover partner option in your connection string if you want to fully use the advantage of database mirroring.  If you don’t specify it, and your database is not available, transparent client redirection will not work and your application will not startup.
And you must admit, it’s not a very time consuming job to modify your connection string Knipogende emoticon

More information can be found on http://technet.microsoft.com/en-us/library/cc917713.aspx

How to configure Availability Groups in 10 steps

Last week I installed SQL Server 2012 RC0 which was released a few weeks ago.

I was playing around with the AlwaysOn high availability groups and noticed some small, but nice, improvements in the Availability Group Wizard.

It has become very easy now to configure an availability group in just 10 steps.

In this post you can find the steps I followed, I will also describe the enhancements in the wizard that were not in the CTP3 version.

Step 1: Startup the wizard

  1. In Object Explorer, connect to the server instance that hosts the primary availability replica, and click the server name.
  2. Expand the AlwaysOn High Availability node.
  3. Right-click the Availability Groups node, and select the New Availability Group

Now here we have already the first difference compared to CTP3. In the old version you had to go to “Management” à Availability Groups. With RC0, Microsoft created a separated node called “AlwaysOn High Availability”

Step2: Specify Availability Group Name

Just specify the name for the availability group.

Step 3: Select Databases

Select the databases that you want to be part of the availability group.
If you notice that you can”t select one of your database, check the status column to find out the reason, for example your recovery model is set to simple or no full backup of the databases is available.
More information on Checklist: Requirements Availability Databases

 

Step 4: Specify replica

In this step, Microsoft changed a lot compared to CTP3.
For each replica, you can now choose if it should use Automatic Failover of not and if it should use Synchronous Commit mode or not.
In CTP3,you only had the option Replica Mode where you could choose the options Automatic Failover,High Performance and High Safety.

Automatic Failover à Synchronous Commit mode will be selected to because you cannot combine asynchronous commit mode with Automatic Failover
High Performance à Don”t select Automatic Failover and don”t select Synchronous Commit
High Safety à Select Synchronous Commit. Don”t select Automatic Failover. Only manual failover will be possible. If you should select Automatic Failover, you”re back to the first mode.

The readable Secondary column has the same functionality but Microsoft just put other “names”
In CTP3 you had Disallow Connection, Allow all connections and allow only read-intent connections

CTP3

RC0

Disallow Connection No
Allow all connections Yes
Allow only read-intent connections Read-intent only

 

 

Step 5: Specify Endpoints

In this section you have to specify the endpoint that you want to use for your mirroring session.
This is actually the same like configuring endpoints for database mirroring.
Nothing changed much here compared to CTP3


Step 6: Backup Preferences

The backup preferences tab is a new tab that has been added in RC0.
It allows you to specify where your backups have to occur.
You can choose between:

  • Prefer Secondary
  • Secondary Only
  • Primary
  • Any Replica

The only “issue” is that the DBA has to implement the logic himself. You can use the sys.availability_groups table to find the backup preference. Also check the sys.availability_replicas to check the replica backup priority. By querying these views and tables, you can determine which replica should run a given backup job by running a simple script.

I”m currently working on that script, which will be posted later on.

More information can be found on Backup on Secondary Replicas (AlwaysOn Availability Groups)

Step 7: Listener

Your application should use the listener to connect to the databases. The listener is owned by the primary server, so for the application it’2014-03-28 19:35:30’s transparent which server is the active on because it”s connecting to the listener.
The big improvement in this wizard of RC0 is that you can specify a static IP address. In CTP3, you only had the option to specify DHCP. If you wanted to use a static IP, you had to skip it in the wizard and configure the Listener afterwards.

Just specify the DNS name, port and network mode.

Step 8: Select Initial Data Synchronization

In CTP3 you only had the option Full (formally known as “Perform initial data synchronization”) and Skip initial data synchronization.

In this new version, Microsoft added an extra option Join only. If you want to use this option, the database must already be restored on the replica.
This option will then just join the selected database to the availability group.


Step 9: Validation

Now your complete setup will be verified. Everything should be successful before you can continue with the next step.

Step 10: Summary and Results

The wizard creates a summary for the user. The script button will generate a script with all the settings that you have selected.
Might be handy if you want to test different configuration and you don”t want to go through this wizard every time.
When you click finish, the availability group is created and finally you should have a successful result