Merge Replication

Friday, July 19, 2002

Yeah! I finally got a SQL Server merge subscription working!

What a pain. The process is really picky, and when it has a problem, it just quits, instead of skipping the problem part. I'm using SQL Server 2000 on the server and MSDE 2000 on my laptop, and want to be able to have full edit access in both locations.

The only way I could get the subscription to work was to use Windows' Synchronize feature. After intalling MSDE 2000, SQL Server subscriptions are available along with Offline Files. Works pretty well:

  1. Enable the database for replication, first.
  2. Create a publication, allowing anonymous subscriptions
  3. Let the initial snapshot get created
  4. In the Synchronize box, add a new subscription
  5. Synchronize it!

Sounds simple, I know. I've been trying to get this working on and off for a few months. Probably because I do a few things out of the ordinary is why I've had problems. Here's what not to do:

  1. I could never get the server to push a subscription to my laptop. I think the way the firewall works, it was resolving the laptop's name to itself, so it didn't really get anywhere. Probably could have gone farther with this.
  2. I couldn't get the laptop to subscribe to the server through the local Enterprise Manager. If I impersonated the Service Manager, it would get an error that it couldn't generate the SSPI context (or something like that). If I entered a different account to use, it didn't work either. I tried lots of combinations with this.
  3. The publication will not replicate anything that requires SQL Server-only features, like full-text indexing. I have a couple Stored Procedures that used the FREETEXT and CONTAINS predicates, and the subscription process screeched to a halt when it found those.
  4. Get the outside stuff, too. I referenced a couple tables in another database, which of course were not on the laptop yet, and this also caused the subscription process to fail. C'mon - tell me about it, continue, and let me add them afterward! So, I syncronized them down, and restarted the main process yet again.
  5. I couldn't add/remove articles from a publication after it was setup. That seems odd, and there must be a better way to do that other than deleting and recreating the publication.

I do have to say, that when the errors did come up in the subscription setup, it was very clear what the problem was! I didn't have to spend time looking up stupid cryptic error messages, just go reconfigure to skip the offending item. It's fortunate that I didn't need any of those items, either; the full-text related code is now all in my web pages, where I have a lot more flexibility in dynamically building the statements for searches (you ought to see how complicated those get! I'lll write an article on how I do that, sometime), but had done some tests in the database. See, if I'd been better with my housekeeping, they wouldn't have been a problem in the first place!

3 Comments