High availability group synchronization

  • For high availability group, is there an option like transaction log shipping that I can choose the interval of time to synchronize the database. So for example  ,   delay it to 30 minutes to synchronize.

    Thanks

     

     

    • This topic was modified 9 months, 1 week ago by  sqlfriend.
  • No - that is not an option for an availability group.  It would also negate the HA requirements - since anything with a lag would no longer satisfy high availability.

    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

  • Any other options if want to for example to synchronize the database every 1 hour. The reason we do this is because we have another database in oracle end, they do refresh every 1 hour. The other systems are SQL servers which  host other applications  which we want to do the failover at the same time oracle did so the entire systems are in synchronization as a whole.  I know we can do transaction log shipping and set to refresh every 1 hour, but that is for each database, and mostly failover works in one direction, to failover back  to primary takes time, no need to say we have multiple servers and databases which we have to create many jobs for each one.

    I am thinking to use high availability group, it will save time for failover as a group of databases, and the failover time is short,  but we now have the issue that we cannot delay the synchronization.

    Any other recommendations, thanks!

    • This reply was modified 9 months, 1 week ago by  sqlfriend.
  • Log shipping or write your own ETL routines are the only thing you can do which builds a delay into synchronisation.

    But you want HA via AOAG, you cannot have a delay, negates the whole concept about it being highly available.

  • sqlfriend wrote:

    Any other options if want to for example to synchronize the database every 1 hour. The reason we do this is because we have another database in oracle end, they do refresh every 1 hour. The other systems are SQL servers which  host other applications  which we want to do the failover at the same time oracle did so the entire systems are in synchronization as a whole.  I know we can do transaction log shipping and set to refresh every 1 hour, but that is for each database, and mostly failover works in one direction, to failover back  to primary takes time, no need to say we have multiple servers and databases which we have to create many jobs for each one.

    I am thinking to use high availability group, it will save time for failover as a group of databases, and the failover time is short,  but we now have the issue that we cannot delay the synchronization.

    Any other recommendations, thanks!

    You can roll your own log shipping that simplifies that. The first responder toolkit has a tool called sp_AllNightLog that is meant to assist with this, but it is pretty complicated to use.

    I am uncertain of the assumption that failing them over to the same point in time would ensure they are consistent, there is no way you could ensure all the transactions were done at the same point in time. Do you have or something that keeps transactions for a period that you can replay?

Viewing 5 posts - 1 through 4 (of 4 total)

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