This is the second part of two scripts that are needed to manually setup replication. This script will creating the publications. Please be sure that the Distribution database has been created by running Manual Replication Scripts (Part 1) .
Please Note: There are items in this script that must be changed to match the system it is being ran on. Specifically, @Active, @Standby, @Computer3, @TPUser, and @TPPassword variables will all need to be changed to reflect the proper hostnames of the SQL Servers in replication.
--------------------------------------------------------------------------------------------------------------------
-- Configure Publisher
--
-- This script must be run on the server that will act as the publisher. In a typical two server system this
-- will be the primary server. You must set the server name variables below.
--
-- Rev 1.0 4/11/06 RH, Bold Technologies Ltd.
-- Rev 1.1 2/8/07 RH
-- Added server options for replication and Log Monitor
-- Rev 1.2 4/11/07 RH
-- Added support for replication to the Ticket Printer
-- Rev 1.3 4/25/07 RH
-- Removed the subscription to LOGS on the DR server
--------------------------------------------------------------------------------------------------------------------
DECLARE @DistServer SYSNAME,@DistSQLServer SYSNAME,@SQLPublisher SYSNAME,@SQLSubscriber SYSNAME,@Active SYSNAME,@Standby SYSNAME
DECLARE @Computer3 SYSNAME, @SQLSubscriber2 SYSNAME, @DRServer SYSNAME,@DRDatabase SYSNAME,@DRUser VARCHAR(50),@DRPassword VARCHAR(50),@HasDR BIT
DECLARE @TPServer1 SYSNAME, @TPServer2 SYSNAME, @TPSQLServer1 SYSNAME, @TPSQLServer2 SYSNAME, @TPUser VARCHAR(50), @TPPassword VARCHAR(50), @HasTP BIT
-- REQUIRED:
-- If a server doesn't exist leave name empty ('')
SET @Active = 'MANITOU1' -- Windows server names of computers that will be publishers
SET @Standby = 'MANITOU2'
-- OPTIONAL:
SET @Computer3 = ''
SET @TPServer1 = ''
SET @TPServer2 = ''
SET @TPUser = 'sa'
SET @TPPassword = 'boldtech'
SET @SQLPublisher = @Active -- If not using default instance you can change the
SET @SQLSubscriber = @Standby -- SQL Server instance name to something other than
SET @SQLSubscriber2 = @Computer3 -- the Windows server name Ex) SET @SQLPublisher = 'SERVERNAME\SQLNAME'
SET @TPSQLServer1 = @TPServer1
SET @TPSQLServer2 = @TPServer2
SET @DistServer = @Standby -- Windows server that will host the distributer server
SET @DistSQLServer = @SQLSubscriber -- SQL instance that will act as the distributer server
SET @HasDR = 0 -- Set to "1" if the customer subscribes to disaster recovery service
SET @DRServer = 'DR-DATA' -- Host name or IP address of the DR database server
SET @DRDatabase = '' -- Name of the database on the DR server
SET @DRUser = '' -- SQL User to connect with
SET @DRPassword = ''
-- END USER CONFIGURATION
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
-- Set local server's distribution server
EXEC master..sp_adddistributor @distributor = @DistSQLServer, @password = 'Man1t0uDist4but!0n'
-- Make @SQLSubscriber a subscriber of this server
EXEC master..sp_addsubscriber @subscriber = @SQLSubscriber, @type = 0, @security_mode = 1
EXEC master..sp_changesubscriber_schedule @subscriber = @SQLSubscriber, @agent_type = 0, @active_end_date = 0
IF (@SQLSubscriber2 <> '')
BEGIN
EXEC master..sp_addsubscriber @subscriber = @SQLSubscriber2, @type = 0, @security_mode = 1
EXEC master..sp_changesubscriber_schedule @subscriber = @SQLSubscriber2, @agent_type = 0, @active_end_date = 0
END
IF (@HasDR = 1)
BEGIN
EXEC master..sp_addsubscriber @subscriber = @DRServer, @type = 0, @security_mode = 0, @login = @DRUser, @password = @DRPassword
EXEC master..sp_changesubscriber_schedule @subscriber = @DRServer, @agent_type = 0, @active_end_date = 0
END
SET @HasTP = 0
IF (@TPSQLServer1<>'')
BEGIN
SET @HasTP = 1
EXEC master..sp_addsubscriber @subscriber = @TPSQLServer1, @type = 0, @security_mode = 0, @login = @TPUser, @password = @TPPassword
EXEC master..sp_changesubscriber_schedule @subscriber = @TPSQLServer1, @agent_type = 0, @active_end_date = 0
END
IF (@TPSQLServer2<>'')
BEGIN
SET @HasTP = 1
EXEC master..sp_addsubscriber @subscriber = @TPSQLServer2, @type = 0, @security_mode = 0, @login = @TPUser, @password = @TPPassword
EXEC master..sp_changesubscriber_schedule @subscriber = @TPSQLServer2, @agent_type = 0, @active_end_date = 0
END
-- Enable replication on the Manitou database
use master
exec sp_replicationdboption @dbname = N'MANITOU', @optname = N'publish', @value = N'true'
use [MANITOU]
-- Adding the transactional publication SYSTEM
DECLARE @AltSnapshotDir SYSNAME
SET @AltSnapshotDir = '\\' + @DistServer + '\Snapshot'
exec sp_addpublication @PUBLICATION = N'SYSTEM', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou System', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'SYSTEM',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
-- Adding the transactional publication LARGE
exec sp_addpublication @PUBLICATION = N'LARGE', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou Large Tables', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'LARGE', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
-- Adding the transactional publication LOGS
exec sp_addpublication @PUBLICATION = N'LOGS', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou Log Tables', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'LOGS', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
-- Adding the transactional publication REPORTS
exec sp_addpublication @PUBLICATION = N'REPORTS', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou Report Queue', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'REPORTS', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
IF (@HasDR = 1)
BEGIN
-- Add the transactional publication CONFIG if customer has disaster recovery service
-- These tables will not be set to the DR server so we don't overwrite the Manitou system configuration
exec sp_addpublication @PUBLICATION = N'CONFIG', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou Tables Not Sent to DR', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'CONFIG', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
END
IF (@HasTP = 1)
BEGIN
-- Adding the transactional publication TICKETPRINTER
SET @HasTP = 1
exec sp_addpublication @PUBLICATION = N'TICKETPRINTER', @restricted = N'false', @sync_method = N'concurrent', @repl_freq = N'continuous', @description = N'Manitou Tables Sent to Ticket Printer', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @retention = 336, @allow_queued_tran = N'false', @snapshot_in_defaultfolder = N'false', @alt_snapshot_folder = @AltSnapshotDir, @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false', @allow_subscription_copy = N'false', @add_to_active_directory = N'false'
exec sp_addpublication_snapshot @PUBLICATION = N'TICKETPRINTER', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 225400, @active_end_time_of_day = 235959
END
-- Add articles to publications
CREATE TABLE ##TABLENAMES (TABLE_NAME SYSNAME, TID INT IDENTITY)
INSERT INTO ##TABLENAMES (TABLE_NAME)
SELECT TABLE_NAME FROM information_schema.tables
WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME NOT LIKE 'SYSLOG%' AND TABLE_NAME NOT LIKE '%_TXT' AND TABLE_NAME NOT LIKE '%_BAK' AND
TABLE_NAME NOT IN ('dtproperties','MSreplication_subscriptions','MSsubscription_agents','MSpub_identity_range','MSpeer_request','MSpeer_response','sysarticles','sysarticlecolumns','sysarticleupdates','syspublications','sysschemaarticles','syssubscriptions','systranschemas','CUST_SIG','InstallShield')
ORDER BY TABLE_NAME
DECLARE @TABLENAME sysname
DECLARE @TABLEBAK sysname
DECLARE @PUBLICATION sysname
DECLARE @INSERTCMD sysname
DECLARE @DELETECMD sysname
DECLARE @UPDATECMD sysname
DECLARE @THISMONTH DATETIME
DECLARE @TABLEMONTH DATETIME
DECLARE @LOOP int
SET @THISMONTH = (SELECT CAST(CONVERT(CHAR(7), getdate(), 20) + '-01' AS DATETIME))
SET @LOOP = 1
SELECT @TABLENAME=TABLE_NAME FROM ##TABLENAMES WHERE TID=@LOOP
WHILE (@@ROWCOUNT<>0)
BEGIN
SET @INSERTCMD = N'CALL sp_MSins_' + @TABLENAME
SET @DELETECMD = N'CALL sp_MSdel_' + @TABLENAME
SET @UPDATECMD = N'MCALL sp_MSupd_' + @TABLENAME
-- Determine which publication to create the article in
IF (@TABLENAME in ('ADDRESS','COMMENTS','CONTACT','CONTLIST','CONTPOINT','CUSTOMER','CUSTPRIV','MONITORING','PERSON','STX','STXPRG','TXTYPEPRG'))
SET @PUBLICATION = 'LARGE'
ELSE IF (@HasDR = 1 AND @TABLENAME IN ('DEVCONF','DEVCONF_D','DNISMAP','FEP','RECEIVERS','RECLINEDES','RECLINEMAP','SYSCONF','WORKSTN'))
SET @PUBLICATION = 'CONFIG'
ELSE IF (@TABLENAME like '%LOG2%' OR @TABLENAME like 'RAWDATA2%')
BEGIN
SET @TABLEMONTH = (SELECT CAST(LEFT(RIGHT(@TABLENAME,6),4) + '-' + RIGHT(@TABLENAME,2) + '-01' AS DATETIME))
IF (@TABLEMONTH - @THISMONTH > -32)
-- TABLES FOR PREVIOUS MONTH, THIS MONTH AND THE FUTURE GO INTO "LARGE" PUBLICATION THAT IS SENT TO DR SERVER
SET @PUBLICATION = 'LARGE'
ELSE
-- TABLES BEFORE LAST MONTH ARE CONSIDERED HISTORICAL DATA AND WILL NOT BE SENT TO DR SERVER
SET @PUBLICATION = 'LOGS'
END
ELSE IF (@TABLENAME IN ('REPQUEUE','REPQUEUE_D'))
SET @PUBLICATION = 'REPORTS'
ELSE
SET @PUBLICATION = 'SYSTEM'
PRINT 'Adding ' + @TABLENAME + ' to the ' + @PUBLICATION + ' publication.'
-- Add the table to the selected publication
EXEC sp_addarticle @PUBLICATION = @PUBLICATION, @article = @TABLENAME, @source_owner = N'dbo', @source_object = @TABLENAME, @destination_table = @TABLENAME, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000D9D3, @status = 16, @vertical_partition = N'false', @ins_cmd = @INSERTCMD, @del_cmd = @DELETECMD, @upd_cmd = @UPDATECMD, @filter = null, @sync_object = null
IF (@HasTP = 1)
BEGIN
IF (@TABLENAME NOT LIKE '%LOG2%' AND @TABLENAME NOT LIKE 'RAWDATA2%' AND @TABLENAME NOT LIKE 'REPORT%' AND @TABLENAME NOT LIKE 'REPQUEUE%' AND @TABLENAME NOT IN ('ALARM','ACTIONSEXEC','PENDING','STXSTS','BOBJECT'))
BEGIN
PRINT 'Adding ' + @TABLENAME + ' to the TICKETPRINTER publication.'
EXEC sp_addarticle @PUBLICATION = 'TICKETPRINTER', @article = @TABLENAME, @source_owner = N'dbo', @source_object = @TABLENAME, @destination_table = @TABLENAME, @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000000D9D3, @status = 16, @vertical_partition = N'false', @ins_cmd = @INSERTCMD, @del_cmd = @DELETECMD, @upd_cmd = @UPDATECMD, @filter = null, @sync_object = null
END
END
SET @LOOP = @LOOP + 1
SELECT @TABLENAME=TABLE_NAME FROM ##TABLENAMES WHERE TID=@LOOP
END
-- Add the subscriptions for each publication
exec sp_addsubscription @PUBLICATION = N'SYSTEM', @article = N'all', @subscriber = @SQLSubscriber, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'LARGE', @article = N'all', @subscriber = @SQLSubscriber, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'LOGS', @article = N'all', @subscriber = @SQLSubscriber, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'REPORTS', @article = N'all', @subscriber = @SQLSubscriber, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
IF (@SQLSubscriber2 <> '')
BEGIN
exec sp_addsubscription @PUBLICATION = N'SYSTEM', @article = N'all', @subscriber = @SQLSubscriber2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'LARGE', @article = N'all', @subscriber = @SQLSubscriber2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'LOGS', @article = N'all', @subscriber = @SQLSubscriber2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'REPORTS', @article = N'all', @subscriber = @SQLSubscriber2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
END
IF (@HasDR = 1)
BEGIN
IF (@SQLSubscriber2 <> '')
exec sp_addsubscription @PUBLICATION = N'CONFIG', @article = N'all', @subscriber = @SQLSubscriber2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'CONFIG', @article = N'all', @subscriber = @SQLSubscriber, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true'
exec sp_addsubscription @PUBLICATION = N'SYSTEM', @article = N'all', @subscriber = @DRServer, @destination_db = @DRDatabase, @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'false'
exec sp_addsubscription @PUBLICATION = N'LARGE', @article = N'all', @subscriber = @DRServer, @destination_db = @DRDatabase, @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'false'
END
IF (@HasTP = 1)
BEGIN
IF (@TPSQLServer1<>'')
exec sp_addsubscription @PUBLICATION = N'TICKETPRINTER', @article = N'all', @subscriber = @TPSQLServer1, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true', @frequency_type=4, @frequency_subday=8, @frequency_subday_interval=4
IF (@TPSQLServer2<>'')
exec sp_addsubscription @PUBLICATION = N'TICKETPRINTER', @article = N'all', @subscriber = @TPSQLServer2, @destination_db = N'MANITOU', @sync_type = N'automatic', @status = N'active', @update_mode = N'read only', @loopback_detection = 'true', @frequency_type=4, @frequency_subday=8, @frequency_subday_interval=4
END
GO
DROP TABLE ##TABLENAMES
GO
-- Set some server options any time this script is run
-- just to make sure that they are set
-- RH 2/8/07
EXEC master..sp_configure 'show advanced option', '1'
GO
EXEC master..sp_configure 'max text repl size', 2147483647
GO
RECONFIGURE
GO
