Microsoft SQL Server consolidated database

Setting up Microsoft SQL Server as a consolidated database
Note

The database user that runs the setup script must be able to create tables, triggers, and stored procedures, so must have the db_owner role.

To set up Microsoft SQL Server to work as a MobiLink consolidated database, you must run a setup procedure that adds MobiLink system tables, stored procedures, triggers, and views that are required for MobiLink synchronization. There are multiple ways you can do this:

  • Run the syncmss.sql setup script, located in install-dir\MobiLink\setup.

  • In the MobiLink plug-in for Sybase Central, choose Mode » Admin; connect to your server database; right-click the database name and choose Check MobiLink System Setup. If your database requires setup, you are prompted to continue. If you want to use an existing MobiLink system setup, then your default_schema should be the schema of the MobiLink system setup.

  • When you use the Create Synchronization Model Wizard or Deploy Synchronization Model Wizard, system setup is checked when you connect to your server database. If your database requires setup, you are prompted to continue.

Note

The database user who runs the setup script is the only user who has permission to change the MobiLink system tables, which is required for configuring MobiLink applications. See Required permissions.

The RDBMS user that the MobiLink server uses to connect to the consolidated database must be able to able to use the MobiLink system tables, procedures, and so on, without any qualifiers (for example, SELECT * from ml_user). See MobiLink server system tables.

ODBC driver

You must set up an ODBC DSN for your SQL Server consolidated database using the ODBC driver that is provided with your SQL Server database. See:

SQL Server issues

  • SET NOCOUNT ON   For Microsoft SQL Server, you should specify SET NOCOUNT ON as the first statement in all stored procedures or SQL batches executed via ODBC. Without this option, network buffers can overflow, silently losing data. This is a known SQL Server problem.

  • Procedure calls   Microsoft SQL Server requires that procedure calls with parameters be written using the ODBC syntax: 
    { CALL procedure_name( {ml param1}, {ml param2}, ... ) }

  • CHAR columns   In Microsoft SQL Server, CHAR data types are fixed length and blank-padded to the full length of the string. In MobiLink remote databases (SQL Anywhere or UltraLite) CHAR is the same as VARCHAR: values are not blank-padded to a fixed width. We strongly recommend that you use VARCHAR in the consolidated database rather than CHAR. If you must use CHAR, the mlsrv11 -b command line option can be used to remove trailing blanks from strings during synchronization. This option is important for string comparisons used to detect conflicts.

    See -b option.

  • Data type mapping   The data types of columns must map correctly between your consolidated and remote database. For details, see Microsoft SQL Server data mapping.

  • Sample database issues   The SQL Server AdventureWorks sample database contains computed columns. You can't synchronize a computed column. You can set the column to be download-only, or you can exclude the column from synchronization.

  • Implementing conflict detection in an upload_update script   The behavior of the SQL Server NOCOUNT option means that sometimes the MobiLink server cannot accurately assess how many rows were changed by an upload script. For SQL Server, it is safer to implement a stored procedure in the upload_update script for conflict detection.

Isolation level

See MobiLink isolation levels.

Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1