Freigeben über

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

  • Artikel

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.

Comments

  • Anonymous
    April 23, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/configure-distribution-wizard-does-not-appear-while-trying-to-configure-distribution-in-sql-2005/

  • Anonymous
    April 29, 2009
    Somebody knows how to do this with SQL Server 2000?

  • Anonymous
    April 29, 2009
    Hi Cristian. In SQL Server 2000 SP4, we do not have this issue. It has the same fix thats made in Katmai. Somehow this was missed in only SQL 2005 The UI will automatically generate distribution1 database if a Distribution database already exist The issue on your side might be different. Please open a ticket with the PSS and they should be able to troubleshoot further Levi

  • Anonymous
    April 15, 2015
    Hi, i am facing the same issue in sql 2012, ut i tried to restore the distribution database , it will go and restored in the user database section. how to restore inthe system database section? pls helpme on this issue. Actuall i am migration replication form SQL 2005 to sql 2012. while i confogure replication for onr database, at the time i need to configure Distribution database , but wheni right click on the Replicatin i cold not see the "Configure Distribution" option in the replication. this is i am facing the issue.