Changing a Publisher's Distribution Database

If necessary you can change the distribution database used by a publication server. The technique you use to change the distribution database depends on whether or not you need to retain existing publications:

If you do not need to retain existing publications, the procedure is simpler but requires you to completely reestablish replication, including re-creating all publications.
If you need to retain existing publications, the procedure is slightly more complex, but does not require you to re-create publications.

Except where otherwise noted, the following steps assume the use of SQL Enterprise Manager.

To drop existing publications and change a publisher's distribution database

Make a list of existing publications and subscriptions, so that they can later be re-created.
Information on publications can be obtained using sp_helppublication and sp_helparticle. Information on subscriptions can be obtained using sp_helpsubscription.
Make a list of existing subscription servers so that they can later be re-created.
Information on subscription servers can be obtained using sp_helpserver and sp_helpsubscriberinfo.
On the publication server, remove all published databases from the Publishing Databases list.
With SQL Enterprise Manager, this list is accessed using the Replication Configuration/Publishing command from the Server menu.
Note Removing a published database from the Publishing Databases list drops all of its publications, and all subscriptions associated with each publication.
On the publication server, remove all subscription servers from the Enable Publishing to These Servers list.
This list is also accessed using the Replication Configuration/Publishing command from the Server menu.
On the publication server, run sp_serveroption to clear the distribution server setting. Type:
sp_serveroption servername, 'dist', false
If the publication server is configured as its own distributor, servername is the name of the local server. If the publication server is configured to use a remote distribution server, servername is the name of that remote distribution server.
Disconnect from and then reconnect to the publication server.
- To disconnect, in the Server Manager window, click the right mouse button on the publication server, and then from the drop-down menu that appears, choose Disconnect.
- To reconnect, click on the "" box for the publication server.
If the publication server was configured as its own distributor, drop the distribution database. However, if the publication server was configured to use a remote distribution server, skip this step.
Install or select a distribution database.
Select the publication server; from the Server menu, choose Replication Configuration; choose Install Publishing; and complete the Install Replication Publishing dialog box that appears.
Continue with replication setup and completely configure replication.
- On the publication server, set the server publication options. Re-select the publication databases. This automatically sets the status of the database to "published" and creates a log reader task for each published database.
- On the publication server, re-enable publishing to all subscription servers. For each subscriber, if it does not already exist, this adds a new entry in the sysservers table. The server status is set to "sub" (subscription server) and an associated entry for the server is placed in the MSsubscriber_info table of the distribution database. Finally, this creates a cleanup task for the server.
- On the publication server, re-create publications and articles.
- On the subscription servers, subscribe to the publications and articles. The first subscription for each destination database will create a distribution task.

If you need to retain existing publications, the procedure is slightly different. You do not remove published databases from the Publishing Databases list, which allows you to retain the server's publications.

To retain existing publications and change a distribution database

Make a list of existing publications and subscriptions.
Information on publications can be obtained using sp_helppublication and sp_helparticle. Information on subscriptions can be obtained using sp_helpsubscription.
Make a list of existing subscription servers.
Information on subscription servers can be obtained using sp_helpserver and sp_helpsubscriberinfo.
From all subscription servers of the publisher, drop all subscriptions to all publications of that publication server.
On the publication server, remove all subscription servers from the Enable Publishing to These Servers list.
With SQL Enterprise Manager, this list is accessed from the Replication Configuration/Publishing command from the Server menu.
Important Do not clear the check boxes for published databases in the Publishing Databases list. If you do so, all publications for those databases will be dropped. If that occurs, to reestablish replication you will need to re-create all publications.
On the publication server, run sp_serveroption to clear the distribution server setting. Type:
sp_serveroption servername, 'dist', false
If the publication server is configured as its own distributor, servername is the name of the local server. If the publication server is configured to use a remote distribution server, servername is the name of that remote distribution server.
Disconnect from and then reconnect to the publication server.
- To disconnect, in the Server Manager window, click the right mouse button on the publication server, and then from the drop-down menu that appears, choose Disconnect.
- To reconnect, click on the "" box for the publication server.
If the publication server was configured as its own distributor, drop the distribution database. However, if the publication server was configured to use a remote distribution server, skip this step.
Install or select a distribution database.
Select the publication server; from the Server menu, choose Replication Configuration; choose Install Publishing; and complete the Install Replication Publishing dialog box that appears.
On the publication server, set the server publication options.
In the Replication Publishing dialog box, add subscription servers to the Enable Publishing to These Servers list. For each subscriber, if it does not already exist, this adds a new entry in the sysservers table. The server status is set to "sub" (subscription server) and an associated entry for the server is placed in the MSsubscriber_info table of the distribution database. Finally, this creates a cleanup task for the server.
If this dialog box does not appear after you install or select a distribution database, open it by selecting the server and choosing Replication Configuration/Publishing from the Server menu.
On the distribution server, add a log reader task for each published database.
To add a scheduled task, select the server from the Server Manager window; from the Tools menu, choose task scheduling; in the Task Scheduling dialog box, choose the New Task button; and complete the New Task dialog box that appears.
For information about creating replication tasks, see Chapter 16, Scheduling Tasks.
On the subscription servers, resubscribe to the publications and articles.
The first subscription for each destination database will create a distribution task.