SQL Server 2005 Merge Replication

I have spent the last hour or so gettng Merge Replication working on SQL Server 2005 - connecting to a PocketPC. It was working fine but I moved the SQL Server on to a different box and couldn't exactly remember how to set it back up.

Here are the steps I used to get it going.

1. Install SQL Server 2005
2. Restore the database into the new SQL Server Instance
3. Create a new login for the database
4. Install SQL Server Mobile 2005 tools – this installs the server tools for creating IIS virtual directory etc
5. Register the dll sqlcesa30.dll in the directory you have created for the IIS virtual dir – the wizard doesn’t do this
6. When you create the Publication – make sure it is for Merge Replication and the Snapshot is stored in the same folder as the folder created above. Make sure the user rights have rights to the SQL Agent and the database you want to merge
7. Create a snapshot – Open the replication monitor to see if has worked. If you are backing up a database from another server - you may have to revalidate the snapshot as well.
8. Yay - If has worked – if not the see troubleshooting below


C# Code to Sync Data (appologies for the formatting - I cant seem to work out how to get the tabs to stay once the code has posted)

public static void SyncData()
{
SqlCeReplication repl = null;

try
{
   // Set the Replication object.
   repl = new SqlCeReplication();

   repl.InternetUrl = "http://[computer-name]/[iisAlias]/sqlcesa30.dll";
   repl.Publisher = "[publisher name]";
   repl.PublisherDatabase = "[database name]";
   repl.PublisherLogin = "[login]";
   repl.PublisherPassword = "[password]";
   repl.Publication = "[pub name];
   repl.SubscriberConnectionString = "Data Source='\\[dbname].sdf'";
   repl.Subscriber = "[sub name]";

   // Create the Local SSCE Database subscription.
   if (!System.IO.File.Exists(@"\[dbname].sdf"))
   {
      repl.AddSubscription(AddOption.CreateDatabase);
   }
   // Synchronize to the instance of SQL Server 2005 to populate the Subscription.
   repl.Synchronize();
   MessageBox.Show("Data is Syncronized");
}

catch (SqlCeException ex)
{
// Use your own error handling routine to show error information.
MessageBox.Show(ex.Message);
}

finally
{
// Dispose of the Replication object.
repl.Dispose();
}
}

*** Change the [xxxxxx] to whatever the names are for you ****

Use the above code in your PDA app to synchronize to the publication. Change the InternetURL to be whatever the IIS name is (usually computer name) and the name of the virtual dir alias you made in step 4.

Login and Password are self explanatory. Subscriber conn str is where the database will be synced to.

TROUBLE SHOOTING

There are a number of errors that could occur.

Open the location of the IIS virtual dir in your web browser – it should say “Sql Server Agent 3.0”. If it doesn’t then the dll hasn’t been registered properly. Try re registering using regsvr32.

If there is a reconciling error, make sure all the usernames and passwords are correct and the snapshot is going into the folder that the IIS alias points too. Look at the Replication Monitor for error information. Usually you can work through the errors from there.

programming