Befriending Dragons

Transform Tech with Anti-bullying Cultures


Leave a comment

Get HDInsight Properties with PowerShell

Small Bites of Big Data from AzureCAT

You’ve created your HDInsight Hadoop clusters and now you want to know exactly what you have out there in Azure. Maybe you want to pull the key information into a repository periodically as a reference for future troubleshooting, comparisons, or billing. Maybe you just need to get a quick look at your overall HDInsight usage. This is something you can easily automate with PowerShell.

Environment

First, open Windows Azure PowerShell or powershell_ise.exe.

Set some values for your environment:

$SubName = "YourSubscriptionName"
Select-AzureSubscription -SubscriptionName $SubName
Get-AzureSubscription -Current
$ClusterName = "HDInsightClusterName" #HDInsight cluster name

HDInsight Usage for the Subscription

Take a look at your overall HDInsight usage for this subscription:

Get-AzureHDInsightProperties

Get-AzureHDInsightProperties returns the number of clusters for this subscription, the total HDInsight cores used and available (for head nodes and data nodes), the Azure regions where HDInsight clusters can be created, and the HDInsight versions available for new clusters:

ClusterCount    : 2
CoresAvailable  : 122
CoresUsed       : 48
Locations       : {East US, North Europe, Southeast Asia, West Europe...}
MaxCoresAllowed : 170
Versions        : {1.6, 2.1, 3.0}

You can also pick out specific pieces of information and write them to a file, store them as variables, or use them elsewhere. This example simply outputs the values to the screen.

write-host '== Max HDInsight Cores for Sub: ' (Get-AzureHDInsightProperties).MaxCoresAllowed
write-host '== Cores Available:             ' (Get-AzureHDInsightProperties).CoresAvailable
write-host '== Cores Used:                  ' (Get-AzureHDInsightProperties).CoresUsed

HDInsight Cluster Information

Get-AzureHDInsightCluster provides information about all existing HDInsight clusters for this subscription:

Get-AzureHDInsightCluster
As you can see this cmdlet tells you the size, connection information, and version.
ClusterSizeInNodes    : 4
ConnectionUrl         : https://BigCAT.azurehdinsight.net
CreateDate            : 4/5/2014 3:37:23 PM
DefaultStorageAccount : sqlcatwomanwestus.blob.core.windows.net
HttpUserName          : Admin
Location              : West US
Name                  : BigCAT30
State                 : Running
StorageAccounts       : {}
SubscriptionId        : {YourSubID}
UserName              : Admin
Version               : 3.0.0.0.661685
VersionStatus         : Compatible

ClusterSizeInNodes    : 4
ConnectionUrl         : https://cgrosstest.azurehdinsight.net
CreateDate            : 5/5/2014 6:09:58 PM
DefaultStorageAccount : cgrosstest.blob.core.windows.net
HttpUserName          : Admin
Location              : West US
Name                  : cgrosstest
State                 : Running
StorageAccounts       : {sqlcatwomanwestus.blob.core.windows.net}
SubscriptionId        : {YourSubID}
UserName              : Admin
Version               : 3.0.2.0.727283
VersionStatus         : Compatible

You can also get information about just one HDInsight cluster at a time:

Get-AzureHDInsightCluster  -name $ClusterName

Or you can get very granular and look at specific properties, even some that aren’t in the default values:

write-host '== Default Storage Account:     ' `
(Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageAccountName.split(".")[0]
write-host '== Default Container:           ' `
(Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageContainerName

This information will be a valuable source of information for tracking past configurations, current usage, and planning. Enjoy your Hadooping!

Sample Script

# Cindy Gross 2014
# Get HDInsight properties
$SubName = "YourSubscriptionName"
Select-AzureSubscription -SubscriptionName $SubName
Get-AzureSubscription -Current
$ClusterName        = "YourHDInsightClusterName" #HDInsight cluster name

Get-AzureHDInsightProperties 
Get-AzureHDInsightCluster 
Get-AzureHDInsightCluster  -name $ClusterName
write-host '== Default Storage Account:     ' `
(Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageAccountName.split(".")[0]
write-host '== Default Container:           ' `
(Get-AzureHDInsightCluster -Cluster $ClusterName).DefaultStorageAccount.StorageContainerName
write-host '== Max HDInsight Cores for Sub: ' (Get-AzureHDInsightProperties).MaxCoresAllowed
write-host '== Cores Available:             ' (Get-AzureHDInsightProperties).CoresAvailable
write-host '== Cores Used:                  ' (Get-AzureHDInsightProperties).CoresUsed


5 Comments

Can I Have a Single Node SQL Server Cluster

I occasionally hear the question of whether you can create a one node SQL Server cluster. That basically means you have a virtualized instance of SQL Server with a virtual IP and virtual name that are differerent from the host box, but that there is only one “possible owner” of that SQL Server. Yes, this is allowed and supported. Often it is used for testing or when you have to get the system up and running now but only have one physical server available at the moment. You can add additional nodes to the Windows cluster later, then optionally add those nodes as potential owners of the SQL Server instance. See the “add node” topics in BOL, the exact steps will vary by version. Basically “add node” means that setup adds the SQL binaries and some registry keys to the new node and updates a few cluster related registry keys so that the cluster “service” becomes aware of the additional node as a “possible owner” of that instance of SQL Server. You still only have one copy of the SQL Server databases no matter how many nodes are possible owners.

Choose “Other Versions” if you want to view the BOL topic for something other than the most recent version of SQL Server:
How to: Add or Remove Nodes in a SQL Server Failover Cluster (Setup)
http://msdn.microsoft.com/en-us/library/ms191545.aspx


Leave a comment

SQL Server 2008 setup may report a Windows 2008 cluster validation failure

You may receive the error “The cluster either has not been verified or there are errors or failures in the verification report” while installing a SQL Server clustered installation on Windows 2008. When see this error you must fix the underlying error that caused Windows validation to fail. Even though in some cases you could avoid the error and continue SQL Server setup with the undocumented SkipRules setting, this is not advisable and will leave you in an unsupported state (unless you are using a geo cluster with no shared storage).

 

The Microsoft Support Policy for Windows Server 2008 Failover Clusters

http://support.microsoft.com/kb/943984

For the Windows Server 2008 Failover Clustering solution to be considered an officially supported solution by Microsoft Customer Support Services (CSS), the solution must meet the following criteria:

·         All hardware and software components must meet the qualifications to receive a “Certified for Windows Server 2008” logo.

·         The fully configured solution must pass the Validate test in the Failover Clusters Management snap-in.

 

Failover Cluster Step-by-Step Guide: Validating Hardware for a Failover Cluster

http://technet.microsoft.com/en-us/library/cc732035(WS.10).aspx

What to do if validation tests fail

In most cases, if any tests in the cluster validation wizard fail, then Microsoft does not consider the solution to be supported. There are exceptions to this rule, such as the case with multi-site (geographically dispersed) clusters where there is no shared storage. In this scenario the expected result of the validation wizard is that the storage tests will fail. This is still a supported solution if the remainder of the tests complete successfully.

 

Error message when you install SQL Server 2008 on a Windows Server 2008-based cluster: “The cluster either has not been verified or there are errors or failures in the verification report. Refer to KB953748 or SQL server books online”

http://support.microsoft.com/kb/953748

What to do if validation tests fail

In most cases, if any tests in the cluster validation rule fail, Microsoft does not consider the solution to be supported. There are exceptions to this rule, such as the case with multiple-site (geographically dispersed) clusters where there is no shared storage. In this scenario, the expected result of the cluster validation wizard is that the storage tests will fail. This is still a supported solution if the rest of the tests finish successfully.


2 Comments

Checklist for installing SQL Server 2005 as a clustered instance

Checklist for installing SQL Server 2005 as a clustered instance

 

Windows/Hardware

1)      Verify the Windows cluster is set up per basic best practices and that basic failover works.

2)      Verify you have the latest patches, especially security patches, for Windows.

3)      For Windows 2008: Validate the configuration using “Validate a Configuration” in Failover Cluster Management.

