IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest1')
 DROP DATABASE dbtest1;

CREATE DATABASE dbtest1
go

--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest1
go

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest1')
 DROP TABLE two_way_dbtest1;
GO

CREATE TABLE two_way_dbtest1
(
 pkcol  INTEGER PRIMARY KEY NOT NULL,
 intcol  INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
  charcol  CHAR(100),
 timestampcol  TIMESTAMP
)


/*Allocate a predetermined range of values to the primary key column
  so that the values on the different servers are not in the same range.
  For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
  and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
  To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest1
go

ALTER TABLE
 two_way_dbtest1
WITH NOCHECK
ADD CONSTRAINT
 checkprimcol CHECK NOT FOR REPLICATION
 (
  pkcol BETWEEN 1 AND 1000
 )
go


--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
sp_adddistributor @distributor = 'Station1\SQL2005'
go

--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go

--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go

exec sp_adddistpublisher
 @publisher = 'Station1\SQL2005',
 @distribution_db ='distribution',
 @security_mode = 0,
 @login = 'xxxxxxx',
 @password = 'xxxxxxx',
 @working_directory ='F:\Replication\Replication Working Directory'

--Enable the identified databases for replication
USE master
go

exec sp_replicationdboption N'dbtest1', N'publish', true
go

--Create the custom stored procedures in the dbtest1 database
USE dbtest1
go

-- INSERT Stored Procedure

CREATE PROCEDURE sp_ins_two_way_dbtest1 
 @pkcol int,
 @intcol int,
 @charcol char(100),
 @timestampcol timestamp,
 @rowidcol uniqueidentifier
AS
 INSERT INTO two_way_dbtest1
 (
  pkcol,
  intcol,
  charcol
 )
 VALUES
 (
  @pkcol,
  @intcol,
  @charcol
 )
go

--UPDATE Stored Procedure

CREATE PROCEDURE sp_upd_two_way_dbtest1 
 @pkcol int,
     @intcol int,
     @charcol char(100),
     @timestampcol timestamp,
 @rowidcol uniqueidentifier,
     @old_pkcol int
as
 DECLARE @x int
 DECLARE @y int
 DECLARE @z char(100)
 
 SELECT
  @x=pkcol,
  @y=intcol,
  @z=charcol
 FROM
  two_way_dbtest1
 WHERE
  pkcol = @pkcol

 

 DELETE
  two_way_dbtest1
 WHERE
  pkcol=@pkcol

 

 INSERT INTO two_way_dbtest1
 (
  pkcol,
  intcol,
  charcol
 )
 VALUES
 (
  CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
  CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
  CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
 )
go

--  DELETE Stored Procedure

CREATE PROCEDURE sp_del_two_way_dbtest1
 @old_pkcol int
AS
 DELETE
  two_way_dbtest1
 WHERE
  pkcol = @old_pkcol
go

--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest1
go

--  Adding the transactional publication.
EXEC sp_addpublication
 @publication = N'two_way_pub_dbtest1',
 @restricted = N'false',
 @sync_method = N'native',
 @repl_freq = N'continuous',
 @description = N'Transactional publication for database dbtest1.',
 @status = N'active',
 @allow_push = N'true',
 @allow_pull = N'true',
 @allow_anonymous = N'false',
 @enabled_for_internet = N'false',
 @independent_agent = N'false',
 @immediate_sync = N'false',
 @allow_sync_tran = N'true',
 @autogen_sync_procs = N'true',
 --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
 --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
 @retention = 120 
go

EXEC sp_addpublication_snapshot
 @publication = N'two_way_pub_dbtest1',
 @frequency_type = 4,
 @frequency_interval = 1,
 @frequency_relative_interval = 0,
 @frequency_recurrence_factor = 1,
 @frequency_subday = 2,
 @frequency_subday_interval = 10,
 @active_start_date = 20080225,
 @active_end_date = 99991231,
 @active_start_time_of_day = 070000,
 @active_end_time_of_day = 235959
go

--  Adding the transactional articles.
EXEC sp_addarticle
 @publication = N'two_way_pub_dbtest1',
 @article = N'two_way_dbtest1',
 @source_owner = N'dbo',
 @source_object = N'two_way_dbtest1',
 @destination_table = N'two_way_dbtest2',
 @type = N'logbased',
 @creation_script = null,
 @description = 'two_way_dbtest1 table data will be replicated to two_way_dbtest2',
 @pre_creation_cmd = N'drop',
        @schema_option = 0x00000000000000F1,
 @status = 16,
 @vertical_partition = N'false',
 @ins_cmd = N'CALL sp_ins_two_way_dbtest1',
 @del_cmd = N'CALL sp_del_two_way_dbtest1',
 @upd_cmd = N'CALL sp_upd_two_way_dbtest1',
 @filter = null,
 @sync_object = null,
 @identityrangemanagementoption = 'manual'
go

/*In this scenario, the dbtest1 database is the central subscriber.
  Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
  and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest1
go
EXEC sp_addsubscription
 @publication = N'two_way_pub_dbtest1',
 @article = N'all',
 @subscriber = 'Station2\SQL2005',
 @destination_db = N'dbtest2',
 @sync_type = N'none',
 @status = N'active',
 @update_mode = N'sync tran',
 @loopback_detection = 'true'
go

EXEC sp_addpushsubscription_agent
 @publication = N'two_way_pub_dbtest1',
 @subscriber = 'Station2\SQL2005',
 @subscriber_db = N'dbtest2'
go

--Create database named test1
IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest2')
 DROP DATABASE dbtest2
go

CREATE DATABASE dbtest2
go

--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set
USE dbtest2
go

IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest2')
 DROP TABLE two_way_dbtest2;
GO

CREATE TABLE two_way_dbtest2
(
 pkcol  INTEGER PRIMARY KEY NOT NULL,
 intcol  INTEGER IDENTITY(1,1) NOT FOR REPLICATION,
  charcol  CHAR(100),
 timestampcol  TIMESTAMP
)


/*Allocate a predetermined range of values to the primary key column
  so that the values on the different servers are not in the same range.
  For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database,
  and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database.
  To do so, use the following code:
*/
-- Constraint to enforce a range of values between 1 and 1000 in database dbtest1
USE dbtest2
go

ALTER TABLE
 two_way_dbtest2
WITH NOCHECK
ADD CONSTRAINT
 checkprimcol CHECK NOT FOR REPLICATION
 (
  pkcol BETWEEN 1 AND 1000
 )
go


--Enable your server as the distributor, and then create a distribution database
--Ensure SQL Server Agent service is running before executing the below statement.
USE master
go
EXEC sp_adddistributor
 @distributor = 'Station2\SQL2005'
go

--create a distribution database for the distributor
USE master
go
sp_adddistributiondb @database='distribution'
go

--Enable the computers running SQL Server that are participating in the replication as publishers
USE master
go

exec sp_adddistpublisher
 @publisher = 'Station2\SQL2005',
 @distribution_db ='distribution',
 @security_mode = 0,
 @login = 'xxxxxxxxx',
 @password = 'xxxxxxx',
 @working_directory ='E:\Replication\Working Directory'

--Enable the identified databases for replication
USE master
go

exec sp_replicationdboption N'dbtest2', N'publish', true
go

--Create the custom stored procedures in the dbtest1 database
USE dbtest2
go

-- INSERT Stored Procedure

CREATE PROCEDURE sp_ins_two_way_dbtest2 
 @pkcol int,
 @intcol int,
 @charcol char(100),
 @timestampcol timestamp,
 @rowidcol uniqueidentifier
AS
 INSERT INTO two_way_dbtest2
 (
  pkcol,
  intcol,
  charcol
 )
 VALUES
 (
  @pkcol,
  @intcol,
  @charcol
 )
go

--UPDATE Stored Procedure

