Backing Up and Restoring a Database (Sync Services)

  • Article

As with any production database, a database that is involved in synchronization should be backed up regularly. If you have to restore a database from a backup, there are two main considerations:

  • Changes that were made in the database after the restore might not have been propagated to clients or other peers. This is because of the timestamp values that are used when changes are selected from a database.

    For example, during client and server synchronization, Sync Services retrieves a new anchor value from the server database and stores it in the client database. This value is used as the upper bound for the current set of changes that are being synchronized. For more information, see Tracking Changes in the Server Database. After the server database is restored, the value that is stored in the client database might be logically ahead of the value that is returned by the server database.

  • For upload and bidirectional scenarios, clients or other peers might have changes that the newly restored database does not have.

The examples in this topic focus on client and server synchronization, but similar principles apply to peer-to-peer synchronization. The server database can be thought of as the remote peer and the client database as the local peer.

Server Database

To understand how the client database can be logically ahead of the server database, consider how updates are tracked in the Sales.Customer table in the Sync Services sample database. The UpdateTimestamp column stores a timestamp value, and the new anchor command returns a value from the SQL Server MIN_ACTIVE_ROWVERSION function. For clarity, integers are used instead of hexadecimal values in the example:

  1. Before the database is restored, MIN_ACTIVE_ROWVERSION returns a value of 31. This value was stored in the client database as the last received anchor.

  2. After the database is restored, MIN_ACTIVE_ROWVERSION returns the value of 19.

  3. Updates are made so that the timestamp value in the UpdateTimestamp column reaches 32.

  4. Synchronization occurs, and MIN_ACTIVE_ROWVERSION returns the value of 32. The final update to Sales.Customer is downloaded because 32 is greater than the last received anchor value of 31. The updates between 19 and 31 are not recognized as changes.

Any tracking scheme that uses a logical clock such as a time stamp is susceptible to this problem of unrecognized changes. Tracking schemes that use a date-and-time–based data type are not susceptible because the wall clock moves forward independently from the state of the database. In peer-to-peer synchronization, a time stamp is required for change tracking.

To handle the problem presented by the time stamp, use one of the following methods:

  • Bring the server time stamp up to the point it was at before the restore operation. In the earlier example, you could perform dummy updates on a separate table until MIN_ACTIVE_ROWVERSION returns 31.

    This is the recommended approach for peer-to-peer synchronization.

  • Store the anchor for each client on the server. In the earlier example, the last-received anchor from the backup would be 19. Therefore, subsequent updates would be recognized, and all changes between 19 and 32 would be downloaded. Sync Services does not provide built-in support for this, but you could create a system on the server in the following way:

    1. Create a table in the server database that has a row for each client. The table would include a column with the ID that Sync Services generates for each client, and a column with the anchor for that client. During synchronization, the application would update this column with a new anchor value.

    2. Change synchronization commands to select the lowest anchor value for the client that is synchronizing. This value could be the value that is stored in the client database or the value that is stored in the server database. After a database-restore operation, the value in the server database should be lower. If there is a failure after you write a value to the server table but before changes are applied to the client, the value in the client database should be lower. If synchronization is occurring as expected, the values should be equal. An insert command might be written as follows:

      SELECT CustomerId, CustomerName, SalesPerson, CustomerType
FROM Sales.Customer
WHERE (InsertTimestamp > (SELECT AnchorValue from ServerAnchorTable WHERE ClientId = @sync_client_id)
OR InsertTimestamp > @sync_last_received_anchor) AND InsertTimestamp <= @sync_new_received_anchor
AND InsertId <> @sync_client_id

Client Databases

After the server database is restored to the present point in time with regard to synchronization metadata, the database could still be missing changes that were made at clients since the server backup was taken. For a client to respond appropriately, the client must know that the server database was restored. You could use a server-side tracking table that indicates whether a restore has occurred since the last time a particular client synchronized. During each synchronization session, the client application would first check this table to determine whether it can synchronize normally or should execute special code to work with the restored database.

After a client application recognizes that the server database has been restored, the application can bring the client and server databases into convergence. There are several ways to accomplish this, including the following approaches:

  • Reinitialize the client database by dropping all tables and then synchronizing with the server. This is the easiest approach, but any changes at the client are lost.

    Reinitialization is the recommended approach for peer-to-peer synchronization. For information about initializing peer databases, see "Initializing Each Database with Schema and Change Tracking Infrastructure" in How to: Configure Change Tracking and Synchronize Peers.

  • Reinitialize the client database after you create a copy of the client tables. An application could use steps like the following:

    1. Identify that the server database has been restored.

    2. Make a copy of all tables in the client database, and then drop the original tables.

    3. Synchronize with the server to download new schema and data.

    4. Compare the rows in the new tables to the copies that were made.

    5. Identify any differences between the two sets of tables, and apply to the new tables any changes that are required.

    6. Synchronize again to upload changes that were applied to the new tables.

See Also

Concepts

Considerations for Application Design and Deployment (Sync Services)