MobiLink Synchronization Wizard in SQL Anywhere 10

If you work with data that is distributed and stored in databases, then you understand the importance of keeping multiple databases synchronized. Mobilink is a technology that can help with that process. Keep reading to learn more.

Distributed Data and Synchronization

Data that is to be distributed and stored in databases typically requires a synchronization technology to manage the complexities of data distribution. Synchronization refers to how a process can achieve a single unified view of the distributed data within multiple databases. During a synchronization process two databases exchange changes made to the data in each database. Special rules can be defined to handle conflicts, such as when the same piece of data has been changed within both databases.

MobiLink is a synchronization technology where a multiplicity of remote databases can synchronize with a single consolidated database. This has a special attraction for "traveling salesman" type scenarios when the client starts synchronizing either to get the latest value/inventory information, or uploads some sales information. This is even more important when such data will be accessed from mobile devices which may connect to the main database only occasionally.

This tutorial is part 1 of a series focused on understanding how the MobiLink synchronization wizard is used to generate a MobiLink synchronization model. In part 2 of the series, deploying this model to a remote server running on a desktop computer using the Sybase Central Management tool will be described. For both parts the test arrangement has the following components:

Details of table used in synchronization using MobiLink

A step-by-step method of using the MobiLink Synchronization wizard will be described. For this purpose a table, Customers10 in the Test10 database on a Microsoft SQL 2005 Server, is chosen for synchronization. This is just one table from the Northwind database. The details of this table are shown in the next picture. This picture shows only part of the data in this table.

{mospagebreak title=Creating the data source for the MobiLink synchronization setup}

MobiLink uses the ODBC interface for its interaction with databases. A User DSN called Dev10 will be used in the synchronization. The following steps show how you may create an ODBC User DSN.

The ODBC DSN can be created both from inside Sybase Central, or externally using the Windows operating system’s ODBC DataSource Administrator which can be accessed from Start –>Control Panel–>Administrative Tools and clicking on the Administrative Options. This opens the ODBC Data Source Administrator window shown in the next picture.

Click on the Add… button which opens up the Create New Data Source window with a list of available drivers from which the SQL Native Client driver is chosen as shown in the next picture. This driver has been suggested as an appropriate driver to use while interfacing with MS SQL 2005 Server. For recommended drivers see Appendix 1 (in the Summary section). 

Now click on the Finish button to open up the Microsoft SQL Server DSN Configuration window. You are required to provide a name for this data source (which the image shows as already chosen: Dev10). The name of the MS SQL Server 2005 instance is Mysorian on the computer named Hodentek. You may add a description.

Click on the Next button to open the window shown in the next picture. Here you will be asked to describe how you may want to be authenticated. The default is Integrated Windows authentication. For the database that is accessed, the authentication is SQL Server authentication which requires a LoginID and a Password. This option is chosen and the credentials are entered as shown.

When the Next button is clicked in the above window, the following window, Create a New Data Source to SQL Server opens up as shown. In this window you need to change the default database to the database you want to access. Here you will be choosing the database whose tables are going to be used in the synchronization scheme. You may see all the databases by clicking on the drop- down. The Test10 database will be used in this tutorial as mentioned earlier.

Choose Test10 from the drop-down and click on the Next button. The following window opens where you may choose a number of options, such as language for system messages, strong encryption of data, ODBC related log file, and so forth. Here the defaults are chosen.

Click on the Finish button which opens up the ODBC Microsoft SQL Server Setup window shown in the next picture. This shows a summary of all the actions you have taken to configure the User DSN, Dev10.

This may be tested here to verify the connectivity. Click on the button Test Data Source… which opens up the following window, SQL Server ODBC Data Source Test showing that the connection is properly configured for the chosen driver.

When you click OK to this screen you will get back to the previous screen. Click on the OK button one more time and you will get to the first screen of the ODBC Data Source Manager shown here where the DSN Dev10 is added to the list of User DSNs. Close out of this window.

{mospagebreak title=MobiLink Synchronization Wizard}

