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

Drop database fails because of aspnet_wp.exe

Hey everyone.

I'm having some problems dropping a database because the aspnet_wp.exe process is accessing it. Whenever I try to drop it, I get an error stating that the database is being used. If I access the task manager and end the aspnet_wp.exe process manually and then retry the database drop it works... :\

Any help? Thaks in advance,

Sérgio

You can't drop the database because it is using by some .NET application. aspnet_wp.exe is a worker process in which .NET runs code, you can take a look at this article:

http://www.xefteri.com/articles/show.cfm?id=14

|||A connection is being kept open by an asp.net application. Either it forgot to close the connection after it was rendering a page, or (more likely), the connection was kept open in the connection pool.

driver's sqlallochandle on sql_handle_env failed

i have just installed sql clinet
(Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
on windows XP sp2 . whenever i try to reg new sql servers through Enterprise
Mgr it gives me this error . it looks like its associated with ODBC . need to
figure out whats the problem
Thanks & Regards
Sid
Try applying SQL Server 2000 SP4 + Cumulative Hotfix Build 2187.
http://www.microsoft.com/downloads/details.aspx?FamilyID=8e2dfc8d-c20e-4446-99a9-b7f0213f8bc5&DisplayLang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=9c9ab140-bdee-44df-b7a3-e6849297754a&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyID=1705bd2f-1fb8-4ec8-b3db-0935361308c7&displaylang=en
http://www.microsoft.com/downloads/details.aspx?familyid=A643980A-26A4-44C1-9B50-53E20E7210B5&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=243A8A89-74D6-48FD-933F-32FF9D8459C2&displaylang=en
http://www.microsoft.com/downloads/details.aspx?FamilyId=2BB62F35-D041-42AC-98DA-6EC97168BE21&displaylang=en
Sincerely,
Anthony Thomas

"moharil" <sid_m15@.yahoo.com> wrote in message
news:DCFB87D2-813A-4707-8D8B-9EDB6E781058@.microsoft.com...
> i have just installed sql clinet
> (Microsoft SQL Enterprise Manager Microsoft Corporation Version: 8.0)
> on windows XP sp2 . whenever i try to reg new sql servers through
Enterprise
> Mgr it gives me this error . it looks like its associated with ODBC . need
to
> figure out whats the problem
> --
> Thanks & Regards
> Sid