Post reply

Possible to Side Load a DB to AOAG then rename

  • April 8, 2022 at 4:30 pm

    #4012795

    Hi,

    I have a large database within an 2016 AOAG that needs to be refreshed quite  in a test environment from a copy in production.

    In order to minimise restore time due to tight maintenance windows, i was looking at an option to restore a copy of the database alongside the existing database in a non recovery state.

    Once in a position to refresh the database , i was hoping i could drop the original database, bring online the copy , rename the db and join to the AOAG saving restore time on the maintenance window.

    Points i have learnt ...

    I understand you cannot rename a database in NORECOVERY state

    I understand you cannot rename a database whilst in AOAG

    I understand you cannot reattach a database in a NORECOVERY state

    Has anyone found themselves in a similar position with a large database, where they have thought of side loading a database copy to the AOAG in parallel and then replacing the original

    database with a renamed copy thus saving time on the day, restoring across all nodes ?

    Just wondered if anyone had experienced this and had a valid approach that may work for me ?

    Thanks

    James

  • April 9, 2022 at 12:55 am

    #4012939

    If you use a SAN, check the SAN documentation for a "SAN Snapshot".  It's almost instantaneous.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 10, 2022 at 4:48 pm

    #4013577

    Jeff Moden wrote:

    If you use a SAN, check the SAN documentation for a "SAN Snapshot".  It's almost instantaneous.

    Which will not work for an AOAG unfortunately.  To add a database to an AG you must restore the full backup in norecovery - then restore transaction log backups up to the point in time and join.  Or, you set automatic seeding on the AG and let SQL Server handle it for you.

    To the OP - why does it matter how long it takes to recover the secondary?  As long as the primary database is up and available - the users will not be impacted.  Using automatic seeding - you can then just add the database to the AG and SQL Server will take care of the rest.  Note: you will need to delete that database on the secondary - and remove backup history.

    As for how long it takes - that is going to depend on several factors.  There is a trace flag you can set that enables compression - which I would recommend you set.  If you are restoring a copy from prod into your test AG - then you can also reduce the amount of time that restore takes by adding MAXTRANSFERSIZE = 4194304.  You may also need to modify BUFFERCOUNT - but generally you can let SQL Server determine that for you.

    I restore a 6TB+ database to a test AG in about 45 minutes, and with automatic seeding it only takes another 1 to 2 hours to have the secondary database synchronized.  That is from a local copy of the database backup file though - so another couple of hours to copy that backup file but I don't count that because the primary database is still available until the restore process starts.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply