Hi, I have configured Always ON with Synchronous Commit on 2 nodes windows cluster on my local environment. I had run the Stress test and in middle of test, forcibly stopped the SQL Server on Primary instance. Then i copied the MDF and LDF files and restored it to some other instance and manually failover the instance to secondary one. With Red Gate as data comparison tool compared the data and can see there are few discrepancy in data. As per the M/S, with Synchronous commit there is no data loss, I am sure i am missing with any setting. Please help me in correcting the configuration. Thanks, Govind
Depending on the nature of the transactions, you could still see data loss. The idea is that a completed transaction has to complete on both databases. But I'm sure it's not going to be 100%, just one heck of a lot more than if you did asynchronous. It's just going to add more load to the systems. You'll also note that the restore to a different server, without the Availability Group, could have the recovery process of the restore operation get transactions that were complete locally, but not in the AG. You might be seeing that. But, I haven't run into issues with either, so I'm speculating a little here.