AlwaysON AG

  • Hello guys,

    I have a WSFC cluster with AlwaysON AG two replicas(syncro)  in  one DC1 and the third  replica (asynchronous)in other DC2.The connection between DC's are quite slow. Management want to start an disaster scenario : Failover to DC2 and shutdown completely  one week DC1 and come back to DC1.  My question is how I can  re-synchronize  AG's  after startup again the DC1  and failback  ?

    Thanks in advance,



  • Isn't that the whole "reason to be" for Ag's ?

    When the partner instance comes back online and is visible to the primary instance, it will pickup log-processing from primary to replica(s) where it left.

    So, depending on your db usage load, you can just have it catch up

    Having a slow connection between both DC's, that may need some time.


    Or,  you may also opt to remove replica DC1 from the AG and re-join it after DC1 is back online following the add-replica procedure ( restore full + log backups until current time and join )

    It all depends on how much time you estimate it will all need for the different scenario's





    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • If DC1 is being shut down after the failover (or simulated being down by cutting the network) you will need to remove it from the AG after failover.

    Thus is because keeping it in the AG will mean log files will continue to grow while DC1 is down. It probably won't take long to fill storage capacity with log files if you try to keep DC1 connected.

    As for failback, that depends on if you are on SQL2019 or above. If you are, then before you reconnect DC1 to the AG you need to delete all DB files at DC1. You can then use the automatic initialisation feature to rebuild everything on DC1. This works well, but if a desired target file already exists at DC1 then the rebuild of the affected DB cannot proceed.

    For SQL2017 and below you will have to use backup and restore to get the DBs sorted out on DC1. Because restore can overwrite exiting files there is no need to delete the DB files on DC1.

    Make sure you have failover and failback checklists that document each process needed so things work smoothly.

    If you have got all this set up in Production, then you will naturally have a pre-production environment also running across DC1 and DC2 with its own AG setup, so test the whole failover and fail back process on pre-prod before doing it with your crown jewels. You will find plenty of things that need to go in your checklists.

    If you do not have a pre-prod across DC1 and DC2 with an AG this should be your first step. The business risk of just having production in an AG is tremendous, unless your organisation likes having extended downtime.

    Original author: 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

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

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