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

I have a silly problem that could have been solved months ago if it was
caught, but it wasn't.
I purchased a Dell Poweredge as my server to host publication databases that
a string of retail stores synchronize (pull merge subscriptions) with to
share customer information. The developer of the software that utilizes the
databases set up the initial databases and publications, and the first
subscriptions. We don't have an IT department, and I am the person who knows
the most about computers out of a very computer illiterate employee base, so
I have become the SQL goto guy.
Since then (it's been about 4 months) I have learned enough about SQL to be
dangerous and have found a problem. Dell ships it's Servers with a small hard
drive (10GB) as the operating system drive (C drive), and the larger mirrored
drive (60GB) is separate from this (D Drive). The software developer created
the publication databases on the small drive and I am getting dangerously
close to the size limit, while leaving the mirrored "safe" drive virtually
empty.
Here is the question, is there a way to move the publications to the larger
drive without dropping the subscriptions and having to recreate the
publications? At the moment I have 16 retail stores subscribed, and they are
not close together, and they are open 18 hours a day (some are 24 hours). If
I need to drop the subscriptions, download the snapshot to each remote site,
and then recreate the subscription, it will be a disaster; the zipped
snapshot file is 215MB at the moment. Can I move the databases to the D Drive
while maintaining the current subscriptions?
Hopefully someone can help me with my problem.
no, but you can add filegroups to the data file and then migrate tables and
indexes to this filegroup. You can also add a filegroup to the tlog and then
shrink the existing one, and cap how large it can grow.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:1DB4433F-833E-4B6B-9D6F-22621EE880FD@.microsoft.com...
> I have a silly problem that could have been solved months ago if it was
> caught, but it wasn't.
> I purchased a Dell Poweredge as my server to host publication databases
that
> a string of retail stores synchronize (pull merge subscriptions) with to
> share customer information. The developer of the software that utilizes
the
> databases set up the initial databases and publications, and the first
> subscriptions. We don't have an IT department, and I am the person who
knows
> the most about computers out of a very computer illiterate employee base,
so
> I have become the SQL goto guy.
> Since then (it's been about 4 months) I have learned enough about SQL to
be
> dangerous and have found a problem. Dell ships it's Servers with a small
hard
> drive (10GB) as the operating system drive (C drive), and the larger
mirrored
> drive (60GB) is separate from this (D Drive). The software developer
created
> the publication databases on the small drive and I am getting dangerously
> close to the size limit, while leaving the mirrored "safe" drive virtually
> empty.
> Here is the question, is there a way to move the publications to the
larger
> drive without dropping the subscriptions and having to recreate the
> publications? At the moment I have 16 retail stores subscribed, and they
are
> not close together, and they are open 18 hours a day (some are 24 hours).
If
> I need to drop the subscriptions, download the snapshot to each remote
site,
> and then recreate the subscription, it will be a disaster; the zipped
> snapshot file is 215MB at the moment. Can I move the databases to the D
Drive
> while maintaining the current subscriptions?
> Hopefully someone can help me with my problem.
|||Hmmm, I think that would help to restrict growth, but I need to move all of
the databases to the larger hard drive. The large hard drive is mirrored
(RAID) and is therefore "safer" than the unprotected smaller drive. Plus, I
may need the space on the smaller drive for program upgrades and installs in
the future.
I read something in one of the threads about moving the file and, if it
hasn't been changed since the last synch, then it doesn't need to synch on
initialization...how is this done? My biggest problem is the synch on
reinitialization when I recreate the subscription, it would take too long to
download the snapshot to all the remote sites.
So, if I detach and then attach the publication database in another
location, but on the same server, can the subscribers reinitialize without
synch'ing if there haven't been any changes?
"Hilary Cotter" wrote:

> no, but you can add filegroups to the data file and then migrate tables and
> indexes to this filegroup. You can also add a filegroup to the tlog and then
> shrink the existing one, and cap how large it can grow.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Pete" <Pete@.discussions.microsoft.com> wrote in message
> news:1DB4433F-833E-4B6B-9D6F-22621EE880FD@.microsoft.com...
> that
> the
> knows
> so
> be
> hard
> mirrored
> created
> larger
> are
> If
> site,
> Drive
>
>
|||When I said adding a file group to tempdb or any other database, I intended
for this filegroup to be on the larger drive.
You can't detach databases which are published for replication. You could
1) Kick all users off the system(s)
2) schedule the merge agents or remove the -Continuous switch, stop and
start the agents and then run then until they stop
3) script out the publication
4) remove the publications
5) unpublish the database
6) detach the databases
7) retach them on the larger drive
8) recreate the publications
9) do a no-sync subscription (the subscriber already has the schema and
data)
10) when the metadata snapshot has been applied, allow your users back on
The no-sync subscription method seems to be the "moving the file and, if it
hasn't been changed since the last synch, then it doesn't need to synch on
initialization..." method you are talking about.
The problem with this is that if there are updates going on on your
subscriber you are asking for trouble.
The good news is that the snapshot with the no-sync subscription is very
small and can be created and distributed very quickly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:58A5855C-EB3B-4AB0-A4FC-0C85934087C3@.microsoft.com...
> Hmmm, I think that would help to restrict growth, but I need to move all
of
> the databases to the larger hard drive. The large hard drive is mirrored
> (RAID) and is therefore "safer" than the unprotected smaller drive. Plus,
I
> may need the space on the smaller drive for program upgrades and installs
in
> the future.
> I read something in one of the threads about moving the file and, if it
> hasn't been changed since the last synch, then it doesn't need to synch on
> initialization...how is this done? My biggest problem is the synch on
> reinitialization when I recreate the subscription, it would take too long
to[vbcol=seagreen]
> download the snapshot to all the remote sites.
> So, if I detach and then attach the publication database in another
> location, but on the same server, can the subscribers reinitialize without
> synch'ing if there haven't been any changes?
> "Hilary Cotter" wrote:
and[vbcol=seagreen]
then[vbcol=seagreen]
was[vbcol=seagreen]
databases[vbcol=seagreen]
to[vbcol=seagreen]
utilizes[vbcol=seagreen]
base,[vbcol=seagreen]
to[vbcol=seagreen]
small[vbcol=seagreen]
dangerously[vbcol=seagreen]
virtually[vbcol=seagreen]
they[vbcol=seagreen]
hours).[vbcol=seagreen]
D[vbcol=seagreen]
|||I think that's what I need to do, a no-synch subscription. So, as long as no
changes are made between the time I detach the publication and then recreate
it and reinitialize it on the larger drive then I shouldn't have any problems?
"Hilary Cotter" wrote:

> When I said adding a file group to tempdb or any other database, I intended
> for this filegroup to be on the larger drive.
> You can't detach databases which are published for replication. You could
> 1) Kick all users off the system(s)
> 2) schedule the merge agents or remove the -Continuous switch, stop and
> start the agents and then run then until they stop
> 3) script out the publication
> 4) remove the publications
> 5) unpublish the database
> 6) detach the databases
> 7) retach them on the larger drive
> 8) recreate the publications
> 9) do a no-sync subscription (the subscriber already has the schema and
> data)
> 10) when the metadata snapshot has been applied, allow your users back on
> The no-sync subscription method seems to be the "moving the file and, if it
> hasn't been changed since the last synch, then it doesn't need to synch on
> initialization..." method you are talking about.
> The problem with this is that if there are updates going on on your
> subscriber you are asking for trouble.
> The good news is that the snapshot with the no-sync subscription is very
> small and can be created and distributed very quickly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Pete" <Pete@.discussions.microsoft.com> wrote in message
> news:58A5855C-EB3B-4AB0-A4FC-0C85934087C3@.microsoft.com...
> of
> I
> in
> to
> and
> then
> was
> databases
> to
> utilizes
> base,
> to
> small
> dangerously
> virtually
> they
> hours).
> D
>
>
|||Pete,
one caveat about nosync initializations -
sp_repladdcolumn or sp_repldropcolumn can't be used
afterwards for the published articles.
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||yes, if you can guarantee that all users are off your subscribers while you
are making these changes you should be fine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:FCBD98A4-2984-4298-9CAA-9B8026195D2E@.microsoft.com...
> I think that's what I need to do, a no-synch subscription. So, as long as
no
> changes are made between the time I detach the publication and then
recreate
> it and reinitialize it on the larger drive then I shouldn't have any
problems?[vbcol=seagreen]
> "Hilary Cotter" wrote:
intended[vbcol=seagreen]
could[vbcol=seagreen]
on[vbcol=seagreen]
it[vbcol=seagreen]
on[vbcol=seagreen]
all[vbcol=seagreen]
mirrored[vbcol=seagreen]
Plus,[vbcol=seagreen]
installs[vbcol=seagreen]
it[vbcol=seagreen]
synch on[vbcol=seagreen]
long[vbcol=seagreen]
without[vbcol=seagreen]
tables[vbcol=seagreen]
and[vbcol=seagreen]
it[vbcol=seagreen]
with[vbcol=seagreen]
first[vbcol=seagreen]
who[vbcol=seagreen]
employee[vbcol=seagreen]
SQL[vbcol=seagreen]
larger[vbcol=seagreen]
developer[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
remote[vbcol=seagreen]
zipped[vbcol=seagreen]
the[vbcol=seagreen]
|||Thanks for the help Hillary, much appreciated :-)
Paul,
What if I reinitialize them with synch (and a full snapshot download)
gradually over the weeks after the initial no-synch reinitialization? Will
that correct the caveat?
"Paul Ibison" wrote:

> Pete,
> one caveat about nosync initializations -
> sp_repladdcolumn or sp_repldropcolumn can't be used
> afterwards for the published articles.
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Using sp_dropsubscription for a separate article and
sp_addsubscription with @.sync_type = automatic (followed
by running the snapshot agent and the distribution agent)
should work.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

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