Setting Up Replication Manually

You can configure replication by using SQL Enterprise Manager, or you can set up replication manually by using the instructions that follow. You will also find the following information useful if you want to create a replication setup script.

To set up replication manually

On the publication server, create a device for the distribution database. 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

On the publication server, create the distribution database. For example:
```
CREATE DATABASE distribution on distdata = 30
LOG ON distlog = 15
go
```
Make the distribution database the current database, and then run the INSTDIST.SQL script.
This script is located in the \SQL60\INSTALL directory. It adds tables, indexes, and stored procedures to the distribution database.
Use regedt32 or xp_regwrite to set key values for the name and the working directory for the 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', '\\"@@servername+"\D$\SQL60\REPLDATA'")
go
```
Use sp_serveroption to define this server as the distribution server. For example, if the server is named WOLFHOUND:
```
sp_serveroption 'WOLFHOUND', 'dist', 'true'
go
```
Use sp_serveroption to define this server as a publication server. For example:
```
sp_serveroption 'WOLFHOUND', 'pub', 'true'
go
```
You can check results with sp_helpserver.
On the publication server, use sp_addsubscriber to add subscription servers. For example:
```
sp_addsubscriber BLOODHOUND
go
sp_addsubscriber AIREDALE
go
```
You can check results with sp_helpserver.
On both subscribers, use sp_addpublisher to add the publication server. For example:
```
sp_addpublisher WOLFHOUND
```
On both subscribers, use sp_dboption to enable a database as a subscription database. For example:
```
use master
go
sp_dboption pubs,subscribed,true
go
```
On the publisher, use sp_dboption to enable a database as a publication database. For example:
```
use master
go
sp_dboption pubs,published,true
go
```
On the publisher, use sp_addtask to manually create the scheduled task that will be used for synchronization of the publication. For example:
```
use msdb
go
sp_addtask 'auth_table_sync','Sync','WOLFHOUND','sa','pubs',
1,4,1,8,1,0,0,19940926,19991231,090000,230000,00000000,160000,0,
'',2,1,'',2,2
```
This adds a task "auth_table_sync" that will be run in the Sync Subsystem every hour from 9 A.M. to 11 P.M. It is scheduled to run after September 26, 1994. The task will start at 4:00 P.M. the first day.
You can check the results by using sp_helptask. For example:
```
use msdb
go
sp_helptask auth_table_sync, @mode = 'full'
go
```
On the publisher, use sp_addpublication to add a publication. For example:
```
use pubs
go
exec sp_addpublication authors_publication,n,@status = active, @description = 'full authors table'
go
```
where "n" is the task ID for the task created using sp_addtask.
You can check the results by using sp_helppublication. For example:
```
sp_helppublication
go
```
Create a table creation script either by hand or by using a utility such as SQL Transfer Manager. The script should be edited to follow the following rules:
- The table schema must match the table in the published article.
- All columns must be created with an explicit NULL or NOT NULL, entered in uppercase only.
- No columns can be created with the identity property.
- Timestamp columns must be changed to binary(8).
The remaining steps in this procedure assume that you have created a script named AUTHORS.SCH. A example of such a script is provided in A Sample Schema Script, next in this chapter.

On the publisher, use sp_addarticle to create an article. For example:

exec sp_addarticle authors_publication, authors,
authors, 
@creation_script = 'c:\sql60\repldata\authors.sch',
@description = 'Authors Table Article'
go

You can check the results by using sp_helparticle and sp_helparticlecolumns. For example:

sp_helparticle
go
sp_helparticlecolumns @publication=authors_publication, @article=authors
go

On the publisher, use sp_addsubscription to add a subscription and automatically synchronize. For example:
```
sp_addsubscription authors_publication, authors, AIREDALE
go
```
While setting up replication, the log reader and distribution tasks were created as needed by using default scheduling frequencies. You can view these by using sp_helptask. For example, to display all currently scheduled tasks:
```
use msdb
go
sp_helptask @subsystem='Distribution'
go
sp_helptask @subsystem='LogReader'
go
```