Zumero


Zumero - Frequently Asked Questions

Zumero for SQL Server

Run the configuration program used during the install process. From the Windows Start menu, run "ZSS Server Configuration", and update the license key value there.

First, you will need to have two pieces of information on hand:

  • Your Zumero for SQL Server license key
  • The Sync URL your clients will use to connect to your ZSS server (e.g. http://test.example.com:8080)

Then visit the Activation page in your web browser to lookup your activation history and create a new activation for your sync URL.

Note: It is highly recommended that you do not use an IP address in your sync URL. You will not be able to delete an activation once it's created, so using a fully qualified domain name will allow your ZSS server to be moved to a new server more easily.

Run the configuration program used during the install process. From the Windows Start menu, run "ZSS Server Configuration", and update the connection string there, optionally using the Connection String Wizard.

Launch Microsoft SQL Server Management Studio.

First, you'll need to create a login. Right-click the "Security" subfolder under your server in SQL Server Management Studio, then select "New -> Login".

On the "Login - New" page, add a login name and password, and select the database you'll be using with Zumero as the default database for this login.

Under the "Databases" folder, find the database for which you want to create a SQL User. Under that database, select "Security -> Users -> New User".

On the resulting dialog, choose "New User With Login". Select a SQL User name (often the same as the login), and include the login name you created earlier.

Under the Membership tab, select at least "db_datareader" and "db_datawriter".

Click OK to create the user. The username/password you chose can be used as UID and PWD values in ZSS ODBC Connect Strings

You should now confirm that the user has VIEW SERVER STATE permission. (See the ZSS Manager User Guide for background)

If any prepared table has an IDENTITY column for its primary key, the Zumero user will also need ALTER permission. (See the ZSS Manager User Guide for background)

My SQL Server database uses identity columns. Do I have to change them to GUIDs to comply with Zumero's distributed model?

No. Your identity columns will continue to work as you'd expect. In client databases, temporary sequential values are inserted for new rows. When syncing, the server allows SQL Server to assign "final" values, which are synced back to the client.

ZSS Server:

Windows Server 2008 R2 or later (64-bit)

IIS v7.5 or later

SQL Server 2008 R2 (10.5) or later

ZSS Manager:

Windows 7 or later

Client:

Database: Generally, SQLite 3.7 or later — see the platform readmes in the client SDK for platform-specific details

Client Platforms:

  • IOS 5.1 or later
  • Windows Phone 8
  • Android Ice Cream Sandwich (android-14: x86, armeabi and armeabi-v7a) or later

Yes. Communications between the ZSS Server and clients can be encrypted by enabling SSL/TLS on the Zumero IIS Site.

SQLite data on client devices can be encrypted using SQLCipher.

Yes, but some configuration changes on the load balancer might need to be made to prevent problems with syncing.

Some load balancers have the TCP inactivity timeouts set too low for ZSS. If this is the case the load balancer might drop connections if the server takes too long to generate a sync response.

Zumero Client API

The front page of the official SQLite website usually recommends using the latest version of SQLite for new development. We concur with this recommendation. Whenever possible, use the latest version of SQLite with Zumero.

Yes. Zumero's housekeeping tables use foreign keys to ensure data integrity. Using SQLite with foreign keys turned ON is highly recommended. Execute this statement each time you open the SQLite database:

PRAGMA foreign_keys = ON;

Use of OR REPLACE with a Zumero table requires that SQLite recursive triggers be turned on. Execute this statement each time you open the SQLite database:

PRAGMA recursive_triggers = ON;

We recommend using WAL mode in most situations. See Write-Ahead Logging for more information.

PRAGMA journal_mode = WAL;

We suggest:

  • Always wrap everything in an explicit transaction. Don't use "autocommit mode".
  • If your transaction does not modify the db, just use "BEGIN TRANSACTION". With WAL mode, this statement should never return a busy error, and all the SELECT statements inside the transaction should be fine as well.
  • If your transaction does modify the db, then use "BEGIN IMMEDIATE TRANSACTION" in a sleep/retry loop to wait and try again if you get SQLITE_BUSY. Once this statement succeeds, you know that you have the write lock, so the statements inside the transaction are immune to busy errors.

Yes, but Zumero will not synchronize it.

No. Sync happens at the granularity of one SQLite dbfile.

No. You can sync only the ones you want.

Just call zumero_sync(). The file will be created on the client.

No. Each call to zumero_sync() will submit any and all pending changes from the client, after which it will download changes from the server.

No.

Zumero creates several housekeeping tables that are used to store the additional information necessary to support synchronization. All of them are prefixed with t$ or z$.

Bad idea.

For maximum compatibility with different platforms on which the server may be running.

Yes.

No. The server will prevent this whenever possible.

The entire package is rejected. The zumero_sync() operation has no effect except to return an error code. Sync and conflict resolution happen atomically for the package being pushed. If you want finer granularity, then sync more often.

No.

No.

Prior to Zumero v1.3, clients stored the entire history of the dbfile on the mobile device. Clients using v1.3 or newer will purge historical data older than the client's last sync point. Use SQLite's VACUUM feature to reclaim deleted space and reduce the size of the client's SQLite file.

These two things are completely unrelated. With Zumero, "conflict resolution" refers to the reconciliation of multiple changes during synchronization. The SQLite ON CONFLICT clauses refer to the way SQLite performs error handling on certain kinds of constraint violations.

Zumero can handle either choice, but there are things to consider.

For integer identity columns, the primary complication is that two client databases can independently insert the same value for the key in their local database. When the newly added row is synced up to the server, the server changes the conflicting key to the latest available key in the SQL Server table. Any foreign key references to the client's key will also be updated to the new key. Once the new row has been accepted by the server, that key will be permanent for all clients, and the original client will update the key in its SQLite table. See additional documentation here.

Pros:

  • Integers are easier to understand.
  • Integers are stored and transferred using a variable number of bytes, so smaller values take up less space.

Cons:

  • Client inserts should consider their keys to be temporary until synced to the server. For example, a field salesman would not be able to give a reliable OrderID to a customer until it has been synced to the server.
  • The server connection needs permission to call the SET IDENTITY_INSERT command. See documentation.

For GUID columns, the key will not change on sync, and will not conflict with existing keys.

Pros:

  • The key will not change after it has been inserted.
  • No special permissions are required for the server connection.

Cons:

  • GUIDs are not as easy to understand.
  • GUIDs are a little slower.
  • Working with keys on the client side is trickier. For examples, see the documentation.
  • A GUID value takes up at least twice as much space as an integer.

If after reading all of this you're still not sure what to do, use a GUID. They're more work, but will have fewer surprises.