What are the challenges you face when working across database platforms? Take the survey
Options

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

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.".
Tagged:

Answers

  • Options
    mghadialimghadiali Posts: 6 New member
    An Edit - The View containing the the Linked server quesry does NOT use a BEGIN DISTRIBUTED TRAN statement
  • Options
    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,


    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • Options
    mghadialimghadiali Posts: 6 New member
    edited May 3, 2019 4:55PM
    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?



  • Options
    mghadialimghadiali Posts: 6 New member
    Is there a way to change the Transaction Isolation Level at Release time?
  • Options
    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:



    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
Sign In or Register to comment.