configure distribution wizard does not appear while trying to configure Distribution in SQL 2005

  • Article

Recently we encountered a case wherein distribution wizard was not appearing when we try to configure distribution server using sql server 2005 management studio.

We do not see any error message in the errorlog or application event log while the cursor turns to hourglass for few seconds and disappears.

Cause
=====

We later found that you can observe this issue when you restore Distribution database as a User Database.

The Distributor is a server that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication. To set up replication, you must configure a Distributor. Each Publisher can be assigned to only a single Distributor instance, but multiple publishers can share a Distributor.

RESOLUTION:

==========

In order to resolve the issue we need to delete the Distribution database since Distribution database is one of the system databases which gets created while configuring the Distributor server and could not be restored as a User Database

STEPS TO REPRO:

============

1. Configure a Distributor (This will create a distribution database (System database))
2. Make a backup of the distribution database
3. Disable the Distribution
4. Drop the distribution database
5. Restore the Distribution database
6. Now in the Management Studio right click the Replication folder and select Configure Distribution
7. Now you will see the hourglass and it will disappear in few seconds

ADDITIONAL INFORMATION:
====================

If you run profiler you will see "Select * from sysdatabases where name ='distribution' running in a loop
It might even slowdown the system. To stop this from happening Close the SQL Management Studio and reopen it.

The above behavior is changed in Katmai in which instead of using the existing restored distribution database, UI will automatically generate a new distribution1 as dist db name.

Levi Justus
Technical Lead, Microsoft Sql Server.