Wednesday, April 23, 2014

“Shop Talk” with Eric Sink, Zumero for SQL Server founder

In my last blog post I introduced you to Zumero, an Enterprise solution, that allow you to replicate and sync SQL Server data across all your team's mobile devices.

I had the pleasure of meeting up with Zumero founder, Eric Sink, @eric_sink at the recent SQL Saturday event in Copenhagen.

image

We agreed to do a short interview by mail, so I could share my Zumero questions with my blog readers. So without further ado, let’s get started.

I know you have been involved with other software products (SourceGear). What is the story behind Zumero?

We think of Zumero as a new start-up that is incubating inside SourceGear, so there is much shared history.

We started SourceGear in 1997, initially as a contracting shop building custom software for mobile devices.  We did a lot of work with Blackberry devices and some very early Motorola smart phones.

Then we got into version control tools, which has been a great business for us over the last 15 years.  We are still serving a large customer base with on-going active development of SourceGear Vault.

But in the last few years, the world of mobile has been drawing us back in.  :-)

And from a certain point of view, our work on version control means that we have been doing "sync" for 15 years.

And SourceGear Vault has always been built on SQL Server.

So that's how we got the three main ingredients of Zumero:  SQL Server, mobile, and sync.

Some of the features that Merge Replication provide are: - Minimal client code, secure, compressed transfer over https, server side filtering, read-only tables, conflict handling. How does Zumero stack up?

I /think/ we stack up pretty well, although we still have many ideas for how to make our product better.

"minimal client code"  -- The Zumero Client SDK tries to make everything easy.  Just call the Zumero sync function and Zumero will synchronize changes in both directions.

"secure" -- Zumero is designed to work over SSL/https.  The protocol includes authentication.  And on the client, Zumero is compatible with bulk encryption libraries such as SQLCipher.

"compressed transfer over https" -- All packages exchanged between Zumero client and server are compressed.  In fact, the packages from server to client are often "deltified" to make them very small indeed.

"server side filtering" -- Zumero does support the ability to exclude tables, or columns from tables, or to filter rows on a custom WHERE clause.

"read only tables" -- This can be accomplished with Zumero's authentication and permissions.

"conflict handling" -- Zumero handle conflict resolution on the server.  The default rules seem to be appropriate for most cases, but those rules can be customized to do whatever is needed for a given situation.

In addition, Zumero adds a few things that SQL CE merge replication does not, most notably, support for iOS and Android devices.

OTOH, Zumero is built on SQLite, which does not resemble SQL Server as closely as SQL CE does.  We are constantly working to provide Zumero customers with a smooth experience by improving features, tooling and documentation.  My blog series on the differences between SQL Server and SQLite is a part of those efforts:

Rob Tiffany has demonstrated scaling Merge Replication. How does Zumero scale?

This is one area where Zumero's youth is evident, and I shall not pretend.  There is much more experiential knowledge about scaling with merge replication.  Rob Tiffany's "cheat sheet" is a gold mine of information:

And some of his advice would be applicable to Zumero as well.

However, we are quite happy with the scalability results we have achieved so far and we continue to push further.

(a)  We do a lot of "crowd testing", using lots of clients to abuse the Zumero server so we can tune it for reliability and performance.

(b) We are currently working with some customers who are integrating Zumero into an environment where merge replication is already in use (as a scaling solution for the SQL Server backend, not as a mobile sync solution).  We have some additional testing to do here, but we hope to able to publish some guidelines about the compatibility of Zumero with this kind of situation.

(c) Finally, we have tested and verified another way of scaling out, by having multiple Zumero servers talking to a single instance of SQL Server.  This can make a significant difference and is not difficult to set up behind a round robin DNS.

You are using Triggers and tracking tables on SQL Server, and not the built-in Change Tracking. Why?

The real reason is that Zumero's core sync code existed before we made the decision to make SQL Server the primary focus of the product.

That said, the documentation for Change Tracking raises questions for us.  We wonder if it would require changes to our sync algorithm.  We wonder if our support for tracking schema changes would still work.  We wonder about the apparent dependence on snapshot isolation.

We may explore this further in the future, but right now, we've got a solution which is robust, so we don't feel much pressure to change it.

Do you provide a Windows Mobile (.NET Compact Framework) client library?

Currently, no, but we are planning to do so.  The implementation work for Windows Mobile 6 support is done, but it has not yet been released as part of the product.  If a Zumero customer needs this, we would be ready to discuss and coordinate making it available.

What do you recommend for use as data access API towards SQLite on Windows platforms?

Right now, there is no API we can recommend without caveats.  Or rather, there are several good choices, but no clear winner.

For desktop Windows only, the core SQLite team products System.Data.SQLite, an ADO.NET provider which is fully compatible with EF6.  If you're not using mobile, this is almost certainly the best choice.

Frank Krueger's sqlite-net wrapper is quite popular, and for some very good reasons.  But last I knew, none of the various PCL forks have been folded back in, and that has been an issue for some.  Nonetheless, this is the wrapper we encounter most often.

WinRT without System.Data is a story with a big hole in it.  We remain hopeful that Microsoft has some more good stuff in the pipeline.  

Looking beyond Windows to include iOS and Android as well, things can get complicated.  Both of these other platforms include a [different] version of SQLite as part of the mobile OS.  Many apps use those.  Some bundle their own.  Some replace SQLite with SQLCipher.  Things can get tricky.  In addressing this set of problems with our customers, we've built some stuff that we are planning to make available to the community as open source (to be announced on my blog, soon, I hope). [Ed: Available as source code on Github, and NuGet packages coming soon]

Thursday, April 10, 2014

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.

Wednesday, April 2, 2014

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.