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. |