DLM automation fails to build database when script involves a LinkedServer connection

mghadiali

One of the database views queries a table on another server via a LinkedServer connection.
However, every time DLM tries to build the database it hits the following error. DTC is enabled on SERVER1. Does DTC need to be enabled on the Linked Server as well i.e. SERVER2?

Note - If the view is queried via SSMS connected via Windows authentication, it returns result. So, not so sure if DTC is the issue. When the view is queried via TFS2017 builds, the build agents have system admin rights on SERVER1 where the scratch database is built successfully but the builds dont progress beyond that because of the linked server issue.
If the views containing the linked server are removed from the builds - then the builds are successful. We need some help in isolating the cause of the failure.

Error: Synchronization of 'Scripts.state' and

2019-04-03T20:08:34.1041304Z 'SERVER1.sqlCI_368258e5-5ddf-406b-a502-32d88b4e0a37' failed: The operation

2019-04-03T20:08:34.1041304Z could not be performed because OLE DB provider "SQLNCLI11" for linked server

2019-04-03T20:08:34.1041304Z "SERVER2" was unable to begin a distributed transaction. OLE DB provider

2019-04-03T20:08:34.1041304Z "SQLNCLI11" for linked server "SERVER2" returned message "The partner

2019-04-03T20:08:34.1041304Z transaction manager has disabled its support for remote/network transactions.".

mghadiali

An Edit - The View containing the the Linked server quesry does NOT use a BEGIN DISTRIBUTED TRAN statement

Andrew P

Thanks for your post!

By default, it uses one long transaction for the Build, with XACT_ABORT ON, so the whole script is rolled back if any part of it fails.

I'd recommend using/adding the NoTransactions Comparison option to the Build Definition. Since a Build is purely for validation, its not really worth it to have in one long transaction if it runs into Distributed Transaction errors.

Cheers,

mghadiali

Thx Andrew - Quick update, we were able to resolve this by adding a SQL Compare Parameter:

/TransactionIsolationLevel:"READ COMMITTED"

to the build script.

We are now encountering a similar issue at Release time. How can we change the default behavior of TransactionIsolationLevel at Release time via RedGate DLM Automation Release?

mghadiali

Is there a way to change the Transaction Isolation Level at Release time?

Andrew P

Yep you can change the Transaction Isolation Level in the Release.

If you are using the PowerShell then it is just the -TransactionIsolationLevel switch in the New-DatabaseReleaseArtifact cmdlet

If you are using the Release Extension it would look like this: