Lesson 6: Creating and modify a synchronization model

This lesson assumes you have completed all preceding lessons. See Lesson 1: Designing the schemas.

In this lesson, you create a synchronization model for your consolidated database. The Create Synchronization Model Wizard provides step by step instructions for setting up synchronization between the consolidated database and remote database.

 Create and modify a synchronization model

  1. On the Welcome page, type sync_ase in the What Do You Want To Name The New Synchronization Model field, and click Next.

  2. On the Primary Key Requirements page, select all three checkboxes. Click Next.

  3. Choose the ase_cons consolidated database from the list, and click Next.

  4. Click No, Create A New Remote Database Schema, and click Next.

  5. On the New Remote Database Schema page, in the Which Consolidated Database Tables And Columns Do You Want To Have In Your Remote Database list, choose the following tables:

    • au_pix
    • authors
    • discounts
    • sales
    • salesdetail
    • stores
    • titleauthor
    • titles

  6. Click Next.

  7. Click Timestamp-based Download, and click Next.

    Timestamp-based downloads minimize the amount of data that is transferred because only data that has been updated since the last download is transmitted.

  8. On the Timestamp Download Options page, click Use Shadow Tables To Hold Timestamp Columns, and click Next.

    Using shadow tables is often preferred because it does not require any changes to existing tables.

  9. Perform the following tasks on the Download Deletes page:

    1. Click Yes on the Do You Want Data Deleted On The Consolidated Database To Be Deleted On The Remote Databases option.

    2. Click Use Shadow Tables To Record Deletions.

      MobiLink creates shadow tables on the consolidated database to implement deletions that need to be synchronized.

    3. Click Next.

  10. Click Yes, Download the Same Data to Each Remote, and click Next.

    You specify how to download specific data to a remote database by using custom logic when editing the synchronization model.

  11. Click No Conflict Detection, and click Next.

    Although this tutorial specifies no conflict detection, many applications require conflict detection.

  12. Perform the following tasks on the Publication, Script Version and Description page:

    1. In the What Do You want To Name The Publication field, type sync_ase_publication.

    2. In the What Do You Want To Name The Script Version field, type sync_ase_scriptversion.

      The publication is the object on the remote database that specifies what data is synchronized. MobiLink server scripts define how uploaded data from remotes should be applied to the consolidated database, and script versions group scripts. You can use different script versions for different applications, allowing you to maintain a single MobiLink server while synchronizing different applications.

    3. Click Finish.

  13. Click View » Folders.

  14. In the left pane of Sybase Central under MobiLink 12, expand ase_project, Synchronization Models, sync_ase.

  15. Set the direction that data is synchronized for each table in the synchronization model.

    Click the Mappings tab in the right pane, and set the rows the Dir column as follows:

    • The sales and salesdetail tables should be set to Bi-directional (both upload and download).
    • The remaining tables should be set to Download To Remote Only.

  16. Filter the rows downloaded to the remote database by remote ID.

    1. For the row containing the stores table, change the Download Subset to Custom.

    2. Click the Download Subset tab at the bottom of the right pane.

    3. Filter the rows by remote ID, which uniquely identifies the remote database, by adding a restriction to the WHERE clause of the download_cursor script.

      Type a search condition in the SQL Expression To Use In The Download Cursor's WHERE Clause field. For example, the following SQL script can be used for the stores table: 

      "dbo"."stores"."stor_id" = {ml s.remote_id}

      The download cursor script specifies what columns and rows are downloaded from each table to the remote database. The search condition ensures that you only download information about one store, namely, the store that has an identifier that equals the remote ID for the database.

  17. Repeat the previous step for the rows containing the sales, salesdetail, and discounts tables.

    Note

    You must rename the table specified in the SQL script to the table name in the row that you are editing.

    Use the following WHERE clause script for the sales table: 

    "dbo"."sales"."stor_id" = {ml s.remote_id}

    Use the following WHERE clause script for the salesdetail table: 

    "dbo"."salesdetail"."stor_id" = {ml s.remote_id}

    Use the following WHERE clause script for the discounts table: 

    "dbo"."discounts"."stor_id" = {ml s.remote_id}

  18. Save the synchronization model.

    Click File » Save.

    The synchronization model is complete and ready for deployment.

  19. Proceed to Lesson 7: Deploying the synchronization model.

 See also
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1