SQL Server 2005 to SQL Server CE Merge Replication

Question

Hi,

In my organization, we have a SQL Server 2005 database server (DBServer). Users of an application will normally be connected to DBServer, but, occasionally, would like to disconnect and continue their work on a laptop using SQL Server Compact Edition 3.5 (SQLCE).

Due to this, we have been looking into using Merge Replication between the DBServer and SQLCE. From what I have read about this process, IIS must be installed on "the server"... yet, I have found no indication to whether this is talking about DBServer or SQLCE. I had assumed the documentation was referring to DBServer and proposed this to our networking staff.

That idea was quickly put to rest as it is not our policy to install IIS on an internal server. This is where our SQL Server 2005 web server (WebServer) entered the picture. The idea being that IIS would be installed on WebServer and would be the conduit for DBServer and SQLCE to communicate.

This sounded like a good idea at first, until I started looking for documentation on this type of setup. Everything I have been able deals with a DBServer --> SQLCE --> DBServer setup... nothing on DBServer --> WebServer --> SQLCE --> WebServer --> DBServer.

Questions:

1. Is going with a 3 server setup ideal?
2. Does anyone have documentation on this type of setup?
3. Does IIS even need to be running on one of the big servers, or can it just run off the laptop with SQLCE on it? (I'd really like this option ;))

Answer 1

Seems like this should be a ServerFault question.

Answer 2

You can setup just the SQLCompact Server Tools on a separate web server running IIS which will host the sql compact merge replication dll's. Then you don't have to open any direct web access to your SQL Server, but Sql compact will still be able to sync via web through the web server. Another note, the web server box hosting IIS will barely get any CPU usage and just some flat files written for the sync so it can be a pretty basic box.

All in all I would recommend looking into the Microsoft Sync Framework formerly ADO. Sync Services. It is a much more flexible solution allowing the developer to really customize every part of the syncronization process including filtering data, syncing directly between clients, conflict resolution, your choice of transfer protocol and better scaling ability than merge replication all without having to be locked into SQL Management studio and have the internals hidden from you. It was built by the same team that build replication for sql server.

If you go the replication route check out Rob Tiffany's book and his Microsoft Blog. "Enterprise Data Synchronization with Microsoft SQL Server 2008 and SQL Server Compact 3.5 Mobile Merge Replication".