Migrating Sync to SQL Server

The first phase of the Data Port Sync migration to SQL Server (2000, MSDE, 2005 and Express) is over with the port of the Jet preparation code. It is quite interesting to see how Microsoft implements the "same" features in all of these databases. With Jet, one can do everything with OLE DB whereas with SQL Server one must resort to T-SQL to get some of the schema information. As I found out when I was developing Data Port Wizard, SQL Server does not allow you to mix direct table access (inserts) with IDENTITY_INSERT: you have to use an SQL INSERT command. Also, Jet and SQL vary wildly in how they manage IDENTITY columns: Jet makes it a piece of cake, SQL is a bit harder especially when you have to know the current seed. Finally, I found out that there is no way to create the equivalent of a ROWGUIDCOL column in Access through a SQL CREATE command - you do have to go through OLE DB.

Having said all this, I am not sure that this will be the final preparation code for SQL Server. As a matter of fact, with a more advanced database engine I will ba able to use triggers to flag any changes to the tracked tables (or so I hope...).