2

With AO SQL 2012, is it possible to configure one of the member as delayed replica? I know ideally we would like this to be synced but in case of human error, it would give us the ability to check on the secondary and recover the uncommitted data from the secondary. In one of our earlier setup we used Log shipping where the restore was delayed by an hour to one of the secondary server. Hence during any issues with data, we were able to recover the data faster instead of restoring the whole database and recovering to the point of time. Any input is appreciated...Thanks in advance

5

No, this is not possible with Availability Groups. Any incurred delays are usually from things like network, secondary replica log block flush, etc. There's no way that I can think of to turn that knob and create more of a time gap.

It sounds like if you want to take this "delayed transactions" approach, Log Shipping may have actually been the best solution there. Or you could just deal with point-in-time restores in the (hopefully corner) case of data manipulation errors. Ideally that wouldn't be happening all that frequently, and you should already have point-in-time restores as part of your operational capability, so you wouldn't need to incur any other additional technology of processes to accomplish this.

1
  • Thanks. There have been articles where people have argued why log shipping should be considered at all with AO in place. I have worked a lil with mongodb and you can make a member as a delayed to enable this functionality. I think it is good to have feature especially when AO allows to run in asynchronous mode for the secondaries, so why wouldn't a delay be added as well :-) – DBAuser Feb 6 '14 at 19:45
5

Yes, this is possible with some manual work.

You can take database snapshots on AlwaysOn Availability Group replicas, and remove those snapshots as well. You could automate the creation of snapshots every X minutes, and then tearing them back down periodically.

It will add storage overhead - it will require additional space that will depend on the change rate of the data in the database.

3
  • Whereas it accomplishes a few of the points the OP has requested, it is a variable amount of implied "delay" in the data. If you create a snapshot every hour, then you have the expanding duration where that data becomes stagnant. It'll only be "10 minutes behind" after 10 minutes have passed since snapshot creation. Obviously you know that, but worth clarifying to the OP if he/she decides to take this route. – Thomas Stringer Feb 6 '14 at 20:18
  • Thanks. I agree. I would definitely be more comfortable using the LS approach as I could restore to the point of time on the secondary just before the damage happened on the primary. – DBAuser Feb 6 '14 at 22:16
  • @ThomasStringer You can create multiple snapshots, of course. – Brent Ozar Feb 6 '14 at 23:33
1

You can also set a single a database on a single secondary replica into "Suspend Data Movement" mode. Under the Availability Databases folder for that availability group, right click and choose "Suspend...".

Data Synchronisation is a "pull" operation, by the secondary replica to the primary replica. Suspending data movement stops that. If you do this on the secondary this works just for that database on just that secondary replica. If you do this on the primary, it does this for all replicas.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.