How to remove and move the Distribution database?

We have 3 servers A, B and C.
There were databases on Server B being replicated in C.
The Distribution database is located in server A.
I have been told that the server A is going away.
Question: How do I move the Distribution database from Server A into one of
the other servers?
Do I need to redo the Replication Setup on Servers B and C?
Thanks,
Suresh Kumar
Disclaimer: I tried posting this message in the Replication group but there
were no replies and hence I trying my luck here.Kumar,
I'm trying to envision here because I don't have experience with that
particular case.
I don't think you need to redo the replication. You only need to make sure
the new distribution server is IDENTICAL to the old one. So how do you
mirror the distribution server?
1. Pause the publisher (B). Make sure all transactions are commited. Stop
replication by stopping all agents (log reader, distribution agents,
snapshot agents, etc.) so that there is no open transactions.
2. Backup master, msdb, distribution db on the distribution server. Script
out logins;
3. Bring down the distriubition server;
4. Turn on the new server. Make sure it is set up exactly the same as the
old distribution server. Rename it to A. check IP address, DNS entries, etc.
5. Install SQL server with same version and service packs. Rename the SQL
server name to what is was called on A.
6. Restore master, msdb and distribution databases; Don't forget to run the
login script to recreate them on the new server.
7. At this point, the new server looks the same as the old one. All
replication related tables are there...Neither the publisher nor subscriber
will notice that the server is no longer what is was;
8. Restart replication agents. You should rock...
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Suresh,
Please reply to the group.
It seems your plan becomes more difficult. If you move distribution db to
either B or C, then both server need to reconfigure 'cause they now have to
repoint to the new distribution server. The system tables involved in
replications are not easy to modify to reflect the change. Although it might
be doable, it takes pain to get everything seamless. Considering the effort
and difficulty, you'd rather script out the replication, sever it, and set
up from scratch. Once you get publisher, distributor, and subscriber in
place, you can change the server name in the script and apply on the
publisher, or even do it manually.
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Richard,
Thanks for your input.
Lucky for me the Replication database has not gone to production yet.
Hence, as per your suggestion, I was able to tear it down and rebuild it
from scratch.
My 2 questions now are:
- There is still an old Distribution database in Server A. How do I delete
it. The Delete is dimmed out under Replication/Distributor properties even
though there are no subscriptions to that database. Can I just delete the
old Ditribution database on Server A (by deleting thru DB properties)
- The Publication and distribution are now on Server B. I am not able to do
pull subscriptions from Server C unless I allow 'Anonymous' subscriptions
for the Publications in B. What are the implications of allowing Anonymous
subsriptions? Does this make us more vulnerable from the securities point of
view?
Thanks,
Suresh Kumar
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%237nfTHHWDHA.652@.tk2msftngp13.phx.gbl...
> Suresh,
> Please reply to the group.
> It seems your plan becomes more difficult. If you move distribution db to
> either B or C, then both server need to reconfigure 'cause they now have
to
> repoint to the new distribution server. The system tables involved in
> replications are not easy to modify to reflect the change. Although it
might
> be doable, it takes pain to get everything seamless. Considering the
effort
> and difficulty, you'd rather script out the replication, sever it, and set
> up from scratch. Once you get publisher, distributor, and subscriber in
> place, you can change the server name in the script and apply on the
> publisher, or even do it manually.
>
> Richard
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> > We have 3 servers A, B and C.
> > There were databases on Server B being replicated in C.
> > The Distribution database is located in server A.
> >
> > I have been told that the server A is going away.
> >
> > Question: How do I move the Distribution database from Server A into one
> of
> > the other servers?
> > Do I need to redo the Replication Setup on Servers B and C?
> >
> > Thanks,
> > Suresh Kumar
> >
> > Disclaimer: I tried posting this message in the Replication group but
> there
> > were no replies and hence I trying my luck here.
> >
> >
>|||Hari,
Thanks for your input.
Suresh
<hkvats_1999@.yahoo.com> wrote in message
news:68c8c86d.0308040059.7e3e8d5e@.posting.google.com...
> Hi Suresh,
> You need to follow the complete process for Disabling Distributor.
> --Deletion of Distribution Database
> Execute sp_dropdistributiondb to delete a distribution database.
> --Disable distributors
> Execute sp_dropdistributor with @.no_checks=1
> 'Anonymous' subscriptions
> ==================================================================> Anonymous subscriptions to allow Subscribers unknown to the Publisher
> to subscribe to this publication and not able to change this after the
> publication is created.
> I suggest you go thru with detailed description on this in BOOKS
> ONLINE Topic titled =============="Anonymous Subscriptions"
>
> regards
> Hari Sharma, India