Install Replication Script

Contents of rpl_inst.sql

rpl_inst.sql


--- Replication Installation Script

print ''
print 'Replication installation script'
print ''

-- Temporary table to store the names of the regional servers
use master
GO
SET NOCOUNT ON
GO

drop table tempdb.#Servers
GO
create table tempdb.#Servers (srv_id int, srv_name nvarchar(20))
GO

insert into #Servers values ( 1, N'BANGALOREAHD' )
insert into #Servers values ( 2, N'CALCUTTAAHD' )
insert into #Servers values ( 3, N'CHENNAIAHD' )
insert into #Servers values ( 4, N'DELHIAHD' )
insert into #Servers values ( 5, N'MUMBAIAHD' )
GO

----------------------------------- Start Replication Setup -----------------------------------
PRINT 'Starting Replication Setup...'

-- Get local install path
declare @install_path nvarchar (255)
exec master.dbo.xp_regread
	N'HKEY_LOCAL_MACHINE',
	N'SOFTWARE\Microsoft\MSSQLServer\Setup',
	N'SQLPath',
	@param = @install_path OUTPUT

-- Adding the distributor
exec sp_adddistributor @distributor = @@servername

print 'Server Name : ' + @@servername

-- Adding the distributor database
declare @data_folder nvarchar(255)
set @data_folder = @install_path + N'\DATA'
exec sp_adddistributiondb
	@database = N'distribution',
	@security_mode = 1,
	@data_folder = @data_folder,
	@data_file = N'distribution_Data',
	@data_file_size = 5,
	@log_folder = @data_folder,
	@log_file = N'distribution_Log',
	@log_file_size = 2

-- Adding the distribution publisher
declare @working_folder nvarchar(255)
set @working_folder = @install_path + N'\REPLDATA'
exec sp_adddistpublisher
	@publisher = @@servername,
	@security_mode = 1,
	@encrypted_password = 1,
	@distribution_db = N'distribution',
	@working_directory = @working_folder

-- Enabling the replication database
exec sp_replicationdboption AHD, N'publish', true
exec sp_replicationdboption AHD, N'merge publish', true

use [AHD]
GO
declare @desc nvarchar(255)
set @desc = N'AHD Replication from ' + @@servername
if @@servername <> N'NOIDAAHD'
	set @desc = @desc + N' Region to Head Office.'
else
	set @desc = @desc + N' to Regions.'

print ''
print 'About Replication : ' + @desc
print ''
print 'Adding Publication'
print ''

-- Adding the transactional publication
exec sp_addpublication
	@publication = N'AHD',
	@restricted = N'false',
	@sync_method = N'character',
	@repl_freq = N'continuous',
	@description = @desc,
	@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'false',
	@retention = 72

print 'After add publication'


-- Set the snapshot agent
exec sp_addpublication_snapshot
	@publication = N'AHD',
	@frequency_type = 4,
	@frequency_interval = 1,
	@frequency_relative_interval = 1,
	@frequency_recurrence_factor = 0,
	@frequency_subday = 4,
	@frequency_subday_interval = 1,
	@active_start_date = 0,
	@active_end_date = 99991231,
	@active_start_time_of_day = 0,
	@active_end_time_of_day = 235959
GO

-- Adding linked server(s)
if @@servername <> N'NOIDAAHD'
	BEGIN
	exec sp_addlinkedserver
		@server = N'NOIDAAHD',
		@srvproduct = N' ',
		@provider = N'SQLOLEDB',
		@datasrc = N'NOIDAAHD',
		@catalog = N'AHD'
	END
