I recently had the opportunity to take a look at Data Synchronisation Studio 3.0, an application developed and marketed by Simego Limited in the UK. I was interested to see how it might be used to transfer data from or to SharePoint lists as I’ve noticed a fair amount of hits and comments on previous blog posts I’ve written which touch on this subject, like this one on using Integration Services to get SharePoint list data into a database.
Let’s consider first where Data Sync Studio (I’ll shorten the name from now on) is positioned, and how it might help with data synchronisation requirements. If you are working with Microsoft SQL Server you will know that one of its very useful components is Integration Services, an excellent tool for moving high volumes of data between applications. If you are populating a data warehouse, loading database tables with data from an external source, cleaning, merging or de-duplicating data and you are a SQL Server customer, you need to get familiar with Integration Services. But of course not everyone needing to move data around has access to Integration Services and even if they do, it may be the case that the data providers they need to work with aren’t catered for, either officially or unofficially. I’ll look at two of those data providers here, namely SharePoint lists, and Active Directory Users, both available in Data Sync Studio. You’ll see though at the bottom of this page there are many more, including the usual Microsoft database formats, Microsoft Exchange and CRM as well as Oracle, MySQL and OData.
Installing the Application
Data Sync Studio is a standalone .Net application which comes in 32-bit and 64-bit versions. I’ve been trying it out on a Windows Server 2008 R2 virtual Machine, but you could just as easily install on a modern Windows desktop PC running Windows 7, Vista or even XP. It is licensed per developer rather than per server. Once you create a synchronisation project, if it is something which needs to be run on a scheduled basis then you will need to have a licenced copy of the application installed on the machine (desktop or server) which runs the scheduled job.
At the time of writing (May 2011) a single user licence of Data Synchronisation Studio currently costs 450 GBP / 720 USD. More information on pricing and licensing options are shown on the product home page.
Importing or Exporting SharePoint Data
Data Sync Studio allows you to use a SharePoint list as either a data source or destination and you can synchronise data in both directions. This means that you can fairly easily connect your list to a SQL Server table and keep the two in sync. Of course you should first be sure this is the best approach to solving your business problem. There are other methods to consider here, not least of all Business Connectivity Services, but BCS is definitely not the answer for everyone as it’s quite difficult to get right and again will need other tools to set up correctly – at the very least access to SharePoint Designer 2010. Also I’ve often come across situations where data needs to be captured in SharePoint then later moved to an external database such as SQL Server for additional analysis, reporting or even archiving.
I took the example I used in my article on using Integration Services with SharePoint data. I created the same list in SharePoint 2010 and a destination table in SQL Server 2008 R2. In Data Sync Studio the SharePoint list became Data Source A and the SQL Server table Data Destination B:
First, I moved all of the current SharePoint list items to SQL Server. This is easily accomplished using the “Compare A>B” button, which gives me a summary of the differences between source and destination. I can then use the “Synchronise” button to push these changes to SQL Server. Updates, additions and even deletions from the SharePoint list can also be handled and sync’d to the SQL Server destination:
And if you want, the sync can be bi-directional – you can specify which synchronisation options are used by setting properties in the source and destination:
Data Sync Studio also includes a “Dynamic Columns” feature which lets you create custom code to express columns. These can range from fairly simple examples such as concatenating multiple columns (for example first and last name) or performing lookup or validation tasks. One example provided is a “SharePoint Lookup Helper”. You would use this if you were importing data into a SharePoint list and during import you needed to match a source value to the ID of an item in a separate lookup list. Note that this facility is available via a C# programming interface so definitely requires .Net expertise. You can read more about this here.
I also tried using the SharePoint data provider with Office 365 but couldn’t get this to work as it wouldn’t connect. Simego tell me they are actively looking at this and I think it would be useful. I can imagine for example a scenario where external users submit data to an Office 365 application and the data is sync’d with a corporate application or reporting system.
Working with Active Directory Data
Data Sync Studio includes LDAP Active Directory Users as a data provider which can be used to import this data into a database or SharePoint list:
I tried this out, loading specific columns of the AD user list into a SharePoint list. I could certainly see some uses for this. Simego say they already have clients using this to populate a staff or team directory in SharePoint. Also, although the Active Directory User data provider doesn’t allow inserts or deletes (a good thing) it does allow you to update configured columns. This could be useful – it’s often the case that when an organisation implements SharePoint they want to get best value from searchable information in Active Directory, but fields related to job title, department, contact phone numbers etc aren’t populated. This could provide a nominated user (maybe a member of the HR department) with a way of updating the fields in a list exported to SharePoint and then syncing the updated fields back to Active Directory.
It’s probably worth noting that bi-directional synchronisation is also possible with the SharePoint 2010 User Profile service, but again, this will present you with lots of configuration challenges and is definitely not for the faint-hearted. Data Sync Studio may be a viable alternative here.
Creating a Project Schedule
Data Sync Studio lets you create a schedule for your synchronisation project using a dialog within the Studio itself:
Once you have selected dates and times to schedule a run and entered login credentials, a Windows scheduled task is created automatically for you. A separate “Data Synchronisation Run Tool” is also provided within which you can schedule multiple projects to run in sequence and it’s also possible to execute a synchronisation project from the command line.
Data Synchronisation Studio is well worth a look if you have a need to move data between line of business applications and your requirement can’t be addressed by SQL Server Integration Services, either because you aren’t able or allowed use it or because the appropriate data providers aren’t available. Certainly it won’t take the place of Integration Services when you are moving very high volumes of data to or from SQL Server and Simego don’t sell it as a replacement. The fact that several of the data providers included currently aren’t in Integration Services could mean it pays its way quite quickly (as well as the two mentioned above I could also see it being valuable for Exchange data and for Microsoft CRM for example). It’s also possible to create your own data providers and make these available as add-ins. Simego provide some example add-in providers showing how this might be done, via a C# project.
You should be aware though that this isn’t an end-user tool. It’s really aimed at developers with some coding experience, someone happier with developer tools such as Visual Studio, Integration Services or Business Intelligence Developer Studio. Also, although the application comes with a help file and lots of online screenshots, it did take me a little while to understand the user interface and perhaps one or two additional “walk-through” sessions in the help file would be useful. Having said that, you might also find what you are looking for in these blog posts.
Overall then my evaluation experience was a positive one. It’s also good to see a home-grown (UK) development company coming up with a tool like this (and even spelling synchronisation without a “z”!).