Create Synchronization Model Wizard is a step-by-step, form-based wizard that helps you define a synchronization model. The model defines the tables and columns that need to be synchronized between a server and a plurality of remote databases on the client computers. It also sets up the various options for the synchronization. The model helps in the mapping between the consolidated database schema and the remote database schema and also the download/upload options for the synchronization process. After the model is created it is deployed to the consolidated and remote databases. Deployment creates the scripts and files needed during synchronization. Deployment will be described in part 2 of this tutorial series.

You invoke the Create a synchronization Model Wizard from within Sybase Central by clicking on the item in MobiLink’s tasks pane shown above, which opens the window shown in the next picture. To begin you need a name for the new synchronization model and a place to save the model file. For this evaluation, Test10Sync is the name of the model and it is saved to the following folder accessed by the Browse button: C:Documents and SettingsJayDesktopSqlAnywhere_FinalArticleFinal. The name of the file that will be created once the model is completely configured is: Test10Sync.mlsm.

Clicking on the Next Button in the above window will open the window shown in the next picture wherein you will check to verify whether the table(s) you plan to synchronize meet the MobiLink‘s primary key requirements.

Primary Key Requirements

For effective synchronization the databases must meet some requirements mostly to do with primary keys in the tables. These are:

  • All tables participating in a synchronization setup should have primary keys.
    It is through primary keys that the tables match up their rows participating
    in the synchronization.
  • The application should not update the primary keys.
  • New rows added to the table have unique primary keys across all databases participating in synchronization.

Read the instructions on this screen about how you may ensure primary key uniqueness and click on the Next Button.

This opens up the window shown in the following picture where you choose a data source that defines the schema that will be used in the consolidated database.

{mospagebreak title=Data Source for the consolidated database schema}

You can choose as the data source a database from a number of vendors –Microsoft SQL Server, Oracle, IBM DB2 UDB, SQL Anywhere, and Sybase Adaptive Server Enterprise. For this evaluation the MS SQL Server 2005 on the Windows platform (Windows XP Professional Media Center Edition) will be used. For each of the databases, database-specific scripts add the MobiLink system set up objects needed during deployment (see "objects added in this set up" in Appendix 2 copied from the MS SQL2005 Server Management Studio after the model was created).

Click on the Choose a consolidated database… button to connect to the consolidated database. The "Connect To Consolidated Database" window pops up; in it, you need to provide UserID, Password and the ODBC Source name. When you complete the sub-tasks (completing the authentication process) the consolidated database will finally be chosen and the details shown under the button in the above screen will be filled up.

If this is the first time the DSN was created you may click on the Browse… button which opens showing a list of all user DSNs on the machine as shown in the next picture.

When you click on the OK button, the Dev10 DSN will be available to the authentication window shown earlier. Finally when you click on the OK button  with all three items entered, the following window opens.

When you click on the Yes button, the Mobilink message screen shows up, followed by the information about your consolidated database schema updating your earlier screen as shown below. The ownership is shown as that of "dbo" since the credentials entered were those of the dbo. A specific user was not configured for the DSN.

Creating the Remote Database Schema

When you click on the Next button on the above window you will be taken to the next window shown where you can create the schema for the remote database. Since the schema for the remote database has not been created yet, the first option, No, create a new remote database schema is chosen. MobiLink has the ability to create a new UltraLite, or SQL Anywhere database based on an existing consolidated database schema. You may also access an existing remote database instead of creating a completely new remote database schema. Click on the Next button.

This opens up the screen shown in the next picture where you need to identify the objects that need to be included in the schema of the remote database. The objects from the MS SQL 2005 Server database show up in this window. The Customers table which was shown earlier in the MS SQL 2005 Server database ‘Test10‘ is the only table object showing here. It is also possible to have a larger number of tables but for this initial documentation a single table was chosen. The schema for this consolidated database object will be used for the remote database schema definition. By placing a check mark against the table(s), you will be choosing those table(s) for synchronization. Click on the Next button.

Type of download during synchronization

During the synchronization process, all changes to the remote database are uploaded to the consolidated database, and all changes to the consolidated database are then downloaded to the remote database. The changes to the remote database can be determined by examining its transaction log. However, the MobiLink server is not able to access the transaction log of the consolidated database. Hence the data to download from the consolidated database must be determined using a timestamp, custom logic, or by simply downloading the entire dataset. The default, Timestamp-based download is chosen for this evaluation. This option can be changed later, if so desired.

