Drop replication question

Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
production that contains DDL, I drop the publication and then drop the
subscription on the replicated server. Once the update is complete, I
recreate the publication with any changes that are necessary and then
re-create the subscription, and then the snapshot agent runs. Is that what
most people do?
This works fine, but I feel it is overkill. Is there a simpler way to
temporarily disable the replication until the DDL changes are made without
dropping and recreating the publication and subscription?
Is there any negative impact by my dropping and recreating the publication
and subscription.
Thanks
Ron
Many schema changes will be automatically replicated is the replicate_ddl
parameter is set to true for sp_addpublication and sp_addmergepublication.
There are some which you will have to do the drop subscriptions and drop
publications, for example changes to pk, adding defaults, identities, etc.
The problem with your approach is that there is add overhead and your
subscriber tables have to be recreated each time.
http://www.zetainteractive.com - Shift Happens!
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
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
> Using SQL2005 SP2 transactional replication. Whenever we have a deploy to
> production that contains DDL, I drop the publication and then drop the
> subscription on the replicated server. Once the update is complete, I
> recreate the publication with any changes that are necessary and then
> re-create the subscription, and then the snapshot agent runs. Is that
> what
> most people do?
> This works fine, but I feel it is overkill. Is there a simpler way to
> temporarily disable the replication until the DDL changes are made without
> dropping and recreating the publication and subscription?
> Is there any negative impact by my dropping and recreating the publication
> and subscription.
> Thanks
> Ron
|||Just so I'm clear, I think what you're saying is that there isn't any
intermediate step such as disable or turn off replication temporarily, you
would need to drop subscription and publication.
We do have "replicate Schema Changes" = TRUE, however one of our deploy code
failed due to replication being active. I guess since not knowing exactly
what can and can't be replicated, it wouldn't hurt to simply drop publication
and subscription, perform the changes and then add the publication and
subscription back again. Since a deploy is done after hours and replication
currently only takes about 15 minutes to add publication and subscription as
well as run the snapshot agent, it sounds like it's the safest way to go as
the time it takes to recreate the publication and subscription isn't an issue
to us.
Thanks.
"Hilary Cotter" wrote:

> Many schema changes will be automatically replicated is the replicate_ddl
> parameter is set to true for sp_addpublication and sp_addmergepublication.
> There are some which you will have to do the drop subscriptions and drop
> publications, for example changes to pk, adding defaults, identities, etc.
> The problem with your approach is that there is add overhead and your
> subscriber tables have to be recreated each time.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:A7ED2B66-F49D-4FD3-AD22-54CD8A54DAB9@.microsoft.com...
>
>
|||Exactly, there is no step that makes replication disabled.
There are some schema changes which are not replicated as you have found
out. I would probably try the deploy code out in a test environment to find
out what breaks and then remove those articles from the publication, make
the change and then add them back in.
http://www.zetainteractive.com - Shift Happens!
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
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...[vbcol=seagreen]
> Just so I'm clear, I think what you're saying is that there isn't any
> intermediate step such as disable or turn off replication temporarily, you
> would need to drop subscription and publication.
> We do have "replicate Schema Changes" = TRUE, however one of our deploy
> code
> failed due to replication being active. I guess since not knowing exactly
> what can and can't be replicated, it wouldn't hurt to simply drop
> publication
> and subscription, perform the changes and then add the publication and
> subscription back again. Since a deploy is done after hours and
> replication
> currently only takes about 15 minutes to add publication and subscription
> as
> well as run the snapshot agent, it sounds like it's the safest way to go
> as
> the time it takes to recreate the publication and subscription isn't an
> issue
> to us.
> Thanks.
> "Hilary Cotter" wrote:
|||Thanks for your help and clarification.
"Hilary Cotter" wrote:

> Exactly, there is no step that makes replication disabled.
> There are some schema changes which are not replicated as you have found
> out. I would probably try the deploy code out in a test environment to find
> out what breaks and then remove those articles from the publication, make
> the change and then add them back in.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:57CDE6A7-E4C1-45D7-8F1F-02BCD8A763C6@.microsoft.com...
>
>