Program With Dave

I have a project I’ve worked on recently which involved several database.

1. Database 1 is the “master” database to which a web interface interacts.
2. Database 2 is an “intermediate” database, which is set up for merge replication, to publish down to handheld clients running Windows Mobile.
3. Database 3 is the database on the handhelds which subscribe to database 2.

Data can be entered in the web interface (and therefore directly to database 1) or it can be entered on the handheld, and replicated up to database 2. The handhelds can’t tie directle to database 1, because some transformations have to be applied.

To tie data back and forth between database 1 and 2, I have an SSIS package which runs every half hour to do the following:

1. The package imports new data from the intermediate database.
2. The package pushes any changes down from database 1 to database 2, to be replicated out to the handhelds.

Here is the problem I encountered. Any new record I put into the web interface would not make it down to the mobile handhelds, even after the SSIS package put the data into database 2 and the handheld synchronized. I would have to re-create the snapshot and then re-subscribe the handheld in order to see new records. That just won’t work!

After pulling my hair our for about an hour trying to figure out the problem, and a lot of web searching, I finally came across this amazing page about SQL replication which solved the problem immediately.

When populating a merge-replication published database using SSIS, DO NOT USE “FAST LOAD”. Fast load does not run the triggers on the tables which are being replicated, and those triggers have to run in order for replication to do its work. I just changed it from using “Fast Load” to using the normal method, and my replication worked like a charm!