No dedicated disk for MSDTC on a cluster?

Question

Hi All,

I am planning to install SQL Server 2008 R2 Ent.(Data Engine and SSIS) on a Windows 2008 R2 Ent. We have only two shared drives; one for Quorum (1GB) and one Data for everything else.

We need MSDTC service. What do you think my options are here?

Should I create a MSDTC resource in SQL Server resource group AFTER SQL installation on Data drive?

Thanks,

Kuzey

Answer 1

Hi

You need a dedicated drive

http://technet.microsoft.com/en-us/library/cc770748(v=ws.10).aspx

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi,

Sorry, but I am not sure that I agree that you need a dedicated drive for the MSDTC in Windows 2008. I always went with dedicated resources and an independant cluster group for MSDTC in Windows 2003 but Windows 2008 is a lot more flexible allowing multiple independent DTC resources and even using allowing the use of local DTC from memory.

I don't see why the DTC could not make use of the SQL disks. My bigger concern in this design is your I/O config for SQL server. Since you are using SQL Enterprise Edition I assume you have a pretty large database therefore I would be more concerned about having independent dedicated LUN's for SQL Data, SQL logs and maybe even for tempdb in order to help decrease resource contention on the I/O subsystem.

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

Answer 3

Sean,

The database is not big; max 1GB. As you can see from the SAN config, they don't worry about Disk I/O.

If I can use the Data drive for DTC, should the DTC resource be defined AFTER SQL installation? Do I still need a separate IP/name for MS DTC?

Thanks,

Kuzey

Answer 4

As far as I have seen, You can use the SQL server group for MSDTC and then you don't need specific ip/name or disk . It uses all from the SQL group. The problem why ppl dont do that is  the failure of msdtc causes failover of sql server if you don't set the option don't do anything if msdtc fails.

If you set that option then you wont come to know if msdtc fails and distributed transactions can fail. That is the reason why it is recommended to keep MSDTC in a seperate group.

But ppl do keep both in the same group, when they want MSDTC to failover along with SQL and vice versa.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 5

Ashwin,

If I decide to use the SQL server group for MSDTC, should I create the MSDTC resource after sql install or before?

Thanks,

Kuzey

Answer 6

 

 If you are only installing Database engine and will not be using Distributed Transaction than you do not need MSDTC as cluster resource.

If you are using Distributed Transaction then you need to install MSDTC as cluster resource before proceeding with SQL server failover cluster and during SQL Server installation setup will detect if MSDTC is clustered.

Keep in mind, you can always add MSDTC as a clustered resource later on.