Step by Step Procedure to Implement SQL Server Transactional Replication with Oracle as Publisher

Article
10/16/2008

/*** Note: Kindly follow the Instruction in each step ***/

/*** If you are using SQL Server 2008 Management Studio, Ctrl-Shift-M might not work. In that case go to “Query” menu and click on “Specify Values for Template Parameters” ***/

1) Logon to ORACLE database as SYSDBA and follow the below steps

a) Create Tablespace to Store SQL Server Replication Metadata Objects

<Sample Script>

Create Tablespace <SQL_REPLUSER_TBL>

datafile '<Path>\<SQL_REPLUSER_TBL>.dbf' size 512M autoextend on next 512M maxsize unlimited ;

</Sample Script>

b) Create <SQLReplUser> on the ORACLE Database and make the tablespace created in step 1 above as the user default tablespace

You can use the Sample Script <oracleadmin.sql> provided by Microsoft which is (By default) located on the following path:

For SQL Server 2008:

C:\Program Files\Microsoft SQL Server\MSSQL10.<Instance ID>\MSSQL\Install

--***************************************************************************

-- File:

-- oracleadmin.sql

-- Purpose:

-- PL/SQL script to create a database user with the required permissions

-- to administer SQL Server publishing for an Oracle database.

-- &&ReplLogin == Replication user login

-- &&ReplPassword == Replication user password

-- &&DefaultTablespace == Tablespace that will serve as the default tablespace for the

-- replication user.

-- The replication user will be authorized to allocate UNLIMITED space

-- on the default tablespace, which must already exist.

-- Notes:

-- This script must be run from an Oracle login having the authorization to

-- create a new user and grant unlimited tablespace on any existing tablespace. The

-- login must also be able to grant to the newly created login the following authorizations:

-- create public synonym

-- drop public synonym

-- create sequence

-- create procedure

-- create session

-- create table

-- create view

-- Additionally, the following properties are also required for transactional publications.

-- create any trigger

-- All of the privileges may be granted through a role, with the exception

-- of create table, create view, and create any trigger. These must be

-- granted explicitly to the replication user login. In the script, all grants are

-- granted explicitly to the replication user.

-- In addition to these general grants, a table owner must explicitly grant select

-- authorization to the replication user on a table before the table can be published.

--***************************************************************************

ACCEPT ReplLogin CHAR PROMPT 'User to create for replication: ';

ACCEPT ReplPassword CHAR PROMPT 'Replication user passsword: ' HIDE;

ACCEPT DefaultTableSpace CHAR DEFAULT 'SYSTEM' PROMPT 'Default tablespace: ';

-- Create the replication user account

CREATE USER &&ReplLogin IDENTIFIED BY &&ReplPassword DEFAULT TABLESPACE &&DefaultTablespace QUOTA UNLIMITED ON &&DefaultTablespace;

-- It is recommended that only the required grants be granted to this user.

-- The following 5 privileges are granted explicitly, but could be granted through a role.

GRANT CREATE PUBLIC SYNONYM TO &&ReplLogin;

GRANT DROP PUBLIC SYNONYM TO &&ReplLogin;

GRANT CREATE SEQUENCE TO &&ReplLogin;

GRANT CREATE PROCEDURE TO &&ReplLogin;

GRANT CREATE SESSION TO &&ReplLogin;

-- The following privileges must be granted explicitly to the replication user.

GRANT CREATE TABLE TO &&ReplLogin;

GRANT CREATE VIEW TO &&ReplLogin;

-- The replication user login needs to be able to create a tracking trigger on any table that is

-- to be published in a transactional publication. The CREATE ANY privilege is used to

-- obtain the authorization to create these triggers. To replicate a table, the table

-- owner must additionally explicitly grant select authorization on the table to the

-- replication user.

-- NOTE: CREATE ANY TRIGGER is not required for snapshot publications.

GRANT CREATE ANY TRIGGER TO &&ReplLogin;

EXIT

</Script>

For SQL Server 2005:

C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Install

Where x is the Instance id

c) Grant Select privilege on table you would like to publisher to <SQLReplUser> created in the step (b) above

