PULL Replication Setup on 3 database servers. Where Publisher, Distributor and Subscriber all are in different servers
1) To set up distributor
· Go to distributor server
· Click on replication folder
· Click configure distribution
2) To set up publisher of distributor (Password setting which will be used while creating publication on publisher Instance)
· Go to distribution server
· Click on replication folder
· Click distributor properties
· Click on publishers
· Add the publisher you want to add
· And provide password
3) Creating publication and article on publisher Instance
· Go to publisher server
· Click on replication folder
· Click on local publications
· Click on new publication
· Select database and publication type
· Select article
· Provide security
4) Setting up subscription on Subscriber
· Go to subscription database
· Click on replication folder
· Click on local subscription
· Click on new subscriptions
· From publisher combo box
· Select Find sql server publisher
· Slect the sql server publication instance
Setting Up Distributor
/****** Scripting replication configuration. Script Date: 4/3/2011 10:01:20 PM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server as a Distributor. Script Date: 4/3/2011 10:01:20 PM ******/
use master
exec sp_adddistributor @distributor = N'AJAY-PC\MSSQLSERVER2', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2\MSSQL\Data', @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'\\AJAY-PC\ReplData1', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'\\AJAY-PC\ReplData1', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'AJAY-PC\MSSQLSERVER2', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\AJAY-PC\ReplData1', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
Adding publication on Publication server
use [AdventureWorks]
exec sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [AdventureWorks]
exec sp_addpublication @publication = N'Ajay_publication', @description = N'Transactional publication of database ''AdventureWorks'' from Publisher ''AJAY-PC\MSSQLSERVER1''.', @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'false', @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'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'Ajay_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_security_mode = 1
use [AdventureWorks]
exec sp_addarticle @publication = N'Ajay_publication', @article = N'Contact', @source_owner = N'Person', @source_object = N'Contact', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Contact', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonContact', @del_cmd = N'CALL sp_MSdel_PersonContact', @upd_cmd = N'SCALL sp_MSupd_PersonContact'
GO
SCRIPT FOR SUBSCRIBER
-----------------BEGIN: Script to be run at Publisher 'AJAY-PC\MSSQLSERVER1'-----------------
use [AdventureWorks]
exec sp_addsubscription @publication = N'Ajay_publication', @subscriber = N'AJAY-PC', @destination_db = N'NewTestSubscription', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------END: Script to be run at Publisher 'AJAY-PC\MSSQLSERVER1'-----------------
-----------------BEGIN: Script to be run at Subscriber 'AJAY-PC'-----------------
use [NewTestSubscription]
exec sp_addpullsubscription @publisher = N'AJAY-PC\MSSQLSERVER1', @publication = N'Ajay_publication', @publisher_db = N'AdventureWorks', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0
exec sp_addpullsubscription_agent @publisher = N'AJAY-PC\MSSQLSERVER1', @publisher_db = N'AdventureWorks', @publication = N'Ajay_publication', @distributor = N'AJAY-PC\MSSQLSERVER1', @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 = 20110403, @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 'AJAY-PC'-----------------
No comments:
Post a Comment