This section describes the replicate database issues and considerations specific to the Microsoft SQL Server data server in a Sybase replication system.
As a replicate data server, Microsoft SQL Server interacts with the DirectConnect for Microsoft SQL Server database gateway. The DirectConnect for Microsoft SQL Server is responsible for accepting commands from the replicate Replication Server, and applying those commands to a Microsoft SQL Server database.
DirectConnect for Microsoft SQL Server supports replication
of large object (LOB) datatypes (image, ntext,
and text) from Replication Server directly
to a Microsoft SQL Server database.
To apply transactions in a replicate database, Replication Server requires a Maintenance User ID that you specify in the Replication Server create connection command. The Maintenance User ID must be defined at the Microsoft SQL Server data server and granted authority to apply transactions in the replicate database. The Maintenance User ID must have the following permissions in the Microsoft SQL Server replicate database:
create table authority to create tables used for Replication Server processing
update authority on all replicate tables
execute authority on all replicate stored procedures
The only significant intrusions or impacts to the Microsoft SQL Server replicate database are the database objects created by the hds_msss_setup_for_replicate.sql script to support Replication Server replicate database operations.
The hds_msss_setup_for_replicate.sql script creates two tables in the replicate database to support Replication Server operations:
rs_info, which contains information about the sort order and character set used by the replicate database
Verify that the insert statements
for the rs_info table (in the hds_msss_setup_for_replicate.sql script)
specify the proper character set and sort order for your Microsoft
SQL Server data server.
When using Replication Server version 12.0 or later, the replicate database sort order and character set must be recorded in the rs_info table.
The Replication Server rs_get_charset and rs_get_sortorder functions retrieve the character set and sort order from the rs_info table in the replicate database.
rs_lastcommit, which contains information about replicated transactions applied to the replicate database
Each row in the rs_lastcommit table identifies the most recent committed transaction that was distributed to the replicate database from a primary database. Replication Server uses this information to ensure that all transactions are distributed.
The Replication Server rs_get_lastcommit function retrieves information about the last transaction committed in the replicate database. For non-Sybase replicate databases, the rs_get_lastcommit function is replaced in the database-specific function-string class by the query required to access the rs_lastcommit table in the replicate database.
A Replication Server database connection name is made up of two parts: a data server name (server_name) and a database name (db_name). The server_name is the name of the DirectConnect for Microsoft SQL Server database gateway server, and the db_name is the name of the Microsoft SQL Server replicate database.
The replicate Replication Server looks for an interfaces file entry for the database gateway server_name specified in the Replication Server database connection. The replicate Replication Server logs in to the replicate data server using the user_name and password specified in the database connection.
You must make an entry in the Replication Server interfaces file to identify the host and port where the DirectConnect for Microsoft SQL Server database gateway server is listening. The interfaces file entry name must match the server_name portion of the Replication Server database connection.
The following replication limitations exist with a Microsoft SQL Server replicate data server:
Microsoft SQL Server supports either 28 digits of precision or 38 digits of precision, depending on the server’s start-up options. The default precision is 28 digits. Replication Server does not provide user-defined datatypes (UDDs) to support the default 28 digits of precision.
If you attempt to replicate numeric data to a Microsoft SQL Server database in excess of the server’s configured precision, Replication Server returns the following error:
E. 2001/12/14 11:14:58. ERROR #1028 DSI EXEC(134(1) dcm_gabeat70_devdb.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|Data Source Name=mssql70_devdb| SQLState=22003|Native Error=1007|Message= [Microsoft] [ODBC SQL Server Driver][SQL Server]The number '9999999999999999999.9999999999999999999' is out of the range for numeric representation (maximum precision 28). [Message Iteration=2|SQLState=22003|Native Error=|Message=[Microsoft][ODBC SQL Server Driver][SQL Server]The number '0.99999999999999999999999999999999999999' is out of the range for numeric representation (maximum precision 28).] <DCA>'
Microsoft SQL Server appears to support identity columns in the same manner as Adaptive Server Enterprise, so the Replication Server function strings that set identity insert off and on work correctly with Microsoft SQL Server. However, to support 28-digit numeric precision, the Sybase native numeric datatype must be translated to the rs_msss_numeric datatype, and as a result of this translation, the identity characteristic is lost.
If you choose to use the numeric to rs_msss_numeric datatype translation to support 28-digit precision in a Microsoft SQL Server replicate database, the replicate table cannot declare the numeric column receiving that data as an identity.
If a replicate Microsoft SQL Server table declares a numeric column receiving translated data as an identity, Replication Server returns the following error:
E. 2001/12/14 12:05:39. ERROR #1028 DSI EXEC(134(1) dcm_gabeat70_devdb.devdb) - dsiqmint.c(2888) Message from server: Message: 30291, State 0, Severity 19 -- '[VENDORLIB] Vendor Library Error: [[Message Iteration=1|Data Source Name=mssql70_devdb|SQL Function=INSERT|SQLState=23000|Native Error=544|Message=[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'ase_alltypes' when IDENTITY_INSERT is set to OFF.] <DCA>'
The heterogeneous datatype support (HDS) feature of Replication Server provides a number of sample SQL scripts that help you set up the HDS feature in the replicate Replication Server and the Microsoft SQL Server replicate database. These include:
hds_msss_setup_for_replicate.sql – a script to be applied to the Microsoft SQL Server replicate database.
The hds_msss_setup_for_replicate.sql script creates the rs_info and rs_lastcommit tables in the replicate database. The script includes grant statements that need to be changed before execution to reference the correct user ID name of the Maintenance User defined in the Replication Server database connection for the replicate database. The Maintenance User ID must have update authority on the tables.
Sybase recommends using the Maintenance User ID to execute
this script.
hds_msss_funcstrings.sql and hds_msss_udds.sql – scripts to be applied to Replication Server System Database (RSSD).
The hds_msss_udds.sql script adds the user-defined datatypes (UDDs) that define the attributes of Microsoft SQL Server native datatypes to the RSSD. The UDDs are required to ensure datatypes received from primary transactions are properly formatted for application to the Microsoft SQL Server replicate database.
You might need to modify the hds_msss_udds.sql script
to reference the correct RSSD database name.
The hds_msss_funcstrings.sql script replaces several default Replication Server function strings with custom function strings designed to communicate with Microsoft SQL Server and access the tables and procedures created by the hds_msss_setup_for_replicate.sql script. These function strings are added to the Replication Server default rs_msss_function_class.
You might need to modify the script to reference the
correct RSSD database name.
Class-level datatype translation scripts to be applied to RSSD.
Class-level translations identify primary datatypes and the replicate datatypes the data should be translated into (for example, DB2 TIMESTAMP should be translated to Microsoft SQL Server datetime).
These translations can affect Replication Server performance.
Only the translations needed for your specific primary database
and replicate database should be applied to the RSSD.
Class-level translation scripts supplied for the Microsoft SQL Server replicate database are:
hds_clt_db2_to_msss.sql – translates DB2 for OS/390 datatypes to Microsoft SQL Server datatypes.
hds_clt_udb_to_msss.sql – translates DB2 Universal Database (for UNIX and Windows) datatypes to Microsoft SQL Server datatypes.
hds_clt_informix_to_msss.sql – translates Informix datatypes to Microsoft SQL Server datatypes.
hds_clt_oracle_to_msss.sql – translates Oracle datatypes to Microsoft SQL Server datatypes.
Class-level translations are not supplied for Adaptive
Server datatypes to Microsoft SQL Server datatypes (or Microsoft
SQL Server datatypes to Adaptive Server datatypes) because Microsoft
SQL Server datatypes are directly compatible with Adaptive Server
datatypes and they require no translation.
hds_msss_connection_sample.sql – a script to be applied to the replicate Replication Server.
The hds_msss_connection_sample.sql script provides a template for creating the Replication Server database connection for a Microsoft SQL Server replicate database using the pre-defined Microsoft SQL Server function-string class provided with Replication Server.
You must modify the hds_msss_connection_sample.sql template
to include your actual server, database, and Maintenance User names
before executing the script.
The following system management issues are specific to a Microsoft SQL Server replicate data server:
The create connection command’s dsi_sql_data_style parameter was used in previous versions of Replication Server to provide some data translations for the replicate database.
With the introduction of the heterogeneous datatype support (HDS) feature in Replication Server version 12.0, the create connection command’s dsi_sql_data_style parameter is no longer valid. Do not use this parameter with Replication Server version 12.0 or later. The default setting should be " " (blank space).
The Component Integration Services (CIS) feature allows users to access both Adaptive Server databases and non-Sybase databases on different servers, through a local Adaptive Server connection.
CIS allows Replication Server to connect to an Adaptive Server database, using default Adaptive Server function strings and connection properties, while CIS handles the data conversion and manipulation, and all communications with the DirectConnect database gateway.
See “Component Integration Services” for a description of the advantages and disadvantages of this approach.
