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.



Microsoft SQL Server 2000 Unleashed
Microsoft SQL Server 2000 Unleashed (2nd Edition)
ISBN: 0672324679
EAN: 2147483647
Year: 2002
Pages: 503

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net