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.

Merge Replication with SQL Server Compact 3.5 SP2 and SQL Server 2014 and 2012

Time for a SQL Server 2014 related blog post to celebrate the release of this product. (And to celebrate me entering my 6th year a s Microsoft MVP).

SQL Server 2012

SQL Server Compact based Merge Replication is “supported” by SQL Server 2012, but there are a few “gotchas” that you need to be aware of before implementing this.

This blog post covers these in detail. As you may know, there are 3 components involved in a replication setup: Client, Web Server and Database Server. For each of these components, certain requirements must be met for the solution to work. And each component does not “just work” out of the box.

Client (Windows desktop/server/Mobile)

The client, which can be a Windows desktop or Windows Mobile device, must run a recent build of the SQL Server Compact runtime files. The file version/ServerVersion of the runtime must be 3.5.8088 or later. You can find download locations for the installers of the recent runtime here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

Web Server (IIS)

The SQL Server Compact agent (sqlcesa35.dll) must be version 3.5.8088 or later. You can find download locations for the installers of the most recent Server Agent here: http://erikej.blogspot.dk/2010/08/sql-server-compact-35-sp2-downloadable.html

In addition if you have a separate web and database server, you must install the SQL Server 2012 Database Engine component (and most likely you want to disable the SQL Server service after install) in order to get the required SQL Server COM files installed. 

You must also install SP1 and the latest CU, as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

Database Server (SQL Server 2012)

You must install Service Pack 1 and the latest CU (Cumulative Update), as a minimum CU5. You can find information about the CUs here: http://sqlserverbuilds.blogspot.dk/

SQL Server 2014

SQL Server 2014 does NOT support merge replication with SQL Server Compact: http://msdn.microsoft.com/en-us/library/bb500342(v=sql.120).aspx – end of story.

But I am working on a blog post about an alternative solution that “just works” and enables you to Synchronize from any device, including Windows desktop/server, WinRT, Windows Mobile, Windows Phone, Android, iOS, Mac OS X and Linux to SQL Server 2008 R2, 2012 and 2014 (even SQL Server Express). Stay tuned, and follow me on Twitter @ErikEJ and subscribe to my blog RSS feed.

SQL Server Compact Toolbox 3.7.1–Visual Guide of new features

After 250.000 (!) downloads, version 3.7.1 of my SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 2010 is now available for download (and available via Tools/Extensions in Visual Studio). This blog post is a visual guide to the few new features included in this minor release, which all centre around Server Explorer (DDEX) , including support for Visual Studio 2012 and 2013.

 

Simple DDEX providers for Visual Studio 2012 and 2013

image

I already blogged about this feature here, it has been extended to also support SQL Compact 3.5. Follow these steps to use the providers with Entity Framework Tools:

– Install this version of the toolbox and launch Visual Studio 2012/2013.
– Open the Toolbox (this will silently run the “installation” of the DDEX provider if required).
Restart VS 2012/2013.
– Add new Toolbox connection to a new or existing SQL Server Compact 4.0 or 3.5 database file.
– Add EntityFramework.SqlServerCompact or EntityFramework.SqlServerCompact.Legacy NuGet package to project and build project.
– You can now use this connection for Generate Model from Database, Generate Database from Model and Update Model from Database, and with the Power Tools.

With this release, the DDEX support matrix looks like this:

  SQL Server Compact 3.5 SQL Server Compact 4.0
Visual Studio 2010 Microsoft None
Visual Studio 2010 SP1 + SQL CE 4.0 Tools Microsoft Microsoft
Visual Studio 2012 ErikEJ Microsoft
Visual Studio 2013 ErikEJ ErikEJ

“Microsoft” means full featured, Microsoft supplied provider
ErikEJ” means simple, basic support, enough to enable Entity Framework Tools, provided by this version of SQL Server Compact Toolbox.

Currently, the 3.5 DDEX provider does not fully work with the Entity Framework Tools, due to a bug (my bug!) in the EntityFramework.SqlServerCompact.Legacy Entity Framework provider; I am working on getting it fixed.

