Requirements

It is expected that the reader is familiar with basic functionality and setup of Mobilink. A quick start guide for Mobilink can be found here.

Introduction

The example in this document uses one consolidated database with two remote databases, all with matching schema. It also requires a working synchronization model with synchronization profiles for each remote database. The Initial Setup section goes through the steps for creating this environment.

Once this basic setup is complete, this document goes through the necessary steps in applying Mobilink Agents and remote tasks to centralize the remote databases for synchronization.

Initial Setup

Creating the Databases and DSNs

First, the required databases need to be created with the appropriate schema.

Open a command prompt in the appropriate directory and run the following commands to create folders for the databases and Mobilink setup:

mkdir “Centralize Remotes Demo”
cd “Centralize Remotes Demo”
mkdir “ConsolidatedDB” “Remote1” “Remote2” “Mobilink Setup” “Debugging”

To create the databases, run the following from the Centralize Remotes Demo directory:

dbinit “ConsolidatedDB\Consolidated.db”
dbinit “Remote1\Remote1.db”
dbinit “Remote2\Remote2.db”

Now start each database:

dbsrv12 “ConsolidatedDB\Consolidated.db” -o “Debugging\ConsLog.log” -zr ALL
dbsrv12 “Remote1\Remote1.db” -o “Debugging\Remote1Log.log” -zr ALL
dbsrv12 “Remote2\Remote2.db” -o “Debugging\Remote2Log.log” -zr ALL

Create a DSN for each newly created database to make connections easier. Open the ODBC Data Source Administrator (Start > Run > odbcad32) and create a new System DSN for each database with the following settings:

• Ensure it is a SQL Anywhere 12 DSN
• Make the data source name the same as the database name (i.e. Consolidated)
• User ID is dba; Password is sql
• Action is connect to a running database on this computer
• Server name is the appropriate engine name hosting each database (from the previous commands, these servers should be named Consolidated, Remote1, and Remote2)

Test the connection to ensure each DSN is configured correctly. This could also be done using the dbdsn command. More information of the dbdsn utility can be found here.

Preparing the Databases for a Mobilink Synchronization

Using ISQL, connect to each of the databases and run the following scripts:

//Run the create table commands on all Databases
create table JobDescriptions(
    JobID integer,
    JobDescription char(100),
    primary key(JobID))

create table Employees(
    EmployeeID integer,
    EmployeeName char(30),
    EmployeeJobID integer,
    foreign key(EmployeeJobID) references JobDescriptions(JobID),
    primary key(EmployeeID))

//Run the insert commands on only the Consolidated
insert into JobDescriptions
values(1000,'Tech Support');
insert into JobDescriptions
values(1001,'Tech Writing');
insert into JobDescriptions
values(1002,'Engineering' );

insert into Employees
values(1,'Mike',1000);
insert into Employees
values(2,'Joe',1001);
insert into Employees
values(3,'Bob',1002);

Each database should have a schema design similar to this:

Setting up a Mobilink Project

A working synchronization model and a Mobilink Project are required for setting up Agents and remote tasks. Open Sybase Central and connect to all 3 databases. Right-click Mobilink 12 and create a new Project. Use Sybase Central and the provided wizards to step through the setup:

Call it Mobilink Setup and store it in your Mobilink Setup Folder:

Add a consolidated database to the project and use the appropriate DSN to connect to it:

Choose to Create a new model:

Add a remote schema name called Standard_Schema and click Finish:

Work through the wizard to complete the setup until the start of the Create Synchronization Model Wizard. Name the new synchronization model Sync:

Choose the database named Consolidated that was configured earlier:

Obtain the remote database schema from Remote1:

Select both tables to be synchronized:

Set both mappings to be download to remote only and click Next then Finish:

Lastly, deploy this model to the entire setup. Open Synchronization Models in the Mobilink Setup and right-click Sync to deploy. Choose to deploy to the Consolidated and Remote database.

Deploy this directly to the Consolidated instead of creating a SQL file to apply the changes:

Choose an existing SQL Anywhere database:

Similar to the Consolidated, deploy this directly to Remote1 instead of creating a SQL file. If there were more than two remote databases to work with, it would be easier to use the SQL file to apply changes to each remote.

Set the username and password for this synchronization profile to Remote1 and sql respectively.

Click Finish to accept the rest of the defaults. Remote1 should be ready to synchronize. Run through the deployment wizard again for the Remote2 database. To save time and minimize effort, choose to deploy using the wizard initialized with the last used settings. That way, only a few changes have to be made.
Note: If an error states that the schema needs to be updated, right-click Sync and choose to Update Schema. The wizard for this process is simple and should only take a few seconds.

Ensure Synchronization is Functioning Properly

Before centralizing the system, ensure the underlying Mobilink setup is working by testing synchronizations on both remotes in Sybase Central.

To do this, start the Mobilink server on the Consolidated database. Include the –zu+ option to make adding new Mobilink users easier. Run the following command on the command line:

mlsrv12 –c “DSN=Consolidated” –zu+ -o Debugging\MlSrvLog.log

Now that the Mobilink server has started, test the synchronizations. In Sybase Central, open the Remote1-DBA tab and navigate to Synchronization Profiles. Sybase Central may need to be refreshed before displaying the new Synchronization profiles.

Right-click Sync_Remote1 and choose Synchronize. This should populate the Remote1 tables with the data from the Consolidated. Repeat the previous steps to verify that Remote2 can synchronize as well

Centralizing the Synchronizations

Creating the Mobilink Agent

A Mobilink Agent is required to run remote tasks on the remote databases. Since this demonstration is set up on a single machine, only one Mobilink Agent is required to manage both remote databases.

To create the Agent, run the following command:

mlagent -cr -a Agent1 -u Remote1 -p sql -x tcpip{host=localhost}

The above command creates an Agent named Agent1, using the Mobilink username and password Remote1 and sql. Agent1 needs to be run before it will appear in the Mobilink Setup:

mlagent

Running mlagent should leave the Agent console window open showing all of its activities. Successful configuration of the agent can be verified by opening the Mobilink Setup in Sybase Central, and refreshing the Agents tab to see the newly created Agent1.

Now that an Agent has been created, it needs to be configured. Right-click Agent1 and select Properties. Change its Synchronization Interval to 20 seconds, as well as its Administration polling interval to 20 seconds. Under the Managed Remote Databases tab, add a new Managed Database. Choose Standard_Schema and use DSN=Remote1 as the connection string.

In order to add another managed remote database (Remote2), another remote schema needs to be created.

Under Remote Schema Names, right-click and add a new Remote Schema Name. Call it Standard_Schema2 and click OK. Go back to Agent1 and add a new Managed Remote Database using the new schema.

There is now a Mobilink Agent managing both remote databases.

Creating Remote Tasks for Mobilink Agents

Mobilink Agents will not execute anything until a remote task is given to them. This is also done in the Mobilink Setup in Sybase Central. Right-click Remote Tasks and choose New Task.

Call this task Synchronize_Remote1 and assign it the Standard_Schema.

Trigger the task based on a schedule and when it is received.

For the Start Time and Date configuration, leave all defaults and click Next. For Repetition, set it to repeat every 20 seconds and click Finish.

There should now be an unconfigured remote Task called Synchronize_Remote1. There are 2 command types to synchronize a database. Choosing the EXEC SQL option gives the option of adding extra features in the future that the Synchronize Remote Task does not include.

A second Remote Task is required to synchronize the Remote2 database. Repeat the previous steps but with Standard_Schema2 to create a task for Remote2. Also, specify a different port to avoid conflicts.

The EXEC SQL changes slightly as well.

Remote Task Deployment

The remote tasks need to be deployed to the Agent before they take effect. Right-click Synchronize_Remote1 and Deploy. Deploy it to the Consolidated database configured earlier.

Make the Recipients Specific agents and choose Agent1. Click Finish.

Repeat the previous steps for Synchronize_Remote2.
The Remote Tasks in Sybase Central should look similar to this:

Shut down the Mobilink Agent and restart it with the mlagent command to see the changes take effect immediately. The Mobilink Agent Console should now display successful synchronizations every 20 seconds.