Zumero

ZSS Manager needs to know what database contains the tables you'll be syncing. The data may reside in the same database as the ZSS Server configuration data, another database on the same server, or on another server altogether.

In this example, we're using data from the same database.

The DBFile wizard now reiterates what will happen when you click Next:

After the DBFile is created, we can next Add a Table or Create a User and Set Permissions:

If your synchronized tables contain some columns that you don't want to be synced to client databases, you can select the synced columns during the Add Tables to DBFile wizard, or by selecting the table in the ZSS Manager window and picking the Choose Columns entry from the context menu.

Note that when new columns are added to a table that is already in the DBFile, they are not synchronized by default. Use the Choose Columns dialog if you wish to include the new column in the client database.

Here we see that:

If we choose to Stop syncing [atomic_mass], we'll be warned of the consequences:

Afterwards, the column is marked as "not synced", and when it's selected we have the option to Start syncing [atomic_mass], just as we would with any unsynced column.

This dialog gives you the opportunity to configure the Conflict Resolution settings.

If you need to stop syncing a table, you select the table in the ZSS Manager window and pick the "Stop Syncing" entry from the context menu. It should be noted, however, that this is destructive and shouldn't be done on live applications with Zumero clients. Any unsynced changes on client databases will be lost, even if the table is re-added to the DBFile.

The Synchronized Tables list will allow you to see a list of the Warnings and Errors that may impact the table's synchronization. To get a clear view of the issues, select the table and choose View Warnings and Errors entry from the context menu.

Selecting one of the errors or issues, you can choose Copy from the context menu to copy the text to the clipboard.

Pressing the Sync button in the Test Client window, the Sync Dialog will come up.

There are two syncing modes, authenticated and anonymous. Authenticated syncs validate against a users table. In order for anonymous syncs to succeed, you must enable anonymous access to your DBFile.

The Test Client Window enables two SQLite features that are off by default. When you open or create a SQLite database, it executes these pragma statements. When writing your own client, you almost certainly want to do the same.

PRAGMA foreign_keys = ON;
PRAGMA recursive_triggers = ON;

Enabling foreign keys means the client database will enforce the foreign key relationships you've defined in your SQL Server database.

Recursive triggers are necessary for Zumero's triggers to correctly handle SQLite INSERT OR REPLACE statements.

When creating a new SQLite database, the Test Client Window also enables WAL (Write-Ahead Logging) mode.

PRAGMA journal_mode = WAL;

WAL mode is strongly recommended for improved concurrency. See Write-Ahead Logging for more information.

{"scheme_type":"default"}

ZumeroClient.Sync(localFilePath, cipherKey, serverUrl, dbfileName, "{\"scheme_type\":\"default\"}", username, password);

Database Authentication is Zumero's default authentication method. This authentication method stores the users and groups in the database. When first configuring Database Authentication, ZSS Manager will ask if it can create the default backing tables for Database Authentication. If you allow this, they will be created as follows:

CREATE TABLE [zumero].[users]
	(
	  [id]   int IDENTITY(1,1) PRIMARY KEY,
	  [name] nvarchar(100) UNIQUE NOT NULL,
	  [pass] nvarchar(100) NOT NULL
	)

	CREATE TABLE [zumero].[groups]
	(
	  [username] nvarchar(100) NOT NULL,
	  [groupname] nvarchar(100) NOT NULL
	)
				

Users and Groups that you created via the Users and Groups dialog will be added to these tables. The passwords are encrypted with bcrypt. ^[3]

Database Authentication has a couple of configurable settings. To access these settings use the following steps:

If you already have users and passwords stored elsewhere in your database, ZSS Manager and the Zumero Server can use them, with some caveats:

If you have existing users and passwords in your database but passwords are neither plain text nor bcrypt hashes, you can still use ZSS Manager to set permissions, which is recommended. Create the zumero.users view with id, name and pass columns, but write the insert/update triggers such that only the name column gets updated. This will allow ZSS Manager to change user names and manage permissions, but any password changes are silently ignored. You can manage passwords outside Zumero, as you did before.

Zumero for SQL Server can also make use of the users and groups in your existing Active Directory setup. This authentication source has the following requirement:

Next, you must configure your DBFile to use Active Directory for authentication.

