Run Multiple Scripts In One Transaction Across Multiple Servers [duplicate]

Question

We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.

Answer 1

I've run into a problem like this. I used ADO.NET 4 with VB.

Here's what I did:

1. Created a .txt file with list of servers.
2. Dump the .txt file into a List Box.
3. Wrote the .NET application to scan through the list box, line by line, dumping each line into a text box on the form.
4. Replace the connection string on the SQL connection with the database currently in the text box.
5. Wrote the SQL scripts inside the command clause.
6. Execute the SQL command on the current database.
7. Next line.

Example:

    'Loop through the server names.
    For i = 0 To lbxServers.SelectedItems.Count - 1
        txtServer.Text = ""
        txtServer.Text &= lbxServers.SelectedItems.Item(i).ToString() & " "
        'Call your sub procedure which contains your sql connection and sql command.
    Next

That will get your loop going, then you need to write some sub procedures for your queries. Like so:

    'Declare SQL connection variables.
    Dim connection As New Data.SqlClient.SqlConnection
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim command As New Data.SqlClient.SqlCommand

    'Connect to the server next in the list box.
    connection.ConnectionString = "Server= " & txtServer.Text & "; integrated security=true"

    'SQL Statement to run on the server.
    command.CommandText = "TSQL"
    connection.Open()
    command.Connection = "connection"
    dataAdapter.SelectCommand = "command"

I put all of this inside of a try/catch loop so if there is an error it will rollback.

The TRY/CATCH loop was inside of my CommandText.

    BEGIN TRANSACTION 
        BEGIN TRY 
        --Run your Statements 
        COMMIT TRANSACTION 
    END TRY 
    BEGIN CATCH 
        ROLLBACK TRANSACTION 
        DECLARE @Msg NVARCHAR(MAX) 
        SELECT @Msg=ERROR_MESSAGE() 
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG 
    END CATCH 

I used my application to grab and dump that local variable for each session into a datagridview. That way if error happened, I saw it in the application.

Answer 2

Linked servers can do this with MSDTC. After setting up MSDTC with instructions from http://www.sqlwebpedia.com/content/msdtc-troubleshooting and creating linked servers to every server, I am now able to do through exec [1.2.3.4].[catalog].dbo.sp_executesql.

System.Transactions.Transaction can also use MSDTC with this. This way I could communicate directly with the server instead of exec sp_executesql.