{mospagebreak title=Choosing download options}

Clicking on the Next button on the above screen brings up the window shown in the next picture. Here the implementation options for timestamp-based download are chosen. If the default option is used, a new column, last-modified, will be added to each of the consolidated tables, if it did not already exist. This simplifies the download process which does not require a join with a shadow table if the other option were to be chosen. It will however modify the schema of the consolidated table due to the extra column, which could impact an existing application.

Since determining the data to download from the consolidated database cannot be determined from the transaction log, there needs to be a way to track what data has been deleted on the consolidated database in order to ensure it is also deleted on the remote database. Similar to before, the deletions can be tracked using a shadow table or by adding a column to the table which indicates whether or not the row has been deleted.

Choosing a Subset of data to download

Click on the Next button in the above window to open the next window. Since the MobiLink software works with a large number of remote databases and presumes that all of them may not need the same data (from the same table/tables) you can choose a subset of the data to be synchronized with the remote database. Synchronization scripts can be written so that different remote databases may synchronize different sets of data (partitioned data) from the source data. Here again the default is chosen as this is the first remote database.

Conflict detection options

Clicking the Next button on the previous screen takes you to the window where you make decisions as to how your upload conflicts needs to be handled. During upload, conflicts can occur between the remote database and the consolidated database if the same data was changed in both databases. There are three options for dealing with conflicts:

1) Ignore them.
2) Detect a conflict if any of the data within the same row has 
   been changed in both databases.
3) Detect a conflict only if the data within the same column of 
   the same row has been changed in both databases.

The default option, which does not check for conflicts and gives the best performance, is chosen. Click on the Next button.

{mospagebreak title=Specifying names for Publication and Script version}

This brings you to the window where you make choices regarding publication and script version. The remote tables that are going to be synchronized are grouped into a publication and the synchronization logic will be assigned a script version. The script version tracks the different synchronization logic you might create in your application for a specific publication. Both of these require names by which they can be called. These can use defaults generated by the wizard as shown. An optional description may also be added as shown in the following picture.

When you click on the Next button on the above window you will be presented with the following window. At this stage the MobiLink synchronization model Test10Sync is defined. This model may be deployed as is, or it is possible to make further changes. For this tutorial click on the Finish button as it is intended to be deployed as is. An XML file Test10Sync.mslm will be created in the chosen directory containing the details of the model.

Review of Test10Sync.mlsm file

