Moving the publisher/distributor to a new server

We are now running a merge publication with ServerA (in our office network)
as the publisher and distributor and Server1 (hosted remotely by our ISP) as
a push subscriber. We are adding ServerB to our office network, and it will
replace ServerA, so it needs to become the publisher/distributor of the
publication. All instances of SQL Server are SQL Server 2000, SP4.
Two alternate approaches to this task come to mind: (1) drop the publication
on ServerA (using the option to delete the database on Server1), use
BACKUP/RESTORE to copy the published database from ServerA to ServerB, create
a new publication on ServerB, and create a new subscription on Server1; or
(2) create a new subscription on ServerB, drop the subscription on ServerB
without deleting the replicated database, drop the publication on ServerA
(again using the option to delete the database on Server1), create a new
publication on ServerB, and create a new subscription on Server1. I can think
of advantages to both approaches. I would greatly appreciate any guidance re
which way to go. Thanks in advance.
Steve,
I would use option (1). This is generally faster than using the snapshot to
set up the database, and you can be sure that all the constraints and
indexes etc have been included. It depends, however on how many tables you
have which don't participate in the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com (Liverpool FC!)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Moving the distribution database to a new server

We have one server that is both the Publisher and Distributer. We need to
move the distributer to a separate server. Does anyone know of a good
article for moving the distributor or the steps involved?
We are doing both merge and transactional replication. We have 450
subscribers now and have seen some locking issues (I was able to resolve
these right now). We have both push and pull subscriptions. Most of our
subscribers only connect once or twice a day. We will be adding 200 users
over the next 6 months. Next year we may be adding 1200 additional users.
Any help would be appreciated.
You have to drop all publications and subscriptions and then rebuild them
after you have migrated to the remote distributor.
Hilary Cotter
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
"andersos" <andersos@.discussions.microsoft.com> wrote in message
news:67AF7017-8B6E-4B1C-8C31-81E35609BC51@.microsoft.com...
> We have one server that is both the Publisher and Distributer. We need to
> move the distributer to a separate server. Does anyone know of a good
> article for moving the distributor or the steps involved?
> We are doing both merge and transactional replication. We have 450
> subscribers now and have seen some locking issues (I was able to resolve
> these right now). We have both push and pull subscriptions. Most of our
> subscribers only connect once or twice a day. We will be adding 200 users
> over the next 6 months. Next year we may be adding 1200 additional users.
> Any help would be appreciated.

Moving subscriber to a new server

Hi,
environment:
SQL Server 2000 EE, SP4, latest security update on win 2003. Using merge PULL
replication.one Publisher, 2 subscribers.
We have a need to move a DB with a pull subscription to a new
server. The new server will be setup on the same network with the same name as
the old one. (publisher on a different network)
after moving the database (detach,attach ,subscriptiom need to be deleted to
do this) and if the publisher has new data inserted during the move how can I
get the subscriber on the new server to synch with publisher and get the data
changes.
CTS DBA
hey can anybody please help?
I got till this Monday only to come up with the solution
CTS DBA
"CTS_DBA" wrote:

> Hi,
> environment:
> SQL Server 2000 EE, SP4, latest security update on win 2003. Using merge PULL
> replication.one Publisher, 2 subscribers.
> We have a need to move a DB with a pull subscription to a new
> server. The new server will be setup on the same network with the same name as
> the old one. (publisher on a different network)
> after moving the database (detach,attach ,subscriptiom need to be deleted to
> do this) and if the publisher has new data inserted during the move how can I
> get the subscriber on the new server to synch with publisher and get the data
> changes.
> --
> CTS DBA
|||Have a look at @.allow_subscription_copy which is a property of the
publication. It can be set to allow copying of subscriber databases. Can't
test your particular requirements at the moment, but please give it a go.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||thanks paul,
copying subscription works for dbs less than 2GB, mine is 12Gb.
I'm still testing this but looks like it's working,
synch the subscriber with the publication,
run the snapshot agant
make a copy of the db and move it to the new server
create a new subscription.
the new subscription will captures changes since the last back up
any body has any commnets?
"Paul Ibison" wrote:

> Have a look at @.allow_subscription_copy which is a property of the
> publication. It can be set to allow copying of subscriber databases. Can't
> test your particular requirements at the moment, but please give it a go.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

moving replication to new servers

I've got a publisher, distributor and subscriber on different servers and I
want to move all 3 to different servers. Is there a way to restore the
publishing database
on the new publisher, the subscribing database on the new subscriber and
then continue replication? knowing, of course, that I'd have to set up the
new distributor as well.
I know I can create the new publication, backup the database and restore
that on the new subscriber and initialize replication backup, but the
databases are fairly large and if I can restore both the publisher and the
subscriber at the same time, it would save much time. I need to minimize
down time.
Thanks!
There is no real way to do this. What Microsoft does support is restoring
master, distribution, msdb, and the publication database on a new server,
restoring the last tlog of the publication database on the new server with
the keep_replication switch, taking the first server offline, rebooting the
standby after renaming it as the original publisher name, and then issuing a
sp_replrestart to get everything working again.
Hilary Cotter
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
"Steve" <Steve.sam@.hotmail.com> wrote in message
news:eb2nTSzaHHA.2448@.TK2MSFTNGP02.phx.gbl...
> I've got a publisher, distributor and subscriber on different servers and
> I want to move all 3 to different servers. Is there a way to restore the
> publishing database
> on the new publisher, the subscribing database on the new subscriber and
> then continue replication? knowing, of course, that I'd have to set up
> the new distributor as well.
> I know I can create the new publication, backup the database and restore
> that on the new subscriber and initialize replication backup, but the
> databases are fairly large and if I can restore both the publisher and the
> subscriber at the same time, it would save much time. I need to minimize
> down time.
> Thanks!
>

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
> >
>

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...
>

