Peer to peer demo code

Article
06/16/2005

Here's the code I used for the Peer to Peer replication demo I did during the SQL Server 2005 TechNet evening on the 14th June at our Reading office. The code sets up peer to peer replication between two tables, Orders and OrderDetails, in two databases East and West (on the same SQL instance)

Pre-requisites: Make your server a publisher with a distribution server and create two databases East and West. Then run these scripts in order

-------------------------------------setupord_orddetails.sql---------------------------------------------------------

--Step 1
USE East
go
DROP TABLE order_details
go

CREATE TABLE order_details(
ordid INT NOT NULL,
dtlid INT NOT NULL,
product VARCHAR(100) NULL,
qty INT )

GO
ALTER TABLE order_details ADD CONSTRAINT ord_dtl_pk PRIMARY KEY(ordid, dtlid)
GO

DROP TABLE orders
GO

CREATE TABLE orders(
ordid int NOT NULL,
orddate DATETIME)
GO
ALTER TABLE orders ADD CONSTRAINT ord_pk PRIMARY KEY(ordid)
GO

INSERT INTO orders VALUES(1, getdate())
insert into orders VALUES(2, getdate())
GO

USE West
GO
DROP TABLE order_details
GO

CREATE TABLE order_details(
ordid INT NOT NULL,
dtlid INT NOT NULL,
product VARCHAR(100) NULL,
qty int )

GO
ALTER TABLE order_details ADD CONSTRAINT ord_dtl_pk PRIMARY KEY(ordid, dtlid)
GO

DROP TABLE orders
go

CREATE TABLE orders(
ordid INT NOT NULL,
orddate DATETIME)
GO
ALTER TABLE orders ADD CONSTRAINT ord_pk PRIMARY KEY(ordid)
GO

INSERT INTO orders VALUES(1, getdate())
INSERT INTO orders VALUES(2, getdate())
GO

--Step 2
use East
select * from orders

use West
select * from orders

----------------------------East.sql-----------------------------------------

-- Step 1
-- Enabling the database West for P2P replication
use master
exec sp_replicationdboption
@dbname = N'West',
@optname = N'publish',
@value = N'true'
GO

-- Step 2
-- Adding the logreader agent
use [West]
exec sp_addlogreader_agent
@job_login='contoso\sqlservice',
@job_password='P@ssw0rd'
GO

-- Step 3
-- Adding the transactional publication
use [West]
exec sp_addpublication
@publication = N'p2p',
@description = N'Transactional publication with updatable subscriptions of database ''West'' from Publisher ''LON-DCSQL-01''.',
@sync_method = N'concurrent',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@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',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'true'
GO

-- Step 4
-- Adding the snapshot
exec sp_addpublication_snapshot
@publication = N'p2p',
@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 = N'contoso\sqlservice',
@job_password = null,
@publisher_security_mode = 1
GO

-- Step 5
-- Granting publication access
exec sp_grant_publication_access
@publication = N'p2p', @login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access
@publication = N'p2p', @login = N'CONTOSO\sqlservice'
GO
exec sp_grant_publication_access
@publication = N'p2p', @login = N'distributor_admin'
GO
exec sp_grant_publication_access
@publication = N'p2p', @login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access
@publication = N'p2p', @login = N'sa'
GO

-- Step 6
-- Adding the transactional articles
use [West]
exec sp_addarticle
@publication = N'p2p',
@article = N'order_details',
@source_owner = N'dbo',
@source_object = N'order_details',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000044F7,
@identityrangemanagementoption = N'manual',
@destination_table = N'order_details',
@destination_owner = N'dbo',
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_order_detailsE4142B7D292E4161849388C810216FC7]',
@del_cmd = N'CALL [sp_MSdel_order_detailsE4142B7D292E4161849388C810216FC7]',
@upd_cmd = N'MCALL [sp_MSupd_order_detailsE4142B7D292E4161849388C810216FC7]'
GO

use [West]
exec sp_addarticle
@publication = N'p2p',
@article = N'orders',
@source_owner = N'dbo',
@source_object = N'orders',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x00000000000044F7,
@identityrangemanagementoption = N'manual',
@destination_table = N'orders',
@destination_owner = N'dbo',
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_orders702225613A1046B791CDB3C523918DEE]',
@del_cmd = N'CALL [sp_MSdel_orders702225613A1046B791CDB3C523918DEE]', @upd_cmd = N'MCALL [sp_MSupd_orders702225613A1046B791CDB3C523918DEE]'
GO

-- Step 7
-- Adding the transactional subscriptions
use [West]
exec sp_addsubscription
@publication = N'p2p',
@subscriber = N'LON-DCSQL-01',
@destination_db = N'East',
@subscription_type = N'Push',
@sync_type = N'replication support only',
@article = N'all',
@update_mode = N'read only'

exec sys.sp_addpushsubscription_agent
@publication = N'p2p',
@subscriber = N'LON-DCSQL-01',
@subscriber_db = N'East',
@job_login = N'contoso\sqlservice',
@job_password = <'P@ssw0rd'>,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 4,
@frequency_subday_interval = 5,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@dts_package_location = N'Distributor'
GO

-------------------------------------------------West SQL-------------------------------------

-- Step 1
-- Enabling the database West for P2P replication
use master
exec sp_replicationdboption
@dbname = N'West',
@optname = N'publish',
@value = N'true'
GO

-- Step 2
-- Adding the logreader agent
use [West]
exec sp_addlogreader_agent
@job_login='contoso\sqlservice',
@job_password='P@ssw0rd'
GO

Comments

Anonymous
January 01, 2003
You can find all the code @ http://blogs.technet.com/mat_stephen/articles/406459.aspx

Partager via

Peer to peer demo code

Comments

Ressources supplémentaires