An alternative to Merge Replication with SQL Server and SQL Server Compact – Zumero for SQL Server

While looking for a migration path for a customer currently using Merge Replication with SQL Server and Windows Mobile, I came across the recently released solution from Zumero, Zumero for SQL Server. As mentioned in my previous blog post, Merge Replication between SQL Server Compact and SQL Server 2014 is no longer possible, and in addition, SQL Server Compact 3.5 SP2 only supports a limited number of client platforms (Windows Mobile/CE and Windows desktop). Microsoft is promoting Azure Mobile Services with Offline support, but for obvious reasons, this solution does not work for on premise databases.

Zumero for SQL Server enables you to synchronize any mobile device with tables on a central SQL Server, using essentially the same components that we know from Merge Replication:

1: Configuration of tables to be synchronized, and added metadata/tracking to those. Before: Using SQL Server Management Studio to create a Merge Publication with articles (tables)
Now: Using ZSS Manager to create a DBFile with Tables

2: An IIS based agent, that exposes a http(s) endpoint for performing the sync process.
Before: Configure Web Synchronization Wizard
Now: ZSS Server

3: Client library for performing sync.
Before: System.Data.SqlServerCe.SqlCeReplication class, part of the SQL Server Compact ADO.NET provider
Now: Zumero Client SDK and SQLite.

using Zumero;

ZumeroClient.Sync(
"/path/to/sqlite.db", // full path to local db file
null,
"http://MY_ZUMERO_SERVER:8080",
"test", // remote DBFile name
null,
null,
null);

To get started testing out Zumero, simply follow the instructions here: http://zumero.com/get-started/ or start by watching the short, introductory videos here: http://zumero.com/howto/

Notice that Zumero works with any edition of SQL Server 2008 R2 Express or higher/later. Zumero Server is not free, but works with the free SQL Server Express for small scale solutions.

On the client side, the following impressive list client Operating Systems are supported:

Android (native and PhoneGap)
iOS (native and PhoneGap)
Xamarin
Windows, WinRT and Windows Phone 8
Mac OS X
Linux (Ubuntu 12.04+, Fedora 17+)

In my next blog post, I will be doing an interview:  “Hard Talk” with Eric Sink, Zumero founder.

Disclaimer: I am simply a Zumero user, and not affiliated in any way.

SQL Server Compact Toolbox 3.0–Visual Guide of new features

After more than 110.000 downloads, version 3.0 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 2010 is now available for download. This blog post is a visual guide to the new features included in this release, many suggested by users of the tool via the CodePlex issue tracker

Extensive support for Sync Framework 2.1

Thanks to a fantastic effort from fellow MVP June Tabadero (blog | twitter), extensive support for Sync Framework 2.1 has been added to the Toolbox, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration. You can read a nice walkthrough of the features in June’s blog post here. Notice that you will need to install the Sync Framework 2.1 bits for any of these features to work, you can download from here. June also recently blogged about using SQL Server Compact 4.0 with Sync Framework here.

clip_image002

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases

I  decided to add the option to generate LINQ to SQL desktop classes, as I recently discovered that you can actually use LINQ to SQL with SQL Server Compact 4.0 (though NOT supported in any way by Microsoft). Read the blog post before you start using LINQ to SQL with 4.0.

clip_image003

clip_image005

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)

As you may know, the Toolbox already has features that allow you to generate a script of a SQL Server Compact database, and run it against a SQL Server database. But this release includes a feature to simplify this process, by not only generating a script, but also immediately executing it against a SQL Server database connected via Server Explorer.

clip_image006

clip_image007

Script only data (with “correct” table ordering)

Due to the increasing number of Database context menu items, I have moved all the script options to a separate “Script Database” sub-menu:

clip_image009

I have also added “Script Database Data”, which scripts only the data (!), sorted correctly by using the QuickGraph topological sorting of a DataSet.

WP DataContext – option to include ConnectionStringBuilder class

I have added the option to also have a ConnectionStringBuilder class generated for Windows Phone, to help constructing valid Windows Phone connection strings, with the limited amount of advanced options available on Windows Phone.

Other improvements

Scripting API fixes:
Tables are now ordered by topological sort when scripting entire database.
Data scripting now uses DbDataReader (or speed and to avoid some OOM issues)
“date” and “datetime2” SQL Server values are converted to “datetime” by default.
SQL scripts with DGML no longer generated.
Server based DGML now includes schema
Duplicate Execution plans fixed.
Improved script execution

Preview of SQL Server Compact Toolbox version 3.0 now available

This short blog post lists the main new features in version 3, with pointers to the menu location of the new features in the upcoming version 3.0 of my SQL Server Compact Toolbox add-in for Visual Studio. Please go ahead and download the preview, and let us know what you think.

New features

Extensive support for Sync Framework 2.1, including Provisioning, Deprovisioning, Code Generation, Local Database Cache Code Generation and Explorer tree integration – thanks to great effort from fellow MVP JuneT (blog | twitter)

image
(Root context menu)

image
(Database context menu)

Generate desktop LINQ to SQL classes both for 3.5 and 4.0 databases (see this blog post for more info)

image
(Database context menu)

Migrate a SQL Server Compact database directly to SQL Server (LocalDB/Express)

image
(Database context menu)

Script only data (with “correct” table ordering, using QuickGraph DataSetGraph with Topological Sort)
image
(Database context menu)

Add own Compact 3.5 based connections when using Visual Studio 11 beta

WP DataContext – option to include a ConnectionStringBuilder class

image

Go and try it out, and let us know what you think!

Meet me at TechEd Europe in Berlin next week

I am attending TechEd from 8-12 November, and looking forward to learning new things about SQL Server Compact, Sync Framework, Entity Framework, OData, WebMatrix, Visual Studio SP1 etc. (all technologies that are related to SQL Server Compact).

I will be present in the Technical Learning Centre, SQL Server Data Technologies & Developer Tools booth during the event, so feel free to come and have a chat about some of the technologies and tools, that the SQL Server Data Programmability team provides.

See you in Berlin!