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:
- 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.
- 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.
- 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.
- 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.
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 )