Moving publisher db to another server. (any steps on how to do this without reinitializing

I will be doing this all via EM since I don't know much about sql. I
want to move a db that is being published on one server to another. I
will configure them both to use the same distributor so I am pretty sure
this is possible.
tia
-comb
Will the other server have the same name? If not, then unless you are going
to hack the entries in replication system tables, you have to reinitialize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <uhQ7aK1wFHA.3720@.TK2MSFTNGP11.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Will the other server have the same name? If not, then unless you are going
> to hack the entries in replication system tables, you have to reinitialize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
no it will be moving from instance vsql13/ep01 to vsql22/ep03. Can, I
not just move the db over and check or uncheck the box that says
"subscriber already has schema/data"?
Thanks for the response paul!
-comb
|||Comb,
ah - I misunderstood you . I was thinking you were talking about taking
the publication as well. If you're recreating the publication, then doing a
no-sync initialization should be ok. Still, I'd script the stored procs,
remove the remnants of replication on the subscriber first using
sp_removedbreplication, then script back in the stored procs.
Cheers,
Paul
|||In article <ugiD#T4wFHA.2232@.TK2MSFTNGP11.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> ah - I misunderstood you . I was thinking you were talking about taking
> the publication as well. If you're recreating the publication, then doing a
> no-sync initialization should be ok. Still, I'd script the stored procs,
> remove the remnants of replication on the subscriber first using
> sp_removedbreplication, then script back in the stored procs.
> Cheers,
> Paul
>
>
Man you are talking to a network guy forced into a sql enviroment
because i can spell sql. hehe. Do you have instructions on how to do
this on your website? http://www.replicationanswers.com
I will use BOL to see if i can figure out how to run
sp_removedbreplication.
Effectivly what I am doing is what you are describing. I will be
dropping replication from the current db. Detatching that db from one
server and move it to another server, and then recreating the
publications. I can do this the 1 way like you said where I script out
the pubs before I delete them. Then edit what I need to in those
scripts and run them on the new server. However, I would have to know
where in the sql script to tell replication that the subscriber already
has everything. I do not know sql well enough (at all really) to do
this.
Will doing it in the gui (EM) give me the same result? I have seen like
two area's where it looked like an option not to do a new snapshot. One
place is under where you choose with table you are going to choose for a
certain publication. In there there is a little ... box to click and i
think inside there you can choose "subscriber already has data". Then
also I think some where in the wizard you can choose something to the
same effect. like the 2nd picture in this article on your site.
http://www.replicationanswers.com/No...alizations.asp
Thanks for all your help.
-comb
|||Comb,
you're definitely almost there - it's the option "No, subscriber has already
has the schema and data" that you'll need (nosync initialization).
( http://www.replicationanswers.com/No...alizations.asp)
Cheers,
Paul Ibison
|||In article <ehh0wy6wFHA.3892@.TK2MSFTNGP12.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> you're definitely almost there - it's the option "No, subscriber has already
> has the schema and data" that you'll need (nosync initialization).
> ( http://www.replicationanswers.com/No...alizations.asp)
> Cheers,
> Paul Ibison
>
>
ok cool thanks for the responses as usual. Wish me luck!
|||You won't need it - it's easy
Cheers,
Paul
"combfilter" <asdf@.adsf.com> wrote in message
news:MPG.1da4cc05bcaa7b979896bf@.news.newsreader.co m...
> In article <ehh0wy6wFHA.3892@.TK2MSFTNGP12.phx.gbl>,
> Paul.Ibison@.Pygmalion.Com says...
> ok cool thanks for the responses as usual. Wish me luck!
>

Moving Published db to different server (cannot reinitialize)

When I've talked about nosync initialization, this was
connected with subscribers. When moving the publisher to
another server of a different name I have always scripted
out the publication, edited it to alter the servernames
then run the script on the new publisher and
reinitialized. If the tables are large, you could
compress the BCP files (winzip9.0) with an alternative
snapshot location being used for the subscription, or
DVDs etc. BTW we have a 35GB database, 20GB of which is
snapshotted occasionally and using fileshares the whole
process takes 2 hours or so.
HTH,
Paul Ibison
In article <18a501c4dd44$b0da7d00
$a501280a@.phx.gbl>, Paul.Ibison@.Pygmalion.Com
says...
> When I've talked about nosync initialization, this was
> connected with subscribers. When moving the publisher to
> another server of a different name I have always scripted
> out the publication, edited it to alter the servernames
> then run the script on the new publisher and
> reinitialized. If the tables are large, you could
> compress the BCP files (winzip9.0) with an alternative
> snapshot location being used for the subscription, or
> DVDs etc. BTW we have a 35GB database, 20GB of which is
> snapshotted occasionally and using fileshares the whole
> process takes 2 hours or so.
> HTH,
> Paul Ibison
>
how long does it take to distribute that snapshot
to a wan subscriber?
Ok on the compression part. Basically I've run
into where windows wont compress a bcp file over
2gb. I guess that is why you are saying use
winzip or rar. Do a normal snapshot to an
alternate location. Then compress those bcp
files, send those compressed bcp files over the
wan link to the subscriber. They unzip them and
insert that information. They I would "push the
new subscription" at that point? How do i keep
the distribution agent from trying to push the
original snapshot that was made , and only push
the data that is in the log reader?
I guess I could move the snapshot files so it
will just move on to the data that is in the log
reader?
Thanks for the help on this paul.
-comb

Moving Published db to different server (cannot reinitialize)

Paul, Hilary , anyone.
Do you guys have a step by step instructions on
how to move a db to another publisher without
reinitializing? Isn't there like a "no sync"
option. I have seen you guys talk about
sometjhign of that sort in here. These tables we
replicate are too big to have to create new
snapshots and send them over. I want to avoid
the whole process and just pick up where it left
off after the db is moved. Can this be done just
using enterprise manager and not query analyzer?
Thanks,
comb
Do a final sync and stop the agents. Then do your move and do no sync
subscriptions.
This should work.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1c20e370aa015dab989701@.news.newsreader.co m...
> Paul, Hilary , anyone.
> Do you guys have a step by step instructions on
> how to move a db to another publisher without
> reinitializing? Isn't there like a "no sync"
> option. I have seen you guys talk about
> sometjhign of that sort in here. These tables we
> replicate are too big to have to create new
> snapshots and send them over. I want to avoid
> the whole process and just pick up where it left
> off after the db is moved. Can this be done just
> using enterprise manager and not query analyzer?
> Thanks,
> comb
|||In article <erYjhRU3EHA.2788
@.TK2MSFTNGP15.phx.gbl>, hilary.cotter@.gmail.com
says...
> Do a final sync and stop the agents. Then do your move and do no sync
> subscriptions.
> This should work.
>
thanks. Can i do no sync option from Enterprise
Manager. I don't recall seeing that
Thanks
-comb

Moving MSSQL 7.0 Replicated database to MSSQL 2000

Hi,
I have a SQL Server 7.0 database which is configured for merge replication.
It has one publication and the publisher and distributor all reside on the
one server.
I now need to move the database to a new server with MSSQL 2000.
I would like to move the DB, replication and all if that's possible.
I have considered the copy database wizard but, as far as I know, it cannot
deal with replicated databases. I also tried restoring the database but get a
invalid column name'excluded_cols' error message which, I believe, is down to
replication being enabled on the original database.
As the merge replication is relatively uncomplicated I could perhaps create
the publication afresh on the new server. However how do I get the database
on to the MSSQL 2000 server in the first place?
I have also considered installing MSSQL 7.0 on the new server and then
restoring the database and upgrading it. Then decommissioning the old server.
Would replication be enabled on the new server?
Or are there any simpler means of achieving the same ends?
Any advice or shared experience appreciated.
DOM
Hi
Why don't you remove replication, restore the database and then re-create
the replication?
John
"Dom" wrote:

> Hi,
> I have a SQL Server 7.0 database which is configured for merge replication.
> It has one publication and the publisher and distributor all reside on the
> one server.
> I now need to move the database to a new server with MSSQL 2000.
> I would like to move the DB, replication and all if that's possible.
> I have considered the copy database wizard but, as far as I know, it cannot
> deal with replicated databases. I also tried restoring the database but get a
> invalid column name'excluded_cols' error message which, I believe, is down to
> replication being enabled on the original database.
> As the merge replication is relatively uncomplicated I could perhaps create
> the publication afresh on the new server. However how do I get the database
> on to the MSSQL 2000 server in the first place?
> I have also considered installing MSSQL 7.0 on the new server and then
> restoring the database and upgrading it. Then decommissioning the old server.
> Would replication be enabled on the new server?
> Or are there any simpler means of achieving the same ends?
> Any advice or shared experience appreciated.
> DOM
>
|||Hi John,
I would like to leave the original MSSQL 7.0 server fully operational as a
backup in case of problems with the new MSSQL 2000 box.
Therefore I want to leave replication intact on the original server .
I have no problem re-creating replication on the new box (although I'd
prefer not to), as I said it's a relatively simple replication scenario.
My problem is how to copy or restore a replicated MSSQL 7 database onto a
MSSQL 2000 box without any problems and leave the original databse intact on
the original server.
Any ideas?
DOM.
"John Bell" wrote:

> Hi
> Why don't you remove replication, restore the database and then re-create
> the replication?
> John
>
|||Hi
I would install SQL 2000 as a second instance on the second server. Restore
the database on the SQL 7 instance. Remove replication from the database.
Backup the database and restore it on SQL 2000.
Once tested you can use the same method for the upgrade, but on the primary
server you can remove SQL 7 and do a fresh SQL 2000 install. Once that is
working you can upgrade the secondary server.
You can do risk assessment on what/if scenarios, and you may want to take
server images in case you need to recover quickly at any point. Your
secondary server can be a standby alternative SQL 7 installation and SQL 2000
installation (you just need the disc space to hold it all!).
John
"Dom" wrote:
[vbcol=seagreen]
> Hi John,
> I would like to leave the original MSSQL 7.0 server fully operational as a
> backup in case of problems with the new MSSQL 2000 box.
> Therefore I want to leave replication intact on the original server .
> I have no problem re-creating replication on the new box (although I'd
> prefer not to), as I said it's a relatively simple replication scenario.
> My problem is how to copy or restore a replicated MSSQL 7 database onto a
> MSSQL 2000 box without any problems and leave the original databse intact on
> the original server.
> Any ideas?
> DOM.
>
> "John Bell" wrote:

Moving MSSQL 7.0 Replicated database to MSSQL 2000

Hi,
I have a SQL Server 7.0 database which is configured for merge replication.
It has one publication and the publisher and distributor all reside on the
one server.
I now need to move the database to a new server with MSSQL 2000.
I would like to move the DB, replication and all if that's possible.
I have considered the copy database wizard but, as far as I know, it cannot
deal with replicated databases. I also tried restoring the database but get a
invalid column name'excluded_cols' error message which, I believe, is down to
replication being enabled on the original database.
As the merge replication is relatively uncomplicated I could perhaps create
the publication afresh on the new server. However how do I get the database
on to the MSSQL 2000 server in the first place?
I have also considered installing MSSQL 7.0 on the new server and then
restoring the database and upgrading it. Then decommissioning the old server.
Would replication be enabled on the new server?
Or are there any simpler means of achieving the same ends?
Any advice or shared experience appreciated.
DOMHi
Why don't you remove replication, restore the database and then re-create
the replication?
John
"Dom" wrote:
> Hi,
> I have a SQL Server 7.0 database which is configured for merge replication.
> It has one publication and the publisher and distributor all reside on the
> one server.
> I now need to move the database to a new server with MSSQL 2000.
> I would like to move the DB, replication and all if that's possible.
> I have considered the copy database wizard but, as far as I know, it cannot
> deal with replicated databases. I also tried restoring the database but get a
> invalid column name'excluded_cols' error message which, I believe, is down to
> replication being enabled on the original database.
> As the merge replication is relatively uncomplicated I could perhaps create
> the publication afresh on the new server. However how do I get the database
> on to the MSSQL 2000 server in the first place?
> I have also considered installing MSSQL 7.0 on the new server and then
> restoring the database and upgrading it. Then decommissioning the old server.
> Would replication be enabled on the new server?
> Or are there any simpler means of achieving the same ends?
> Any advice or shared experience appreciated.
> DOM
>|||Hi John,
I would like to leave the original MSSQL 7.0 server fully operational as a
backup in case of problems with the new MSSQL 2000 box.
Therefore I want to leave replication intact on the original server .
I have no problem re-creating replication on the new box (although I'd
prefer not to), as I said it's a relatively simple replication scenario.
My problem is how to copy or restore a replicated MSSQL 7 database onto a
MSSQL 2000 box without any problems and leave the original databse intact on
the original server.
Any ideas?
DOM.
"John Bell" wrote:
> Hi
> Why don't you remove replication, restore the database and then re-create
> the replication?
> John
>|||Hi
I would install SQL 2000 as a second instance on the second server. Restore
the database on the SQL 7 instance. Remove replication from the database.
Backup the database and restore it on SQL 2000.
Once tested you can use the same method for the upgrade, but on the primary
server you can remove SQL 7 and do a fresh SQL 2000 install. Once that is
working you can upgrade the secondary server.
You can do risk assessment on what/if scenarios, and you may want to take
server images in case you need to recover quickly at any point. Your
secondary server can be a standby alternative SQL 7 installation and SQL 2000
installation (you just need the disc space to hold it all!).
John
"Dom" wrote:
> Hi John,
> I would like to leave the original MSSQL 7.0 server fully operational as a
> backup in case of problems with the new MSSQL 2000 box.
> Therefore I want to leave replication intact on the original server .
> I have no problem re-creating replication on the new box (although I'd
> prefer not to), as I said it's a relatively simple replication scenario.
> My problem is how to copy or restore a replicated MSSQL 7 database onto a
> MSSQL 2000 box without any problems and leave the original databse intact on
> the original server.
> Any ideas?
> DOM.
>
> "John Bell" wrote:
> > Hi
> >
> > Why don't you remove replication, restore the database and then re-create
> > the replication?
> >
> > John
> >

Moving MSSQL 7.0 Replicated database to MSSQL 2000

Hi,
I have a SQL Server 7.0 database which is configured for merge replication.
It has one publication and the publisher and distributor all reside on the
one server.
I now need to move the database to a new server with MSSQL 2000.
I would like to move the DB, replication and all if that's possible.
I have considered the copy database wizard but, as far as I know, it cannot
deal with replicated databases. I also tried restoring the database but get
a
invalid column name'excluded_cols' error message which, I believe, is down t
o
replication being enabled on the original database.
As the merge replication is relatively uncomplicated I could perhaps create
the publication afresh on the new server. However how do I get the database
on to the MSSQL 2000 server in the first place?
I have also considered installing MSSQL 7.0 on the new server and then
restoring the database and upgrading it. Then decommissioning the old server
.
Would replication be enabled on the new server?
Or are there any simpler means of achieving the same ends?
Any advice or shared experience appreciated.
DOMHi
Why don't you remove replication, restore the database and then re-create
the replication?
John
"Dom" wrote:

> Hi,
> I have a SQL Server 7.0 database which is configured for merge replicatio
n.
> It has one publication and the publisher and distributor all reside on the
> one server.
> I now need to move the database to a new server with MSSQL 2000.
> I would like to move the DB, replication and all if that's possible.
> I have considered the copy database wizard but, as far as I know, it canno
t
> deal with replicated databases. I also tried restoring the database but ge
t a
> invalid column name'excluded_cols' error message which, I believe, is down
to
> replication being enabled on the original database.
> As the merge replication is relatively uncomplicated I could perhaps creat
e
> the publication afresh on the new server. However how do I get the databas
e
> on to the MSSQL 2000 server in the first place?
> I have also considered installing MSSQL 7.0 on the new server and then
> restoring the database and upgrading it. Then decommissioning the old serv
er.
> Would replication be enabled on the new server?
> Or are there any simpler means of achieving the same ends?
> Any advice or shared experience appreciated.
> DOM
>|||Hi John,
I would like to leave the original MSSQL 7.0 server fully operational as a
backup in case of problems with the new MSSQL 2000 box.
Therefore I want to leave replication intact on the original server .
I have no problem re-creating replication on the new box (although I'd
prefer not to), as I said it's a relatively simple replication scenario.
My problem is how to copy or restore a replicated MSSQL 7 database onto a
MSSQL 2000 box without any problems and leave the original databse intact on
the original server.
Any ideas?
DOM.
"John Bell" wrote:

> Hi
> Why don't you remove replication, restore the database and then re-create
> the replication?
> John
>|||Hi
I would install SQL 2000 as a second instance on the second server. Restore
the database on the SQL 7 instance. Remove replication from the database.
Backup the database and restore it on SQL 2000.
Once tested you can use the same method for the upgrade, but on the primary
server you can remove SQL 7 and do a fresh SQL 2000 install. Once that is
working you can upgrade the secondary server.
You can do risk assessment on what/if scenarios, and you may want to take
server images in case you need to recover quickly at any point. Your
secondary server can be a standby alternative SQL 7 installation and SQL 200
0
installation (you just need the disc space to hold it all!).
John
"Dom" wrote:
[vbcol=seagreen]
> Hi John,
> I would like to leave the original MSSQL 7.0 server fully operational as
a
> backup in case of problems with the new MSSQL 2000 box.
> Therefore I want to leave replication intact on the original server .
> I have no problem re-creating replication on the new box (although I'd
> prefer not to), as I said it's a relatively simple replication scenario.
> My problem is how to copy or restore a replicated MSSQL 7 database onto a
> MSSQL 2000 box without any problems and leave the original databse intact
on
> the original server.
> Any ideas?
> DOM.
>
> "John Bell" wrote:
>