Wednesday, April 2, 2014

Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

Time for a SQL Server 2014 related blog post to celebrate the release of this product. (And to celebrate me entering my 6th year a s Microsoft MVP).

SQL Server 2012

SQL Server Compact based Merge Replication is "supported" by SQL Server 2012, but there are a few "gotchas" that you need to be aware of before implementing this.

This blog post covers these in detail. As you may know, there are 3 components involved in a replication setup: Client, Web Server and Database Server. For each of these components, certain requirements must be met for the solution to work. And each component does not “just work” out of the box.

Client (Windows desktop/server/Mobile)

The client, which can be a Windows desktop or Windows Mobile device, must run a recent build of the SQL Server Compact runtime files. The file version/ServerVersion of the runtime must be 3.5.8088 or later. You can find download locations for the installers of the recent runtime here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

Web Server (IIS)

The SQL Server Compact agent (sqlcesa35.dll) must be version 3.5.8088 or later. You can find download locations for the installers of the most recent Server Agent here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

In addition if you have a separate web and database server, you must install the SQL Server 2012 Database Engine component (and most likely you want to disable the SQL Server service after install) in order to get the required SQL Server COM files installed. 

You must also install SP1 and the latest CU, as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

Database Server (SQL Server 2012)

You must install Service Pack 1 and the latest CU (Cumulative Update), as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/


SQL Server 2014

SQL Server 2014 does NOT support merge replication with SQL Server Compact: http://msdn.microsoft.com/en-us/library/bb500342(v=sql.120).aspx – end of story.

But I am working on a blog post about an alternative solution that “just works” and enables you to Synchronize from any device, including Windows desktop/server, WinRT, Windows Mobile, Windows Phone, Android, iOS, Mac OS X and Linux to SQL Server 2008 R2, 2012 and 2014 (even SQL Server Express). Stay tuned, and follow me on Twitter @ErikEJ and subscribe to my blog RSS feed.

7 comments:

misterman said...

Hi, the option to sync from SQL Server 2014 for any device sounds very promising. Do you have any insights, yet?

ErikEJ said...

Yes, look at the 2 blog posts following this one

Anonymous said...

Hi, thanks for the simple answer on SQL compact and SQL Server 2014. Not what I was hoping to find, but at least crystal clear.

Unknown said...

I've been reading your various posts for a couple days working through an endless stream of errors and roadblocks. Trying to set up merge replication to SQL Server Compact on Windows Server 2012 machines with SQL Server 2012. Separate IIS machine. Migrating from older servers and SQL Server 2008.

Sidebar: I'm pursuing zumero as an alternative, also an idea from you I got a while back, but the solution isn't ready in time for my server migration. :-(

Back to my issue:
Got the CU6 server tools to install after installing full db engine on the web server. Ran the wizard which set up virtual directory. All looks good, triple checked permissions and every other imaginable setting, but getting "404 - File or directory not found" when I try to pull up ssce/sqlcesa35.dll in a browser. After putting in credentials.

Do you have any advice? Thanks so much in advance..

ErikEJ said...

Erik: some related tips here: https://msdn.microsoft.com/en-us/library/ff714039.aspx and also able to provide help on a consultancy basis

Unknown said...

Thanks Erik! That article put me on the right track. I'd come across it previously and thought it was just for "regular" merge replication, not SSCE. In the end it seems like I had to have both the sqlcesa35.dll and replisapi.dll registered and set up with handlers in the same virtual directory.

Definitely a complicated and poorly documented setup. Appreciate the direction and offer for consulting! I was about an hour from paying you anything you wanted!

ErikEJ said...

Erik: you actually do not need the replisapi.dll to sync with SQL Server Compact, but it was more the other IIS advice I found useful.