Wish you all a very Happy New Year. I hope this year brings more success and prosperity in your life.
Recently,I was having a discussion about DTC with my colleagues which I thought would be worth sharing. So, here we are:
1. Is it mandatory to install the DTC resource before installing SQL failover cluster – The answer is “it depends” – If we are going to install only SQL server database engine components then we can proceed without creating DTC resource. But if we have to install SQL server and integration services or workstation components, the DTC resource prior to SQL cluster installation is mandatory. For more information, please refer this article :- http://msdn.microsoft.com/en-us/library/ms189910%28v=sql.105%29.aspx
2. Can we have separate DTC resource when we install SQL server on windows 2003 – No, on windows 2003 cluster only one DTC resource is feasible. For more information, please refer this article :- http://msdn.microsoft.com/en-us/library/windows/desktop/ms686499%28v=vs.85%29.aspx , http://blogs.technet.com/b/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx
3. For SQL server 2005, Can we have a separate DTC resource for SQL server group – If we install SQL server 2005 on windows 2008, then of course we can have a separate DTC resource. But the only difference is, we need to map DTC resource manually with SQL server (not required for SQL 2008). Here is the article which explains about how to do that: – http://technet.microsoft.com/en-us/library/cc742483(v=ws.10).aspx. Here is how the steps will look like:
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)”
§ 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.
Please feel free to comment , if you are looking for any further information.