Is there a way to implement a cross-database task on SQL Server 2012 with the Availability Groups feature?

Question

We use SQL Server 2012 and its new Availability Groups (AG) feature. There is a task for moving old data of some tables from one database to another database. Both databases are included into different availability groups.

Previously (before using the AG feature) the task was resolved by adding the second server instance as a linked server (sp_addlinkedserver) and executing a distributed transaction in the following way:

1. begin transaction
2. insert old data into server2.table2 from server1.table1
3. delete old data from server1.table1
4. commit transaction

Unfortunately, distributed transactions are not supported for AG because databases may become inconsistent in case of failover (http://technet.microsoft.com/en-us/library/ms366279.aspx).

Is there some way to implement this task with keeping the AG feature and without implementing the rollback logic in case of exceptions?

Answer 1

First, you can technically do it with DTC or linked server transactions - it's just not supported, and it's a bad idea for performance anyway. Let's talk about a better method.

The way the question is written, it sounds like you're doing archival - moving data from the "current" tables on Server1.Table1 to a set of "archive" tables on Server2.Table2. In that case, it's okay if the data is in both places for a short period of time, so I'd break it up into two separate tasks:

Phase 1: The Copy - Insert old data into server2.table2 from server1.table1. Assuming this is an archival process, you could have this as a scheduled job that runs nightly at midnight (or whatever day/times work for you.)

Phase 2: The Delete - Reading from the read-only replica of server2.table2, delete data that still exists in server1.table1. Do this as a scheduled job that runs X minutes after phase 1, every X minutes, for an hour (or however often you want to retry.) This way, if the read-only replica is running several minutes behind, your delete will still happen eventually.

It's important to read from the read-only replica rather than server2 directly - after all, your goal is to make sure the data is actually protected. (This is why the cross-database stuff isn't supported.)

To design this for performance, I wouldn't actually join between server2.table2 and server1.table1, either - that's a recipe for a blocking mess. Instead, grab the max ID in server2.table2, and that will tell you the data to delete on server1. If record ID# 500 has made it to server2.table2, then you can delete all IDs <= 500 on server1.table1. You can use the same trick with dates. Bonus points if you use the fast ordered delete technique.

If you're archiving individual records rather than going by an ID or date field, you'll still have to join directly between the tables - just don't join on server2 directly, but instead its read-only replica.

Yes, this is more work than a single delete statement wrapped in a transaction, but as you start to push performance and availability boundaries (like scaling data across multiple servers with AlwaysOn AGs), you have to do more of this stuff yourself rather than hitting the easy button, heh.