How to: Initialize a Transactional Subscription from a Backup with Multiple Backup Files

Below is a slight modification to the How to: Initialize a Transactional Subscription from a Backup steps to handle Publisher database backups to multiple BAK FILES.

1) Execute sp_addpublication (Transact-SQL) at the Publisher on the publication database. Specify a value of true for @allow_initialize_from_backup = N'true' and @immediate_sync = N'true'

2) Create a backup of the publication database using the BACKUP (Transact-SQL) statement.

a. Full Database Backup to multiple BAK files

b. Start copy of the multiple BAK files to the Subscriber (may take hours to perform)

c. Transaction Log backup to TRN file

d. Copy the Transaction log backup to the Subscriber

3) Restore the backup on the Subscriber using the RESTORE (Transact-SQL) statement.

a. Restore the Fullback from the BAK files using “no recovery”

b. Restore the Transaction log TRN backup file using “with recovery”

4) At the Publisher on the publication database, execute the stored procedure sp_addsubscription (Transact-SQL) with the following changes

5) Modify the parameters:

a. @sync_type = N'initialize with backup'

b. Add new parameters:

i. @backupdevicetype = 'Disk'

ii. @backupdevicename = 'C:\BACKUP\MyLogBackup.TRN'.

WATCH OUT:

For PUSH Distribution Agents, the TRN location (step 5.2) must be accessible from the Publisher by either

1) point to the original TRN file on the Publisher
-- or --
2) use UNC mapping to the TRN on the Subscriber.

If using @sync_type = N'initialize with backup' what do you plan to do about the user-defined Constraints, Identities, and Triggers? You have 2 options.

1. Enable the “NOT FOR REPLICATION setting on the Publisher Constraints, Identities and Triggers before you create the Publication. Click HERE for details.
2. Disable Constraints, Identities, and Triggers AFTER you restore on the Subscriber but before you enable the Subscription. For example:

EXEC sp_MSforeachtable @command1="ALTER TABLE ? DISABLE TRIGGER ALL"

Troubleshooting replication issues when the subscriber is initialized from the backup

When the subscriber is initialized from backup, there can be so many things that can go wrong. Below are some of the error messages that might encounter during replication setup.

Msg 21408

Cannot create the subscription. You must specify a value of "Active" or "Subscribed" for the @status parameter. This is because the value specified for the @sync_type parameter is "initialize with backup" or "replication support only".

Msg 18786

The specified publication does not allow subscriptions to be initialized from a backup. To allow initialization from a backup, use sp_changepublication: set 'allow_initialize_from_backup' to 'true'.

Msg 21407

Cannot create the subscription. If you specify a value of "initialize with backup" for the @sync_type parameter, you must subscribe to all articles in the publication by specifying a value of "all" for the @article parameter.

Msg 21399

The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.

Msg 21397

The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup.

Quick Checks:

-- on the publisher database

select allow_initialize_from_backup, min_autonosync_lsn,* from syspublications

allow_initialize_from_backup = 1 min_autonosync_lsn = the lsn from which the commands should be applied at the subscriber

--on distribution database

select min_autonosync_lsn,* from distribution.dbo.mspublications

min_autonosync_lsn will be the same as the syspublications.min_autonosync_lsn

During the execution of sp_addsubscription, we perform validation in sp_MSaddautonosyncsubscription procedure before we create the subscription.

--on distribution database

select subscription_seqno,publisher_seqno,ss_cplt_seqno,* from distribution.dbo.mssubscriptions

min_autonosync_lsn should be always less than the subscription_seqno

When we execute sp_addsubscrption with @backupdevicename='backup device', we are executing the “RESTORE HEADERONLY” on the backup and getting the LASTLSN of the latest backup if the backup set contains multiple backups. All this logic is handled in sp_MSextractlastlsnfrombackup proc. This proc is executed inside sp_MSaddautonosyncsubscription procedure.

RESTORE HEADERONLY FROM DISK on the backup set and get the lastLSN from the latest backup. Use the following SQL to get the binary form of the lsn.

declare @numericlsn numeric(25,0)

declare @high4bytelsncomponent bigint,

@mid4bytelsncomponent bigint,

@low2bytelsncomponent int

--set the lsn here

set @numericlsn = 93000000070800001

select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))

select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000

select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))

select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000

select @low2bytelsncomponent = convert(int, @numericlsn)

SELECT convert(binary(4), @high4bytelsncomponent) + convert(binary(4), @mid4bytelsncomponent) + convert(binary(2), @low2bytelsncomponent)

This is the lsn from which the Distribution agent will pick up the commands from msrepl_transactions and apply at the subscriber.

Potential Problems

