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)