--Execute the below statement from SQL*PLus

Grant Select on <Owner>.<Table> to <SQLReplUser>;

</Script>

2) Configure the Distributor at the SQL Server and enable Oracle instance as Publisher , use the below script

/****** Script to Configure SQL Server as a Distributor. ******/

/* Press Ctrl-Shift-M to define Distributor Server name, dir structure for database files and Snapshot Folder directory where this script is stored. */

USE MASTER

exec sp_adddistributor @distributor = [<Distribution Server, sysname,>], @password = N''

exec sp_adddistributiondb @database = N'distribution', @data_folder = N'<Data Folder Path,nvarchar,C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\DATA>', @log_folder = N'<Log Folder Path,nvarchar,C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\Log>', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1

use [distribution]

if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))

EXEC sp_updateextendedproperty N'SnapshotFolder', N'<Snapshot Folder Path,nvarchar,C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\ReplData>', 'user', dbo, 'table', 'UIProperties'

else

EXEC sp_addextendedproperty N'SnapshotFolder', N'<Snapshot Folder Path,nvarchar,C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\ReplData>', 'user', dbo, 'table', 'UIProperties'

exec sp_adddistpublisher @publisher = N'<Oracle Instance Name, sysname,ORCL>', @distribution_db = N'distribution', @security_mode = 0, @login = N'<Oracle Login (SQL Publication),nvarchar,>', @password = N'<Oracle Login Password,nvarchar,>', @working_directory = N'<Snapshot Folder Path,nvarchar,C:\Program Files\Microsoft SQL Server\MSSQL.x\MSSQL\ReplData>', @thirdparty_flag = 0, @publisher_type = N'ORACLE'

</Configure Distributor>

3) Create Oracle Publication, The below Script will just create the Oracle Publication but will not add any article to it

/****** Creating Oracle Publication. ******/

/****** Press Ctrl-Shift-M to define Oracle Publication name ,Oracle Instance name. ******/

-- Adding the transactional publication

use [distribution]

exec sp_addpublication @publication = N'<Publication, sysname,Oracle Publication>', @description = N'Transactional publication from Oracle Publisher ''<Publisher, sysname,ORCL>''.', @sync_method = N'concurrent_c', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @publisher = N'<Publisher, sysname,ORCL>', @replicate_ddl = 0, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

exec sp_addpublication_snapshot @publication = N'<Publication, sysname,Oracle Publication>', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher = N'<Publisher, sysname,ORCL>'

exec sp_grant_publication_access @publication = N'<Publication, sysname,Oracle Publication>', @login = N'sa', @publisher = N'<Publisher, sysname,ORCL>'

exec sp_grant_publication_access @publication = N'<Publication, sysname,Oracle Publication>', @login = N'NT AUTHORITY\SYSTEM', @publisher = N'<Publisher, sysname,ORCL>'

exec sp_grant_publication_access @publication = N'<Publication, sysname,Oracle Publication>', @login = N'BUILTIN\Administrators', @publisher = N'<Publisher, sysname,ORCL>'

exec sp_grant_publication_access @publication = N'<Publication, sysname,Oracle Publication>', @login = N'distributor_admin', @publisher = N'<Publisher, sysname,ORCL>'

</Create Publication>

You will get the below warning message once you execute the above script

Job <Log Reader Agent Job Name>' started successfully.

Warning: The logreader agent job has been implicitly created and will run under the SQL Server Agent Service Account.

</Warning message>

You can ignore the above warning message.

4) Add Article to the Oracle Publication, Use the below Script for each article, you want to publish

/****** Script to Add Article to Oracle Publication ******/

/* Press Ctrl-Shift-M to specify Oracle Publication name, Article name, Oracle Instance, Oracle Table Name and Table Owner and Specify all values in Upper Case*/

/* Note: You needs to execute the Script for each article you would like to publish separately */

Declare @Table_Owner varchar (4000)

Declare @Table varchar (4000)

Declare @Schema varchar (8000)

Declare @Str_Ins varchar (4000)

Declare @Str_Upd varchar (4000)

Declare @Str_Del varchar (4000)

Set @Table_Owner = N'<Article Owner, nvarchar,>'

Set @Table = N'<Article, nvarchar,>'

Set @Schema = @Table_Owner+@Table

Set @Str_Ins = N'CALL sp_MSins_'+@Schema+''

Set @Str_Upd = N'CALL sp_MSupd_'+@Schema+''

Set @Str_Del = N'CALL sp_MSdel_'+@Schema+''

-- Adding the transactional articles

use [distribution]

exec sp_addarticle @publication = N'<Publication, sysname,Oracle Publication>', @article = N'<Article, nvarchar,>', @publisher = N'<Publisher, sysname,ORCL>', @source_owner = N'<Article Owner,nvarchar,>', @source_object = N'<Article,nvarchar,>', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000083, @use_default_datatypes = 1, @destination_table = N'EMP', @destination_owner = N'dbo', @status = 16, @vertical_partition = N'false', @ins_cmd = @Str_Ins, @del_cmd = @Str_Del, @upd_cmd = @Str_Del

**Note: Run the above script for each Oracle Table you want to Publish.

Up to this Point you have Configured Distributor, enable Oracle Database for Publishing and Created Oracle Publication.

Now you can Create either Push Subscription or Pull Subscription and the Subscriber could be either any edition of SQL Server

5) To create Push Subscription, use the below Script

/****** Script to Add Push Subscription to an Oracle Publication ******/

/** Press Ctrl-Shift-M to specify Oracle Publication name, Subscriber and Subscription Info **/

/****** Note: This Script is designed to create Push Subscription to SQL Server Subscriber ******/

----------------BEGIN: Script to be run at Publisher -----------------

use [distribution]

exec sp_addsubscription @publication = N'<Oracle Publication, sysname,>', @subscriber = N'<SQL Subscriber, sysname,>', @destination_db = N'<Subscriber Database, sysname,>', @publisher = N'<Oracle Publisher, sysname,>', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N'<Oracle Publication, sysname,>', @subscriber = N'<SQL Subscriber, sysname,>', @subscriber_db = N'<Subscriber Database, sysname,>', @publisher = N'<Oracle Publisher, sysname,>', @job_login = N'<Job Owner, varchar,>', @job_password = N'<Job Owner Password, varchar,>', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20081016, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

-----------------END: Script to be run at Publisher -----------------

</Add Push Subscription (SQL Server)>

On executing the above script, you will get a warning message

Job ‘<Distribution agent Job name>' started successfully.

Warning: The distribution agent job has been implicitly created and will run under the SQL Server Agent Service Account.

Job <Distribution agent Job name>' started successfully.

You can ignore the above warning message.

6) To Create Pull Subscription, Use the below Script

/****** Script to Add Pull Subscription to an Oracle Publication ******/

/****** Press Ctrl-Shift-M to specify Oracle Publication name, Subscriber and Subscription Info ******/

/****** Note: This Script is designed to create Push Subscription to SQL Server Subscriber ******/

-----------------BEGIN: Script to be run at Distribution Server -----------------

USE [distribution]

exec sp_addsubscription @publication = N'<Oracle Publication, sysname,>', @subscriber = N'<SQL Subscriber, sysname,>', @destination_db = N'<Subscriber Database, sysname,>', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only', @publisher = N'<Oracle Publisher, sysname,>'

-----------------END: Script to be run at Distribution Server-----------------

-----------------BEGIN: Script to be run at Subscriber-----------------

use [<Subscriber Database, sysname,>]

exec sp_addpullsubscription @publisher = N'<Oracle Publisher, sysname,>', @publication = N'<Oracle Publication, sysname,>', @publisher_db = N'distribution', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1

exec sp_addpullsubscription_agent @publisher = N'<Oracle Publisher, sysname,>', @publisher_db = N'distribution', @publication = N'<Oracle Publication, sysname,>', @distributor = N'<Distributor Server, sysname,>', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20081016, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0

-----------------END: Script to be run at Subscriber-----------------

</Add Pull Subscription>

Gurwinderjit Singh
SE, Microsoft SQL Server

Step by Step Procedure to Implement SQL Server Transactional Replication with Oracle as Publisher

Additional resources