Creating remote databases

Any SQL Anywhere database can be converted for use as a remote database in a MobiLink installation. You can also create a new SQL Anywhere remote database that uses all or part of the schema of the consolidated SQL Anywhere database. 

You create the database on your desktop using the SQL Central SQL Anywhere plug-in, the Create SA Database utility in the Database painter, or another tool. If your database uses an English character set, use the 1252 Latin1 collation sequence.

To use a database as a remote database for MobiLink synchronization, you need to create at least one publication and MobiLink user, then add a subscription to the publication for the user. See Creating and modifying publications, Creating MobiLink users, and Adding subscriptions.

Remote database schemas

Tables in a remote database need not be identical to those in the consolidated database, but you can often simplify your design by using a table structure in the remote database that is a subset of the one in the consolidated database. Using this method ensures that every table in the remote database exists in the consolidated database. Corresponding tables have the same structure and foreign key relationships as those in the consolidated database.

Tables in the consolidated database frequently contain extra columns that are not synchronized. Extra columns can even aid synchronization. For example, a timestamp column can identify new or updated rows in the consolidated database. In other cases, extra columns or tables in the consolidated database might hold information that is not required at remote sites.

Creating and modifying publications

You create publications using SQL Central or the SQL CREATE PUBLICATION statement. In SQL Central, all publications and articles appear in the Publications folder. This section describes how to create publications in SQL Central. For information about creating and modifying publications using SQL, see the online MobiLink - Client Administration book.

Connecting to the database in SQL Central

You use the SQL Anywhere plug-in in SQL Central, not the MobiLink Synchronization plug-in, to work with MobiLink clients and remote databases. For information on starting SQL Central from the PowerBuilder design time environment, see the Users Guide.

You must have DBA authority to create or modify publications, MobiLink users, and subscriptions.

To connect to the database in SQL Central:

  1. Start SQL Central, select Connections>Connect with SQL Anywhere 16 from the SQL Central menu bar.

  2. On the Identification page in the Connect dialog box, enter DBA as the user name and SQL as the password, select or browse to the data source name or file and click OK.

Publishing all the rows and columns in a table

The simplest publication you can create is a single article that consists of all rows and columns of one or more tables. The tables must already exist.

To publish one or more entire tables in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the Publications folder and select File>New>Publication from the SQL Central menu.

  3. Type a name for the new publication and click Next.

  4. On the Specify Tables page, select a table from the list of available tables and click Add.

    The table appears in the list of selected tables on the right.

  5. Optionally, add more tables. The order of the tables is not important.

  6. Click Finish.

Publishing only some columns in a table

You can create a publication that contains all the rows but only some of the columns of a table.

To publish only some columns in a table in SQL Central:

  1. Follow the first four steps of the procedure in Publishing all the rows and columns in a table.

  2. Click Next. On the Specify Columns page, double-click the table's icon to expand the list of available columns, select each column you want to publish, and click Add.

    The selected columns appear on the right.

  3. Click Finish.

Publishing only some rows in a table

You can create a publication that contains some or all of the columns in a table, but only some of the rows. You do so by writing a search condition that matches only the rows you want to publish.

In MobiLink, you can use the WHERE clause to exclude the same set of rows from all subscriptions to a publication. All subscribers to the publication upload any changes to the rows that satisfy the search condition.

To create a publication using a WHERE clause in SQL Central:

  1. Follow the first four steps of the procedure in Publishing all the rows and columns in a table, and optionally the first two steps of the procedure in Publishing only some columns in a table.

  2. Click Next. On the Specify Where Clauses page, select the table and type the search condition in the lower box.

    Optionally, you can use the Insert dialog box to help you format the search condition.

  3. Click Finish.

Adding articles

You can add articles to existing publications.

To add articles in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the Publications folder and double-click the name of the publication to which you want to add an article.

  3. Select File>New>Article from the SQL Central menu.

  4. In the Create Article wizard, select a table and click Next.

  5. If you want only some columns to be synchronized, select the Selected Columns radio button and select the columns.

  6. If you want to add a WHERE clause, click Next and enter the clause.

  7. Click Finish.

Modifying and removing publications and articles

You can modify or drop existing publications in SQL Central by navigating to the location of the publication and selecting Properties or Delete from its pop-up menu. You can modify and remove articles in the same way.

