Here is an issue more on the obscure side; nevertheless, it may prove beneficial to someone, somewhere, at some point. The issue pertains to a SQL database going Suspect after a Zerto Failover. To adequately explain the cause, we will first look at Microsoft SQL Server best practices for both VMware and Zerto.
Let’s start with VMware best practices. Assuming the back-end storage is spinning disks and the virtual disks reside on VMFS volumes, VMware recommends separating SQL files. Meaning, SQL Server binaries, SQL data (mdf), SQL transaction logs (ldf), and tempdb files are placed on separate VMDKs. Since SQL Server accesses all that data in different I/O patterns, separating their files helps minimize disk head movements and limits I/O contention; thus optimizing storage performance. The disk configuration in the affected environment looked like this:
- C: Drive – OS\SQL Server Binaries
- D: Drive – SQL Data (mdfs)
- L: Drive – SQL Transaction Logs (ldfs)
- T: Drive – TempDB
Now, Zerto best practices. Zerto endorses VMware’s recommended separated disk configuration. With separation, admins can specify individual disks for Temp Data on replicated vms. If a disk marked as Temp in Zerto, it will perform an initial data sync, but not replicate any subsequent changes. Typically, the TempDB disk is designated as such due to RPO spikes during heavy transactional updates.
Tying this all together, the affected environment had not only specified the TempDB drive but also the SQL log drive as Temp Data. That being the case, the transaction logs had not been updated since the initial sync. This presumably caused the database to go Suspect, as SQL was unable to bring the database back to a consistent state during its startup recovery. Once the transaction log drive was unmarked as Temp Data, the databases started successfully upon failover.
NOTE: Disks can be specified as Temp under the Storage tab when editing the VPG in Zerto.
VMware and Microsoft SQL Best Practices documentation.
Zerto Replication and Microsoft SQL Best Practices documentation.