Merge Replication between SQL Server 2000 and SQL Server CE

Introduction

This article will show how to create a replication environment between SQL Server 2000 and SQL Server CE.

It is intended to be used by developers and software configuration staff.

Background

The following article assumes you already understand the concept of replication.
There are tons of different environments where replication can be used and some others where replication must be used. Of course, you can always program your own replication engine, but what's the point of doing such a thing if you have SQL Server. She will do everything you need.
(yes, I think of SQL Server as a lady, you better threat her right!)

Let's get started

This article wil be separated in three sections. First of all, configuring your server. Then I'll go step by step with the publication wizard so you do get behind. And after that I'll give a simple code you can use to get databases synchronized.

Getting the server ready

I'll go real quick thru this point, this is just the software and configuration you have to do before even thinking of synchronizing.

Have IIS up and running on you server
Install SQL Server 2000 and it's SP4
Install SQL CE SQL2000 SP4 (I don't know if I need to say this but anyways, also on your server)
After installing SQL CE a config app starts up, DON'T TOUCH ANYTHING! (unless you know what you're doing of course, in which case, why are you reading this?). That config app will create a virtual directory from which your clients will connect to the server. just click thru it
Register the sscerp20.dll library (Start -> Run, regsvr32 fullpathtothelibrary)

All set!, you're ready to config SQL Server now. Before we do that try to access http://localhost/sql/sscesa20.dll, do you see anything? you should read "SQL Server CE Server Agent" on your browser.

Configuring the database for replication

There's a beautiful wizard that will help you thru. Do exactly what I say!
Open your SQL Server Enterprise Manager and right click on the Publications leave under Replication, and click on "New Publication"
See the wizard?, ok, on the first screen "READ IT!!!" and click Next.
Choose the database you want to replicate, next
Choose "Merge Replicaton", but please, read what it says... next
Select "Devices running SQL Server CE" for this case, next
Select the tables you whant to synchronize or just click on "Publish All", next
See a warning sign? that tells you that SQL Server will create a guid column in each table to be synchronized, unless you already have a guid column in your tables
Publication name, enter what ever you want... remember it!, next
Just let SQL Server create the publication as specified, next
READ THE WHOLE FORM!, and finish

Let's replicate'em

You have everything you need to replicate your data. Now I'll show you some .net 1.1 (C#) code to control your replication proccess.

private void getSyncReady()
{
    _repl = new SqlCeReplication();
    _repl.InternetUrl = "http://192.168.1.2/sql/sscesa20.dll"; //The URL to the server
    _repl.Publisher = "SQLSERVER"; //The name of the SQL Server
    _repl.PublisherLogin = "loginuser"; //The database user 
    _repl.PublisherPassword = "********"; //The database user's password
    _repl.PublisherSecurityMode = SecurityType.DBAuthentication;
    _repl.Publication = "PubName"; //Your publication name, remeber?
    _repl.PublisherDatabase = "Northwind"; //The database name
    _repl.SubscriberConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0;Data Source=" + strDBFile; //strDBFile contains the path to the database on the device
    _repl.Subscriber = "PPC"; //Some name
    
    if (!System.IO.File.Exists(strDBFile))
    {
        MessageBox.Show("La aplicacion requiere sincronización\r\nPor favor aguarde","Replicator",MessageBoxButtons.OK,MessageBoxIcon.Asterisk,MessageBoxDefaultButton.Button1);
        _repl.AddSubscription(AddOption.CreateDatabase);
        sync();
    }
}

The getSyncReady method runs when my "Smart Device Application" starts up Take a look at the File.Exists caluse. I'm using Replication to create the data base on the device the first time the application is ran.

private void sync()
{
    Cursor.Current = Cursors.WaitCursor;
        try
    {
        _repl.Synchronize();
        MessageBox.Show("Base sincronizada","Replicator",MessageBoxButtons.OK,MessageBoxIcon.Asterisk,MessageBoxDefaultButton.Button1);
    }
    finally
    {
        Cursor.Current = Cursors.Default;
    }
}

After that, all I have to do is calling the sync method whenever I want to synchronize my data. Cool uh?!

Points of Interest

What happened? I did everything you said and I got an error. Yeah! I know, sh*t happens and it happens a lot when you're experiencing with replication, it's gonna be frustrating at the beginig, check on your permissions, database users, their roles, the IIS user, ect.
But trust me, it works. Google your error messages or write me an email (and I'll Google'em for you ;-) )

Did you get it working?, is there anything I forgot? Please let me know, you can reach me at SebastianGomezCorrea[at]gmail.com