INF: Manual Replication Setup w/ A Remote Distributor

• Microsoft SQL Server, version 6.0

SUMMARY

The Microsoft SQL Server version 6.0 "Administrator's Companion" documents how to set up Replication manually, but does not specifically address how to set up Replication manually when a remote distribution server is used. This article discusses the necessary changes to do this.

MORE INFORMATION

The information here is based on the information in the SQL Server "Administrator's Companion," Chapter 15, "Advanced Replication," under the section titled "Setting up Replication Manually." Steps one through six are presented here with modifications that are necessary to set up Replication manually when a remote distribution server is used.

1. On the distribution server, create a device for the distribution database if it does not already exist. For example:

   DISK INIT NAME = 'distdata', PHYSNAME = 'c:\sql60\data\distrib.dat', VDEVNO = 5, SIZE = 15360 go DISK INIT NAME = 'distlog', PHYSNAME = 'c:\sql60\data\dislog.dat', SIZE = 7680 VDEVNO = 6 go

2. On the distribution server, create the distribution database if it does not already exist. For example:

   CREATE DATABASE distribution on distdata = 30 LOG ON distlog = 15 go

3. On the distribution server, make the distribution database the current database, and then run the INSTDIST.SQL script. This is only necessary if the distribution database has not already been installed.

   This script is located in the \SQL60\INSTALL directory. It adds tables, indexes, and stored procedures to the distribution database.

4. On the publication server, use regedt32 or xp_regwrite to set key values for the name and the working directory for the remote distribution database.

   For example, if on a server named WOLFHOUND you have created a distribution database named distribution and will use a working directory of D:\SQL60\REPLDATA, you could set the key values as follows:

   xp_regwrite 'HKEY_LOCAL_MACHINE',

      'SOFTWARE\Microsoft\MSSQLServer\Replication',
      'DistributionDB',
      'REG_SZ',
      'distribution'
      go
   
      exec("xp_regwrite 'HKEY_LOCAL_MACHINE',
      'SOFTWARE\Microsoft\MSSQLServer\Replication',
      'WorkingDirectory',
      'REG_SZ',
      '\\WOLFHOUND\D$\SQL60\REPLDATA'")
      go
   
   

5. On the publication server, add the distribution server using sp_addserver. Then, use sp_serveroption to define this server as the distribution server. For example, if the server is named WOLFHOUND:

   sp_addserver 'WOLFHOUND' go sp_serveroption 'WOLFHOUND', 'dist', 'true' go

   On the distribution server, add the publishing server using sp_addserver. If the distribution server is being installed for the first time, use sp_serveroption to define this server as the distribution server. For example, if the distribution server is named WOLFHOUND, and the publishing server is named BEAGLE:

   sp_addserver 'BEAGLE' go sp_serveroption 'WOLFHOUND', 'dist', 'true' go

6. Use sp_addserver to define the remaining settings. For example, on the distribution server:

   sp_addpublisher 'BEAGLE', 'dist' go

   And on the publishing server, for example:

   sp_addpublisher 'BEAGLE'