Change Tracking and Data Restore

Applications that require synchronization must consider the case in which a database that has change tracking enabled reverts to an earlier version of the data. This can occur after a database is restored from a backup, when there is a failover to an asynchronous database mirror, or when there is a failure when using log shipping. The following scenario illustrates the issue:

  1. Table T1 is change tracked, and the minimum valid version for table is 50.

  2. A client application synchronizes data at version 100 and obtains information about all changes between versions 50 and 100.

  3. Additional changes are made to table T1 after version 100.

  4. At version 120, there is a failure and the database administrator restores the database with data loss. After the restore operation, the table contains data up through version 70, and the minimum synchronized version is still 50.

    This means that the synchronized data store has data that no longer exists in the primary data store.

  5. T1 is updated many times. This brings the current version to 130.

  6. The client application synchronizes again and supplies a last-synchronized version of 100. The client validates this number successfully because 100 is greater than 50.

    The client obtains changes between version 100 and 130. At this point, the client is not aware that the changes between 70 and 100 are not the same as before. The data on the client and server are not synchronized.

Note that if the database was recovered to a point after version 100, there would be no problems with synchronization. The client and server would synchronize data correctly during the next synchronization interval.

Change tracking does not provide support for recovering from the loss of data. However, there are two options for detecting these types of synchronization issues:

  • Store a database version ID on the server, and update this value whenever a database is recovered or otherwise loses data. Each client application would store the ID, and each client would have to validate this ID when it synchronizes data. If data loss occurs, the IDs will not match and the clients would reinitialize. One drawback is if the data loss had not crossed the last synchronized boundary, the client might do unnecessary reinitialization.

  • When a client queries for changes, record the last synchronization version number for each client on the server. If there is a problem with the data, the last synchronized version numbers would not match. This indicates that a reinitialization is required.