Transactional Replication PT4 .. Setting the Distributor
- In my case I have a cluster
- My publisher will also be the distributor
- The subscriber will be another server
- Default admin shares have been removed so the default share for replication must be defined
- I'm placing the distribution database files where I want them to be
- I want to name my distribution database
use
master
go
--
-- First set up your server as a distributor
-- use select @@servername to find the name
--
exec
dbo.sp_adddistributor 'MyServer'
go
--
-- add the distribution database ( used to handle replication )
--
declare
@data_folder nvarchar(255),@log_folder nvarchar(255),@dbname sysname
--
-- this is the location of the database files and the database name
-- Place these in the location you wish
-- I don't wish to use the defaults
--
set
@data_folder = 'drive\path\datafolder\'
set
@log_folder = 'drive\path\logfolder\'
set
@dbname = 'MyDistributionDB'
--
-- folder structure must exist for the database creation so create folders
--
exec
master.dbo.xp_cmdshell ' md drive\path\datafolder\'
exec
master.dbo.xp_cmdshell ' md drive\path\logfolder\'
--
exec
dbo.sp_adddistributiondb @database = @dbname
, @data_folder = @data_folder
, @data_file_size = 5
, @log_folder = @log_folder
, @log_file_size = 5
, @security_mode = 1
, @createmode = 1
--
-- now configure the distribution database
-- @publisher is the servername
--
-- working directory is where replicated data goes prior to distribution
-- for a cluster this must not be on a local drive
-- the path below is not the default location
-- The default location requires the admin shares drive$ to be available
-- policy has removed the admin shares so we need a dedicated share
-- this share must be added to the cluster resource group.
--
exec
dbo.sp_adddistpublisher @publisher = 'MyServer'
, @distribution_db = @dbname
, @working_directory = '\\UNC\PATH\REPLDATA'
--
go
--
-- add the subscribing server(s)
-- use @@servername to get the name
--
exec
dbo.sp_addsubscriber 'MySubscriber'
--