Home About Eric Topics SourceGear

2013-12-11 12:00:00

Announcing the release of Zumero for SQL Server

So, as we say here in the Midwest, I'm as giddy as a fox in the hen house while the dog ain't home.

Today we released version 1.0 of Zumero for SQL Server.

Eric, what are you talking about here?

Zumero for SQL Server. I mentioned it here on my blog a few weeks ago.

Why do I need this? Can't I just make my SQL Server data available to mobile devices using a REST API?

Yes, you can do that. And if that solution meets your requirements, then you may not need Zumero. But there are some pretty big benefits to the "replicate and sync" approach, including performance and offline support.

There's some info about this on our website.

Or you could check out Sync is the Future of Mobile Data, by Couchbase co-founder J. Chris Anderson. Most of Chris' preaching tends to be compatible with Zumero, except of course that he is of the NoSQL religion and we practice the SQL doctrine.

Or you could take a look at offlinefirst.org. These guys are also sort-of a competitor to Zumero, but they're singing the same tune we are, and they're singing it pretty darn well in my opinion.

What's in Zumero for SQL Server?

ZSS consists of three parts:

ZSS Server is a module for Internet Information Services (IIS). Simply point it at your SQL Server database (which need not be on the same machine) using an ODBC connection string. We include an easy-to-use installer as well as instructions for people who prefer to do the installation manually.

ZSS Manager is a desktop application used to setup your data for sync. You can specify which tables you want your mobile devices to see and/or modify.

ZSS Client SDK is a collection of libraries which allow you to sync on mobile devices.

Do you have a screen shot of ZSS Manager?

By golly I do!

Lots more of them on the Zumero website, BTW.

What mobile platforms are supported?

So this is only for mobile? What if I want clients on desktop machines?

Actually, we've also got sync libraries for MacOS, Linux and Windows. The focus of the product is mobile, so we sometimes list them in the supporting cast.

What SQL database are you using on the mobile device?

SQLite (see www.sqlite.org)

SQLite? That's lame.

Certainly not.

The world's population divides very cleanly into two groups:

  1. Those who are amazed by the sheer awesomeness of SQLite
  2. Those who do not understand SQLite

If you write code and you're in group 2, regardless of whether you become our customer or not, for your own sake, get yourself into group 1. It's a better way to live.

Can I just add ZSS to my existing solution and get mobile?

Yes. ZSS is designed to be added into an existing environment without screwing up anything already there. If you've already got a SQL db which is serving a website or a bunch of desktop apps, ZSS will play nicely with your existing stuff while giving you the ability to replicate your data so it can be used with SQLite on mobile devices.

Do I have to change my existing database tables?

No. ZSS does not require you to make any changes to your existing schema, but it does need to install some new housekeeping tables and triggers and views. The ZSS Manager application generates the T-SQL for these items and displays it so that you can review everything before any changes to your database are made.

What does the Zumero sync API look like?

It's one function. At the C level, it looks like this:

int zumero_sync(
  const char *zFilename,
  const char *zCipherKey,
  const char *zServerUrl,
  const char *zDbfile,
  const char *zAuthScheme,
  const char *zUser,
  const char *zPassword,
  const char *zTempDir,
  char **pzErrorDetails

Much of the Client SDK is wrappers to make this friendlier. For example, in C#, it looks like this:

namespace Zumero
    public static class ZumeroClient
        public static void Sync(
                string filename,
                string cipherkey,
                string server_url,
                string dbfile,
                string auth_scheme,
                string user,
                string password

And so on...

Oh, and by the way, I exaggerated. It's not one function, it's actually eight. But the other seven don't matter very often. Mostly.

Can I install ZSS "on-premises", on our existing servers?


Do you sell a cloud-hosted version of ZSS?

Currently, no, although you are obviously free to install your licensed ZSS Server in your own cloud instance if you like.

So how does "Zumero for SQL Server" relate to this "Zumero Cloud" thing I see on your website?

Sorry for the confusion. We're working to clean all this up.

ZSS and Zumero Cloud are actually quite different. Zumero Cloud was first launched back in March 2013. We think of it as "version 0" of the Zumero architecture. ZSS is built on a very different architecture, which we might call "version 1". Simply put, the overall design of ZSS is just better. We will be upgrading the Zumero Cloud service to the new architecture, but that will involve some compatibility-breaking changes, so we need to do the transition carefully.

What database API do I use on the client to work with my data in SQLite?

Whatever you like. ZSS doesn't care.

When you ask the sync library to bring down a copy of the prepared data from the ZSS Server, you provide it with the path name of a SQLite database file. Once that file is on your mobile device, you can interact with it using whatever SQLite API or wrapper that you like.

For example, on Android/Java you would probably use android.data.sqlite.

On iOS/ObjC, you could perhaps use FMDB.

On WinRT, there's a SQLite component. And there's one for Windows Phone 8 as well. The samples in our Client SDK make use of these.

On Cordova (Phonegap), everybody seems to be using this SQLite plugin. Well actually, the link in the previous sentence is to our Github fork of that plugin, containing one little tiny change. Because Zumero needs to know the full path name of the SQLite database file, we tweaked the API to provide that info back to the app.

On Xamarin, sqlite-net is quite popular. Or you could use Mono.Data.SQLite, an ADO.NET-style API which is built into the Xamarin toolset.

The list above is not complete.

Can I use Core Data with ZSS?

Short answer: no

Longer answer:

We haven't tried that yet. If you're not going to be crushingly disappointed when it doesn't work, please try it and let us know.

But I'd bet a dollar that this is going to require some additional effort. Core Data isn't really a general-purpose SQLite wrapper. It's a big, thick abstraction which happens to [sometimes] use SQLite under the hood.

There is some progress in this area. Joel Grasmeyer has done some Core Data support for Zumero Cloud. This code is not compatible with ZSS right now, but we're talking with him. About stuff.

Is the ZSS Client SDK compatible with SQLCipher or SQLite Encryption Extension?


How do I deal with authentication and permissions?

ZSS Manager allows you to configure permissions at fine granularity, allowing you to assign SELECT, UPDATE or DELETE rights to users on a per-table basis.

For ZSS 1.0, the authentication support is fairly basic, allowing you to authenticate users against a password list in a SQL table, with support for either plaintext passwords (ick) or bcrypt. Things like Active Directory and OAuth2 are on the way.

Can I sync only part of a table, excluding certain columns, or filtering the rows?

In ZSS 1.0, no. This feature is under development for inclusion in a 1.1 release, planned for January.

Is ZSS open source?


What about pricing?

See http://zumero.com/pricing/.

Are you planning to support other SQL databases on the server?

Yep. We've been getting plenty of requests for other backends. PostgreSQL is perhaps the most often heard. Email sales@zumero.com to cast your vote.