SharePoint List and SQL Table 2 Way Synchronisation

This is a guide to show how you should go about configuring a 2 Way Synchronisation between a SQL Table and a SharePoint List with Data Synchronisation Studio.

Prerequisites

• Data Synchronisation Studio 3.0 You can download the 7 day trial here to try out this solution
• SQL Server Compact Edition 4.0 via Web Platform Installer

Background

To successfully implement a 2 way sync you need to use a key value that will always be unique even when the new row is created from the other side. The simplest way is to use a GUID and this is what we recommend. Data Sync supports a special column we created called DSID in SharePoint that when Data Sync see's this column and it's blank it writes a new Guid back to the Row so that it always remains unique.

Setup Data Sources

SQL Server Table

In this example were setting up a simple table that contains contact records and the important column in here is DSID which is configured as a uniqueidentifier and has a default of newid() so that new rows automatically get a new Guid value.

CREATE TABLE [dbo].[Contacts] (
    [ContactID] int IDENTITY(1, 1) NOT NULL,
    [FirstName] nvarchar(255) NULL,
    [LastName] nvarchar(255) NULL,
    [MiddleName] nvarchar(255) NULL,
    [Suffix] nvarchar(50) NULL,
    [EmailAddress] nvarchar(255) NULL,
    [Phone] nvarchar(255) NULL,
    [DSID] uniqueidentifier DEFAULT(newid()) NOT NULL
)
GO

ALTER TABLE [dbo].[Contacts] ADD CONSTRAINT [PK_Contacts_0B234716] PRIMARY KEY CLUSTERED ([ContactID] ASC) 

The table data will look like this below

SharePoint List

I have created a standard Contact List in a new SharePoint Site to be the Target for this Data. (This is SharePoint 2007 although you can use SharePoint 2010 as well).

This list does not have the column DSID yet so we need to add it and Data Sync has a special Wizard that lets you add new columns that are hidden from the user.

From the tools menu select the SharePoint Column Wizard

Step through the Wizard and Choose your SharePoint Server

Select the List on which to add the new Column

Add the New Column DSID to this List and make it hidden so the user cannot modify it.

Setup Data Sync Project

Start Data Sync and setup the project by connecting Data Source A to your SQL Table and Data Source B to the SharePoint List.

Your Project would look like this with SQL Table on the Left and the Target SharePoint List on the right.

The Schema Map would look like this with DSID as the Key and note that the SharePoint ID column is not included in this Map.

Now we should sync 1 way all our data into SharePoint by simply Compare A->B and Synchronise.

Sync the contact records into SharePoint

Run a Compare A->B again to check that all the contact records are in Sync with SharePoint.

And check SharePoint

Configure 2 Way Sync

Now to configure the 2 way sync we need to adjust the project properties, save the project and load it into the Run Tool.

Select File -> Properties

Change the Sync option to SyncAandB to enable 2 way synchronisation.

Then save the project.

Run Tool

Open the "Data Synchronisation Studio Run Tool" this is the program you use to configure unattended execution of Data Sync projects. With this you can create a Data Sync solution of many Data Sync projects and set the order of execution, send email and call other programs in the pipeline.

Under the File menu select Import and browse to your Data Sync project.

Save the Run Tool project and click Run in the Toolbar, if all is working you will get a nice green tick.

On the Log tab you can see the detailed log of event's, here you can see a new SQL Server Compact Database is created and loaded with data from each side and this is how it detects the changes made on each side.

The Snapshot Database is stored in the same path as the Run Tool solution project file like this.

Now if you run the project again you will see a shorter log and no changes.

Where as if you make a change in SharePoint you will see a change detected on B and applied to A like this.

And this is essentially all you need to do to create a 2 way sync between a SharePoint list and a SQL Table. The only remaining step is to configure a schedule to run the project regularly.