Sync Framework and SQL Express 2012 LocalDB


With the upcoming release  of SQL Server 2012 comes a new variant of SQL Express called LocalDB, a feature that is least talked about compared to all the other new features in the SQL 2012 release.

I won’t go in deep detail  to explain what LocalDB is,  suffice to say, I like to think of it as something in between SQL Compact and SQL Express, a hybrid if you may, combining the ease-of-use of SQL Compact and the power of SQL Express. A SQL Express service that gets started and stopped as needed.

To quote from the SQL Express Weblog:

At a very high level, LocalDB has the following key properties:

  1. LocalDB uses the same sqlservr.exe as the regular SQL Express and other editions of SQL Server. The application is using the same client-side providers (ADO.NET, ODBC, PDO and others) to connect to it and operates on data using the same T-SQL language as provided by SQL Express.
  2. LocalDB is installed once on a machine (per major SQL Server version). Multiple applications can start multiple LocalDB processes, but they are all started from the same sqlservr.exe executable file from the same disk location.
  3. LocalDB doesn’t create any database services; LocalDB processes are started and stopped automatically when needed. The application is just connecting to “Data Source=(localdb)\v11.0″ and LocalDB process is started as a child process of the application. A few minutes after the last connection to this process is closed the process shuts down.
  4. LocalDB connections support AttachDbFileName property, which allows developers to specify a database file location. LocalDB will attach the specified database file and the connection will be made to it.

For more information on SQL Express Local DB, see: Introducing LocalDB, an improved SQL Express

I have previously tested if Sync Framework and the built-in SQLSyncProvider will work against LocalDB and found there’s nothing special that needs to be configured in Sync Framework to make it work. As you would have noticed above, to establish a connection to LocalDB, it requires a new format in the form of “(localdb)\v11.0”. instead of the usual server name or servername\instance name format. Prior to the release of the NET Framework 4 support for LocalDB, I was only able to test Sync Framework using a named pipe connection. Now that the NET Framework 4 support for LocalDB has been released, you can now use the “(localdb)\v11.0” format in connection strings.

Here’s a simple application using the “(localdb)\v11.0. and “(localdb)\v11.0. + AttachDBFileName formats in the connection string.

Code Snippet
  1. using System;
  2. using System.Data.SqlClient;
  3. using Microsoft.Synchronization;
  4. using Microsoft.Synchronization.Data;
  5. using Microsoft.Synchronization.Data.SqlServer;
  6. namespace LocalDBSync
  7. {
  8.     class Program
  9.     {
  10.         static void Main()
  11.         {
  12.             //setup scope name
  13.             const string scopeName = “LocalDBScope”;
  14.             //setup the connections
  15.             var serverConn = new SqlConnection(@”Data Source=(localdb)\v11.0; Initial Catalog=SyncFxLocalDBServer; Integrated Security=True”);
  16.             var clientConn = new SqlConnection(@”Data Source=(localdb)\v11.0; AttachDbFileName=Z:\JuneT\SyncFxLocalDBClient.mdf; Integrated Security=True”);
  17.             //provision server
  18.             var serverProvision = new SqlSyncScopeProvisioning(serverConn);
  19.             if (!serverProvision.ScopeExists(scopeName))
  20.             {
  21.                 var serverScopeDesc = new DbSyncScopeDescription(scopeName);
  22.                 // add table
  23.                 var serverTableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable(“SampleTable”, serverConn);
  24.                 serverScopeDesc.Tables.Add(serverTableDesc);
  25.                 serverProvision.PopulateFromScopeDescription(serverScopeDesc);
  26.                 //apply the scope definition
  27.                 serverProvision.Apply();
  28.             }
  29.             //provision client
  30.             var clientProvision = new SqlSyncScopeProvisioning(clientConn);
  31.             if (!clientProvision.ScopeExists(scopeName))
  32.             {
  33.                 //get scope description from server
  34.                 var scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope(scopeName, serverConn);
  35.                 clientProvision.PopulateFromScopeDescription(scopeDesc);
  36.                 //apply the scope definition
  37.                 clientProvision.Apply();
  38.             }
  39.             // create the sync orchestrator
  40.             var syncOrchestrator = new SyncOrchestrator();
  41.             //setup providers
  42.             var localProvider = new SqlSyncProvider(scopeName, clientConn);
  43.             var remoteProvider = new SqlSyncProvider(scopeName, serverConn);
  44.             syncOrchestrator.LocalProvider = localProvider;
  45.             syncOrchestrator.RemoteProvider = remoteProvider;
  46.             // set sync direction
  47.             syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;
  48.             // execute the synchronization process
  49.             var syncStats = syncOrchestrator.Synchronize();
  50.             // print statistics
  51.             Console.WriteLine(“Start Time: “ + syncStats.SyncStartTime);
  52.             Console.WriteLine(“Total Changes Uploaded: “ + syncStats.UploadChangesTotal);
  53.             Console.WriteLine(“Total Changes Uploaded and Applied: “ + syncStats.UploadChangesApplied);
  54.             Console.WriteLine(“Total Changes Uploaded and Failed: “ + syncStats.UploadChangesFailed);
  55.             Console.WriteLine(“Total Changes Downloaded: “ + syncStats.DownloadChangesTotal);
  56.             Console.WriteLine(“Total Changes Downloaded and Applied: “ + syncStats.DownloadChangesApplied);
  57.             Console.WriteLine(“Total Changes Downloaded and Failed: “ + syncStats.DownloadChangesFailed);
  58.             Console.WriteLine(“Complete Time: “ + syncStats.SyncEndTime);
  59.             Console.WriteLine(String.Empty);
  60.             Console.ReadKey();
  61.         }
  62.     }
  63. }

As you probably noticed, there is nothing to be else that needs to be configured or tweaked in Sync Framework to make it work. The only difference is in the connection strings.

With the release of SQL Server 2012, SQL Express LocalDB is a welcome alternative to SQL Compact for synchronization, especially since SQL Compact 4.0 doesn’t support SQL replication  nor Sync Framework synchronization (see:Microsoft SQL Server Compact 4.0 is available for download )

About these ads

4 comments

    1. you mean cool? :)

  1. [...] 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 [...]

  2. [...] fact, both service also works with the new SQL variant called SQL Express LocalDB (see: Sync Framework and SQL Express 2012 LocalDB  and SQL Azure Data Sync Service, SQL Express and SQL Express 2012 LocalDB). So you can [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 189 other followers

%d bloggers like this: