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
Sql-server – AlwaysOn Availability Groups Delayed Replica
availability-groupssql serversql-server-2012
Related Question
- Sql-server – By Mistake Deleted AlwaysOn Availability Database from live Production Server
- Sql-server – AlwaysON Availability Groups – IP address & subnet change of secondary replica node
- Sql-server – AlwaysOn DDL and Schema Changes
- Sql-server – Problem joining log-shipped secondaries in STANDBY to AlwaysOn group
- HA SQL AlwaysOn backup using Copy Only + Logs from secondary replica
- Sql-server – Read only replica part of always on availability group for Reporting
- Sql-server – Automatic Failover on SQL Server Always On Availability Groups is not triggered when primary replica becomes offline
Best Answer
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.