none
Re-create distribution database RRS feed

  • Question

  • Hello all,

    We have one publisher and one subscriber, both sql 2008 R2. The distributor and subscriber server is the same, so the distribution database is located in this server. We had transactional replication running, it wasn't working properly. I want to re-create the distribution database, so I removed all publications and subscriptions.

    So to drop and re-create the distribution database, should I run the "disable publishing and distribution" wizard at distributor/subscriber server? Do I need to run the wizard from publisher as well? Also, how can I re-create the distribution database for the same pubslisher server again?

    Please provide the steps for doing this.

    Thanks in advance.

    Monday, October 15, 2012 1:46 PM

Answers

All replies

  • I don't advise putting the distributor on the subscriber. In fact why are you using a remote distributor at all?

    To answer your questions,

    1) run disable publishing and distribution wizard at your publisher and then subscriber.

    2) to recreate the distribution database run the enable publishing wizard at the distributor, configure a replication (administrative link) password. Then enable publishing at the publisher and select the remote distributor and give it the replication (administrative link) password


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Marked as answer by Ryan_XH Monday, October 15, 2012 4:12 PM
    Monday, October 15, 2012 3:40 PM
    Moderator
  • Thanks for your reply.

    Actually the reason I am planning to have a remote distributor is beacuse the publisher is a busy server, so I think having the distributor with the subscriber server would allow to isolate the subscription synchronization process from the publisher. 

    Monday, October 15, 2012 3:53 PM
  • How many databases are publisher on your publisher? Is the answer is one, or under 10 it is likely you will not need a remote distributor.

    You can tell by seeing how much cumulative CPU the log reader agents take.

    Now if you are still hung up with using a remote distributor is your remote distributor clustered? What are you going to do if your subscriber/distributor goes down for an extended period? Your tlog on your publisher will get very large during that time.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, October 15, 2012 4:01 PM
    Moderator