Sql-server – Why is Full Recovery model a requirement for an Availability Group

availability-groupsdisaster recoverylog-shippingrecovery-modelsql server

We have all our production databases in simple recovery model, and are very happy about it because it fully satisfies our RPOs and RTOs.

Now we want to implement AG DR solution for our databases and we found out that full recovery model is a requirement.

My question is WHY? Theoretically, is there any technical reason why AG or other SQL Server DR solutions (Log shipping, DB Mirroring) shouldn't work with simple recovery model?

Best Answer

There is no point in HA if anyhow DR technologies are allowed in simple recovery. Transaction log is a must for point-in-time recovery. The transaction log is what a DR technology uses to replay transactions that are done on Logshipped/mirrored databases, and since a log backup is not possible in simple recovery, you cannot configure HA/DR in simple recovery either. In mirroring, a transaction log block change is sent to a mirror server; in log shipping, a transaction log backup is sent. So, you can see the very basic concept on which Mirroring/Logshipping/AlwayON works is a Transaction log.

The transaction log is the most important part of the database—it's the only place where all changes to the database are guaranteed to be described in the event of a crash. Log records for a single transaction are linked by their LSNs and are almost like a timestamp. This timestamp is internally used to link various transactions and replay it on DR servers.

The main aim of a DR technology is to provide as little data loss as possible, and this can only be achieved if the transaction log is maintained structurally consistent. In simple recovery, transaction logs are truncated the moment the transaction commits, which means the information is not required and the space occupied by the old transaction would now be used to write a new transaction. In that case the old information is lost. If you somehow want to get it, you cannot. But in full recovery you can do it.