Restoring From SQL Server Source Discontinuity Event (KBA1782)

Applicable Delphix Versions

Major Release	All Sub Releases
5.2	5.2.4.0

Issue

Delphix Virtualization Engine 5.2.4.0 introduced a new SQL Server feature to provide source continuity for dSources in the event that the source database underwent a restore event that changes the recovery fork GUID.  This GUID, when changed, indicates a different incarnation of the database and will cause the dSource staging database to get into an inconsistent state such that transaction log ingestion stops on the previous recovery fork GUID.

To determine such an occurrence, errors/faults received on the dSource will look like this:

 title          | The recovery fork GUID of the source database has changed from "AB89633C-F4CB-42DE-A29B-588F113FAAD1" to "8422A6AF-8B34-49E3-9A1A-30EB85928435"
 description    | Detected a change in recovery fork GUID for source database "vehflex".
 response       | Validated sync for the dSource has been disabled.
 action         | Perform a manual sync on the dSource using a full or differential database backup from the new recovery fork GUID in order to resume validated sync.

When this happens a manual synchronization on a Full or Differential backup of the source database is necessary to resume ingestion, in which case a new Timeflow is created and the validated sync will resume from that point forward.

In the new feature documentation there isn't a suggested sequence of activities to perform to utilize the new feature, nor does it occur automatically (the ingestion of backup). This article provides suggestion on how to find the Full/Differential backup closest to where the recovery fork change occurred.  This helps to keep the new incarnation consistent with this Full/Differential backup, ingesting transaction logs from this point onward (if you enable validated sync on transaction logs, same applies with validated sync on full/differential backups).

Prior to this feature, the dSource had to be unlinked, renamed so storage dependencies on VDBs would remain.  Then a new link of the dSource creates a whole new Timeflow.  This was a manual and time consuming process. Now, with source continuity, a manual ingestion is all that is required. Delphix will create the new Timeflow and persist the previous one until such a time as retention removes all dependencies. 

Be aware that storage consumption of this dSource will increase due to the addition of the new Timeflow.  When the older Timeflow has storage reclamation due to retention, that storage will naturally decrease.

Troubleshooting

The Delphix administrator just needs to be aware that when a Source Continuity scenario occurs, to inspect the message, taking notice of the recovery fork GUID so it can be used to determine a specific Full/Differential backup to ingest.  One can also just ingest most recent if they aren't concerned about how far back to go with the new backups.

Resolution

To demonstrate the issue, here is a test dSource with one snapshot from initial full backup and two recent transaction log ingestions via validated sync:

Now restore on the source database is performed - the state of the database prior to the changes made found in the two transaction logs. After the restore insert a row in the source database and then create a transaction log.

In my test, I restored the source using WITH REPLACE.  This changed recovery fork, creating another transaction log. After this I ran a Full backup and another recovery fork change.  In the faults for the dSource you'll see something like this:

At this point you might want to check the backupset history.  In this case, the Full backup I ran after this recovery fork change, also changed the recovery fork, from 17B46ED9-9835-476B-90F3-851BB1F2152E to C11EE8E1-5B6D-4A13-9FD6-F3872BB8FADF.

You can find this out running this query on the backupset history on the source SQL Server:

select backupset.database_name,
backupset.type,
backupset.last_recovery_fork_guid,
backupset.backup_set_uuid,
backupset.first_lsn,
backupset.last_lsn,
backupset.database_backup_lsn,
backupset.name,
backupset.backup_start_date,
backupset.backup_finish_date,
backupset.database_guid,
mediafamily.logical_device_name,mediafamily.physical_device_name,device_type
from msdb.dbo.backupmediafamily mediafamily join msdb.dbo.backupset backupset
on mediafamily.media_set_id = backupset.media_set_id
where backupset.database_name = 'chinook'
order by backupset.backup_finish_date desc;

Output (from SQL Server Management Studio, SSMS):

In this case, I see that the second record has a Full backup, with the newest recovery fork GUID. In a real production scenario, there might be a bunch of transaction logs preceding the new Full backup, with same recovery fork GUID. As you approach this list, and want to reduce the log gap, select the first Full/Differential backup when this GUID changed.

In this case, the Full backup (type = D) backup set UUID I want to use is C50C9398-4FA0-404D-A37D-5069D231A091.

To complete the task we can manually ingest this Full backup and it will create a new Timeflow and clear the fault.

Simply click the snapshot (camera) icon and select "Use a specific full or differential backup" and enter the backupset UUID you copied from the backup set query:

When completed the timeflow is marked where the source continuity reset point occurs:

The snapshots below the source continuity reset point occurs are the backups from the previous database incarnation on the older recovery fork GUID. Above that line are the new backups from the changed recovery fork GUID.

At this point, new transaction logs will be ingested via the validated sync workflow, linked to the new recovery fork GUID. Here another transaction log has been ingested on the new Timeflow:

At this point, validated sync continues to ingest transaction logs as they are created.

Related Articles

• New Features in Delphix Engine 5.2 shows when this feature was introduced
• KBA1044 How to Solve Issues with MSSQL Backups When Recovery Fork GUID Changes on the Source Database describes the process required for dealing with this issue in versions prior to 5.2.4.0
• KBA1390 How to reconnect a SQL Server dSource following a Mirroring failover describes continuity of dSource operations following failovers of SQL Server databases using High Availability technologies