none
Is there a way to implement a cross-database task on MSSQL 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.

    Is there some way to implement this task with keeping the AG feature and without implementing the rollback logic in case of exceptions?
    Sunday, August 25, 2013 9:26 PM

Answers

  • Hi,

    You can try like this with help from temporary table.

    1. insert data into server2.parentTable from server1.parentTable

    2. if no error then insert marking into temporary parentTable indicate which primary key already transferred. 

    3. insert data into server2.childTable from server1.childTable

    4. if no error then insert marking into temporary childTable indicate which data already transferred. 

    5. for each success inserted data that marked in temporary table, you can delete data from server1.parentTable and server1.childTable

    This is a bit variant from what Uri Dimant gave before, you can merge the solution with mine above for marking indicator.


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by metravl Wednesday, August 28, 2013 9:04 AM
    Wednesday, August 28, 2013 2:08 AM

All replies

  • --SQL2008 and onwards
    insert into server1.fooArchive..FredArchive 
    select getdate(),d.*
    from (delete top (10000) *
            from foo..Fred
            output deleted.*) d
            go

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance


    Monday, August 26, 2013 8:14 AM
  • --SQL2008 and onwards
    insert into server1.fooArchive..FredArchive 
    select getdate(),d.*
    from (delete top (10000) *
            from foo..Fred
            output deleted.*) d
            go

    Best Regards,Uri Dimant SQL Server MVP

    Uri, thanks for the reply. This solution helps avoid explicit transactions but it is applicable for one table. What if we need to move old data from 2 related tables to 2 corresponding related tables in the 2-nd database. In this case there will be 3 statements:

    1. insert data into server2.parentTable from server1.parentTable (without deleting from server1.parentTable in order to not violate the reference constraint)
    2. insert data into server2.childTable from server1.childTable (with deleting, no violation of the reference constraint)
    3. delete data from server1.parentTable

    Obviously all these 3 statements must be in a transaction. If such transaction is impossible for AG databases, how can I implement it? Should I check error level after each statement in the stored procedure, stop execution of the procedure and rollback changes explicitly? Is there some alternative?
    Monday, August 26, 2013 9:28 AM
  • Hi Metravl,

    Thanks for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.
    Thank you for your understanding and support.

    Thanks

    Candy Zhou


    Wednesday, August 28, 2013 1:46 AM
  • Hi,

    You can try like this with help from temporary table.

    1. insert data into server2.parentTable from server1.parentTable

    2. if no error then insert marking into temporary parentTable indicate which primary key already transferred. 

    3. insert data into server2.childTable from server1.childTable

    4. if no error then insert marking into temporary childTable indicate which data already transferred. 

    5. for each success inserted data that marked in temporary table, you can delete data from server1.parentTable and server1.childTable

    This is a bit variant from what Uri Dimant gave before, you can merge the solution with mine above for marking indicator.


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Marked as answer by metravl Wednesday, August 28, 2013 9:04 AM
    Wednesday, August 28, 2013 2:08 AM