Scan Solution for sdf files

This new menu item will scan the active Solution for any sdf files, and add those to the Toolbox:

image

Prefer DDEX

image

This new option allows you to always use the Toolbox supplied dialogs for connecting to a SQL Server Compact database file, which will allow you to connect to any file, and does not require an .sdf file type. (By choosing not to prefer!)

Bug fixes and enhancements

Add connection will now always use DDEX provider if available
Handle apostrophe in file and folder names when saving connections
Fixed error: The specified table does not exist. (Databases)
Make it clearer that sqlmetal dependent features requires 3.5 to be installed

Please report any issues you encounter and provide feature suggestions and requests here.

Entity Framework 6 & SQL Server Compact (3)–Getting started with the SQL Server Compact 3.5 provider (and Merge Replication)

As you may know, SQL Server Compact version 4.0 does not have support for Merge Replication and Remote Data Access (you can still use RDA, however). This was a showstopper if you wanted to use the latest version of Entity Framework, as up until now, only SQL Server Compact version 4.0 was supported with Entity Framework 5 and 6. But now a SQL Server Compact 3.5 provider for Entity Framework 6 is available, currently as pre-release on NuGet.

The new 3.5 provider is based on the exact same codebase as the 4.0 provider, and thus has all the new features (SqlCeFunctions, Migrations etc.), bug fixes and performance improvements included in the 4.0 provider. In addition, this new provider supports IDENTITY Keys, just like the 4.0 provider. (A showstopper for many with the present 3.5 provider). As it is based on the same codebase as the 4.0 provider, any future improvements and bug fixes will also be included with this provider.

This blog post will describe how you can get started with the new 3.5 provider with Entity Framework 6, and will also include some pointers on getting started with Merge Replication. I will assume that you have Visual Studio 2012 or 2013 Professional or higher installed, and also have my SQL Server Compact Toolbox add-in installed. If you are using Visual Studio 2012 and Database First, you must also have the Entity Framework 6.0.2 Tools installed, download from here. And of course you must have the SQL Server Compact 3.5 SP2 Desktop runtime installed.

So let us create a new console app, that uses Entity Framework 6 and SQL Server Compact 3.5, and which could potentially be a Merge Replication subscriber.

Create a new Console project:

image

For this walkthrough, we will use Database First, but you can of course also use Code First.

Add the EntityFramework.SqlServerCompact.Legacy NuGet package to the project (remember to allow pre-releases), by right clicking the project and selecting Manage NuGet Packages…

image

This will install Entity Framework 6.1-alpha1 and the 3.5 provider and add the required registration in app.config.

Build the project.

Connect/create the database that you want to use in the SQL Server Compact Toolbox, right click it, and select “Add Entity Data Model to current Project”

image

Just click OK:

image

This will add Chinook.edmx and invoke code generation that builds a DbContext derived class and POCO classes for each table in your database.

You can now add Merge Replication to your solution, you can start by installing my Merge Replication client helper library via NuGet, http://www.nuget.org/packages/ErikEJ.SqlCeMergeLib/, read more about it here: https://sqlcemergelib.codeplex.com/

In order to configure Merge Replication on your SQL Server database and web server, I have a brief blog post here, but otherwise I can highly recommend the book by Rob Tiffany.

Notice that if you want to add Merge Replication to a SQL Server 2012 database, you need SP1 and CU4 or later, and you will need a recent build (8088 or newer) of the SQL Server Comapct 3.5 runtime installed, as listed in my blog post here.

Hopefully you will now be able to get started with this combination of the latest Microsoft data access technology and  “legacy” technologies like Merge Replication and Sync Framework. If you have any questions, please ask in the MSDN forum or Stack Overflow.

SQL Server Compact Toolbox 3.4–Visual Guide of new features

After nearly 190.000 downloads, version 3.4 of my free, open source SQL Server Compact Toolbox extension for Visual Studio 2013, 2012 and 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

Data compare