4)      For Windows 2008: Make sure your quorum choice is appropriate for the number of nodes and other factors in your environment.

5)      Unless you have verified that your network cards support it, disable TCP Chimney and the other SNP settings.

6)      Verify none of the cluster nodes are domain controllers.

7)      Request a disk subsystem configured per IO and recoverability best practices that meets your minimum performance requirements (often you will require that Avg disk sec/read < 10-20ms and Avg disk sec/write < 3-5ms for a given load on the system).

8)      Have one or more “shared disks” that are not otherwise used by anything else (not even another instance of SQL) available to SQL Server. Create a new group and move the disk(s) from “Available Storage” (new in Windows 2008) to the group you created for this SQL Server instance. The disks should be configured per database best practices. Generally no other resources should be in this group.

 

Prep

9)      Download whatever service packs (SPs) and cumulative updates (CUs) you will be installing. Make sure the files you download are the proper architecture (x86 vs. x64).

10)   Download Visual Studio 2005 SP1.

11)   Find a new, (preferably) static IP address that is not currently used by anything. You will enter this during setup and the setup process will take care of adding it to DNS and other locations as appropriate.

12)   Find or create one or more domain groups that will be used by setup. The best practice is to use three unique groups for each instance of SQL Server, one each for SQL Server, SQL Agent, and Full Text. If the account you are using for setup does not have the permission to add the startup account(s) to the groups, add them manually ahead of time.

13)   Determine your SQL virtual name (cannot be used for a physical or virtual machine anywhere in the domain).

14)   Determine an instance name which will be unique in the cluster. Note that the name of a default instance is implicitly MSSQLSERVER so you can only have one default instance per cluster.

15)   Determine which domain user account(s) you will assign to run SQL Server, SQL Agent, and Full Text. The account(s) you choose will be added to the domain groups by the setup process if your setup account has permissions to do so. As a security best practice each service should have a unique account.

16)   Look through the items in my blog to find possible setup blockers http://blogs.msdn.com/cindygross/archive/2009/06/10/sql-server-2005-clustering-tips-references.aspx.

 

Install

17)   From the node which currently owns the group with the disks to be used by SQL Server, log in with an account that is a local admin on all nodes.

18)   Make sure no one is logged on through terminal services to any of the remote nodes.

19)   Make sure the Remote Registry service, Cryptography services, and Task Scheduler service are started on all nodes.

20)   Verify all available disks in the cluster are online, even those that SQL Server will not use.

21)   Stop non essential services that may slow down file copies (like virus scanners) or try to connect to SQL Server (like IIS or monitoring tools).

22)   Install the RTM version of SQL Server, for SQL Server 2005 you run setup once per instance. It will install the cluster aware components on all nodes. This includes SQL Server and Analysis Services if you choose them.

1.       Install the SQL SP.

2.       Install the SQL CU.

3.       Install the VS05 SP1.

23)   On every other node in the cluster, if you want the non-cluster aware components to be available, install those components on each node.

1.       RTM (for example, you may want to install the client tools and SSIS on the other node(s))

2.       SQL SP.

3.       SQL CU.

4.       VS05 SP1.

 

After

24)   Make SQL depend on any drives it will use for data and log files. Those drives must be in the SQL Server group.

25)   If you will be using DTC, you may want to cluster it.

26)   Update MSDTSSrvr.ini.xml on each node to point to the SQL Server virtual nameinstance. If you have multiple instances of SQL Server that will store SSIS packages you can add multiple instance names to the file.

27)   Consider setting “Max server memory” for each instance of SQL Server.

28)   Make sure the SQL Server service is set to “affect the group”.

29)   Follow your normal SQL Server best practices and standard configuration such as removing builtinadministrators and configuration maintenance operations.

 

References

·         The 3 Things you Need to Know to Install SQL 2005 on Windows 2008 Cluster http://blogs.msdn.com/psssql/archive/2009/04/08/the-3-things-you-need-to-know-to-install-sql-2005-on-windows-2008-cluster.aspx

