Reconfigure replication to use a remote distributor

• David Brusowankin-283008

  SSC-Addicted

  Points: 471

  More actions

  December 22, 2010 at 12:36 pm

  #388488

  Hi,

  I have to reconfigure transactional replication so that instead of using a local distributor it will use a remote distributor. Does anyone have a script for this?

  Thanks,

  David

• Satnam Singh

  Hall of Fame

  Points: 3542

  More actions

  January 26, 2011 at 11:02 pm

  #1278925

  On the remote machine where u r thinking of keeping the distribution database first configure the distributor there and theen start setting up the transactional replication on the machine which u have decided.While setting up the transactional replictation specify the distributor server location. U will come across this once u start setting up.

  Satnam

• RML51

  SSCarpal Tunnel

  Points: 4410

  More actions

  January 28, 2011 at 9:11 am

  #1279762

  Here's a script that we use on a SQL2005-SP3 setup for transactional replication...

  USE master

  EXEC sp_adddistributor

  @distributor = N'MyServerDISTDB\MyServerDIST',

  @password = N'SomePW'

  EXEC sp_MSupdate_agenttype_default @profile_id = 1

  EXEC sp_MSupdate_agenttype_default @profile_id = 2

  EXEC sp_MSupdate_agenttype_default @profile_id = 4

  EXEC sp_MSupdate_agenttype_default @profile_id = 6

  EXEC sp_MSupdate_agenttype_default @profile_id = 11

  EXEC sp_adddistributiondb

  @database = N'distribution',

  @data_folder = N'D:\Microsoft SQL Server\MSSQL.2\MSSQL\Data',

  @data_file = N'distribution.MDF',

  @log_folder = N'D:\Microsoft SQL Server\MSSQL.2\MSSQL\Data',

  @log_file = N'distribution.LDF',

  @security_mode = 0,

  @login = N'AccountToUse',

  @password = 'MyPW'

  EXEC sp_adddistpublisher

  @publisher = N'MyServerInstance\MyInstance',

  @distribution_db = N'distribution',

  @security_mode = 1,

  @working_directory = N'\\MyServerDISTDB\Snapshot',

  @thirdparty_flag = 0

  GO

  Good Luck!