@Luke Wilkinson - it's what I'd generally recommend - unless the tables in the source and destination databases are identical in terms of structure and data - in which case I'd be looking at Replication instead.
NET’s Entity Framework Database Migrations and SQL Azure on one of my current development projects which has been working out quite nicely for me.
To do this, I leveraged my existing Database Migrations project by adding the connection string to my SQL Azure database subscription.
First, I needed to clear out my existing migrations in the cloud (Run these commands through the in Visual Studio).
Once that was finished, all I had to do was run the database migrations from the beginning to apply the most up-to-date version of my database in the cloud.
I have created a trigger that updates an remote SQL Server 2005 database table.
The code works if it is a manual query but will not work in a trigger because it tries to place it in a transaction and fails because of MSDTC.
Our internal server has appropriate access to the remote server but the remote server does not have access to the internal server, would this cause the trigger to fail?The error I get is: OLE DB provider "SQLNCLI10" for linked server "Link Server" returned message "The partner transaction manager has disabled its support for remote/network transactions.".Msg 7391, Level 16, State 2, Line 1 The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Link Server" was unable to begin a distributed transaction.to be part of the original transaction, and if anything goes wrong in the trigger, that transaction will be rolled back.You do not, usually, want this to happen if there's an intermittent problem reaching a remote resource (be that a different DB on the same server/instance, or something truly remote).Have you looked into decoupling this activity with, for instance, Service Broker?