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?
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.
It's because these methods rely on the transaction log, which itself is only reliable in the full recovery model. Please read the following page in its entirety, as there are several hints there about the differences between simple and full recovery, and why full is so important for DR:
If you want to use simple recovery, then you'll only be able to achieve DR by taking full and differential backups. In which case you lose point-in-time recovery.
In some cases that's ok, but we have no idea what your RPO/RTO is, nor do we know your tolerance for data loss, disk space used by backups, etc. You can stay in simple recovery mode and have relatively reliable RPO, if you want to take a full or diff backup every minute all day long. Log backups tend to be a quicker, easier and more reliable way to achieve optimal RPO, and I suspect most people are using full recovery already for that very reason. So the requirement of AlwaysOn and other technologies that take advantage of this model is not usually much of a stumbling block or head-scratcher. But it is a hard requirement, and the reasons are rather irrelevant, but you can probably assume that one of the most basic reasons is that in simple recovery (or even bulk-logged) you can actually lose transactions that won't ever get shipped to secondaries, leaving it in an inconsistent state.
Everything is a trade-off. My feeling is that a lot of people prefer simple recovery because there are fewer issues they have to deal with regarding the transaction log (it's a myth that these issues dwindle to 0, however). It's like a lot of things - that trade-off works fabulous until you actually experience a disk failure, human error or other outage that requires you to revert to backups. In full recovery you are kind of forced to manage your logs proactively (otherwise they will consume your disks), and you can always get to a specific point in time in the event of a failure. In simple this seems to me to be a very easy gateway to not paying attention to backups at all. That's just an opinion, and not a technical fact, but it is based on a very large sample size.
Suppose I run the following query on a database with a recovery model of full:
INSERT INTO dbo.MY_FAVORITE_MSFT_EMPLOYEES WITH (TABLOCK)
SELECT 'SEAN GALLARDY' UNION ALL SELECT 'JOE SACK';
Under the full recovery model, SQL Server will write some representation of the data that was inserted to the transaction log. This allows redo threads on the secondary database to read the transaction log and to apply the transactions that occurred on the primary database to the secondary database. This is only possible because there's some representation of "JOE SACK" and "SEAN GALLARDY" written to the transaction log.
Now suppose I run the same query on a database with a recovery model of simple and my insert qualifies for minimal logging:
INSERT INTO dbo.MY_FAVORITE_MSFT_EMPLOYEES WITH (TABLOCK)
SELECT 'SEAN GALLARDY' UNION ALL SELECT 'JOE SACK';
In this case SQL Server may not write a full representation of the data that was inserted to the transaction log. Instead, it may just log page ids or extent ids. This blog post is instructive:
Under minimal logging, the SQL Server does not log individual rows and logs only the page and extent allocations. So if each page can fit 100 rows, we are trading 100 log records for 1 log record, a significant improvement.
Knowing the page ids or extent ids of what changed is enough for SQL Server to perform rollbacks as necessary. However, it is not enough information for the redo writer to apply the transaction to a secondary database. Primary and secondary databases need to be byte-for-byte copies so the simple recovery model is not compatible with AG.
