Tracking tables in SQL CE Provisioned database. Why? RRS feed

  • Question

  • First let me say that the work done on SDK2 is great and I'm seeing huge improvements in speed - plus the introduction of the 'Scope' provisioning has made the creation of the sync process much simpler.

    To my question: Referencing the SDK 2 release and the example outlined here: http://msdn.microsoft.com/en-us/library/dd918848%28SQL.105%29.aspx - everything works - it runs and does everything it says it should...

    We find that we get our SQL Server database with tracking tables, sp's and triggers created, that's all great, but we also find that the 2 SQLCE databases provisioned both contain tracking tables as well.

    I can't work out why they are included and what purpose they serve.  My tests so far when adding and changing data in the SQLCE databases show that the tracking tables are not effected by anything (which is what I expected, as there are no triggers/sp's to write data to them or update them.)  And we still see the __sysCHangeTxBsn and __sysInsertTxBsn columns added to the base tables which appear to be used to determine what has been updated/inserted etc.

    Could someone point me at the docs or explain why these tracking tables in SQLCE are required?  And if they are not, then is there a way to prevent them being created when provisioning a new CE database?

    Monday, January 11, 2010 10:16 PM


  • Glen,

    These providers are capable of synchronizing in a peer-to-peer fashion, and the tracking tables are required for storing the extra metadata required for these scenarios.  Changes that are made locally to the CE database will not affect the metadata in the tracking table, however items that are received through sync will have their metadata in the tracking table updated to reflect the correct peer-to-peer version information.  Due to this, they are required and must be created.

    Wednesday, January 13, 2010 12:25 AM