The XML content of the file generated by the model shown in the next listing:

 Listing 1: <?xml version=’1.0′ encoding=’UTF-8′?><sync-model filespec-version="1" version-major="10" version-minor="0" version-patch="0" version-build="2465" name="Test10Sync" description="Synchronization Model for Customers10 table in the MS SQL Server 2005’s Test10 Database" script-version="Test10Sync" publication="Test10Sync"><auth-settings enabled="false" type="pop3" /><remote factory="sa"><schema><table name="Customers10" owner="dbo" catalog="Test10" type="table"><column name="CustomerID" isprimarykey="true" nullable="false" ordinal="1" category="string" jdbc="nchar" typename="nchar" length="5" /><column name="CompanyName" isprimarykey="false" nullable="false" ordinal="2" category="string" jdbc="nvarchar" typename="nvarchar" length="40" /><column name="ContactName" isprimarykey="false" nullable="true" ordinal="3" category="string" jdbc="nvarchar" typename="nvarchar" length="30" /><column name="ContactTitle" isprimarykey="false" nullable="true" ordinal="4" category="string" jdbc="nvarchar" typename="nvarchar" length="30" /><column name="Address" isprimarykey="false" nullable="true" ordinal="5" category="string" jdbc="nvarchar" typename="nvarchar" length="60" /><column name="City" isprimarykey="false" nullable="true" ordinal="6" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="Region" isprimarykey="false" nullable="true" ordinal="7" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="PostalCode" isprimarykey="false" nullable="true" ordinal="8" category="string" jdbc="nvarchar" typename="nvarchar" length="10" /><column name="Country" isprimarykey="false" nullable="true" ordinal="9" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="Phone" isprimarykey="false" nullable="true" ordinal="10" category="string" jdbc="nvarchar" typename="nvarchar" length="24" /><column name="Fax" isprimarykey="false" nullable="true" ordinal="11" category="string" jdbc="nvarchar" typename="nvarchar" length="24" /></table></schema></remote><consolidated factory="mss"><schema name="consolidatedSchema"><table name="Customers10" owner="dbo" catalog="Test10" type="table"><column name="CustomerID" isprimarykey="true" nullable="false" ordinal="1" category="string" jdbc="nchar" typename="nchar" length="5" /><column name="CompanyName" isprimarykey="false" nullable="false" ordinal="2" category="string" jdbc="nvarchar" typename="nvarchar" length="40" /><column name="ContactName" isprimarykey="false" nullable="true" ordinal="3" category="string" jdbc="nvarchar" typename="nvarchar" length="30" /><column name="ContactTitle" isprimarykey="false" nullable="true" ordinal="4" category="string" jdbc="nvarchar" typename="nvarchar" length="30" /><column name="Address" isprimarykey="false" nullable="true" ordinal="5" category="string" jdbc="nvarchar" typename="nvarchar" length="60" /><column name="City" isprimarykey="false" nullable="true" ordinal="6" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="Region" isprimarykey="false" nullable="true" ordinal="7" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="PostalCode" isprimarykey="false" nullable="true" ordinal="8" category="string" jdbc="nvarchar" typename="nvarchar" length="10" /><column name="Country" isprimarykey="false" nullable="true" ordinal="9" category="string" jdbc="nvarchar" typename="nvarchar" length="15" /><column name="Phone" isprimarykey="false" nullable="true" ordinal="10" category="string" jdbc="nvarchar" typename="nvarchar" length="24" /><column name="Fax" isprimarykey="false" nullable="true" ordinal="11" category="string" jdbc="nvarchar" typename="nvarchar" length="24" /><column name="last_modified" isprimarykey="false" nullable="false" ordinal="12" category="other" jdbc="datetime" typename="datetime" /></table></schema></consolidated><notifier-settings enabled="false" isolation-level="0" poll-interval="5m" /><sync-settings conflict-detection="none" conflict-id-column="process_id" conflict-resolution="consolidated" conflict-resolution-new-table="[table]_new" conflict-resolution-old-table="[table]_old" delete-column="deleted" deleted-value="Y" delete-mode="shadow" delete-table-pattern="[table]_del" delete-timestamp-column="last_modified" delete-insert-trigger-pattern="[table]_ins" delete-delete-trigger-pattern="[table]_del" download-deletes="true" download-index-pattern="[table]_ml" download-subset="none" download-timestamp-column="last_modified" download-timestamp-from-shadow-table="false" download-timestamp-table-pattern="[table]_mod" download-timestamp-trigger-pattern-del="[table]_del" download-timestamp-trigger-pattern-ins="[table]_ins" download-timestamp-trigger-pattern-upd="[table]_upd" download-type="timestamp" merge-existing-triggers="false" merge-generated-triggers="true" non-deleted-value="N" remote-ds-join="false" remote-ds-non-join-column="sync_remote" user-ds-join="false" user-ds-non-join-column="sync_user" /><table-mappings><table-mapping remote="Test10.dbo.Customers10" consolidated="Test10.dbo.Customers10" direction="both"><sync-settings conflict-detection="none" conflict-id-column="process_id" conflict-resolution="consolidated" conflict-resolution-new-table="[table]_new" conflict-resolution-old-table="[table]_old" delete-column="deleted" deleted-value="Y" delete-mode="shadow" delete-table-pattern="[table]_del" delete-timestamp-column="last_modified" delete-insert-trigger-pattern="[table]_ins" delete-delete-trigger-pattern="[table]_del" download-deletes="true" download-index-pattern="[table]_ml" download-subset="none" download-timestamp-column="last_modified" download-timestamp-from-shadow-table="false" download-timestamp-table-pattern="[table]_mod" download-timestamp-trigger-pattern-del="[table]_del" download-timestamp-trigger-pattern-ins="[table]_ins" download-timestamp-trigger-pattern-upd="[table]_upd" download-type="timestamp" merge-existing-triggers="false" merge-generated-triggers="true" non-deleted-value="N" remote-ds-join="false" remote-ds-non-join-column="sync_remote" user-ds-join="false" user-ds-non-join-column="sync_user" /><column-mapping remote="CustomerID" consolidated="CustomerID" /><column-mapping remote="CompanyName" consolidated="CompanyName" /><column-mapping remote="ContactName" consolidated="ContactName" /><column-mapping remote="ContactTitle" consolidated="ContactTitle" /><column-mapping remote="Address" consolidated="Address" /><column-mapping remote="City" consolidated="City" /><column-mapping remote="Region" consolidated="Region" /><column-mapping remote="PostalCode" consolidated="PostalCode" /><column-mapping remote="Country" consolidated="Country" /><column-mapping remote="Phone" consolidated="Phone" /><column-mapping remote="Fax" consolidated="Fax" /></table-mapping></table-mappings><events template-checksum="1289762775"><connection-event id="begin_connection"><generated-script type="SQL" error="false">SET NOCOUNT ON </generated-script></connection-event><connection-event id="authenticate_user" /><connection-event id="end_publication" /><table-event id="download_cursor" table="Test10.dbo.Customers10"><generated-script type="SQL" error="false">SELECT "dbo"."Customers10"."CustomerID", "dbo"."Customers10"."CompanyName", "dbo"."Customers10"."ContactName", "dbo"."Customers10"."ContactTitle", "dbo"."Customers10"."Address", "dbo"."Customers10"."City", "dbo"."Customers10"."Region", "dbo"."Customers10"."PostalCode", "dbo"."Customers10"."Country", "dbo"."Customers10"."Phone", "dbo"."Customers10"."Fax" FROM "dbo"."Customers10" WHERE "dbo"."Customers10"."last_modified" >= {ml s.last_table_download} </generated-script></table-event><table-event id="upload_insert" table="Test10.dbo.Customers10"><generated-script type="SQL" error="false">/* Insert the row into the consolidated database. */ INSERT INTO "dbo"."Customers10" ( "CustomerID", "CompanyName", "ContactName", "ContactTitle", "Address", "City", "Region", "PostalCode", "Country", "Phone", "Fax" ) VALUES ( {ml r."CustomerID"}, {ml r."CompanyName"}, {ml r."ContactName"}, {ml r."ContactTitle"}, {ml r."Address"}, {ml r."City"}, {ml r."Region"}, {ml r."PostalCode"}, {ml r."Country"}, {ml r."Phone"}, {ml r."Fax"} ) </generated-script></table-event><table-event id="upload_delete" table="Test10.dbo.Customers10"><generated-script type="SQL" error="false">/* Delete the row from the consolidated database. */ DELETE FROM "dbo"."Customers10" WHERE "CustomerID" = {ml r."CustomerID"} </generated-script></table-event><table-event id="upload_update" table="Test10.dbo.Customers10"><generated-script type="SQL" error="false">/* Update the row in the consolidated database. */ UPDATE "dbo"."Customers10" SET "CompanyName" = {ml r."CompanyName"}, "ContactName" = {ml r."ContactName"}, "ContactTitle" = {ml r."ContactTitle"}, "Address" = {ml r."Address"}, "City" = {ml r."City"}, "Region" = {ml r."Region"}, "PostalCode" = {ml r."PostalCode"}, "Country" = {ml r."Country"}, "Phone" = {ml r."Phone"}, "Fax" = {ml r."Fax"} WHERE "CustomerID" = {ml r."CustomerID"} </generated-script></table-event><table-event id="upload_fetch_column_conflict" table="Test10.dbo.Customers10" /><table-event id="upload_old_row_insert" table="Test10.dbo.Customers10" /><table-event id="download_delete_cursor" table="Test10.dbo.Customers10"><generated-script type="SQL" error="false">SELECT "Customers10_del"."CustomerID" FROM "Customers10_del" WHERE "Customers10_del"."last_modified" >= {ml s.last_table_download} </generated-script></table-event><table-event id="upload_fetch" table="Test10.dbo.Customers10" /><table-event id="upload_new_row_insert" table="Test10.dbo.Customers10" /><table-event id="resolve_conflict" table="Test10.dbo.Customers10" /></events><consolidated-connection-info><connection-info classname="com.sybase.asa.logon.GenericConnectionInfo" data="UID=sa;DSN=Dev10" /></consolidated-connection-info></sync-model>

 