·         List of known issues when you install SQL Server 2005 on Windows Server 2008 http://support.microsoft.com/default.aspx?scid=kb;EN-US;936302

·         SQL Server 2005 Failover Clustering White Paper http://www.microsoft.com/downloads/details.aspx?familyid=818234dc-a17b-4f09-b282-c6830fead499&displaylang=en

·         System Configuration Check (SCC) http://msdn.microsoft.com/en-us/library/ms143185(SQL.90).aspx

·         Hardware and Software Requirements for Installing SQL Server 2005  http://msdn.microsoft.com/en-us/library/ms143506(SQL.90).aspx

·         How to: Create a New SQL Server 2005 Failover Cluster (Setup) http://msdn.microsoft.com/en-us/library/ms179530(SQL.90).aspx

·         SQL Server 2005 Readme http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm

·         Changes to the readme file for SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;907284

·         (my blog) SQL Server 2005 Clustering Tips/References http://blogs.msdn.com/cindygross/archive/2009/06/10/sql-server-2005-clustering-tips-references.aspx

·         (my blog) How to configure DTC for SQL Server in a Windows 2008 cluster http://blogs.msdn.com/cindygross/archive/2009/02/22/how-to-configure-dtc-for-sql-server-in-a-windows-2008-cluster.aspx


Leave a comment

SQL Server 2005 Clustering Tips/References

I have copied this over from an older blog. I have cleaned it up a bit to clarify a few areas and added some links.

SQL Server 2005 Clustering Tips/References

This information will supplement the clustering information I wrote in chapter 10 of SQL Server 2005 Practical Troubleshooting: The Database Engine  

— Handy cluster related info

select SERVERPROPERTY(‘IsClustered’) as _1_Means_Clustered

, SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as CurrentNode

, SERVERPROPERTY(‘Edition’) as Edition

, SERVERPROPERTY(‘MachineName’) as VirtualName

, SERVERPROPERTY(‘InstanceName’) as InstanceName

, SERVERPROPERTY(‘ServerName’) as Virtual_and_InstanceNames

, SERVERPROPERTY(‘ProductVersion’) as Version

, SERVERPROPERTY(‘ProductLevel’) as VersionNameWithoutHotfixes

select * from sys.dm_io_cluster_shared_drives

select * from sys.dm_os_cluster_nodes

Best practices

Setup (RTM, Service Pack, Cumulative Update, or hotfix)

·         The account you use to launch setup must be a local admin on all nodes. However, it is not required that the account you choose to assign as the service account for each service be a local admin. Only setup requires local admin permissions.

·         Install and cluster DTC before installing any SQL instance. For Windows 2003, DTC should ideally go in its own group with its own disk and IP address. Second best is to place it in the cluster group and make it depend on the quorum disk and IP address. For Windows 2008 see this blog.

·        For SQL 2005, the client tools, SSIS, NS, and RS are only installed on the node where setup was run because they are not cluster aware. If you are installing as many or more instances than nodes, you can run the install program for each instance from a different node so the non cluster aware components are installed on each node. Otherwise install the non cluster aware components later on the other node(s). This means that the service packs and hotfixes must be installed on each node as well so that the non cluster aware components are updated. Setup is run once per instance and that will update all the cluster aware and components for that instance. The first time setup (RTM, SP, hotfix) is run from each node it will also update any non cluster aware components on the box such as the tools, SSIS, NS, and RS.

·         All nodes should be configured identically.

·         Windows level policies should be the same on all nodes.

·         Remote Registry, Cryptography services, and Task scheduler must be started on all nodes during the setup process.

·         No Terminal Services users can be logged in on any remote nodes during setup.

·         Do not use quotes in the password for SQL service account.

·         Do not allow <, >, ‘, “, & in the cluster group names.

·         Virtual Server name should be 14 characters or less.

·         NIC name cannot have trailing spaces.

·         Stop any non-essential applications or services as they may hold open files that setup needs to modify or may otherwise interfere with setup.

·         All nodes must have access to setup files without prompting for credentials.

·         All disks in all groups must be online during setup.