If the backup set contains multiple backup sets, the sp_addsubscrption logic select the first backup set and not the most recent backup set. As result, the Subscriber may not have the most recent copy of the Publication database. After all, reason we we’re doing this method is to reduce the data transfer time to the Subscriber. We didn’t count on the backup set having multiple copies of the Published database. Kinds of defeats trying to “save time” using Backups.

Solution: When using “allow initialization from backup files” always create the Publisher backup to a NEW backup (*.bak) file.

How to initialize the subscriber from backup

Here is the step by step procedure on how to initalize subscriber from backup

1. Create the publication using user interface Replication Wizard.
2. Under subscription options for the publication, set “allow initialization from backup files” to true
3. Create a new full backup of the publisher database. If you have existing full backup of the publisher database, you can still use that backup set but we have take a new log backup or differential backup of the publisher database and restore at the subscriber.
4. Replication UI does not allow the option to create the subscription to allow initialization from back. We have to TSQL when creating the subscription.
   
   exec sp_addsubscription @publication = N'Repl2000', …..
   @sync_type = N'initialize with backup',
   @backupdevicetype='Disk',
   @backupdevicename='C:\Repl2000_RestoreThis.bak'--this is the last backup used to restore on the subscriber that was taken after the publication was created
   go
   exec sp_addpushsubscription_agent …….
   go
   
   
5. This should create the subscription and the Distribution agent should be replicating the commands successfully.

How to identify Foreign Key dependencies in SQL Server database?

To understand the database schema design especially to determine the referential integrity constraints and dependencies, here I’m sharing a sample script which will help you in following:

1)      To identify which table(s) are dependent on a given object

2)      Which columns are being referenced (have foreign keys defined on them)

3)      What action to perform in response to update/delete operations

Note: If you would like to run the below script to identify the forign key dependencies w.r.t to only one table e.g. ‘My Table’, uncomment the filter in the WHERE clause

SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(fk.referenced_object_id), clm1.name) as 'S.No',fk.referenced_object_id as ReferencedObjID,

       constraint_column_id as ColumnID,
       OBJECT_NAME(fk.referenced_object_id) as [ReferencedTable(Parent)],
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.referenced_object_id,N'SchemaId') AS bit)) as [ParentSchema],
       clm2.name as ReferencedColumnName,
       OBJECT_NAME(constraint_object_id) as ConstraintName,
       fk.parent_object_id as ReferencingObjID,
       OBJECT_NAME(fk.parent_object_id) as [ReferencingTable (Foreign)],
       clm1.name as ForeignKeyColumn,
       SCHEMA_NAME (CAST(OBJECTPROPERTYEX(fk.parent_object_id,N'SchemaId') AS bit)) as [ForeignSchema],
      [Action on Update] = CONVERT(varchar,CASE OBJECTPROPERTY(fk.referenced_object_id,'CnstIsUpdateCascade')
                           WHEN 1 THEN 'CASCADE'
                           ELSE 'NO_ACTION'
                           END),
       [Action on Delete] = CONVERT(varchar,CASE OBJECTPROPERTY(fk.referenced_object_id,'CnstIsDeleteCascade')
                           WHEN 1 THEN 'CASCADE'
                           ELSE 'NO_ACTION'
                           END)

FROM sys.foreign_key_columns fk
       JOIN sys.columns clm1
          ON fk.parent_column_id = clm1.column_id
          AND fk.parent_object_id = clm1.object_id
       JOIN sys.columns clm2
          ON fk.referenced_column_id = clm2.column_id
          AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(fk.referenced_object_id) = 'My Table'  --- table name which is being referenced by other tables via Foreign Keys

ORDER BY OBJECT_NAME(fk.referenced_object_id)

Below is the output from AdventureWorks database

I hope this would help you at some point in carrying out your DBA or Developer task in understanding the database Schema design.

Do you know the Maximum number of databases that can be mirrored?

Recently I came across an interesting scenario where the SQL Server was frequently generating Deadlock Scheduler mini dumps on the mirrored SQL Server instance. In this post I put together various pieces of information, so thought of sharing with you.

Scenario: Suppose you are using SQL Server 2008 R2 Enterprise x64 edition installed on windows Server 2008 SP1. You are using SQL Server database mirroring feature and has setup database mirroring on 104 databases. You are frequently getting Deadlock Scheduler dump (Error 17884) on the mirror site, even though you restart SQL Server instance on the mirror site, still the Error 17884 deadlock scheduler appears within few minutes on the mirror site.

After analyzing the Deadlock Scheduler mini dump, generated on the very first occurrence of error 17884.

Important to note, SQL Server generate mini memory dump only on the first occurrence of Error 17884 and for the subsequent occurrences, it simply job an entry in the SQL Server error log something like

"New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 360 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the ""max worker threads"" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 10%%. System Idle: 88%%."

I found that the following:

Ø  There were 464 workers performing Parallel operation (i.e. Query Parallelism).

