Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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.

share|improve this answer
+1 This feature has been requested but so far I'm the only one to have voted for it. – Aaron Bertrand Feb 6 '14 at 19:04
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
@AaronBertrand no you no longer are ;-). Thanks for making me aware of this feature request! – Edward Dortland Feb 6 '14 at 20:05

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.

share|improve this answer
Thanks Brent. Appreciate your input. I have used snapshot as a measure for pre-batch processing and has helped a lot. – DBAuser Feb 6 '14 at 19:47
You're welcome! – Brent Ozar Feb 6 '14 at 19:58
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

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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