·         All other, existing instances in the cluster must have valid, non-UNC paths for the SQL Server registry keys

·         If there are many trusts for the domain the nodes/service account reside in, see KB 910070 before running setup

·         For Windows 2003, the complete node must be on HCL, for Windows 2008 each node in the cluster must pass a validation.

·         Copy the setup files to the “primary” node or at least make sure all nodes can access the setup files without being prompted for credentials.

·         Any mounted drives must have an associated drive letter and be clustered, even if they will not be used by SQL Server. Do not use mounted drives anywhere on a cluster if SQL Server 2000 will exist in the cluster. A mounted drive must be in SQL resource group and SQL Server must depend on them if you want that instance of SQL Server to use them for data or log files.

·         Verify you have not installed terminal services, there are no compressed drives, and no node is a domain controller.

·         Disable netbios on private NICs.

·         Pre-create the domain groups needed by setup.

·         Make sure the SQL Server resource is set to “affect the group”. Often it’s best to leave SQL Agent, FTS, and DTC to not “affect the group” but that depends on your business needs.

·         Test failover of all groups before any SQL setup, test again before any service packs or hotfixes. If any group gets an error during failover, address that problem before running SQL setup.

After setup

·         Go back to the cluster administrator and take the SQL Server resource offline. Make SQL Server dependent on the disk(s) and mount points in their group (any disk or mount point where you need to create data files, log files, or full text catalogs) then bring SQL Server, SQL Agent, and FTS back online.

·         For the SSIS service, %ProgramFiles%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml must have the SQL virtual server name instead of “.”

·         Configure memory to handle instances moving due to failover.

·         If you installed NS, you now have to configure it separately.

·         Leave SQL services set to manual in the services applet.

Maintenance

·         Use add/remove programs to add/remove nodes.

·         Add/remove non-cluster aware components must be done from cmd line.

·         Can rename the virtual server name but not the instance name.

·         Make IP changes in cluster admin instead of setup.

·         Cluster service account must have a login in SQL, must be sysadmin only for FTS.

·         Clustered SQL Server startup account can only be a local admin if SQL 2000/7.0 is not installed side-by-side with 2005. Even if a lower version is not installed side-by-side it is best not to make the SQL Server startup account a local administrator or otherwise give it excessive permissions.

·         After adding new node, fail to that node to  apply SPs/hotfixes.

·         For Kerberos on a cluster, you need two SPNs per instance, one with and one without the port. They must belong to the current SQL Server startup account and not to any other accounts.

·         If you use SSL encryption, install certificates on all nodes before turning on the SSL encryption.

·         If antivirus is installed, see http://support.microsoft.com/kb/250355

References:

·         Updated Books Online: http://www.microsoft.com/downloads/details.aspx?FamilyId=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

·         915846  Best practices that you can use to set up domain groups and solutions to problems that may occur when you set up a domain group when you install a SQL Server 2005 failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;915846

·         819546  SQL Server 2000 and SQL Server 2005 support for mounted volumes http://support.microsoft.com/default.aspx?scid=kb;EN-US;819546

·         913815  Error message when you install a SQL Server 2005 failover cluster on a node: “The drive specified cannot be used for program location” http://support.microsoft.com/default.aspx?scid=kb;EN-US;913815

·         922670  How to use the Add or Remove Programs item in Control Panel to add or remove components for stand-alone installations and clustered installations of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922670

·         910230  How to install SQL Server 2005 Analysis Services on a failover cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910230

·         910233  Migrate a SQL Server 2000 Analysis Services cluster to a SQL Server 2005 Analysis Services cluster http://support.microsoft.com/default.aspx?scid=kb;EN-US;910233

·         912397  The SQL Server service cannot start when you change a startup parameter for a clustered instance of SQL Server 2000 or of SQL Server 2005 to a value that is not valid http://support.microsoft.com/default.aspx?scid=kb;EN-US;912397

·         910851  You receive error messages when you try to set up a clustered instance of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;910851

·         926621  Error message when you try to install SQL Server 2005 in a cluster environment: “SQL Server Setup could not validate the service accounts” http://support.microsoft.com/default.aspx?scid=kb;EN-US;926621

