I already described how to configure distributor and publisher by using GUI.
How to setup Transactional Replication? Part 1
However if you have to setup more than one publisher to setup and also multiple distributors (or distribution dbs) it is getting quite hard to do it quickly. I often you one remote distributor server and multiple distribution dbs on that one server. That below script helped me a lot. By default I set retention to 48 hours and 24 hours for history. You can change it in the script or later by using GUI. Script will also generate code that you need to run on Publisher server in order to enable Replication and configure distributor.
Note: It will try to create a folder, if parameter @create_folder set to 1, otherwise it will create db in default or provided path. Still if you want to create folder for new distribution database xp_cmdshell need to be enabled on the server.
If you have any improvements or comments to below script let me know.
/* NOTE: 1.You need to run the script for EACH distribution db u want to create 2.Model db cant be in use during the installation (It need exclusive lock on model in order to create distribution db) */ /****** Installing the server as a Distributor. ******/ use master declare @distribution_Db nvarchar(300), @path nvarchar(300), @SQL nvarchar(max), @publisher nvarchar(300), @password nvarchar(300), @distributor nvarchar(200), @create_folder bit -------------- setting variables ------ set @distributor=@@servername set @distribution_db='' set @password='' set @publisher='' set @path =NULL -- leave null if u want to create distribution db in this forlder: --select substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1 set @create_folder =1 --xp_cmdshell need to be enabled otherwise distribution db will be created in default folder. Set to 0 if you created folder manually. -------------- end of variables -------- --Configuring Distributor if not exists (select 1 from sys.servers where is_distributor=1 and name ='repl_distributor' and data_source=@distributor) BEGIN exec sp_adddistributor @distributor = @distributor, @password = @password END if @create_folder =1 and @path is NULL BEGIN select @path=substring(physical_name,1,patindex('%tempdb%',physical_name)-1) from sys.master_files where database_id in (select database_id from sys.databases where name='tempdb') and file_id =1 END if @create_folder =1 and @path is not NULL BEGIN set @path = @path + @distribution_Db set @sql = 'exec xp_cmdshell ''mkdir ' +@path +'''' print @sql if @create_folder =1 BEGIN if exists (select 1 from sys.configurations where name ='xp_cmdshell' and value_in_use =1) BEGIN print 'Creating Folder ' + @path exec sp_executesql @sql END ELSE BEGIN print 'xp_cmdshell not enabled. Db will be created in default folder' set @path =NULL END END END -- Adding the distribution databases if @path is not NULL set @path = @path + '\' if @path is NULL print 'Distribution db will be created in default folder' if not exists (select 1 from sys.databases where name =@distribution_db) BEGIN exec sp_adddistributiondb @database = @distribution_db, @data_folder = @path, @log_folder = @path, @min_distretention = 48, @max_distretention = 48, @history_retention = 24, @security_mode = 1 END -- Adding the distribution publishers exec sp_adddistpublisher @publisher =@publisher, @distribution_db = @distribution_Db, @security_mode = 1, @publisher_type = N'MSSQLSERVER' /****** End: Script to be run at Distributor ******/ select 'Execute on publisher ' + @publisher select 'use master exec sp_adddistributor @distributor = ''' + @distributor + ''' , @password = '''+ @password +''''
One Reply to “Configuring distributor and publisher with T-SQL”