When using Active Directory as the authentication source, the Users & Groups dialog will not be populated with all of the users and groups from Active Directory. If you wish to add permissions for a user or group you will need to use the “Add…” button to create an ACL entry for the specified Active Directory object.

No changes are necessary for Zumero clients, they should send the username and password as they would with Database Authentication. There is a slight difference on the server that is worth noting. At authentication time the username is converted to the User Principal Name ^[4] (<username>@<domain>). This is most notable when using the {{ZUMERO_USER_NAME}} token with filters.

Zumero checks permissions from most to least specific. When an authenticated user Winifred syncs, permissions are checked in this order:

As soon as either Allow or Deny is found, the checking stops. If no permission is set, the checking continues. If no match is found the sync is denied.

Zumero syncs are atomic: they succeed or fail as a single unit. This comes into play when permissions are checked. Say Winifred has modified existing rows and added some new ones. When she syncs, she must have permissions to do both, or the entire sync will be denied.

Zumero for SQL Server has four permissions:

By default, authentication is performed by looking up users in the zumero.users table in the same databases as the DBFile's data. You may wish, however, to authenticate against a table stored elsewhere -- e.g. when sharing a user table among DBFiles in different databases.

In this case, you'll still need a table in one of those databases. Access to "remote" user tables is always done by looking up the connection information for a DBFile which lives in the user table's database. In the Authentication... dialog, you'd select the DBFile and click the Modify button

...which brings up the Select Authentication Source dialog:

Here, we've selected the remotedbfile DBFile as the link to our user data. The Table drop-down now lists user tables found in the database containing remotedbfile. We're sticking with that database's zumero.users for now.

The Scheme box contains an authorization scheme string, ready to be used as the scheme parameter in a client application's sync() call. Copy and paste this code to avoid manually creating a matching scheme string.

On return to the Users and Permissions dialog, and beginning to Add a user permission entry, we now see the users from that remote users table:

As a rule, it's best to use the default authentication scheme. There are two main advantages:

If necessary (primarily for backwards-compatibility with older versions of Zumero), you can explicitly specify table or table2 authentication.

When choosing a filter to apply, the Zumero server takes these issues into account:

The Edit Filter dialog contains all of the settings for a single filter. The list on the left shows all of the tables that are synchronized in the current DBFile. If any of the table names are bold, they already have some filter settings applied.

The incoming package is trying to modify a row which has been deleted.

The possible resolutions are:

The incoming package is trying to delete a row which has been modified.

The possible resolutions are:

The incoming package is trying to modify a row which has been modified.

The possible resolutions are:

For the sake of completeness, note that the situation where a sync is attempting to delete a row which has already been deleted is not considered a conflict. If everybody wants the row to go away,it does.

The Conflict Rules dialog does not list this as a conflict situation and does not allow you to change this behavior.

Consider a scenario involving two clients, Phil and Lew, who are sharing a database on the server.

Phil's sync will cause a violation of the unique constraint.