Publications can be modified only by the DBA or the publication's owner. You must have DBA authority to drop a publication. If you drop a publication, all subscriptions to that publication are automatically deleted as well.

Avoid altering publications in a running MobiLink setup

Altering publications in a running MobiLink setup is likely to cause replication errors and can lead to loss of data unless carried out with care.

Creating MobiLink users

MobiLink users are not the same as database users. Each type of user resides in a different namespace. MobiLink user IDs can match the names of database users, but there is no requirement that they match.

To add a MobiLink user to a remote database in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the MobiLink Users folder and select File>New>User from the SQL Central menu.

  3. Enter a name for the MobiLink user.

    The name is supplied to the MobiLink synchronization server during synchronization. In production databases, each user name is usually added to the consolidated database, then provided to the individual user.

  4. Click Finish.

To configure MobiLink user properties in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the MobiLink Users folder, right-click the MobiLink user, and select Properties from the pop-up menu

  3. Change the properties as needed.

To drop a MobiLink user in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the MobiLink Users folder, right-click the MobiLink user, and select Delete from the pop-up menu.

Dropping MobiLink users

You must drop all subscriptions for a MobiLink user before you drop the user from a remote database.

Adding MobiLink users to the consolidated database

The consolidated database contains a table called ml_user that is used to authenticate the names of MobiLink users when a synchronization is requested. When you add a user to a remote database, you need to be sure that the user is also added to the ml_user table.

You can add users automatically by selecting the Automatic Addition of Users check box in the MobiLink Synchronization Server Options dialog box and then starting the server. You open this dialog box from the Utilities folder in the Database painter or Database Profiles dialog box. You can also start the server from a command prompt, passing it the -zu+ switch.

Any users defined in the remote database are added to the ml_user table in the consolidated database, as long as the script for the authenticate_user connection event is undefined. Typically the -zu+ switch is not used in a production environment. Names are usually added to the ml_user table in the consolidated database, then added to each of the remote databases. Each user is given a unique name and optional password.

Adding subscriptions

A synchronization subscription links a particular MobiLink user with a publication. You must have at least one publication and one user to create a subscription.

A subscription can also carry other information needed for synchronization. For example, you can specify the address of the MobiLink server and other connection options. Values for a specific subscription override those set for individual MobiLink users.

Overriding options in the wizard

You can override the MobiLink server name and port set for the subscription and user with settings in the ASA MobiLink Synchronization wizard in PowerBuilder.

Synchronization subscriptions are required in MobiLink SQL Anywhere remote databases. Server logic is implemented through synchronization scripts, stored in the MobiLink system tables in the consolidated database.

A single SQL Anywhere database can synchronize with more than one MobiLink synchronization server. To allow synchronization with multiple servers, create different subscriptions for each server.

To add a subscription for a MobiLink user in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the Publications folder, select the publication for which you want to enter a subscription, select the Synchronization Subscriptions tab in the right pane of SQL Central, then select File>New>Synchronization Subscription from the menu bar.

    Instead of creating a new subscription in the Publications folder, you can create one in the MobiLink Users folder by double-clicking the user for whom you want to create a subscription, and then selecting File>New>Synchronization Subscription from the menu bar.

  3. In the Create Synchronization Subscription wizard, select the user for whom you want to enter a subscription and click Finish.

    If you started the wizard from the MobiLink Users folder, the wizard prompts you to select the publication to which you want to subscribe. In this case, select the publication and click Finish.

To modify a subscription in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the MobiLink Users folder and double-click the name of the MobiLink user who owns the subscription you want to modify.

  3. On the Synchronization Subscriptions tab, right-click the subscription you want to modify and select Properties from the pop-up menu.

  4. Change the properties as needed on the Connection and Extended Options pages of the Synchronization Subscription Properties dialog box.

To delete a synchronization subscription in SQL Central:

  1. Connect to SQL Central as described in Connecting to the database in SQL Central.

  2. Open the MobiLink Users folder and double-click the name of the MobiLink user who owns the subscription you want to delete.

  3. On the Synchronization Subscriptions tab, right-click the subscription you want to delete and click Delete.

  4. Click Yes in the Confirm Delete dialog box.