Backing Up and Restoring a Server Database (Synchronization Services)

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

• Changes that were made in the server database after the restore might not be propagated to clients. This is because of the anchor values that are used when changes are selected from the server database.
  During synchronization, Microsoft Synchronization Services for ADO.NET 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.
• Client databases that are involved in upload-only and bidirectional synchronization might have changes that the newly restored server database does not have.

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 Synchronization 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 timestamp 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.

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

• Bring the server timestamp 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.
• 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. Synchronization 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 Synchronization 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.
• 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 (Synchronization Services)