This section describes the primary database issues and considerations specific to the Microsoft SQL Server data server in a Sybase replication system.
As a primary data server, Microsoft SQL Server interacts with Sybase Replication Agent. The Replication Agent identifies and transfers information about data-changing operations (or transactions) from a Microsoft SQL Server primary data server to a primary Replication Server.
The Replication Agent interacts with the primary Replication Server, and with the RSSD of the primary Replication Server, if so configured.
The Replication Agent requires a Microsoft SQL Server user login that has permission to access data and create new objects in the primary database.
The Microsoft SQL Server login must have the following permissions in the primary database:
select on any table to be replicated
create trigger on any trigger on any table to be replicated
drop trigger on any trigger on any table to be replicated
create table
drop table
create procedure on any procedure to be replicated
drop procedure on any procedure to be replicated
The performance and operation of Microsoft SQL Server primary data servers in a Sybase replication system might be affected by the following:
The Sybase Replication Agent uses its own proprietary transaction log to capture and record transactions in the primary database. The Replication Agent transaction log consists of shadow tables, stored procedures, and triggers that are created as objects in the primary database.
For each table operation or procedure invocation to be replicated, a sequence of triggers and/or stored procedures is executed to copy the operational data or parameters into a copy (shadow) table. The shadow tables are read periodically by the Replication Agent, which sends the transaction data to the primary Replication Server.
The impact of making these copies and scanning them depends on the capacity of the host machine and data server at the time of execution. A server with low transaction volumes or generous system resources may experience very little or no noticeable impact on performance. For a heavily loaded system or one with limited resources, the impact on performance could be significant. In addition, the need to keep copies of primary transaction data may require significant additional disk space for systems with large transaction volumes.
Sybase Replication Agent updates the @@IDENTITY global system variable in Microsoft SQL Server when either of the following occurs:
The Replication Agent trigger executes on a marked table.
A stored procedure marked for replication is invoked.
If a client application uses the @@IDENTITY column after an update to a replicate object, the value returned is the identity value from a Replication Agent system table, not the value from the client application table.
For example, if table_a has an identity column, an insert into table_a should set the @@IDENTITY global variable to the value set in the table_a identity column. If table_a is marked for replication, then the Replication Agent trigger executes after the insert into table_a, which inserts a record into a Replication Agent system table. The value of the @@IDENTITY global variable becomes the value of the Replication Agent system table identity column, not the value of the identity column in table_a.
A workaround for the Microsoft SQL Server @@IDENTITY global variable
issue is described in the Sybase Replication Agent Administration Guide.
Sybase Replication Agent requires the following to connect to a Microsoft SQL Server primary data server:
Sybase Replication Agent 12.6 requires a JDBC 2.0-compliant driver for connectivity to a Microsoft SQL Server primary database. Sybase provides the DataDirect JDBC 2.0 driver for Microsoft SQL Server with the Sybase Replication Agent 12.6 software.
The values of the following Replication Agent configuration parameters must be set as described:
pds_username – the Microsoft SQL Server user login that the Replication Agent uses to log in to the primary database. (This user login must be granted permissions as described in “Microsoft SQL Server primary database permissions”.)
pds_password – the password for the user login that the Replication Agent uses to log in to the primary database.
pds_connection_type – must be set to MSMERJDBC for connectivity to a Microsoft SQL Server primary database.
The value of the pds_connection_type parameter
is set automatically when the Replication Agent instance is created.
Refer to the Sybase Replication Agent Administration Guide for
more information about Replication Agent instances.
pds_host_name – the name of the host machine on which the Microsoft SQL Server data server resides.
pds_port_number – the client socket port number where the Microsoft SQL Server data server listens for connections.
pds_server_name – the server name of the Microsoft SQL Server data server.
pds_database_name – the name of the primary database on the Microsoft SQL Server data server, from which transactions will be replicated.
A separate Replication Agent instance is required for
each database from which transactions are replicated.
The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent can connect to the primary Replication Server:
rs_host_name – the name of the host machine on which the primary Replication Server resides.
rs_port_number – the client socket port number where the primary Replication Server listens for connections.
rs_username – the user ID that the Replication Agent uses to log in to the primary Replication Server. This user ID must be defined and granted connect source permission in the Replication Server.
rs_password – the password for the user ID that the Replication Agent uses to log in to the primary Replication Server.
rs_source_ds – the server name of the primary data server specified in the Replication Server database connection.
rs_source_db – the database name of the primary database specified in the Replication Server database connection.
Sybase Replication Agent uses TCP/IP and the
Sybase JDBC driver (jConnect for JDBC, which is included in Replication
Agent installation) to communicate with other Sybase servers. The
Replication Agent does not rely on the Sybase interfaces file for
connectivity information.
The values of the following Replication Agent configuration parameters must be set as described so that the Replication Agent can connect to the RSSD of the primary Replication Server:
rssd_host_name – the name of the host machine for the data server that contains the RSSD.
rssd_port_number – the client socket port number where the RSSD data server listens for connections.
rssd_username – the user ID that the Replication Agent uses to log in to the RSSD. This user ID must be defined and granted select permission in the RSSD.
rssd_password – the password for the user ID that the Replication Agent uses to log in to the RSSD.
rssd_database_name – the database name of the RSSD. When logging in to the data server specified in the rssd_host_name parameter, the Replication Agent invokes a use command for the database name specified in the rssd_database_name parameter.
The Sybase Replication Agent imposes the following limitations on a Microsoft SQL Server primary data server:
The maximum number of columns allowed in a primary table marked for replication is three columns less than the maximum number of columns allowed by the Microsoft SQL Server instance. The shadow table that stores captured transaction data uses the primary table schema, plus three additional columns for Replication Agent system information. If three more columns cannot be added to the number of columns in a primary table, the shadow table creation fails.
The Microsoft SQL Server @@IDENTITY global system variable is updated whenever a Replication Agent trigger executes on a table to be replicated, or whenever a stored procedure to be replicated is executed.
See “Replication intrusions and impacts in Microsoft SQL Server” for more information about this issue.
A workaround for the @@IDENTITY global
variable issue is described in the Sybase Replication Agent Administration
Guide.
Replication Server Manager has the following limitations with a Microsoft SQL Server primary data server:
Replication Server Manager cannot start or stop a Sybase Replication Agent instance.
See the Sybase Replication Agent Administration Guide for more information on starting and stopping the Replication Agent instance.
Replication Server Manager does not provide a method to invoke the Replication Agent pdb_xlog command. You must log in to the Replication Agent instance to execute this command.
See the Sybase Replication Agent Administration Guide for more information on logging in to a Replication Agent instance and using the pdb_xlog command.
All the installation issues and configuration parameter details for a Microsoft SQL Server primary data server are provided in the Sybase Replication Agent Installation Guide. The following are a few items that may need additional attention:
All configuration parameter values in the Replication Agent configuration file are case sensitive. Be careful when specifying the values of the rs_source_ds and rs_source_db parameters, as Replication Server is also case sensitive. If the same case is not used in both Replication Agent and Replication Server parameters, no connection occurs.
The Replication Agent filter_maint_userid configuration parameter controls whether the Replication Agent forwards transactions performed by the Maintenance User to the primary Replication Server. The Maintenance User name is defined in the Replication Server create connection command for the primary database.
In a bidirectional replication environment (replicating both into and out of the same database), the value of the filter_maint_userid parameter should be set to true. If it is not, transactions replicated to another site could return to be applied at the originating site, creating an endless loop.
The Replication Agent ltl_character_case configuration parameter controls the character case in which the Replication Agent sends database object names to the primary Replication Server.
For example, if a replication definition is created for all tables named testtab, the table name sent by the Replication Agent must be testtab, or no match occurs. Because Replication Server is case sensitive, a value of TESTTAB does not match a value of testtab.
If you use Replication Server Manager to create replication definitions, set the value of the Replication Agent ltl_character_case configuration parameter to asis. This allows the Replication Agent to send database object names in the same case as they appear in the primary database, which is the way RSM creates replication definitions, using the case returned by the primary data server. If you create replication definitions outside RSM, it may be easier to choose a default case (for example, create all replication definitions in either all uppercase or all lowercase), and change the value of the Replication Agent ltl_character_case parameter to match.
In a Microsoft SQL Server database, object names are stored in lowercase by default, if no case was forced when the object was created. That means the Replication Agent sends object names in lowercase to the primary Replication Server, unless configured to do otherwise.
See the Sybase Replication Agent Administration Guide for more information on the ltl_character_case parameter.
No Open Client interface application (such as isql) is provided as part of the Sybase Replication Agent installation. Use the Replication Server Manager, or use an Open Client application provided with another Sybase product (for example, the isql installed with Adaptive Server Enterprise or Replication Server).
The Replication Agent use_rssd configuration parameter controls whether the Replication Agent sends Log Transfer Language (LTL) that contains only the columns specified in a replication definition, or all of the columns in the primary table.
When the value of the use_rssd parameter is set to false, the Replication Agent sends LTL with data for all of the columns in the primary table. When the value of the use_rssd parameter is set to true, the Replication Agent sends LTL with data for only the columns specified in the replication definition for each primary table.
By sending data for only the columns specified in the replication definition, network traffic is reduced, which can improve performance.
In addition, column names and parameter names are removed from the LTL because the Replication Agent can send information in the order identified by the replication definition. The LTL minimal columns and structured tokens options are also available when the value of the use_rssd parameter is set to true. See the Sybase Replication Agent Administration Guide for more information.
The Microsoft SQL Server datetime datatype is compatible with the Sybase datetime datatype, so the Replication Agent pdb_convert_datetime parameter is not significant for a Microsoft SQL Server primary data server.
All Microsoft SQL Server datatypes are compatible with the corresponding Adaptive Server datatypes.
The Sybase Replication Agent provides a number of commands that return metadata information about the primary database (database names, table names, procedure names, column names, and so forth). It does this by issuing specific JDBC calls designed to return this information, or by querying the system tables directly.
Replication Server Manager (RSM) can be configured to use either the Replication Agent or a DirectConnect database gateway to return information about the primary database. Typically, RSM uses the Replication Agent to obtain information from the primary database, and uses a DirectConnect gateway to obtain information from a replicate database. You can specify which product is used to return metadata information to RSM when you add a non-Sybase data server using the RSM Add Server wizard.
Further considerations of using RSM are:
RSM cannot create a Sybase Replication Agent instance, nor can it start and stop a Replication Agent instance.
RSM can fully configure a Replication Agent instance, as well as invoke most Replication Agent administrative commands.
RSM can monitor and display the contents of Replication Agent instance system logs.
For RSM latency calculations to work correctly, the value of the Replication Agent ltl_origin_time_required parameter must be true.
The Sybase Replication Agent provides a set of sample scripts that you can use to set up simple, single-table replication from Microsoft SQL Server to Adaptive Server. These scripts can be found in the $SYBASE/rax-12_6/scripts directory.
See the Sybase Replication Agent Administration Guide for more information about the sample scripts and their use.
