MS DTC and VSS Based Backups

• Philip Barry

  SSCrazy

  Points: 2642

  More actions

  December 12, 2013 at 6:13 am

  #395978

  Although I don’t think this is strictly a SQL Server issue, as SQL Server is the only user of MS DTC it appears that this situation has fallen on the laps of the DBAs (isn’t that always the case!).

  Background

  We are deploying a new application. This application is composed of multiple application servers connecting to a central SQL Server (a standalone named instance SQL Server 2008 R2 Standard Edition x64 SP2). All servers use Windows Server 2008 Standard SP1 for the operating system.

  The application is Java based and makes extensive use of JDBC XA transactions with SQL Server which then, automatically, invokes the Microsoft Distributed Transaction Coordinator (MS DTC) to manage the XA transaction.

  The estate is virtualised on the VMware ESX hypervisor and backups of the VM are undertaken by Symantec NetBackup which utilises VSS to backup the VM (such as OS and, using the NetBackup agent, SQL Server databases).

  From testing, it appears that during the backup of the OS hosing SQL Server (during the snapshot delete phase) pinging from the application machine (to the SQL Server machine), it could be seen when the "stuns" are happening as it processed each partition in the server. This was correlated with the "I/O frozen" messages in the MS-SQL server logs when it got to the phase for the database backup. The test application was running fine during these stuns, including the "I/O frozen" messages. However when, I believe, the system partition was reached (where MSDTC is logging its transactions) it was at this point that the test application threw exceptions from SQL JDBC calls.

  Question

  Having reviewed http://technet.microsoft.com/en-us/library/cc753861.aspx the following highlighted text may be of concern:

  Limitations of VSS support for distributed transactions

  ________________________________________

  VSS operations are limited to the local computer. Where Microsoft Distributed Transaction Coordinator (MS DTC) support for VSS is available, it is limited to local deployment and transaction coordination scenarios. The following configurations are not supported:

  • Any cluster topology

  • Any remote proxy topology where the MSDTC service from a remote computer is used to coordinate transactional activity locally

  • Any application topologies that involve distributed transactions

  I’m asking the community if anyone else uses this application topology, especially invoking MS DTC, with a VSS based backup?

  If there are these configurations out there, are you also encountering this type of error and if so, have you mitigated them in any way?

  Regards,

  Phil