Ø  No lock blocking seen at the Server.

Ø  There were 372 workers performing DBM redo operation.

Total workers: 710

Workers in RUNNABLE state: 0

As per SQL Server configuration settings

name	minimum	maximum	config_value	run_value
max worker threads	128	32767	704	704

Before I proceed further Lets check the Mirror Server configuration

Intel(R) Xeon(R) CPU           L5630  @ 2.13GHz. Bios Version is HP  - 2

16 x64 level 8664, 2 Mhz processor (s).

Total Physical Memory = 49141 MB           
Available Physical Memory = 45098 MB       

For 64 bit operating system:

If total # of available logical CPU’s > 4 :   

Max worker threads = 512 + ((logical CPUS’s - 4) * 16

In our case max worker threads = 512 + ((16 - 4) * 16=704  which is default no. of worker thread SQL Server will spawn.

You must be wondering Max Worker Threads setting is correctly set as per above calculation

Before I comment on this, let’s talk about Database mirroring requirements for worker threads

·        Principal server:

1 global thread and 2 threads per each of the mirrored databases.

·        Mirror server: 64 bit architectures:

1 global thread, 2 threads per mirrored databases and one additional thread for each mirrored database for every 4 processor cores.

In short, the number of thread needed on mirror server are greater than those on the primary, so you need to use the mirror server system configuration settings to calculate the no. of databases that can be mirrored.

Number of threads required per mirrored database = (2+16/4) = 6 threads per database. Where 16 is the number of logical CPU cores on the mirror server

Maximum number of databases that can be mirrored = 704/6 = 117

Now you must be wondering, in our scenario, we are just mirroring 104 databases (which is less than 117 i.e. Max databases that can be mirrored), then what could be the cause error 17884 (Deadlock Scheduler) occurring too frequently on the mirror server.

In Summary, not all the 704 workers would be available to perform mirroring activities as some threads are assigned to core database activity on the server. Hence you have to set the value of max server thread to 1024 and you will be able to bring your mirror server back online and operational.

Additionally, you can run the below query to determine the number of worker threads available on your SQL Server.

select count(*) from sys.dm_os_threads

In my case it returned 759

When you rename a domain group in Active Directory, you are going to have some issues related to the login in SQL Server

Few days back I came across an interesting scenario related to a login in the SQL Server replication.

The Active Directory group used to grant access to a snapshot publication does not show up in the Publication access list (PAL) anymore when viewing the properties of the publication. However, the AD group does show up in the MSPublication_Access table for the snapshot publication in the Distribution database.

There is a disparity between what the Replication property settings and what is actually in the MSPublication_Access table. When I try to add the group to the PAL in the GUI, it doesn't give any error message and completes. But when I again check the PAL, it doesn't show up. Does it sound weird?

Now the question is what could have caused such a weird scenario ?

On investigating in a real life customer scanrio, what I found something very interesting.

1) The login was changed in the AD sometime back. The change was that the login was renamed but not deleted/recreated. So the SID remained same for the changed login in the AD. But because of this change in the AD, SQL Server was never aware of the change of the name. The login with the old name still exist in the server and mapped to the same SID.

2) Another interesting finding, somehow the login was deleted and recreated at the SQL Server level. So when the login was created again at the SQL Server, the new name was created on the SQL Server but the same Old SID was associated with it.

But the entry for the login remained there in the mspublication_access table. This login was never removed during the deletion of the login from the SQL Server. Afterwards whenever they used GUI to check the PAL, it was unable to find the login name from the SQL Server logins and hence didn't show up in the PAL list. But whenever you try to add the login in the PAL, it was able to find an existing record with the same SID and hence failed to add the login. But it never gave any error message.

To workaround the above weird scenario, go ahead and Delete the record manually from the MSPublication_Access table in distribution database. Then again add the login to the SQL Server and Publication Access List (PAL).

How-to: Implement SQL Server Transactional Replication with Oracle as Publisher

/*** 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

<Script>

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

-- Copyright (c) 2003 Microsoft Corporation

-- 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

<Script>

--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

<Configure Distributor>

/****** 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''

GO

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

GO

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'

GO

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'

GO

</Configure Distributor>

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

<Create Publication>

/****** 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'

GO

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>'

GO

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

GO

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

GO

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

GO

</Create Publication>

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

<Warning message>

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

<Add Article to Oracle Publication>

/****** 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

GO

<Add Article to Oracle Publication>

**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

<Add Push Subscription (SQL Server)>

/****** 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'

GO

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

</Add Push Subscription (SQL Server)>

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

<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.

<Warning message>

You can ignore the above warning message.

6) To Create Pull Subscription, Use the below Script

<Add Pull Subscription>

/****** 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,>'

GO

-----------------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

GO

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

</Add Pull Subscription>