CREATE PROCEDURE sp_upd_two_way_dbtest2 
 @pkcol int,
     @intcol int,
     @charcol char(100),
     @timestampcol timestamp,
 @rowidcol uniqueidentifier,
     @old_pkcol int
as
 DECLARE @x int
 DECLARE @y int
 DECLARE @z char(100)
 
 SELECT
  @x=pkcol,
  @y=intcol,
  @z=charcol
 FROM
  two_way_dbtest2
 WHERE
  pkcol = @pkcol

 

 DELETE
  two_way_dbtest2
 WHERE
  pkcol=@pkcol

 

 INSERT INTO two_way_dbtest2
 (
  pkcol,
  intcol,
  charcol
 )
 VALUES
 (
  CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,
  CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,
  CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END
 )
go

--  DELETE Stored Procedure

CREATE PROCEDURE sp_del_two_way_dbtest2
 @old_pkcol int
AS
 DELETE
  two_way_dbtest2
 WHERE
  pkcol = @old_pkcol
go

--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases
--In the database dbtest1.
USE dbtest2
go

--  Adding the transactional publication.
EXEC sp_addpublication
 @publication = N'two_way_pub_dbtest2',
 @restricted = N'false',
 @sync_method = N'native',
 @repl_freq = N'continuous',
 @description = N'Transactional publication for database dbtest2.',
 @status = N'active',
 @allow_push = N'true',
 @allow_pull = N'true',
 @allow_anonymous = N'false',
 @enabled_for_internet = N'false',
 @independent_agent = N'false',
 @immediate_sync = N'false',
 @allow_sync_tran = N'true',
 @autogen_sync_procs = N'true',
 --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions
 --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent.
 @retention = 120 
go

EXEC sp_addpublication_snapshot
 @publication = N'two_way_pub_dbtest2',
 @frequency_type = 4,
 @frequency_interval = 1,
 @frequency_relative_interval = 0,
 @frequency_recurrence_factor = 1,
 @frequency_subday = 2,
 @frequency_subday_interval = 10,
 @active_start_date = 20080225,
 @active_end_date = 99991231,
 @active_start_time_of_day = 070000,
 @active_end_time_of_day = 235959
go

--  Adding the transactional articles.
EXEC sp_addarticle
 @publication = N'two_way_pub_dbtest2',
 @article = N'two_way_dbtest2',
 @source_owner = N'dbo',
 @source_object = N'two_way_dbtest2',
 @destination_table = N'two_way_dbtest1',
 @type = N'logbased',
 @creation_script = null,
 @description = 'two_way_dbtest2 table data will be replicated to two_way_dbtest1',
 @pre_creation_cmd = N'drop',
        @schema_option = 0x00000000000000F1,
 @status = 16,
 @vertical_partition = N'false',
 @ins_cmd = N'CALL sp_ins_two_way_dbtest2',
 @del_cmd = N'CALL sp_del_two_way_dbtest2',
 @upd_cmd = N'CALL sp_upd_two_way_dbtest2',
 @filter = null,
 @sync_object = null,
 @identityrangemanagementoption = 'manual'
go

/*In this scenario, the dbtest1 database is the central subscriber.
  Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1
  and in the dbtest1 database that subscribe to the publication at dbtest2
*/
--Create all the subscriptions with the LOOPBACK_DETECTION option enabled
--Adding the transactional subscription in dbtest1.
USE dbtest2
go
EXEC sp_addsubscription
 @publication = N'two_way_pub_dbtest2',
 @article = N'all',
 @subscriber = 'Station1\SQL2005',
 @destination_db = N'dbtest1',
 @sync_type = N'none',
 @status = N'active',
 @update_mode = N'sync tran',
 @loopback_detection = 'true'
go

EXEC sp_addpushsubscription_agent
 @publication = N'two_way_pub_dbtest2',
 @subscriber = 'Station1\SQL2005',
 @subscriber_db = N'dbtest1'
go