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
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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.