{mospagebreak title=Fine tuning the model using the Sybase Central Management Interface}

When the model is completed, the model details are also available in the MobiLink Model Tasks list in Sybase Central as shown in the next picture. In this window you can perform several activities related to the synchronization model and make changes if needed. It allows a wide range of tuning using the graphical interface.

When you double click the icon of the model in the above view of Sybase Central, you can access other details that you may modify before deployment. The next picture shows the Mappings, in particular the column mappings. The details in the lower pane will not appear in the default view of this window. When the Mappings tab is in view, you need to click in the region shown by annotation to see the column details. For the top tab, Mappings, you can see a number of tabs in the bottom. Each of these represents graphically the choices made during the model generation.

By clicking on the "Dir" column you can choose any of the three options to be effective while synchronizing as shown in the next picture. Here the ‘Bidirectional‘ option (default) is chosen for this exercise. The other options for deleted rows and conflict resolution were chosen during model generation. In the present tutorial only one table was chosen, but you may switch to another table/tables if such were the case. You could also specify a different subset for the download. Of course in this case all remote databases (only one in the present case) receive the same data.

When you tab to the ‘Events‘ tab you will see the display shown in the next picture. The drop-down list can be used to add other events if necessary, but the script must be created by the developer (Sybase Central will not provide scripts).

