Sybase Forums,Unix Forums-Sybase Adaptive server|Replication Server|Sun Solaris Unix|Perl| Sybase Training

Hi,

I am looking for some information regarding Replicating from MSSQL server to Sybase ASE. Please forward me any good material, white papers etc.

Thanks in Advance,
RK

This white paper could be useful.

Replication Between Adaptive Server Enterprise and Microsoft SQL Server
Copyright:
Sybase, Inc.
One Sybase Drive
Dublin, CA 94568

Summary

This white paper describes how to configure replication from Adaptive Server Enterprise (ASE) to Microsoft SQL Server (MS SQL Server), and from MS SQL Server to ASE.

1.0 Overview

This document describes the steps required to configure replication from Adaptive Server Enterprise (ASE) to Microsoft SQL Server (MS SQL Server), and from MS SQL Server to ASE.

These steps involve configuring the following products:

a) ASE
b) Replication Server
c) DirectConnect for MS SQL Server
d) Replication Agent for MS SQL Server

We do not cover the steps for installing/configuring DirectConnect, Replication Server, or ASE. It is assumed that these products are already configured and running.

2.0 Building the replication environment between ASE and MS SQL Server

These steps are illustrated with table tab1, which is created with the following DDL:

create table tab1
(cola int,
colb datetime)
go
create unique index ix1 on tab1(cola)
go
grant all on tab1 to public
go

The ASE table is on the pubs2 database and the MS SQL Server table is on the pubs database. The table structure is the same on ASE and MS SQL Server. The DirectConnect access service is called rmsql_acs.

The steps for building the replication environment are as follows:

1. Configure DirectConnect (DC) to access MS SQL Server

2. Add the DC service to sysservers on ASE. Connect to ASE and execute the following command:

sp_addserver rmsql_acs,direct_connect,rmsql_acs

3. Connect to MS SQL Server and add the tables required to support MS SQL Server as Replicate using Sybase-supplied function strings defined in Replication Server. To change the character set or sort order, change the appropriate insert statement. These must be valid Replication Server values.

The script for adding these tables is as follows:

Note:
You must grant the maintenance user UPDATE authority on the rs_lastcommit table and SELECT authority on the rs_info table; this user must also have EXECUTE authority on the rs_update_lastcommit procedure defined here. Add those grants if the user running the script is not the maintenance user.

Execute in MS SQL Server

use master
go
create table server_info (
arg varchar(30),
name varchar(30))
go

insert into server_info values ('server_csname', 'cp850')
go

insert into server_info values ('server_soname','noaccents_cp850')
go

create procedure sp_serverinfo (@arg varchar(30))
as
select name
from server_info
where arg = @arg
go

grant execute on sp_serverinfo to public
go

use pubs
go

create table rs_info (rskey varchar (20), rsval varchar (20))
go

insert into rs_info values ('charset_name', 'cp850')
go

insert into rs_info values ('sortorder_name', 'noaccents_cp850')
go

create table rs_lastcommit
(
origin int,
origin_qid binary(36),
secondary_qid binary(36),
origin_time datetime,
dest_commit_time datetime
)
go

create unique clustered index rs_lastcommit_idx on rs_lastcommit(origin)
go

create procedure rs_update_lastcommit
@origin int,
@origin_qid binary(36),
@secondary_qid binary(36),
@origin_time datetime
as
update rs_lastcommit
set origin_qid = @origin_qid, secondary_qid = @secondary_qid,
origin_time = @origin_time,
dest_commit_time = getdate()
where origin = @origin
if (@@rowcount = 0)
begin
insert rs_lastcommit (origin, origin_qid, secondary_qid,
origin_time, dest_commit_time)
values (@origin, @origin_qid, @secondary_qid,
@origin_time, getdate())
end
go


/* Create the procedure to get the last commit for all origins. */
create procedure rs_get_lastcommit
as select origin, origin_qid, secondary_qid
from rs_lastcommit
go

4. Create the connection for DirectConnect service. Connect to Replication Server for this task.

Create DSI for DirectConnect service (execute in Replication Server)

create connection to rmsql_acs.pubs
set error class rs_sqlserver_error_class
set function string class rs_sqlserver_function_class
set username pubs_maint
set password pubs_maint_ps
go

configure connection to rmsql_acs.pubs
set dsi_cmd_separator to ';'
go

5. In ASE, execute the sp_setreplicate procedure for the table to be replicated.

6. Create the replication definition and subscription in Replication Server:

create replication definition tab1_rep
with primary at ASENT.pubs2
with all tables named 'tab1'
(cola int,
colb datetime)
primary key(cola)

create subscription tab1_sub
for tab1_rep
with replicate at rmsql_acs.pubs
without materialization
3.0 Building the replication environment between MS SQL Server and ASE

These steps are illustrated with table tab2, which is created with the following DDL:

create table tab2
(cola int,
colb datetime)
go
create unique index ix1 on tab2(cola)
go
grant all on uniplex1 to public
go

The ASE table is in the pubs2 database and the MS SQL Server table is in the pubs database. The table structure is the same on ASE and MS SQL Server. The Replication Agent (RA) service is called ramsql.

1. Configure the Replication Agent for MS SQL Server.

You will need to do the following:

* Create the ODBC DSN for MS SQL Server.
* Create the instance for the RA using the “administrator” BAT file located in the rax-12_1 directory

2. Connect to Replication Server and create the connection:

create connection to ramsql.pubs
set error class rs_sqlserver_error_class
set function string class rs_sqlserver_function_class
set username sa
set password repsyb
with log transfer on, dsi_suspended

3. Connect to Replication Agent for MS SQL Server and configure the parameters for Replication Server:

ra_config rs_source_ds, ramsql
go
ra_config rs_source_db, pubs
go
ra_config rs_host_name, teste-pc ß host machine
go
ra_config rs_port_number, 7000
go
ra_config rs_username, sa
go
ra_config rs_password, repsyb
go
ra_config rssd_host_name, teste-pc ß host machine
go
ra_config rssd_port_number, 5000
go
ra_config rssd_database_name, RSSD
go
ra_config rssd_username, RSSD_prim
go
ra_config rssd_password, repsyb
go

4. Connect to Replication Agent for MS SQL Server and configure the primary database connection:

ra_config pds_database_name, pubs
go
ra_config pds_connection_type, ODBC
go
ra_config pds_username, sa
go
ra_config pds_password, repsyb
go
ra_config pds_datasource_name, ramsql
go

5. Test the connection


6. Connect to Replication Agent for MS SQL Server and create the Rep Agent transaction log:

pdb_xlog create

7. Connect to Replication Agent and mark the table for replication:

pdb_setreptable tab2, mark

8. Connect to Replication Server and create the replication definition and subscription:

create replication definition tab2_rep
with primary at ramsql.pubs
with all tables named 'tab2'
(cola int,
colb datetime)
primary key (cola)

create subscription tab2_sub
for tab2_rep
with replicate at ASENT.pubs2

9. Connect to Replication Agent and enable the table for replication:

pdb_setreptable tab2, enable

4.0 Conclusion

This document provides a single source of information to help the database administrator configure a replication environment between ASE and MS SQL Server.

Thx,
Tsu