One of the most common questions asked about the SQL Azure Data Sync Service is if it actually supports SQL Express as an on-premise member in the Data Sync Service. If you look at the documentation, it simply mentions SQL Server 2005 SP3 and later and makes no mention of SQL Express.
As you probably know (or may be not), the SQL Azure Data Sync Service is actually built on top of Sync Framework. In Sync Framework, the data base provider SQLSyncProvider is actually the same provider used for synchronizing between SQL Express, SQL Server and SQL Azure. To synchronize SQL Server and SQL Azure databases, the SQL Azure Data Sync Service uses a slightly different version of the SQLSyncProvider.
So to answer the question if the SQL Azure Data Sync Service works with SQL Express, the answer is YES. I mentioned “works with SQL Express” instead of “supports SQL Express” because I think unless otherwise explicitly specified in the documentation that SQL Express is officially supported, you’re on your own. Better, you should contact MS to clarify if they’ll provide support for the service if you use SQL Express before you decide on using it.
Now that we know SQL Express works with the service, you’ll be glad to know (or disappointed if it’s not “supported”) that it also works with the new SQL Express 2012 variant called LocalDB (see: Introducing LocalDB, an improved SQL Express). I’ve also blogged about using Sync Framework with SQL Express 2012 LocalDB here : Sync Framework and SQL Express 2012 LocalDB. One of the advantages of this new variant is that it’s a SQL Express that gets started and shutdown on demand. And as I have described in my other post, it’s like a hybrid between SQL Compact and SQL Express.
To connect to a LocalDB instance, you use the new format “(localdb)\v11.0” instead of the regular servername or servername\instancename format. Make sure you get the .NET Framework Update that works with LocalDB, see: .NET Framework 4 support for LocalDB for more information on this update.
Here’s a screen shot registering a SQL Express 2012 LocalDB instance as an on-premise member in the Data Sync Service.
Here’s a screen shot to show it being added.
A screenshot of a LocalDB database after provisioning.
Screen shot of the LocalDB member as reflected in the Azure Portal
Screen shot of the LocalDB member in a Sync Group
So there you go, another on-premise member database option for SQL Azure Data Sync.