The major new feature of this release is the table based data compare feature, that will generate a script with INSERT, UPDATE and DELETE statements to make two tables contains the same data. The two tables must have compatible schemas and same names. The feature works across both SQL Server and SQL Server Compact tables.

To try out this new (beta) feature, right click on a table and select “Compare Data…”:

clip_image002

Select the target database:

clip_image003

A script with the required statements will then open in the SQL editor.

Database Information

This feature will script information about the selected database in the SQL editor, both general information about the database, including Locale ID and case sensitivity, and also list number of rows for all user tables.

clip_image004

clip_image005

Maximum column width in Edit grid

This new option allows you to set a limit on the column width in the edit grid, useful if you have some columns with very long text string, and you want them all to be visible.

clip_image007

After setting the option to for example 200 pixels:

clip_image009

Visual Studio 2013 support

Server Explorer in Visual Studio 2013 no longer supports SQL Server Compact 4.0, and other tools that depend on Server Explorer (DDEX) will no longer work with SQL Server Compact 4.0. However, you can still use the SQL Server Compact Toolbox in Visual Studio 2013, both with version 3.5 and 4.0 database files. The only requirement is that you have the relevant SQL Compact runtime MSIs installed. In addition, the Toolbox supports code generation of LINQ to SQL DataContext classes, both for Windows Phone 7.5/8 and Desktop apps. For Entity Framework, no code generation is required provided you use the Code First workflow.

clip_image010

Other improvements and bug fixes

Validate Connections improved
Merge Replication save properties fixed
CSV import unicode issue fixed + improved error message
milliseconds included in datetime in text results
SQLite script contained GO separators, they are no longer there
Avoided scripting spatial indexes (SQL Server scripting)

SQL Server Compact Toolbox 3.2–Visual Guide of new features

After more than 150.000 downloads, version 3.2 of my SQL Server Compact Toolbox extension for Visual Studio 2012 and 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

Export from SQL Server to SQL Server Compact 4.0 in a single workflow

Previously with the Toolbox, in order to export a SQL Server database to SQL Server Compact, you had to export a script, create a new blank 4.0 database file, and then run the script against this new file. This process has been simplified to a single workflow, where you define the database and tables to export, and then specify the 4.0 database file name.

To use the feature, right click the root node in the Toolbox:

clip_image001

Select the server database:

clip_image002

If selecting a LocalDB database, type (localdb)v11.0 in the server name box, and type the database name, do not pick the database name from the dropdown list and do not press the test connection button. This is caused by the fact that this connection dialog (that supports SQL Server authentication) uses an old API to connect to SQL Server, which only partially works with LocalDB.

Select which tables to be scripted:

clip_image003

And type the name of the new SQL Server Compact database file:

clip_image004

Once the background process completes, you can see in the Visual Studio status bar that the export completed:

clip_image005

As this is a new feature, please provide any suggestion for improving it…

Option to preserve Server schema names as part of Server export files

There is now a new option that enables you to keep the Server schema name (for example dbo) as part of the exported table name:

clip_image006

Once this feature is enabled, the resulting CREATE TABLE script will look like this:

CREATE TABLE [dbo.Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

rather than the usual:

CREATE TABLE [Shippers] (

[Shipper ID] int NOT NULL IDENTITY (33,1)

, [Company Name] nvarchar(40) NOT NULL

);

This does not mean that SQL Server Compact now supports or understands the concept of schemas, it simply means that the table name is now: “dbo.Shippers”

 

Red Gate SmartAssembly integration

As I blogged about earlier, SmartAssembly is now integrated with the Toolbox for error reporting and anonymous feature usage tracking. If you encounter an error that merits being reported to me, so I can fix it in a future release, please do so.

Once an error is encountered, you will see this dialog:

clip_image007

If the error is not expected, click “Send Error Report”:

clip_image008

Enter your email address if desired, and I will contact you regarding the error, and click send:

clip_image009

I have also added an option to opt out of feature tracking:

image

 

Improvements

SQL Server tables displayed are properly sorted by schema.name:

clip_image010

Notifying user that 3.5 SP2 is required for many features to work – this has now become an issue on new Windows 8 installation with Visual Studio 2012 only, as SQL Server Compact 3.5 SP2 is not installed on these systems:

clip_image011

Click the red link:

clip_image012

As always, you can use the About box (blue question mark on the toolbar above) to see what SQL Server Compact components you have installed (notice that the Visual Studio 2012 Server Explorer does not support SQL Server Compact 3.5, so no DDEX provider in VS 2012):

clip_image013

Improved SQL Server DGML diagram, the DGML feature now works with AdventureWorks2012.

Improved object tree view performance, in particular for databases with many objects.

Bug fixes

SQL query editor button texts now visible with Visual Studio 2012 dark theme:

clip_image014

Merge Replication dialog fixed to work with “Windows Integrated Authentication”:

clip_image015

Updated scripting API, with foreign key related bug fixes, and various performance improvements

SQL Server Compact Merge Replication Library alpha released

I have just published a new Codeplex project, that contains a library to help with SQL Server Merge Replication using SQL Server Compact 3.5 SP2.

This library simplifies the code and configuration to do Merge Replication from a SQL Server Compact 3.5 SP2 desktop client, with a number of useful helper methods.
Features:

  • Is intended for use from a WinForms or WPF application, and the Synchronize method runs async.
  • Implements best practices for optimal performance, and attempt to properly detect expired subscriptions, by throwing a PublicationMayHaveExpiredException.
  • Will create the database file for you as required, so an existing database file is not required.
  • Optionally logs sync status to a SyncLog table (which is a part of the publication)
  • Generate INSERT script in order to rescue local data in case of a disaster (for example publication expiry)
  • Validate a Publication, for example after initial Sync
  • Properly format a SqlCeException as a string to get all available error information
  • Source includes a demo form to test app.config parameters and see the library in action
using ErikEJ.SqlCeMergeLib;
using System.Data.SqlServerCe;
...
string sdfFile = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "MergeTest.sdf");
conn = new SqlCeConnection(string.Format("Data Source={0}", sdfFile));

DateTime syncDate = sync.GetLastSuccessfulSyncTime(conn);
textBox1.Text = "Last Sync: " + syncDate.ToString();

sync.Completed += SyncCompletedEvent;
sync.Progress += SyncProgressEvent;
sync.Synchronize(conn, 1002);

Other useful methods:
Generate INSERT script for the local database (for disaster recovery):

public string GenerateInsertScripts (
SqlCeConnection connection,
List<string> tableNames
)

Format a SqlCeException as a String:

public string ShowErrors (
SqlCeException e
)

Validate that the local database is properly Merge Replicated;

public bool Validate (
SqlCeConnection connection
)

Configuration:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="InternetLogin" value=""/>
<add key="InternetPassword" value=""/>
<add key="InternetUrl" value="http://erik-pc/ssce35sync/sqlcesa35.dll"/>
<add key="Publication" value="PubPostCodes"/>
<add key="Publisher" value="Erik-PCSQL2008R2"/>
<add key="PublisherDatabase" value="PostCodes"/>
<add key="PublisherLogin" value="sa"/>
<add key="PublisherPassword" value="pw"/>
<add key="UseNT" value="false"/>
</appSettings>
</configuration>

repl.jpg

Hope you will find it useful, and please post any bugs and suggestion via the Issue Tracker on CodePlex.

Note, that it appears that Merge Replication against SQL Server 2012 with SP1 or later is currently broken (but works with SQL Server 2012 RTM).

SQL Server Compact Toolbox 3.1–Visual Guide of new features

After more than 130.000 downloads, version 3.1 of my SQL Server Compact Toolbox extension for Visual Studio 2012 and 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

Export to SQLite

As I have blogged about earlier, the Toolbox now has a feature that enables migration to SQLite, for use for example for migrating Windows Phone solutions to Windows 8 Apps.

clip_image002

