Scripting Replication

Earlier, it was suggested to generate SQL Scripts for all that you do because going through wizards every time you have to configure replication is no way to run a production environment. An option in the Configure and Manage Data Replication screen allows you to generate SQL scripts. Use this option! Next is an example of the SQL scripts needed to generate the same data replication configuration that we just built with wizards. These scripts minimize the errors we will make while supporting our data replication environments.

use master 
GO
exec sp_adddistributor  @distributor = 'C81124-C\DBARCH01', @password =''
GO
-- Adding the distribution database
exec sp_adddistributiondb  @database = 'distribution', @data_folder =
   'd:\MSSQL2000\data',
@data_file ='distribution.MDF', @data_file_size = 3, @log_folder =
   'd:\MSSQL2000\data',
@log_file = 'distribution.LDF', @log_file_size = 1, @min_distretention = 0,
@max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
-- Adding the distribution publisher
exec sp_adddistpublisher  @publisher = 'C81124-C\DBARCH01',
   @distribution_db = 'distribution',
@security_mode = 1, @working_directory = 'd:\MSSQL2000\ReplData',
   @trusted = 'true',
@thirdparty_flag = 0
GO
-- Enabling the replication database
use master
go
exec sp_replicationdboption @dbname = 'Northwind', @optname = 'publish',
    @value = 'true'
go
use [Northwind]
GO
-- Adding the transactional publication
exec sp_addpublication @publication = 'NW_TRANS_Publication',
   @restricted = 'false',
@sync_method = 'native', @repl_freq = 'continuous',
@description='Transactional publication of Northwind database from Publisher
   C81124-C\DBARCH01.',
@status = 'active', @allow_push = 'true', @allow_pull = 'true', @allow_
   anonymous = 'false',
@enabled_for_internet = 'false', @independent_agent = 'false', @immediate_
   sync = 'false',
@allow_sync_tran = 'false', @autogen_sync_procs = 'false', @retention = 336,
@allow_queued_tran = 'false',@snapshot_in_defaultfolder = 'true', @compress_
   snapshot='false',
@ftp_port = 21, @ftp_login = 'anonymous',@allow_dts ='false',@allow_
   subscription_copy='false',
@add_to_active_directory = 'false',@logreader_job_name =
   'C81124-C\DBARCH01-Northwind-1'

exec sp_addpublication_snapshot @publication = 'NW_TRANS_Publication',
   @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 = 224300, @active_end_
   time_of_day = 0,
@snapshot_job_name = 'C81124-C\DBARCH01-Northwind-NW_TRANS_Publication-1'
GO
-- Granting access to the publication
exec sp_grant_publication_access @publication = 'NW_TRANS_Publication',
@login = 'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @publication = 'NW_TRANS_Publication',
@login = 'distributor_admin'
GO
exec sp_grant_publication_access @publication = 'NW_TRANS_
   Publication', @login = 'sa'
GO
-- Adding the transactional articles
exec sp_addarticle @publication = 'NW_TRANS_Publication',
   @article = 'Categories',
@source_owner = 'dbo', @source_object = 'Categories', @destination_
   table = 'Categories',
@type = 'logbased', @creation_script = null, @description = null,
   @pre_creation_cmd = 'drop',
@schema_option = 0x00000000000000F3, @status = 16, @vertical_
   partition = 'false',
@ins_cmd = 'SQL', @del_cmd = 'SQL', @upd_cmd = 'SQL', @filter = null,
   @sync_object = null,
@auto_identity_range = 'false'
GO
exec sp_addarticle /* etc. for all 13 tables to be published */
go
exec sp_addsubscriber @subscriber = 'C81124-C\DBARCH01',
   @type = 1, @login = '',
@password = '', @security_mode = 0, @frequency_type = 64,
   @frequency_interval = 1,
@frequency_relative_interval = 2, @frequency_recurrence_factor = 0,
   @frequency_subday = 8, @frequency_subday_interval = 1, @active_
   start_date = 0, @active_end_date = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235900,
   @description = ''
GO
-- Adding the transactional subscription
exec sp_addsubscription @publication = 'NW_TRANS_Publication',
   @article = 'all',
@subscriber = 'C81124-C\DBARCH01', @destination_db = 'Southwind',
   @sync_type = 'none',
@update_mode = 'read only'
GO

You also need to monitor the appropriate replication stored procedures and break down the configuration.