How can I store SQL Server Database Metadata for Sync Framework in a different database on the same server?

Question

I would like to be able to store the tracking tables in a different database the original. For a couple of reasons.

1. I would like to be able to drop it on demand if I change versions of my application.

2. I would like to have multiple sync scopes separated by user permissioning.

I am sure through the sqlmetadatastore class there is a way, but I have not found it yet.

Answer 1

the sqlmetaadatastore will not help you in any way with what you're trying to achieve. am pretty sure its not in anyway exposed in the database sync providers you're using.

note that the tracking tables are not the only objects Sync Framework provisioning creates, you will have triggers, tracking tables, stored procedures and user defined table types. and you're not supposed to be dropping them separately or even dropping them by yourself, but you should be using the deprovisioning API.

now if you really want to have the tracking tables on a separate db, the provisioning API has a Script method that can generate the SQL statements required to create the Sync Fx objects.

you can alter that to create the tracking tables on another DB, but you have to alter the triggers as well to insert on this other database.