·         327518  The Microsoft SQL Server support policy for Microsoft Clustering http://support.microsoft.com/default.aspx?scid=kb;EN-US;327518

·         254321  Clustered SQL Server do’s, don’ts, and basic warnings http://support.microsoft.com/default.aspx?scid=kb;EN-US;254321

·         942176  Description of the SQL Server Integration Services (SSIS) service and of alternatives to clustering the SSIS service http://support.microsoft.com/default.aspx?scid=kb;EN-US;942176

·         922209  The SQL Server 2005 Setup program does not remove all IP address cluster resources when you uninstall SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;922209

·         295732  How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed http://support.microsoft.com/default.aspx?scid=kb;EN-US;295732

·         263712  How to impede Windows NT administrators from administering a clustered instance of SQL Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;263712

·         932881  How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult http://support.microsoft.com/default.aspx?scid=kb;EN-US;932881

·         934749  BUG: Error message when you try to install SQL Server 2005 Service Pack 1 or SQL Server 2005 Service Pack 2 from the existing active node: “The product instance <InstanceName> been patched with more recent updates” http://support.microsoft.com/default.aspx?scid=kb;EN-US;934749

·         283811  How to change the SQL Server or SQL Server Agent service account without using SQL Enterprise Manager in SQL Server 2000 or SQL Server Configuration Manager in SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;283811

·         910070  FIX: The SQL Server 2005 Setup program may take much longer than expected to finish running http://support.microsoft.com/default.aspx?scid=kb;EN-US;910070

·         909967  How to uninstall an instance of SQL Server 2005 manually http://support.microsoft.com/default.aspx?scid=kb;EN-US;909967

Windows 2003 SP2 is better than SP1 because:

·         918483  How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005 http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483

·         922658  SQL Server 2000 or SQL Server 2005 may temporarily stop responding on a Windows Server 2003 Service Pack 1-based computer http://support.microsoft.com/default.aspx?scid=kb;EN-US;922658

·         904160 Network performance is slower than expected in Windows Server 2003 SP1 http://support.microsoft.com/?id=904160

Cluster specific:

·         923830  Recommended hotfixes for Windows Server 2003 Service Pack 1- based server clusters http://support.microsoft.com/default.aspx?scid=kb;EN-US;923830


12 Comments

How to configure DTC for SQL Server in a Windows 2008 cluster

How to configure DTC for SQL Server in a Windows 2008 cluster

[This post was revised 11 May 2009 with new mapping information, fixed formatting issues 23 March 2010]

With Windows 2008 you now have the option to use multiple DTCs. There is a lot of confusion over whether to do that for SQL Server and what the exact steps are. If you use multiple DTCs you must also make sure you map each SQL Server to a specific DTC.

How and Where

Here is some information to help you choose how many DTCs to have and where to put them:

Multiple DTCs:

·         Positive: Better performance (not sure how noticeable to the average application)

·         Negative: More administration

One DTC:

·         Positive: Easy to admin

·         Negative: May become overloaded/performance bottleneck (probably rare)

One DTC per SQL Server but all the DTCs in one group instead of in each SQL group:

·         Negative: you have to assign a disk and IP to each

·         Positive: you can have DTC affect the group – if you’re willing to have all DTCs failover when one fails

·         Negative: you can end up with DTC and SQL on separate nodes which (with the possible exception of a geo-cluster) probably has a negligible performance impact but still could theoretically present more problems than having DTC and SQL both local.

One DTC no matter how many SQL Servers – in its own group

·         Negative: if it does down/fails over it affects all SQLs (or other apps that use it)

·         Positive: easy to set up/maintain

·         Negative: DTC and SQL can be on separate nodes (see above)

·         Negative: you have to assign a unique disk and IP

One DTC no matter how many SQL Servers – in one of the SQL groups

·         Negative: if DTC goes down/fails over it affects all SQLs (or other apps that use it)

·         Positive: easy to set up/maintain

·         Negative: DTC and SQL can be on separate nodes (see above)