else
	BEGIN
	@srv_num integer
	set @srv_num = (select count(*) from #Servers)
	while (@srv_num >= 1)
	BEGIN
		declare @srv_name varchar(20)
		set @srv_name = (select srv_name from #Servers where srv_id = @srv_num)
		exec sp_addlinkedserver
			@server = @srv_name,
			@srvproduct = N' ',
			@provider = N'SQLOLEDB',
			@datasrc = @srv_name,
			@catalog = N'AHD'
		set @srv_num = @srv_num - 1
	END
END
GO

-- Adding the articles
DECLARE @article_name varchar(20), @set_article_filter varchar(50)

if @@servername <> N'NOIDAAHD'
BEGIN
	DECLARE i cursor FOR
		SELECT name from AHD..sysobjects where xtype = N'U' and uid = 5
			and name not in N'options', N'seqctl', N'dlgtsrv', N'dlgtsta', N'dlgtxpt',
			N'zcat', N'zclassification', N'zdesignation', N'zlob', N'zmanufacturer',
			N'zpayment_mode', N'zqualifications', N'zregion_office', N'zskill', N'zsolve_mode',
			N'zstate', N'zstation', N'zstatus', N'ztech', N'ct_ty', N'net_res', N'nr_com', N'pri',
			N'prob_ctg', N'srv_desc', N'tr_cde', N'tt', N'urgncy', N'zrepair_code')
		SELECT @set_article_filter = 'c_ctp_id != 2309'
END

if @@servername = N'NOIDAAHD'
BEGIN
	DECLARE i cursor FOR
		SELECT name from AHD..sysobjects where xtype = N'U' and uid = 5
			and name in (N'zcat', N'zclassification', N'zdesignation', N'zlob', N'zmanufacturer',
			N'zpayment_mode', N'zqualifications', N'zregion_office', N'zskill', N'zsolve_mode',
			N'zstate', N'zstation', N'zstatus', N'ztech', N'ct_ty', N'net_res', N'nr_com', N'pri',
			N'prob_ctg', N'srv_desc', N'tr_cde', N'tt', N'urgncy', N'zrepair_code',N'ctct')
		SELECT @set_article_filter = 'c_ctp_id = 2309'
END

print ''
print 'Article Filter Clause : ' + @set_article_filter

OPEN i
FETCH NEXT FROM i INTO @article_name
	WHILE @@FETCH_STATUS = 0
	BEGIN

		print ''
		print 'Article Name : ' + @article_name

		if @article_name = 'ctct'
		exec sp_addarticle
			@publication = N'AHD',
			@article = @article_name,
			@source_owner = N'AHD',
			@source_object = @article_name,
			@destination_table = @article_name,
			@type = N'logbased',
			@creation_script = null,
			@description = null,
			@pre_creation_cmd = N'none',
			@schema_option = 0x00000000000000F3,
			@status = 0,
			@vertical_partition = N'false',
			@ins_cmd = null,
			@del_cmd = null,
			@upd_cmd = null,
			@filter = null,
			@sync_object = null,
			@filter_clause = @set_article_filter
		else
		exec sp_addarticle
			@publication = N'AHD',
			@article = @article_name,
			@source_owner = N'AHD',
			@source_object = @article_name,
			@destination_table = @article_name,
			@type = N'logbased',
			@creation_script = null,
			@description = null,
			@pre_creation_cmd = N'none',
			@schema_option = 0x00000000000000F3,
			@status = 0,
			@vertical_partition = N'false',
			@ins_cmd = null,
			@del_cmd = null,
			@upd_cmd = null,
			@filter = null,
			@sync_object = null

		exec sp_changearticle
			@publication = N'AHD',
			@article = @article_name,
			@property = N'destination_owner',
			@value = N'AHD'

		FETCH NEXT FROM i INTO @article_name
	END
CLOSE i
DEALLOCATE i

if @@servername = N'NOIDAAHD'
	EXEC sp_articlefilter N'AHD', N'ctct', N'dbo.FLTR_ctct_1__9', N'[AHD].[ctct].[c_ctp_id] = 2309'
else
	EXEC sp_articlefilter N'AHD', N'ctct', N'dbo.FLTR_ctct_1__9', N'[AHD].[ctct].[c_ctp_id] != 2309'

-- Adding the Server(s) as a registered subscriber
if @@servername <> N'NOIDAAHD'
BEGIN
	exec sp_addsubscriber
		@subscriber = N'NOIDAAHD',
		@description = N'',
		@encrypted_password = 1,
		@type = 3,
		@login = N'sa',
		@security_mode = 0,
		@frequency_type = 4,
		@frequency_interval = 1,
		@frequency_relative_interval = 1,
		@frequency_recurrence_factor = 0,
		@frequency_subday = 4,
		@frequency_subday_interval = 1,
		@active_start_date = 0,
		@active_end_date = 99991231,
		@active_start_time_of_day = 0,
		@active_end_time_of_day = 235959,
		@status_batch_size = 100,
		@commit_batch_size = 100

	exec sp_changesubscriber_schedule
		@subscriber = N'NOIDAAHD',
		@agent_type = N'0'

	exec sp_addsubscription
		@publication = N'AHD',
		@subscriber = N'NOIDAAHD',
		@destination_db = N'distribution',
		@update_mode = N'read only',
		@sync_type = N'none',
		@subscription_type = N'push'
	END
else
BEGIN
	declare @srv_num integer
	set @srv_num = (select count(*) from #Servers)
	while (@srv_num >= 1)
	BEGIN
		declare @srv_name varchar(20)
		set @srv_name = (select srv_name from #Servers where srv_id = @srv_num)
		exec sp_addsubscriber
			@subscriber = @srv_name,
			@description = N'',
			@encrypted_password = 1,
			@type = 3,
			@login = N'sa',
			@security_mode = 0,
			@frequency_type = 4,
			@frequency_interval = 1,
			@frequency_relative_interval = 1,
			@frequency_recurrence_factor = 0,
			@frequency_subday = 4,
			@frequency_subday_interval = 1,
			@active_start_date = 0,
			@active_end_date = 99991231,
			@active_start_time_of_day = 0,
			@active_end_time_of_day = 235959,
			@status_batch_size = 100,
			@commit_batch_size = 100

		exec sp_changesubscriber_schedule
			@subscriber = @srv_name,
			@agent_type = N'0'

		exec sp_addsubscription
			@publication = N'AHD',
			@subscriber = @srv_name,
			@destination_db = N'distribution',
			@update_mode = N'read only',
			@sync_type = N'none',
			@subscription_type = N'push'
		set @srv_num = @srv_num - 1
	END
END
GO

exec master.dbo.xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\ComputerAssociates\CA Service Desk\4.0',
	N'Replication', N'REG_SZ', N'installed'

PRINT 'Replication Setup Complete...'