This is the first part of two scripts that are needed to manually setup replication. This first script will create the distribution database and is required before running Manual Replication Scripts (Part 2) .
Please Note: There are items in this script that must be changed to match the system it is being ran on. Specifically, @Active, @Standby, and @Computer3 variables will all need to be changed to reflect the proper hostnames of the SQL Servers in replication.
--------------------------------------------------------------------------------------------------------------------
-- Configure Distributer Server and Distribution Database for SQL Server Replication
--
-- This script must be run on the server that will act as the distributer. In a typical two server system this
-- will be the standby 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
--------------------------------------------------------------------------------------------------------------------
DECLARE @DistSQLServer SYSNAME, @SQLServer1 SYSNAME, @SQLServer2 SYSNAME, @SQLServer3 SYSNAME, @DistServer SYSNAME, @Active SYSNAME, @Standby SYSNAME, @Computer3 SYSNAME
-- REQUIRED:
-- If a server doesn't exist leave name empty ('')
SET @Active = 'TECH-01' -- Windows server names of computers that will be publishers
SET @Standby = 'QA-03'
-- OPTIONAL:
SET @Computer3 = ''
SET @SQLServer1 = @Active -- If not using default instance you can change the
SET @SQLServer2 = @Standby -- SQL Server instance name to something other than
SET @SQLServer3 = @Computer3 -- the Windows server name Ex) @SQLServer1 = 'SERVERNAME\SQLNAME'
SET @DistServer = @Standby -- Windows server name hosting the distributer
SET @DistSQLServer = @SQLServer2 -- SQL instance that will act as the distributer server
-- END USER CONFIGURATION
--------------------------------------------------------------------------------------------------------------------
DECLARE @SnapshotDir varchar(200)
-- Make the local server a distribution server
IF (@DistSQLServer <> '' AND @Active <> '' AND @Standby <> '')
BEGIN
EXEC master..sp_adddistributor @distributor=@DistSQLServer, @password = 'Man1t0uDist4but!0n'
EXEC master..sp_adddistributiondb @database= 'Distribution'
-- Allow the following server to be publishers
SET @SnapshotDir = '\\'+@DistServer+'\C$\Snapshot'
EXEC master..sp_adddistpublisher @publisher = @SQLServer1, @distribution_db = 'Distribution', @security_mode = 1, @working_directory = @SnapshotDir
SET @SnapshotDir = '\\'+@DistServer+'\C$\Snapshot'
EXEC master..sp_adddistpublisher @publisher = @SQLServer2, @distribution_db = 'Distribution', @security_mode = 1, @working_directory = @SnapshotDir
IF (@Computer3 <> '')
BEGIN
SET @SnapshotDir = '\\'+@Computer3+'\C$\Snapshot'
EXEC master..sp_adddistpublisher @publisher = @SQLServer3, @distribution_db = 'Distribution', @security_mode = 1, @working_directory = @SnapshotDir
END
END
ELSE
PRINT 'ERROR: You must set @DistSQLServer, @Active and @Standby'
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