Currently, Zumero does not have any mechanism to allow for alternative handling of regular UNIQUE constraint or UNIQUE index violations. (It does have provisions for handling certain PRIMARY KEY constraint conflicts, though, as we'll see in the next section.) So whenever possible, the best way to deal with UNIQUE constraint violations during sync is to carefully design your app to avoid them happening in the first place. Any time you INSERT or UPDATE data in a column which has a UNIQUE constraint, try to make sure that the data is likely to be unique.

Like UNIQUE constraints, PRIMARY KEY constraints will cause a violation on sync if a client has inserted the same value as a previous client. There are two exceptions to this behavior:

Consider a scenario involving two clients, Nancy and Ann, who are sharing a database on the server.

Nancy's sync will cause a violation of the CHECK constraint, because column merge will result in a record (25, 35, 50), which is a violation because 50 is not > (25 + 35).

Nancy will not be able to perform any sync operations that contain this conflict. Her client must call zumero_quarantine_since_last_sync() to remove the update operation. This is the default behavior. It cannot be changed.

Also, note that currently ZSS does not replicate any of the server's CHECK constraints to the client. App developers using Zumero should have logic in place at the higher layers which will ensure the validity of the data being inserted (though this would be true even if the CHECK constraints were replicated, to avoid client-side SQL constraint violation errors). That being said, it is still technically possible that, like with FOREIGN KEY constraints, a client package could encounter a CHECK constraint violation on the server, even if there were no other "conflicting" changes involved.

Zumero's triggers depend on a table's primary key to uniquely identify rows. A side-effect of this requirement is that in a synchronized table, primary key values can no longer be changed. If you try, the transaction will fail with a foreign key constraint violation from a Zumero housekeeping table which looks like this:

The UPDATE statement conflicted with the REFERENCE constraint "fk_test$z$x$5975771".
The conflict occurred in database "test", table "zumero.test$z$x$5975771", column 'test'.

Zumero creates an AFTER UPDATE trigger on your table. In it, Zumero needs to be able to correlate a row from the implicit DELETED table with its counterpart in the implicit INSERTED table. Unfortunately SQL Server provides no way to do this beyond a set of columns which uniquely identifies the row and doesn't change. By preventing updates to primary key values, we always have this.

For this reason and others, changing primary key values in SQL Server is often cited as a bad practice. If you have existing applications that rely on changing primary key values, you may want to change them whether or not you use Zumero. If you must change a row's primary key values, you will need to delete the row and add it back with the desired changes.

ZSS Manager will refuse to add a table unless it meets certain requirements. To be added, a table must...

ZSS Manager will display warnings for certain tables. These warnings don't prevent you from adding a table but usually indicate the table has characteristics which won't be replicated to a client SQLite database.

The following table characteristics will not be automatically replicated to client databases:

ZSS supports SQL Server schemas. Any table belonging to a schema other than dbo will appear in the client database prefixed by the schema name and an underscore:

When adding a table to the DBFile, ZSS Manager calls sp_settriggerorder to set Zumero's update trigger as "First". If this step fails, the table will still be added, but a warning is issued: “Unable to set the Zumero trigger as First. If any update trigger on this table changes the table before the Zumero trigger runs, serious data corruption may occur. Check your trigger order, and make sure that any update triggers that change the data in the table are run after the Zumero trigger.”

The data corruption in the table arises when there is an update trigger on the table that performs another update operation within the table (for example, to keep a timestamp column up-to-date). If you do not have any update triggers on the table that update the table (and know that you never will) this warning can be ignored.

If, on the other hand, you have any update triggers that update the table, these triggers must be set to execute after Zumero's trigger. If a non-Zumero update trigger executes before Zumero's update trigger and then performs an update within the table, Zumero will store the wrong versions the updated rows in its record of the table's history. This corrupt historical data could then be used when performing conflict resolution (e.g. in a Column Merge), which then produces corrupt data in the live table in surprising ways.

Triggers that issue counts or PRINT statements have been known to interfere with the Zumero server's connection to the database.

Make sure that a SET NOCOUNT ON statement is executed at the beginning of each trigger, and remove any PRINT statements.

Some triggers that change the effect of the executed SQL may prove to be incompatible with Zumero. In particular, watch out for following situations:

As is the case for any database-backed app, schema changes are a pain. Generally speaking, you have to deploy the new schema and the app changes to support them simultaneously. ZSS has no silver bullet for this issue, but we've attempted to make schema changes in Zumero-enabled databases no more difficult than they already were. When you change the schema of a synchronized table, clients pull down the new schema on their next sync. So your client app needs to be capable of dealing with the new schema before it's applied to the production database. For a simple change, you'd do something like this:

The standard sp_rename procedure cannot be used to rename synchronized tables. If attempted, SQL will issue an error message stating "Object '[dbo].[foo]' cannot be renamed because the object participates in enforced dependencies." To work around this limitation, Zumero provides a stored procedure zumero.RenamePreparedTable which can be used to do the rename. For example, to rename table [foo] to [bar] in the [dbo] schema, issue the command:

EXEC [zumero].[RenamePreparedTable] 'dbo', 'foo', 'bar';
				

When you add a new column to a table, Zumero will not add that column to client devices unless you explicitly tell it to start syncing the column. This can be done from ZSS Manager's "Choose Columns..." dialog for that table. You can also tell Zumero to start syncing a column by calling a Zumero-provided SQL stored procedure. This could be called directly from within the same SQL script being used to add the column. For example:

BEGIN TRANSACTION;
	ALTER TABLE [dbo].[foo] ADD [c] int;
	EXEC [zumero].[StartSyncingColumn] 'dbo', 'foo', 'c';
	COMMIT TRANSACTION;
				

Similarly, Zumero provides a StopSyncingColumn stored procedure which allows you stop syncing a certain column without removing it from the server database. When this is used, the column will be removed from all client devices, and Zumero's history and housekeeping data for that column will be removed from the server as well.

Many schema change operations will cause Zumero to drop and re-create its triggers on the host table. When this happens (as when first adding the table to the dbfile) sp_settriggerorder is used to set Zumero's update trigger as "First". If sp_settriggerorder fails, a warning is issued saying “Zumero Warning: Unable to set trigger as First. If any update trigger on this table changes the table before the Zumero trigger runs, serious data corruption may occur. Check your trigger order, and make sure that any update triggers that change the data in the table are run after the Zumero trigger.”

This warning message is referring to the fact that an update trigger that performs updates within the table will cause data corruption if it executes before Zumero's trigger. This is described in more detail in the Trigger Order section.

Additionally, certain other warnings may be issued similar to the warnings when adding the table, in particular when altering a column of a data type that may require special handling by Zumero clients.

In a synchronized table, the references and triggers created by Zumero will prevent certain schema changes from succeeding, including:

There are other changes which Zumero does not prevent from happening, but which put the database into a state that Zumero does not support. When this happens, all Zumero clients will receive a "server_misconfiguration" error every time they try to sync with the affected DBFile. Schema changes in this category include:

Finally, certain other schema changes are supported, but can cause trouble if you're not careful. These include the following.

Most of these issues will be listed on the Errors and Warnings dialog.

A uniqueidentifier column is stored in the client SQLite database as 16-byte binary values, just like in SQL Server.

You can use SQLite's randomblob function to create new uniqueidentifier values:

insert into my_table (my_guid_col, my_text_col) values (randomblob(16), 'my data');

However, SQL Server developers are accustomed to seeing uniqueidentifier values (GUIDs) in their standard textual representation. For example: EA296149-4E9A-4DB7-9944-F3459931140C. For the purposes of display and literal values with the uniqueidentifier data type, SQL Server automatically converts to/from this textual representation.

But SQLite does not have built-in support for this textual representation of a 16-byte blob. If you ask SQLite to SELECT the value of 16-byte blob, you will get 16 binary bytes. It will not automatically convert to the textual representation typically used to display a GUID.

SQLite does have a hex function which can be used to get a textual representation in hexadecimal:

select hex(my_guid_col) from my_table;

However, this will result in a textual representation which is different: 496129EA9A4EB74D9944F3459931140C. The dashes are missing. And the byte order is different as well.

Microsoft's GUID format uses little endian byte order for the first three fields of the GUID^[6]. In the first three fields, the order of hexadecimal pairs is reversed as follows:

The differing textual representations also come into play when using a uniqueidentifier value as a literal. If you try to do this the normal SQL Server way:

insert into my_table (my_guid_col) values ('EA296149-4E9A-4DB7-9944-F3459931140C');

... you will get an error. For SQLite, the GUID value shown is a string literal, not a GUID value, and SQLite will not automatically convert to a 16-byte blob.

Use SQLite's binary literal syntax to specify a uniqueidentifier value:

select my_text_col from my_table 
where my_guid_col = x'496129EA9A4EB74D9944F3459931140C';

insert into my_table (my_guid_col, my_text_col) 
values (x'496129EA9A4EB74D9944F3459931140C', 'my data'); 

update my_table set my_text_col='test123' 
where my_guid_col = x'496129EA9A4EB74D9944F3459931140C';

For most projects, you'll have (at least) a development Zumero server and a production server. You'll test things out, create and change DBFiles, tweak filters, find just the right back-end schema, etc. in the development environment. At some point, though, you need to move that setup into production — preferably without having to go through all of the Zumero configuration steps again. ZSS Migration handles that.

ZSS can migrate, on a DBFile-at-a-time basis:

ZSS Migration (like ZSS in general) doesn't create or alter your underlying data. In particular, Migration will not:

Select the DBFile that you wish to migrate. From the menubar, choose DBFile -> Migrate DBFile

To begin, select Migrate the current DBFile... from the DBFile menu.

Our first step is to select the destination database for the migrated DBFile. This would be the primary database, e.g. the one that your production ZSS Server is configured to use.

Next, we need to select the database where your data lives in the destination environment (i.e. the secondary database)

By default, if a zumero.users table is found alongside your data tables, ZSS Manager will migrate those users to the matching zumero.users table at the destination. You can choose not to do this if, for example, you're moving from a development environment full of dummy users into a production environment where things need to be more locked-down.

If you choose to migrate these users, you can either:

Randomized passwords spare you from re-creating your users (and their associated permissions) without compromising security. Only those users you specifically edit will be allowed to actually do anything.

We're almost ready to migrate our configuration, but first we need to be sure we can migrate safely. To do so, click Check for Issues. If any errors are found (missing columns, etc.) migration won't proceed until they are corrected.

We click Migrate, and see a series of status messages as the migration proceeds.

If we connect to the otherdb database and load the demo DBFile, we see the same synchronized table...

Including a column exclusion carried over from the source...

Users, filters and permissions are all in place, as well.

This is for DBFiles that live on the same database server as the Primary ZSS database. In these cases, you only need to edit the name of the database. ZSS Server will connect using the primary connection string, then switch to the Secondary database.

This is for DBFiles that live on a different database server than the Primary ZSS database, or any DBFile for which you need to use different credentials. Here you'll build a full ODBC connection string.

You can use the Test button to try connecting using the connection string before clicking OK to save it. Be aware that, depending on your system configuration, the connection string needed by the ZSS Server may not work from the system on which ZSS Manager is running.

ZSS Manager will still allow you to save the connect information without testing, or when the test fails.

The first setting in the Rollback Recovery dialog is a permissions setting. In order for the server to import a recovery package from a client, authenticated users must be granted permission to recover the rolled back changes.

Note, though, that even if the client has permission to perform data recovery, the recovery could still fail. This is because the client might only have a filtered view of the rolled back changes, and because there might have been new changes to the database after the rollback. Thus recovery could fail due to a constraint violation or a conflict rule setting.

The second setting in the Rollback Recovery dialog controls what will happen whenever a recovery package is rejected or fails to import for any reason. By default, the server will send a server_rollback_detected error to the client, and report details about why their particular recovery package failed in the Event Viewer entry for the failed sync request. This allows the clients to preserve their local changes and retry again later.

Ideally, after you have given users permission to recover changes, then all clients will have a successful sync after all the changes have been recovered. Unfortunately, there are cases where, due to conflicts or missing changes, some clients are not able to recover. When this happens, you can adjust the client sync retry rule to "abandon changes on the client device and re-sync from the restore point." This will allow their syncs to succeed, and they will no longer be stuck getting an error.

It is recommended that when using this option, users are still given permission to recover rolled-back changes to Zumero. Without this permission, there will be no record of what changes on the client were rolled back. If, on the other hand, the user has permission to report rolled-back changes, then raw information from their failed recovery package will be left in the DBFile's r$dump and r$dump_rows tables. These rows contain JSON-serialized data from the client. Columns requiring conversion will be in their client-side SQLite format. Data in the r$dump and r$dump_rows tables is never used by Zumero itself, and can be deleted at any time.

There is one more setting worth mentioning related to recovering from a database rollback. In order to recover from a rollback, the client needs to store a (usually) small amount of extra data in the client-side database. By default, the client will cap this extra data at "two month's worth of data" (actually sixty-five days). In other words, clients will be able to recover if the database is rolled back to a restore point up to sixty-five days in the past. If the server is rolled back to a point earlier than this "cap", clients will not be able to recover any data, and will not be able to get out of the "server_rollback_detected" error state.

There is no way to modify this cap from ZSS Manager, however administrators wanting to modify it may do so by inserting (or modifying) the rule directly in the database. The rule is stored in the t$rules table. Its "situation" number is one, and the "action" represents the number of days clients will store.

For example, an administrator desiring to be "safer" and have clients store 100 days worth of data for the DBFile named "my_dbfile" would issue the command:

INSERT INTO zumero.my_dbfile$t$rules (sit, act) VALUES (1, 100);

On the other hand, an administrator who is confident that 20 days is enough, and wants to save a little bit of bandwidth and client storage space would issue the command:

INSERT INTO zumero.my_dbfile$t$rules (sit, act) VALUES ('my_dbfile', 1, 20);

Finally, an administrator who does not want clients to be able to recover from a rollback and wants to use the minimum amount of bandwidth and storage could effectively turn off the feature by issuing:

INSERT INTO zumero.rules_dbfile (dbfile, sit, act) VALUES ('my_dbfile', 1, -1);

Azure Active Directory, Azure AD Domain Services, and Azure SQL must all be properly configured to use this authentication method. Additionally if "Azure Active Directory - Integrated" is the desired authentication method then the machine running ZSS Manager must be joined to your Azure Active Directory domain.

The configuration of these services is outside of the scope of this documentation, but more information on their configuration can be found in the Microsoft documentation.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

Once those services are configured and connecting using your preferred Azure AD authentication method via SQL Server Management studio has been tested, there is still one more aspect of Azure AD that must be configured to allow ZSS Manager to connect to your database. An App Registration must be created in Azure AD for ZSS Manager

There are two kinds of change-tracking information, Historical and Row Contents. Imagine these statements:

INSERT INTO mytable (id, appliance, color) VALUES (72, 'refrigerator', 'stainless');
UPDATE mytable SET appliance = 'washer' where id = 72;
UPDATE mytable SET appliance = 'dryer' where id = 72;

When you purge, you can choose to purge Row Contents and Historical Data somewhat independently. Of course, you can't purge Historical Data for a certain transaction without also purging the Row Contents for that transaction as well.

In the example above, purging Row Contents would remove the Zumero data containing the string 'refrigerator'. Purging Historical Data would remove all Zumero information that version 1 had ever existed.

After you purge, there is an increased chance of your client devices encountering an error. The error will depend greatly on the state of the client and the type of purge. For client library versions 2.3 and below, purging Historical Data will always cause client sync errors.

To illustrate this, imagine Ben, a gentle, WiFi enabled bear. In the fall, Ben synced the latest version of the database. Ben then fell into a state of hibernation, and slept for six months. During Ben's hibernation, his Zumero administrator faithfully purged data. When Ben finally awoke in the spring, his database is now six months out of date.

Fortunately, if Ben hadn't made any changes to his client database before hibernating, he won't see any errors when syncing again in the spring. If the administrator had purged Historical Data older than six months ago, Ben will download the entire database again, as if it was the first time syncing with his device. If the administrator had only purged Row Contents, then Ben will only download the changes in the last six months.

However, if Ben did make changes before hibernating, he may get an error.

Once you're used to purging, it is possible to automate the process, so that it happens when there isn't a heavy load on SQL Server. Set up scheduled job in SQL Server to run a statement like this:

DECLARE @t datetime2;
SELECT @t = DATEADD(month, -18, CURRENT_TIMESTAMP);
EXEC [zumero].[PurgeDBFile] @dbfile='foo', @t=@t, @purge_history=0; --@purge_history=0 means purge contents

SELECT @t = DATEADD(month, -20, CURRENT_TIMESTAMP);
EXEC [zumero].[PurgeDBFile] @dbfile='foo', @t=@t, @purge_history=1; --@purge_history=1 means purge contents and history

If a client is in a state where it is getting "purged_data_referenced" errors when it tries to sync, there are a few different ways to proceed.

An incorrect or missing ODBC connection string will cause the server to return 500 errors. To change it, perform the following steps:

If you are attempting to use Trusted_Connection in your connection string, it is possible that the permissions are not set to allow the Zumero server to connect and make changes to your database. During testing, it is often easier to use the sa account, as illustrated in the above example connect string.

These are a series of errors indicate that the license or activation key provided in the IIS site's web.config file is invalid or malformed. The most likely cause is that the license key was copy and pasted incorrectly. Locate the original copy of your license key and follow these steps:

If the error was not resolved by these steps contact support@zumero.com

This error indicates that the number of users connecting to the Zumero server exceeds the limit set in your license key. The Zumero server determines the number of users by examining recent sync activity. In most cases this error indicates that a new license key needs to be purchased for the increased activity. To do so contact sales@zumero.com.

It is also possible that the Zumero server is still considering users who are no longer active syncing against the Zumero server. This could happen if you recently deleted users, decommissioned client devices, or changed authentication methods. In this case Zumero for SQL Server Manager provides a mechanism for removing these users from your active user list.