SQL Server built-in Change Tracking vs Sync Framework's C#/VB libraries

Question

I went into a problem where using the following code did not change the scope_id value in the replica key map and scope_info table of Sync Framework 2.1 elements in my database, on a restored database on Windows XP 32-bit / SQL Server Express 2008 (x86). It DOES work on my other client that is Windows 7 64-bit / SQL Server Express 2008 (x86) :

SqlSyncStoreRestore databaseRestore = new SqlSyncStoreRestore(clientConn); databaseRestore.PerformPostRestoreFixup();

SUB QUESTION 1: It does not work but does not throw any exception either, which makes it much harder to troubleshoot. It is executed from a small console application (target x86) I built on my x64 computer (using Visual Studio x86). I wonder if the 32/64 bit thing is the culprit. Any clue?? But then, this made me wonder:

SUB QUESTION 2: "Is there an SQL query equivalent to using PerformPostRestoreFixup() of Sync Framework's C#?"

I am fairly new to Sync Framework and I do not yet entirely see the whole picture of synchronization of Microsoft SQL Server databases. It came to my attention that the SyncOrchestrator/SqlSyncProvider combo does not use built-in SQL Change Tracking stuff. Incredibly surprising to me! It now made me haunted by an even more tormenting question:

SUB QUESTION 3: Is the built-in SQL Server Change Tracking and Sync Framework two totally independent platform on which to build our synchronization scheme? I hope I am using the right words in English. In other words, is each of them completely unaware of the other??

Thanks in advance for your help! I am soooo lost LOL!

Kind Regards,

Zyxy

Answer 1

Question 1 - have you tried enabling sync fx tracing?

the Platform Target under your VS Project Properties->Build Tab should match the platform of Sync Fx installed. e.g., x86 Platform Target should have Sync Fx x86

if there is a mismatch on the x86/x64 thing, it would normally raise a COM exception.

Question 2 - you can run SQL Profiler to see what the PerformPostRestoreFixup. But i'd stick with the Framework doing this as there's some sync metadata processing involved.

Question 3 - SQL Change Tracking is just Change Tracking. you still need a synchronization app to use it.

SQL CT tracks what has changed and allows you query what has changed. it's not a sync platform by itself.

you need a sync app to query it just like Sync Fx. or you can hand code your own SQL queries to query for changes.

Sync Fx older SQLCeClientSyncProvider/DBServerSyncProvider/Sync Agent (the one used in the Local Database Cache Project Item) can use SQL CT.

Answer 2

Update: reason why it did not change the value but did not throw an exception either? The following is most likely the explanation. It does not completely answer the question as to why scope_id does not get updated, but it certainly provide additonal hints:

The PerformPostRestoreFixup() apply to all scopes in a database. I had another (valid) scope defined in the database that was successfully change its scope_id value. However, my 2nd scope (the one I could not change the scope_id) is stored in another schema (not the default .dbo). I guess it just did not see the scope, or deemed it invalid and therefore did not bother notifying me. I tried to deprovision that 2nd scope and got the following error:

---

Cannot find a valid scope with the name 'syncScope' in table '[scope_info]'. Ensure that this scope exists and that it has a corresponding valid configuration in the configuration table '[scope_config]'.

So these two issues might have the same root cause.

The 2nd scope does exist and I can go look at that darn scope_id with a select statement. Now how could I tell whether the configuration is valid or not? Why would it suddenly becomes invalid after a successful restore? I think it narrows it down but not enough for me hehe ;-) I will keep posting my findings and solution in case someone ever give a damn LOL! Just kidding but one day whow knows who it might help (or confuse) depending on the mood of the day I guess ;-) Cheers!

Answer 3

Ok, thanks to JuneT pointing me to Sync Fx 2.1 SDK's documentation (here or I read it on another discussion thread?), I realise I had a property I did not try yet. Reading online that the Sync Fx 2.0 (and others?) had issues with non-dbo schemas and issues with multi-scope database, I thought I would give it a try. Simply adding the following C# line in my console app solved it!

databaseRestore.ObjectSchema = "syncSchemaName";

Many of JuneT has pointed me, eventually, to some good readings and therefore I'll mark that one as the answer. MANY THANKS!!