One DTC per SQL Server, each in the same group as the SQL Server it is mapped to

·         Positive: If you need to move or restart DTC it only affects one SQL Server

·         Positive: no extra disks or IPs needed (though giving DTC its own disk is still recommended for the best performance)

·         Positive: it’s easy for admins to see how its configured (assuming they didn’t forget the mapping)

·         Negative: requires more administration (though still very minimal, just a couple minutes per DTC)

·         Positive: best performance

·         Positive: It can simplify troubleshooting as your testing can be isolated to one group.

One DTC per SQL Server, each in its own DTC group with only a disk, IP, and name

·         Positive: DTC can affect the group

·         Negative: requires more disks and IPs

·         Negative: DTC and SQL can be on separate nodes (see above)

Multiple DTCs, but some DTCs service more than one SQL

·         Combination of above

Steps for DTC in a SQL Group

Here are the steps for creating DTC in a Windows 2008 cluster with one DTC per SQL Server and the DTC is in the SQL Server group. The ability to use multiple DTCs is new in Windows 2008, for older versions of Windows these steps will not work.

In Windows 2008 you can no longer make DTC dependent on the quorum disk so it either needs its own disk or can share with an application such as SQL Server. For the best performance you should give DTC its own disk.

There are two ways you can go through these steps, both options are covered below.

1. Connect

·         Start “Failover Cluster Management”

·         Connect to your cluster (click on the local cluster on the left or choose “Manage a Cluster” on the right and enter your remote cluster name)

2. If you are using a firewall make sure it has an exception for DTC and/or the DTC IP addresses.

3. Add a new DTC resource using one of the two options below.

 This is Option 1  from the blog mentioned at the end of this thread. The advantages are:

·         DTC may have its own IP and name.

·         Some steps can be completed before SQL is installed.

·         Requires a disk NOT used by SQL Server during setup, but it can be a disk that will later be used by SQL Server.

·         You can choose the name of the DTC resource.

 

a)      Create a new DTC resource

                                i.            Look for a green arrow in the middle pane next to “Configure a Service or Application” and click on it to open the “High Availability Wizard”

                              ii.            Choose “Next” to go to the “Select Service or Application” screen

                            iii.            Choose “Distributed Transaction Coordinator (DTC)” then click “Next”

                             iv.            You can either use the default name or create your own unique name for the “Client Access Point” then click “Next”. You may want to name it something like DTC_YourSQLServerResourceName. For example: if your SQL instance’s virtual name is SQL01, call your DTC resource something like DTC_SQL01 or MSDTC-SQL Server (SQL01).

                               v.            Choose a disk that is not already used and will not be the disk you choose during SQL Server setup.

                             vi.            Click “Next” twice.

b)      Install SQL Server on at least one node.

c)      Move DTC to the SQL Server group

                                i.            Right click on the DTC resource and choose “More Actions” then “Move this resource to another service or application”.

                              ii.            Choose your SQL Server group in “Select A Service or Application”.

                            iii.            Right click on the DTC resource and choose “Properties”. Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over. This is the same thing as “affect the group” in older versions of Windows.

                             iv.            Optionally you can take DTC offline and make it dependent on the SQL Server name and disk. Then remove its dependencies on the original name and disk. Then you can optionally delete the original IP, name, and disk or move them out of the SQL Server group.

d)      (Optional for SQL Server 2008) Map SQL Server to this DTC resource. If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default for the entire cluster or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.

                                i.            Open a dos prompt using “Run as Administrator”.

                              ii.            Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)”  <<– Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n]
y
[enter]

                            iii.            Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINEClusterMSDTCTMMappingService as a new key named the same as the mapping parameter used in the msdtc command.

                             iv.            To verify SQL Server is mapped to the expected instance of DTC you can either

§Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.

§Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.

e)      Enable network access

                                i.            Run DcomCnfg

                              ii.            Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

                            iii.            Right click on the DTC service for this SQL Server group and select “Properties”.

                             iv.            Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

                               v.            When you hit “Apply” or “OK” it will restart the DTC resource.

This is Option 2 from the blog mentioned at the end of this thread – Pros/Cons include:

·         DTC will depend on the SQL Server resource name and IP

·         SQL must be installed first

·         You have no choice on the DTC resource name

·         Fewer overall steps

 

a)      Install SQL Server on at least one node.

b)      Add DTC

                                i.            Right click on your SQL Server group under “Services and Applications” and choose “Add a resource” then “More resources” then “2 – Add Distributed Transaction Coordinator”.

                              ii.            Right click on the new (offline) DTC resource and choose “Properties”. Note that the DTC resource name is MSDTC-SQL Server (YourInstanceName) and it cannot be changed.

                            iii.            Go to the “Dependencies” tab make DTC dependent on a disk in the SQL Server group. If DTC is not heavily used you can choose a disk already used for SQL Server, but this could have a performance impact.

                             iv.            Go to the “Dependencies” tab make DTC dependent on the SQL Server name.

                               v.            Go to the “Policies” tab and uncheck “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

                             vi.            Bring DTC online.

c)      (Optional for SQL Server 2008) Map SQL Server and this DTC resource. If you skip this step SQL Server 2005 will use the default DTC. SQL Server 2005 will only use the DTC in the SQL Server group if that one is the default or if you do the mapping step. SQL Server 2008 will use the DTC in its group unless that DTC is not online/started when SQL Server starts or if you create a mapping to a different DTC.

                                i.            Open a dos prompt using “Run as Administrator”.

                              ii.            Modify the below statement with a mapping name (make up a name to id the mapping), service (the SQL Server service name – not the display name), and ClusterResourceName (DTC resource name) then run it
Example: Msdtc -tmMappingSet -name DTC_SQL01_Mapping -service “MSSQL$SQL01” -ClusterResourceName “MSDTC-SQL Server (SQL01)”  <<– Note the correction I made here on 3/21/09 to the service name for SQL, and the additional verification steps below
Do you want to continue with this operation? [y/n]
y
[enter]

                            iii.            Run “msdtc -tmmappingview *” to verify the mapping was created successfully. The mapping is also stored in HKEY_LOCAL_MACHINEClusterMSDTCTMMappingService as a new key named the same as the mapping parameter used in the msdtc command.

                             iv.            To verify SQL Server is mapped to the expected instance of DTC you can either

§Shut down all the other DTC services and make sure the mapped DTC is online. Verify you can do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server.

§Do a BEGIN DISTRIBUTED TRANSACTION from this SQL Server, verify it succeeds and roll it back. Then shut down the mapped DTC and verify a new BEGIN DISTRIBUTED TRANSACTION will work.

d)      Enable network access

                                i.            Run DcomCnfg

                              ii.            Navigate to Computers.Component Services.My Computer.Distributed Transaction Coordinator.Clustered DTCs

                            iii.            Right click on the DTC service for this SQL Server group and select “Properties”.

                             iv.            Go to the security tab and check “Network DTC Access”, “Allow Inbound”, and “Allow Outbound”.

                               v.            When you hit “Apply” or “OK” it will restart the DTC resource.

Hints:

·         Repeat the above steps for any additional SQL Server instances you install.

·         If you use DTC heavily you should consider putting it on its own disk for optimal performance. Otherwise you can associate it with your least used SQL Server disk.

·         Do NOT make SQL Server dependent on the DTC resource.

·         Do NOT check “if restart is unsuccessful, fail over all resources in this service or application” for the DTC resource unless you are sure you want a DTC failure to cause SQL Server to fail over.

More information:

·         SQL Server 2008 Failover Clustering White Paper http://download.microsoft.com/download/6/9/D/69D1FEA7-5B42-437A-B3BA-A4AD13E34EF6/SQLServer2008FailoverCluster.docx (link updated 15 April 2014)

·         How to Configure Multiple Instances of Distributed Transaction Coordinator (DTC) on a Windows Server Failover Cluster 2008 http://blogs.technet.com/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx

·         Windows Server 2008 Changes Simplify SQL Server Clustering  http://www.sqlmag.com/Articles/ArticleID/101502/101502.html?Ad=1