Mobilizing Microsoft SQL Server Resource Kit
This resource kit amalgamates several resources and documents to help you get started with the creation of a custom synchronization environment with Microsoft SQL Server as the consolidated database. You may use the kit as a reference to individual topics or as a step-by-step guide by using the listed navigation below.
Introduction to MobiLink Technology
MobiLink is a part of the SQL Anywhere product. It is a session-based synchronization technology for exchanging data among relational databases and other non-relational data sources. Customers use MobiLink to synchronize tens of thousands of users with a single enterprise system, yet the server's compact size and the simplicity of its deployment means it can operate in remote sites and one or two user scenarios.
Advanced synchronization logic ensures the transactional integrity of the databases in the event a network connection is lost, and offers sophisticated strategies for the resolution of data change conflicts.
MobiLink provides a high level of integration capabilities. It supports many enterprise databases out of the box, and its object-based data flow API can be used to synchronize with non-relational data sources such as application servers, ERP systems such as SAP, Web services, XML files, or other third party relational databases.
Industry-leading database management and data movement technologies built for frontline environments.
SQL Anywhere® is a comprehensive package providing data management and data exchange technologies that enable the rapid development and deployment of database-powered applications. SQL Anywhere offers enterprise caliber databases that scale from 64-bit servers with thousands of users down to small handheld devices. SQL Anywhere's data exchange technologies extend information in corporate applications and enterprise systems to databases running in mission-critical frontline environments. Design and management tools within SQL Anywhere enable developers to implement and deploy frontline applications and equip administrators to easily manage and support them. The front lines have evolved into a mission-critical computing environment as businesses look for new ways to fuel competitive advantage and growth. iAnywhere's award-winning solutions have been built from inception to enable frontline applications-whether server, desktop, remote, or mobile-to be just as reliable, secure, and available as those running in the data centers of a traditional enterprise.
A synchronization is a process of data exchange between MobiLink clients and a synchronization server. During this process, the client must establish and maintain a session with the synchronization server. If successful, the session leaves the remote and consolidated databases in a mutually consistent state.
Synchronization typically begins when a MobiLink remote client opens a connection to a MobiLink synchronization server. During synchronization, the MobiLink client uploads database changes that were made to the remote database since the previous synchronization. Upon receiving this data, the MobiLink synchronization server updates the consolidated database, and then downloads changes on the consolidated database to the remote database.
Synchronizing data from a Microsoft SQL Server database to the frontlines of an enterprise enables mobile employees and those at remote or branch offices to access and manipulate corporate data without requiring a constant network connection. This significantly reduces the costs and technical challenges often associated with network connectivity in frontline environments, and ensures that remote office workers stay productive.
The MobiLink Advantage
MobiLink is a session-based synchronization system that allows two-way synchronization between a main database, called the consolidated database, and many remote databases. The consolidated database, which can be one of several ODBC-compliant databases, holds the master copy of all the data.
MobiLink provides out of the box support for:
MobiLink’s synchronization capabilities include:
There are a number of potential data replication and synchronization options on the market today which makes it extremely hard to determine which technology is the best fit when synchronizing data from a SQL Server database. The following section outlines some of the considerations when choosing a data synchronization technology.
Efficient Data Capture
MobiLink has the ability to capture the changes that occurred in the remote database, directly from the transaction log. The transaction logs give the synchronization system an efficient way of packaging up the changes to be sent to the server. This is accomplished by scanning the transaction log for everything that occurred since the previous synchronization. There is no need to use complex queries over various change capture tables to encapsulate the information.
Multi OS Support
Devices are constantly changing. Many years ago, Palm was the OS of choice for mobile development. Today Windows Mobile Smartphones are becoming increasingly popular. When choosing a synchronization solution it is important to consider one that supports a wide range of devices and operating systems. With MobiLink, the remote databases run on virtually every major device and operating system including Windows Mobile, Palm, Symbian, Windows, Linux, Mac OS X, and many more.
Feature Rich Data Store
With MobiLink, SQL Anywhere is supported as the remote database which gives you all of the enterprise database features you are familiar with and does not require a significant rewrite to your mobile database access code. Furthermore, SQL Anywhere includes a complete database optimizer that allows your application to continue to use complicated database queries and get results in a timely manner.
Graphical user interfaces are very useful when building prototypes quickly. However, once that prototype has been created, an administrator will need the ability to deploy it to large numbers of remote users. With SQL Anywhere all of the steps performed through the graphical user interface have a corresponding executable. This means that every step made by the user interface can be reproduced externally within batch files. Furthermore, all of the statements executed by the interface can be logged in a file. This allows the administrator to reproduce the environment as needed.
Priority Data Synchronization
Priority synchronization gives the user (or mobile application) the ability to independently control when different data subsets are synchronized based on network connectivity. The main advantages of priority synchronization include:
With MobiLink, data can be separated into "publications", which allow an application or users to control the order of data synchronization.
Advanced Conflict Detection and Resolution
MobiLink provides an extensive level of conflict resolution handling. By default, MobiLink will make the last user who made the change win the conflict and the first user to make the change receive the updated information. However, with MobiLink, conflict handling can be customized to provide a variety of different conflict mechanisms. This may include last-in wins for certain tables and first-in wins for other tables. Other times, the business rules may indicate that the values should be merged together.
Remote Schema Upgrades
With MobiLink, tools are embedded directly in the remote database engine, which allows for schema changes. During synchronization, special schema upgrade stored procedures can be executed within the database to make modifications to the remote database. Schema changes do not require a redistribution of data to the remote device, and they can also be automated. Furthermore, MobiLink includes the concept of script versioning that allows an administrator to slowly deploy schema upgrades to small groups of users while maintaining both old and new users on the same MobiLink server.
Extensive Server to Remote Table Mapping
When a query joining multiple tables is executed on an enterprise database server there will typically not be a noticeable performance impact. However, on remote systems the performance effect may be much more noticeable. As such, to increase application performance, an administrator may wish to join two or more tables on the server side into a single table on the remote side. This technique is called data denormalization. MobiLink has an extensive server to remote table mapping system that allows for this type of denormalization. Furthermore, any change that is made to the denormalized table can be tracked and applied to the (normalized) central database tables. This allows the schemas between the consolidated and the remote databases to be different.
Combining Multiple Data Sources
In many instances, the data that needs to be sent to the remote user will be scattered over multiple corporate databases. MobiLink has the ability to map synchronization logic to a number of data sources. This could include mappings from a variety of relational data sources such as Sybase, Oracle, Microsoft, or IBM, as well as application servers such as SAP, Siebel, and Service Oriented Architectures (SOA) such as Web Services. The ability to map different data sources can also become useful in the event of a corporate merger. Rather than having to immediately merge data from two different database systems, MobiLink can send some data from one database to one group of users and other data to other groups of users.
Integration with Existing Business Logic
During any point in the synchronization process, MobiLink has the ability to call customized .NET or Java logic. This means that prior to applying data changes to the central data source, data can be filtered within the corporate business logic, which may exist in a separate application server. This also allows for customization of synchronization events such as user authentication.
Pre-population of Remote Database
In circumstances where a remote database system requires a large volume of data, it is inefficient (and often expensive) to require the user to download all of their data during the initial synchronization. It is far more efficient to pre-populate the user database with information prior to deployment to ensure a user's first synchronization will only contain the “new” changes. As another option, an administrator may wish to create one template database that contains common information (such as product price lists). This template database can be used by any remote user. Upon synchronization, the user will only download the unique data that applies to them.
Efficient Data Transfer
Since the MobiLink server manages the mappings of the remote and central database tables, there is no need to send column names, data types, or SQL statements. It only requires the actual row data, table name, and before image. The before image is the data as it existed prior to being updated. This before image is used in the event of a conflict. Tests have shown that this efficiency can greatly reduce the overall size of the data stream.
Push synchronization allows information to be pushed from the central database server to remote databases. This capability is critical when important data changes are made on the server and a single user or group of users need to be notified. With MobiLink, synchronization can be initiated remotely as well as from the server. MobiLink can either send out alerts to the users effected by the data changes or physically initiate synchronization on the remote to download the set of data updates. In many cases a user may not even realize that their local database is being updated. Furthermore, the server has the ability to track the status and location of the remote. In the event that a user moves from one location to another, the server can track the users changing address and queue notifications until the user comes back into coverage.
User Based Subscriptions
MobiLink gives the administrator a wide range of options when configuring the information that is downloaded to the remote users. In most cases they will simply partition the data based on column values (such as where State is equal to NY). In other cases an administrator may wish to let the remote user choose which information they receive. This can be useful when a remote user wishes to subscribe to a specific set of information. Similarly, when the user no longer needs the information they can simply unsubscribe from it, removing the data from the local database. With MobiLink all of this is easily accomplished through the use of subscription tables and customized synchronization scripts.
Creating a System DSN
In order to connect to the MS SQL Server database, it is necessary to create a User ODBC Data Source Name to refer to it. The following steps demonstrate the process of creating a new User DSN. More detailed steps are available in the Whitepapers section of the right sidebar. View the video.
Creating a Synchronization Model
Sybase Central includes the MobiLink plug-in for creating, administrating, and deploying a synchronization model. A synchronization model defines what is synchronized between both consolidated and remote databases and how. More detailed steps are available in the Whitepapers section of the right sidebar. View the video.
Deploying the Synchronization Model
The MobiLink plug-in can automatically deploy the synchronization model to the Microsoft SQL Server database. It can also create a new remote database for synchronization, as well as create scripts for starting up an instance of the MobiLink server. More detailed steps are available in the Whitepapers section of the right sidebar. View the video.
Creating a Compatible Remote Database
The remote database that was generated automatically by the MobiLink plug-in is ready for synchronization right away. Typically, however, realistic projects will require that the remote database schema match the consolidated database schema more closely. Thus, the following section details the duplication of column defaults, foreign key relationships, triggers, and stored procedures. It is important to note that since the remote database is a SQL Anywhere database, a wide array of rich database features is available, such as stored procedures and aggregate functions.
Now that the synchronization model has been fully developed and deployed, synchronization is as simple as following these steps:
Microsoft SQL Server Specifics
MobiLink software is designed for use, right out of the box, with different types of data sources, including Microsoft SQL Server. Creating a synchronization model will typically require few, if any, changes to your existing SQL Server database schema. Of course, if you are going to be designing or re-designing your schema already, then you may want to keep the following considerations in mind in order to better take advantage of the MobiLink software.
Namely, you should pay specific attention to the use of primary keys, a timestamp column, identity columns, as well as computed columns.
Primary Key Uniqueness
One of the primary issues of distributed database design is maintaining unique primary keys across many remote databases. The documentation can be accessed online from the following URL: http://dcx.sybase.com/dcx_home.php.
For a more detailed look at how to maintain a large number of remote databases with unique keys, and how to easily deploy new remote databases synchronizing with a Microsoft SQL Server consolidated database (and others), please read the whitepaper Primary Keys in a Distributed Database Environment.
The above whitepaper outlines:
It is usually unnecessary and inefficient to delete and re-insert all rows of all tables when synchronizing. It is usually only necessary to synchronize changes that have occured since the last synchronization. For this purpose, it is necessary to keep track of when every row is modified. This can be done using multiple shadow tables or a timestamp column.
Typically shadow tables are used to avoid changes to the existing schema. The primary key values of modified or added columns are copied into secondary (shadow) tables. Shadow tables are generally named the same as the original table, but suffixed with _mod or _del depending on the type of shadow table. Upon synchronization, changes are retrieved by examining the shadow tables. The advantage is that this does not change the existing schema. In order to avoid conflicts, it should be ensured that primary keys are not reused after a deletion.
Alternatively, it is possible to keep track of time of last modification or time of insertion directly in a dedicated column. A "last modified" or "ModifiedDate" column can be added to all tables with an automatic default to the time of modification (default current timestamp). The advantage to this is that no additional shadow tables will need to be created or maintained.
If using the MobiLink plugin and wizards to deploy your synchronization model, a trigger will be created on the consolidated database to keep the shadow tables or timestamp column up-to-date every time a modification occurs on the original table.
Logical Delete Columns
One of the features of using MobiLink synchronization is that deletions are synchronized if desired such that deletions on either the consolidated databases, any remote databases, or both are propogated to the other. However, it will be necessary to keep track of deletions. This can be done either by use of a shadow table, or by using logical deletes.
Typically, a shadow table is used to keep track of which rows have been deleted. A row that has been deleted from a table will be deleted as expected, and a copy of the row's primary key will be kept in a secondary (shadow) table. Shadow tables generally have the same name as the original tables, but are suffixed with _del. Thus, when synchronization occurs, the list of rows that were deleted is available. The benefit to this approach is that this does not change the existing schema or dependant queries. However, to avoid unwanted deletions, primary keys should not be allowed to be reused after deletion.
Alternatively, a logical delete column can be used. A logical delete column is simply a bit or flag that keeps track of whether the row has been "marked for deletion". Thus, when synchronization occurs, the list of rows that were deleted is available. This method is typically not used because it requires changes to the existing schema, as well as all dependant queries (add an 'WHERE' clause). In addition, this method will require more free space since rows will never actually get deleted, and all rows (regardless of whether they have been deleted) will gain an additional bit/flag.
If using the MobiLink plugin and wizards to deploy your synchronization model, a trigger will be created on the consolidated database to keep the shadow table or logical delete column up-to-date every time a deletion occurs on the original table.
"Identity Columns" are a Microsoft SQL Server native data type used to store a counter's value and then increment it. They are often used as primary key columns. Use of this data type prohibits insertions or updates on this column. For example, a column declared as an identity column may not be included in an insert or update statement. This represents a problem for synchronization since it is typically required that all data matches between the consolidated and remote databases. A work around does exist, however, if Identity Columns must be used or the existing schema cannot be changed.
The following code should appear in the 'upload_insert' script of any consolidated table that is bi-directionally synchronized and that has an identity column. Essentially, it temporarily deactivates the identity property of the column to allow the insertion. After the insertion is complete, the property is reactivated and the identity column is reseeded back to its original value.
Reducing Enterprise Data Synchronization Headaches: Mobilizing Microsoft SQL Server with MobiLink – This whitepaper servers as an introduction to mobilizing Microsoft SQL Server. The focus of this paper is on the many benefits of choosing SQL Anywhere as a means to build, deploy and maintain Microsoft SQL Server remote databases.
How to Mobilize a Microsoft SQL Server Database – This whitepaper walks through synchronizing data between a Microsoft SQL Server database and a remote client database. The AdventureWorks sample schema that can be downloaded from the Microsoft website is used.
Cogon Systems – Cogon Systems' Moment of Care Information Systems powered by the SQL Anywhere database and synchronization technologies have resulted in improved patient care and reduced healthcare costs.
Helveta – Helveta's CI World technology, which includes UltraLite and MobiLink, the industry-leading mobile technologies from Sybase iAnywhere, helps enable sustainable forestry management of the world's tropical hardwood resources by providing seamless traceability, improved production efficiency and compliance checking across extended supply chains.
SQL Anywhere – Learn more about SQL Anywhere and MobiLink technologies.
Free Developer Edition – Download a copy of SQL Anywhere Developer Edition and start implementing your solution.
CodeXchange – Freely exchange code samples, utilities, scripts and other tools.
Support & Downloads – Should you have further questions, or require assitance, it would be iAnywhere's pleasure to accomodate you. Simply visit our Support & Downloads section for additional information.
Find Out More – If you are interested in finding out more about how MobiLink and the rest of SQL Anywhere can empower your frontline business, please contact us. It will be our pleasure to find a match between your needs and our solutions. Please call us at 1-800-801-2069, or email us to find out more.