Moving Replication Distributor/Publisher

I have an SQL Server 2k box set as a publisher/distributor with several
publications. Each publication has two subscriptions each to seperate
sql 2k servers. I need to move the existings replicated databases from
the current publisher/distributor to a new SQL 2k5 Server that will act
as the new publisher/distributor for the existing publications. All
replication strategies are using merge replication.
Current setup:
Server A (win svr 2000, sql 2k, pub/dist, merge repli)
|
database
|
________ publication _______
| |
subscription B subscription c
| |
| |
Server B (subscriber, sql 2k) Server C (subscriber, sql 2k)
Server D (win svr 2003, sql 2k5, pub/dis, merge repli)
Need to replace Server A with Server D
Does anyone have a decent process for this. Since I am not doing
anything to the subscriber databases, I shouldn't have anything to do
at the distant end? I can't replicate from 2k to 2k5 so it looks like
I would need downtime to move/copy database exactly from server A to
server D. If I cancel the subscriptions from the SQL 2k server, will I
have a problem with the subscribers accepting the new subscriptions
from the new distributor/publisher?
Tks for any help,
TonyYour best approach is to run the agents one last time, queisce the systems,
script out the subscriptions and publications, drop them, and then run them
on server d.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Tony" <tony.otis@.gmail.com> wrote in message
news:1149271877.583934.41800@.i39g2000cwa.googlegroups.com...
>I have an SQL Server 2k box set as a publisher/distributor with several
> publications. Each publication has two subscriptions each to seperate
> sql 2k servers. I need to move the existings replicated databases from
> the current publisher/distributor to a new SQL 2k5 Server that will act
> as the new publisher/distributor for the existing publications. All
> replication strategies are using merge replication.
> Current setup:
> Server A (win svr 2000, sql 2k, pub/dist, merge repli)
> |
> database
> |
> ________ publication _______
> | |
> subscription B subscription c
> | |
> | |
> Server B (subscriber, sql 2k) Server C (subscriber, sql 2k)
>
> Server D (win svr 2003, sql 2k5, pub/dis, merge repli)
> Need to replace Server A with Server D
> Does anyone have a decent process for this. Since I am not doing
> anything to the subscriber databases, I shouldn't have anything to do
> at the distant end? I can't replicate from 2k to 2k5 so it looks like
> I would need downtime to move/copy database exactly from server A to
> server D. If I cancel the subscriptions from the SQL 2k server, will I
> have a problem with the subscribers accepting the new subscriptions
> from the new distributor/publisher?
> Tks for any help,
> Tony
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uNrLGdphGHA.4892@.TK2MSFTNGP02.phx.gbl...
> Your best approach is to run the agents one last time, queisce the
systems,
> script out the subscriptions and publications, drop them, and then run
them
> on server d.
I will add (having done this w/o incident) that in case it's not clear, when
you subscribe on the subscribers, if you say that the subscriber already has
the schema and data you should be able to pick up where you left off w/o
incident.
Incidentally, what I've done to "speed things up" a bit is something like
the following pattern:
Full backup of databases on Server A.
Restore to D with NORECOVERY
Queisce the systems.
Transaction log backup of databases on Server A
Restore to D with RECOVERY
Rebuild replication
Start up connections to Server D.
(note, if the full backups take a long time you can insert another round of
transaction log back/restore BEFORE you queisce the systems.)
Done this way, you can effectively "move" very large databases in a very
short time (since effectively the bulk of the time is spent in the
transaction log backup/restore which should be FAR smaller than copying the
full database.) This is even more true if you've got this all scripted out
in advance.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Tony" <tony.otis@.gmail.com> wrote in message
> news:1149271877.583934.41800@.i39g2000cwa.googlegroups.com...
> >I have an SQL Server 2k box set as a publisher/distributor with several
> > publications. Each publication has two subscriptions each to seperate
> > sql 2k servers. I need to move the existings replicated databases from
> > the current publisher/distributor to a new SQL 2k5 Server that will act
> > as the new publisher/distributor for the existing publications. All
> > replication strategies are using merge replication.
> >
> > Current setup:
> > Server A (win svr 2000, sql 2k, pub/dist, merge repli)
> > |
> > database
> > |
> > ________ publication _______
> > | |
> > subscription B subscription c
> > | |
> > | |
> > Server B (subscriber, sql 2k) Server C (subscriber, sql 2k)
> >
> >
> > Server D (win svr 2003, sql 2k5, pub/dis, merge repli)
> > Need to replace Server A with Server D
> >
> > Does anyone have a decent process for this. Since I am not doing
> > anything to the subscriber databases, I shouldn't have anything to do
> > at the distant end? I can't replicate from 2k to 2k5 so it looks like
> > I would need downtime to move/copy database exactly from server A to
> > server D. If I cancel the subscriptions from the SQL 2k server, will I
> > have a problem with the subscribers accepting the new subscriptions
> > from the new distributor/publisher?
> >
> > Tks for any help,
> >
> > Tony
> >
>