Tabbing to the Authentication tab displays other authentication options to access the model. In the present case, the default, MobiLink’s authentication, will be used. Email, POP3, and LDAP are other options.

Tabbing to the next tab, Notifications, displays the view shown in the next picture. The Download cursor seen earlier in the events is shown here. The polling is every 30 seconds by default, which may be changed (30 seconds to one hour). The isolation level choices are also shown. For details on isolation levels and how they affect performance, seek information at the SQL Anywhere web site. In general the higher the numeric value of isolation level the higher the protection but the lower the concurrency. At isolation level 1, which is the default in this exercise, dirty reads are prevented with greater cursor stability.

Model related objects in Sybase Central

After the model is created, model related information may be accessed in Sybase Central from the MobiLink plug-in. In order the access this information you need to log in to the Consolidated database. This gives access to the various objects of the model in MobiLink as shown in Appendix 3.

{mospagebreak title=Summary}

This tutorial showed step-by-step how to create a synchronization model using the MobiLink plug-in in SQL Anywhere 10. The model created could be deployed to synchronize with a table in the MS SQL 2005 Server database. However, the procedure is the same for any number of tables. The tutorial also described the model as represented on Sybase Central’s Administration panel. Accessing the model and tweaking the synchronization parameters using the graphical user interface adds a great deal of flexibility. The next tutorial describes how this model may be deployed using the Deployment Wizard.

Appendix1: Recommended Version of Microsoft SQL Native Client
                      (From SQL Anywhere web site)

Appendix 2: Objects added to SQL 2005 Server after the model is created

a. Tables added

b. Views added

c. Stored Procedures added

d. Scalar-valued Functions

Appendix 3: Objects of the synchronization Model in MobiLink Plug-in

a. Overview of objects

b. Tables (dbo)

b. Tables (INFORMATION SCHEMA)

b. Tables (sys) [partially shown]

One thought on “MobiLink Synchronization Wizard in SQL Anywhere 10

  1. Mobile device management is expected to grow very fast in the coming years and Sybase has a large share of that market already. How do you get your data synchronized with the data stored in the server?The MobiLink described in the article is used for this synchronization. The article describes the creation of a Model using a wizard which will be deployed on a remote database described in a forthcoming article. While the data can sit on SQL, Oracle,and Sybase servers, remote clients using the SqlAnywhere databases can synchronize their data with them using MobiLink technology.
    I look forward to your comments, suggestiosn and questions. Sincerely, Jay

[gp-comments width="770" linklove="off" ]