 | |
SQL Server Cluster and MSDTC
microsoft.public.sqlserver.clustering microsoft.public.sqlserver.clustering
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: SQL Server Cluster and MSDTC -
08-06-2009
, 09:49 PM
|
I’m also confused about whether I have an active/passive cluster or an active/active one. Here’s my configuration… Physical Server 1 SQL Instance 1 (clustered, primary node) SQL Instance 2 (clustered, primary node) SQL Instance 3 (clustered, primary node) SQL Instance 4 (clustered, passive node) SQL Instance 5 (clustered, passive node) Physical Server 2 SQL Instance 1 (clustered, passive node) SQL Instance 2 (clustered, passive node) SQL Instance 3 (clustered, passive node) SQL Instance 4 (clustered, primary node) SQL Instance 5 (clustered, primary node) SQL Instance 6 (local) SQL Instance 7 (local) "robd08" wrote: Thanks. I've read that article before. I find the the technet link confusing - it gives the impression I can just use the local DTC as long as my applications "do not require the degree of availability that is provided by failover clusters". I don't understand that. If I've clustered the instance I want high availablility. If I don't cluster MSDTC does it mean the databases themselves are highly available but MSDTC isn't? How does that work in practice? Does it mean my users can connect to their databases OK in the event of a failover but distributed transactions will be lost? "Geoff N. Hiten" wrote: This should about cover it: http://weblogs.sqlteam.com/geoffh/ar...-Clusters.aspx -- Geoff N. Hiten Principal SQL Infrastructure Consultant Microsoft SQL Server MVP "robd08" <robd08 (AT) discussions (DOT) microsoft.com> wrote in message news:E2551FAA-B396-49C3-A87F-0CFDBCF2FA12 (AT) microsoft (DOT) com... I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases. When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers. So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC. |
#12
| |||
| |||
|
|
Manpreet Thanks for the detailed info on how to cluster DTC. But is still not really what I'm after. The question is - do I need to go to all that trouble of clustering DTC. What will be the effects if I don't. Rob "Manpreet Singh" wrote: Follow these steps to install MS DTC in cluster environment. • Start Cluster Administrator. • In the left pane, expand Groups. Right-click the group that contains the dedicated disk for Use with MS DTC, and select Rename it; enter the new name for the resource group as something logical, such as MS DTC • Right-click the newly renamed resource group, select New, and then select Resource In the New Resource dialog box, enter an appropriate name for the IP resource that will be associated with MS DTC such as MS DTC IP Address. In the Resource Type drop-down list, select IP Address. In the Group drop-down list, make sure the right resource group is selected. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the drive from Available Resources, and click Add to add it to the Resource Dependencies list. When finished, click next. • In the TCP/IP Address Parameters dialog box, enter a static IP in the Address input box, and select the proper public network to use. Click Finish. • If the resource is created, then resource now appears in the resource group and has a state of Offline. Right-click MS DTC’s resource group, selects New, and then select Resource. • In the New Resource dialog box, enter an appropriate name for the network name resource that will be associated with MS DTC such as MS DTC Network Name. In the Resource Type drop-down list, select Network Name. In the Group drop-down list, make sure the right resource group is selected.. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the MS DTC IP address from Available Resources, and click Add to add it to the Resource Dependencies list. When finished. Click Next. • In the Network Name Parameters dialog box, enter a name for MS DTC that will be used by other applications. Click Finish. • After resource is created. Click OK. The resource now appears in the resource group and has a state of Offline. • Right-click MS DTC’s resource group, select New, and then select Resource. In the New Resource dialog box, enter an appropriate name for the MS DTC resource itself such as MS DTC. In the Resource Type drop-down list, select Distributed Transaction Coordinator. In the Group drop-down list, make sure the right resource group is selected. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the physical disk and the MS DTC network name From Available Resources, and click Add to add them to the Resource Dependencies list. • Start the newly created resources by right-clicking the MS DTC group and selecting Bring Online Also, to ensure that external processes can utilize the clustered MS DTC, you must enable network MS DTC access. Follow these steps: 1. Log on to the node that currently owns MS DTC. 2. Open Add or Remove Programs in Control Panel. 3. Click Add/Remove Windows Components. 4. Select Application Server, Click Details. 5. In the Application Server dialog box, make sure that the Enable Network DTC access option is checked. Click OK. 6. Click Next. You might be prompted for your Windows installation media, so you should have it nearby or on an accessible network share. 7. Click Finish on the Completing the Windows Components Wizard dialog box. 8. Stop MS DTC by right-clicking the cluster group and selecting the option Bring Offline. 9. Restart MS DTC by right-clicking the cluster group and selecting the option Bring Online. For more information see http://crazysql.wordpress.com/2009/0...ws-clustering/ Manpreet Singh http://crazysql.wordpress.com/ MCITP –DBA, DD - SQL server 2005 MCTS – SQL server 2005 MCTS –SharePoint Server 2007 "robd08" wrote: I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases. When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers. So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC. |
#13
| |||
| |||
|
|
Easy, in case of cluster MSDTC you will get a unique IP address for MSDTC , same as cluster Sql server instance. In case of failure , when fail over occur then the MSDTC resource will also get fail over resulting continuity in operation with no downtime. But on the other hand if you use local MSDTC specific to server , then if the server goes down you won’t be able to use MSDTC and all the transaction depend upon MSDTC will also get fail. Manpreet Singh http://crazysql.wordpress.com/ MCITP –DBA, DD - SQL server 2005 MCTS – SQL server 2005 MCTS –SharePoint Server 2007 "robd08" wrote: Manpreet Thanks for the detailed info on how to cluster DTC. But is still not really what I'm after. The question is - do I need to go to all that trouble of clustering DTC. What will be the effects if I don't. Rob "Manpreet Singh" wrote: Follow these steps to install MS DTC in cluster environment. • Start Cluster Administrator. • In the left pane, expand Groups. Right-click the group that contains the dedicated disk for Use with MS DTC, and select Rename it; enter the new name for the resource group as something logical, such as MS DTC • Right-click the newly renamed resource group, select New, and then select Resource In the New Resource dialog box, enter an appropriate name for the IP resource that will be associated with MS DTC such as MS DTC IP Address. In the Resource Type drop-down list, select IP Address. In the Group drop-down list, make sure the right resource group is selected. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the drive from Available Resources, and click Add to add it to the Resource Dependencies list. When finished, click next. • In the TCP/IP Address Parameters dialog box, enter a static IP in the Address input box, and select the proper public network to use. Click Finish. • If the resource is created, then resource now appears in the resource group and has a state of Offline. Right-click MS DTC’s resource group, selects New, and then select Resource. • In the New Resource dialog box, enter an appropriate name for the network name resource that will be associated with MS DTC such as MS DTC Network Name. In the Resource Type drop-down list, select Network Name. In the Group drop-down list, make sure the right resource group is selected.. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the MS DTC IP address from Available Resources, and click Add to add it to the Resource Dependencies list. When finished. Click Next. • In the Network Name Parameters dialog box, enter a name for MS DTC that will be used by other applications. Click Finish. • After resource is created. Click OK. The resource now appears in the resource group and has a state of Offline. • Right-click MS DTC’s resource group, select New, and then select Resource. In the New Resource dialog box, enter an appropriate name for the MS DTC resource itself such as MS DTC. In the Resource Type drop-down list, select Distributed Transaction Coordinator. In the Group drop-down list, make sure the right resource group is selected. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the physical disk and the MS DTC network name From Available Resources, and click Add to add them to the Resource Dependencies list. • Start the newly created resources by right-clicking the MS DTC group and selecting Bring Online Also, to ensure that external processes can utilize the clustered MS DTC, you must enable network MS DTC access. Follow these steps: 1. Log on to the node that currently owns MS DTC. 2. Open Add or Remove Programs in Control Panel. 3. Click Add/Remove Windows Components. 4. Select Application Server, Click Details. 5. In the Application Server dialog box, make sure that the Enable Network DTC access option is checked. Click OK. 6. Click Next. You might be prompted for your Windows installation media, so you should have it nearby or on an accessible network share. 7. Click Finish on the Completing the Windows Components Wizard dialog box. 8. Stop MS DTC by right-clicking the cluster group and selecting the option Bring Offline. 9. Restart MS DTC by right-clicking the cluster group and selecting the option Bring Online. For more information see http://crazysql.wordpress.com/2009/0...ws-clustering/ Manpreet Singh http://crazysql.wordpress.com/ MCITP –DBA, DD - SQL server 2005 MCTS – SQL server 2005 MCTS –SharePoint Server 2007 "robd08" wrote: I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases. When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers. So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC. |
#14
| |||
| |||
|
|
Thank you. Just one more question. If I use local MSDTC specific to the server and the server goes down will the transaction fail cleanly (as in the transaction is rolled back) or will it result in orphaned transactions, corrupt data etc. "Manpreet Singh" wrote: Easy, in case of cluster MSDTC you will get a unique IP address for MSDTC , same as cluster Sql server instance. In case of failure , when fail over occur then the MSDTC resource will also get fail over resulting continuity in operation with no downtime. But on the other hand if you use local MSDTC specific to server , then if the server goes down you won’t be able to use MSDTC and all the transaction depend upon MSDTC will also get fail. Manpreet Singh http://crazysql.wordpress.com/ MCITP –DBA, DD - SQL server 2005 MCTS – SQL server 2005 MCTS –SharePoint Server 2007 "robd08" wrote: Manpreet Thanks for the detailed info on how to cluster DTC. But is still not really what I'm after. The question is - do I need to go to all that trouble of clustering DTC. What will be the effects if I don't. Rob "Manpreet Singh" wrote: Follow these steps to install MS DTC in cluster environment. • Start Cluster Administrator. • In the left pane, expand Groups. Right-click the group that contains the dedicated disk for Use with MS DTC, and select Rename it; enter the new name for the resource group as something logical, such as MS DTC • Right-click the newly renamed resource group, select New, and then select Resource In the New Resource dialog box, enter an appropriate name for the IP resource that will be associated with MS DTC such as MS DTC IP Address. In the Resource Type drop-down list, select IP Address. In the Group drop-down list, make sure the right resource group is selected. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the drive from Available Resources, and click Add to add it to the Resource Dependencies list. When finished, click next. • In the TCP/IP Address Parameters dialog box, enter a static IP in the Address input box, and select the proper public network to use. Click Finish. • If the resource is created, then resource now appears in the resource group and has a state of Offline. Right-click MS DTC’s resource group, selects New, and then select Resource. • In the New Resource dialog box, enter an appropriate name for the network name resource that will be associated with MS DTC such as MS DTC Network Name. In the Resource Type drop-down list, select Network Name. In the Group drop-down list, make sure the right resource group is selected.. Click Next. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the MS DTC IP address from Available Resources, and click Add to add it to the Resource Dependencies list. When finished. Click Next. • In the Network Name Parameters dialog box, enter a name for MS DTC that will be used by other applications. Click Finish. • After resource is created. Click OK. The resource now appears in the resource group and has a state of Offline. • Right-click MS DTC’s resource group, select New, and then select Resource. In the New Resource dialog box, enter an appropriate name for the MS DTC resource itself such as MS DTC. In the Resource Type drop-down list, select Distributed Transaction Coordinator. In the Group drop-down list, make sure the right resource group is selected. • In the Possible Owners dialog box, verify that all nodes of the server cluster are listed as possible owners of this resource. If a node is not listed on the right side, select it from the Available Nodes list, and click Add. Click Next. • In the Dependencies dialog box, select the physical disk and the MS DTC network name From Available Resources, and click Add to add them to the Resource Dependencies list. • Start the newly created resources by right-clicking the MS DTC group and selecting Bring Online Also, to ensure that external processes can utilize the clustered MS DTC, you must enable network MS DTC access. Follow these steps: 1. Log on to the node that currently owns MS DTC. 2. Open Add or Remove Programs in Control Panel. 3. Click Add/Remove Windows Components. 4. Select Application Server, Click Details. 5. In the Application Server dialog box, make sure that the Enable Network DTC access option is checked. Click OK. 6. Click Next. You might be prompted for your Windows installation media, so you should have it nearby or on an accessible network share. 7. Click Finish on the Completing the Windows Components Wizard dialog box. 8. Stop MS DTC by right-clicking the cluster group and selecting the option Bring Offline. 9. Restart MS DTC by right-clicking the cluster group and selecting the option Bring Online. For more information see http://crazysql.wordpress.com/2009/0...ws-clustering/ Manpreet Singh http://crazysql.wordpress.com/ MCITP –DBA, DD - SQL server 2005 MCTS – SQL server 2005 MCTS –SharePoint Server 2007 "robd08" wrote: I'm setting up my first cluster (SQL Server 2005 on Windows 2008) and need advice regarding MSDTC (Microsoft Distributed Transaction coordinator). From my research it seems one of the steps in setting things up is to cluster MSDTC and that appears to be a fairly complicated process for a humble DBA. In a non clustered environment you don't have to worry about it - MSDTC is at the operating system level and just works. Apparently it's role is to coordinate distributed transactions to ensure consistent updates across multiple databases. When clustering SQL on Windows 2003 you have to first cluster MSDTC (otherwise I think the SQL install won't complete). But with Windows 2008 clustering has changed in some ways and you can actually install a clustered SQL instance without clustering MSDTC, as I have done. I think this has something to do with a 2008 cluster being able to access the local MSDTC service whereas 2003 can't. I have monitored our database servers with SQL profiler and I can't find any applications that use distributed transactions, although we do use distributed queries via linked servers. So my question is, given that I'm using Windows 2008 and none of my apps use distributed transactions, do I need to go to the trouble of clustering MSDTC? I've read something about Integration Services relying on it, but I have that running on my cluster now without issue. Also, in the future if I do get an app that uses distributed transactions, what behaviour should I expect on a cluster without MSDTC clustered? Will errors show in the SQL logs or will things seem to work as normal except updates won't be consistent between databases? In my current environment I've been able to run a BEGIN TRANSACTION statement across 2 servers without issue, so I don't see why I need to waste time clustering MSDTC. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.