No-Sync Replication and Future Schema changes

I have the responsibility of setting up SQL2000 (merge and
transactional) replication of a 30GB database between 3 physical
locations (within 100 miles), over 256KB pipes.
I have done this with no-sync before (backup publisher to USB HD and
restore at subscriber), but the downside of that is that future table
schema changes (using sp_repladdcolumn etc) will not propagate to the
subscriber.
Question: Is there a way that I can setup no-sync replication in such
a way that I can also propagate future schema changes "over the wire"?
Not with merge replication. With transactional replication it is possible to
trick the publisher into thinking that it is dealing with a "automatic"
subscriber.
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 Campbell" <dukeytoo@.gmail.com> wrote in message
news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>I have the responsibility of setting up SQL2000 (merge and
> transactional) replication of a 30GB database between 3 physical
> locations (within 100 miles), over 256KB pipes.
> I have done this with no-sync before (backup publisher to USB HD and
> restore at subscriber), but the downside of that is that future table
> schema changes (using sp_repladdcolumn etc) will not propagate to the
> subscriber.
> Question: Is there a way that I can setup no-sync replication in such
> a way that I can also propagate future schema changes "over the wire"?
>
|||On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Not with merge replication. With transactional replication it is possible to
> trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
Any idea how?
|||Hi Hilary,
I too am interested in knowing the answer to this question (see my recent
thread in this NG title Add additional Subscribers) using a similar approach
(backup existing Subscriber DB, and restore it to a new Subscriber using
nosync) but it appears that would require the future schema changes to be
applied to the subscriber(s) through scripting (sp_addsubscription), and no
longer possible through the Publication Properties, Articles tab ?
I'm using Transactional under SQL 2000 SP4, 1 Publisher/Distributor and 3
PUSH Subscribers; and I need to ADD 2 more PUSH Subscribers to the topology
in the same well connected environment.
If the answer is nestled away in your book somewhere, I do own a copy...just
point me in the right direction.
Many thanks !
Regards,
Frank
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u%23PkYakwHHA.600@.TK2MSFTNGP05.phx.gbl...
> Not with merge replication. With transactional replication it is possible
> to trick the publisher into thinking that it is dealing with a "automatic"
> subscriber.
> --
> 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 Campbell" <dukeytoo@.gmail.com> wrote in message
> news:1183993715.504073.91200@.c77g2000hse.googlegro ups.com...
>
|||This is how I do it.
select * from syssubscriptions where sync_type=2--locate your no sync
subscription.
update syssubscriptions set sync_type=1 where dest_db='nosyncdatabase'
sp_refreshsubscriptions 'your publication name'
Put it back when you are done.
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 Campbell" <dukeytoo@.gmail.com> wrote in message
news:1184005859.811318.34520@.n60g2000hse.googlegro ups.com...
> On Jul 9, 11:31 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> Any idea how?
>
sql