Upgrade a SQL Server Compact 3.5 database directly to 4.0

The Toolbox already supports scripting a 3.5 database and running the generated script against a version 4.0 database, but this release contains a feature that does a direct upgrade, using the SqlCeEngine Upgrade method. In addition, the new database will be connected to Server Explorer and the Toolbox automagically.

clip_image003

Option to ignore IDENTITY columns when scripting table data

Some users have requested the option to script table data without SET IDENTITY_INSERT statements, basically ignoring the IDENTITY column. This is now possible via a new user option:

clip_image004

New SQL Server Connection dialog

When connecting to SQL Server to generate scripts, the dialog used in previous version of the Toolbox only worked with Integrated Security, not connections using SQL Server authentication. This is the new dialog:

clip_image005

Other improvements

Improved DataGrid results performance – when enabling showing query results in Grid, performance was not ideal – this has been improved:

clip_image006

Support for VS 2012:

clip_image007

Notice that both 4.0 and 3.5 database file versions are supported by the Toolbox in VS 2012, but that 3.5 SP2 must be installed separately, as it is no longer installed by Visual Studio. Toolbox support for 4.0 in VS 2012 does not require 3.5 SP2 to be installed.

Bug fixes

All SyncFx menu items now only available with SyncFx 2.1 installed
Updated scripting API
Fix of 2 bugs related to “correct” table sorting

The state and (near) future of SQL Server Compact

I recently got asked about the future of SQL Server Compact, and in this blog post I will elaborate a little on this and the present state of SQL Server Compact.

Version 4.0 is the default database in WebMatrix ASP.NET based projects, and version 2 of this product has just been released.

There is full tooling support for version 4.0 in Visual Studio 2012, and the “Local Database” project item is a version 4.0 database (not LocalDB). In addition, Visual Studio 2012, coming in august, will include 4.0 SP1, so 4.0 is being actively maintained currently. Entity Framework version 6.0 is now open source, and includes full support for SQL Server Compact 4.0. (Entity Framework 6.0 will release “out of band” after the release of Visual Studio 2012).

The latest release (build 8088) of version 3.5 SP2 is fully supported for Merge Replication with SQL Server 2012 (note that “LocalDB” cannot act as a Merge Replication subscriber), and Merge Replication with Windows Embedded CE 7.0 is also enabled.

On Windows Phone, version 3.5 is alive and well, and will of course also be included with the upcoming Windows Phone 8 platform. Windows Phone 8 will also include support for SQLite, mainly to make it easier to reuse projects between Windows Phone 8 and Windows 8 Metro.

On WinRT (Windows 8 Metro Style Apps), there is no SQL Server Compact support, and Microsoft is currently (doubt that will change) offering SQLite as an alternative. See Matteo Paganis blog post also: http://wp.qmatteoq.com/using-sqlite-in-your-windows-8-metro-style-applications

So, currently SQL Server Compact is available of the following Microsoft platforms: Windows XP and later, including ASP.NET, Windows Phone, Windows Mobile/Embedded CE.

On the other hand, SQL Server Compact is not supported with: Silverlight (with exceptions), WinRT (Windows 8 Metro Style Apps).

So I think it is fair to conclude that SQL Compact is alive and well. In some scenarios, SQL Server “LocalDB” is a very viable alternative, notice that currently LocalDB requires administrator access to be installed (so no “private deployment”). See my comparison here.

SQL Server 2008 R2 SP1 Merge Replication hotfixes

It seems that the recent Cumulative Update 4 to SQL Server 2008 R2 contains a couple of essential hotfixes, if you are using Merge Replication with SQL Server Compact 3.5 against a SQL Server 2008 R2 instance.

The 2 hotfixes are:

FIX: Poor performance when you synchronize the data from a SQL Server 2008 R2 publisher to a SQL Server compact 3.5 subscriber 2616718

FIX: Merge replication changes are missing on a SQL Server Compact subscriber when they subscribe to a partition in SQL Server 2008 R2 2644396

As always, test before applying this Update in production.