I have setup transactional replication and its going fine since some time in
testing. I was trying to remove one of the tables from publication, but it
always says that i have to drop subscription inorder to drop table from
publication.
If I drop subscription and recreate new subscription after the table is
dropped, I will be out of synch. with publisher database as publisher is
still on-line.
Is there any easy way to get arround this...
Regards,
Ravi
Hi Ravi,
Not sure if I fully understand your situation but you can definitely drop
the subscription for just the table that you want to drop using
sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
allow you to perform such article-level operation however.
HTH
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>I have setup transactional replication and its going fine since some time
>in
> testing. I was trying to remove one of the tables from publication, but it
> always says that i have to drop subscription inorder to drop table from
> publication.
> If I drop subscription and recreate new subscription after the table is
> dropped, I will be out of synch. with publisher database as publisher is
> still on-line.
> Is there any easy way to get arround this...
> Regards,
> Ravi
>
|||Raymond thanks for the reply, all i ma trying to do is drop one table from
existing publication and subscription.
and if possible add another table to same publication and subscription...
Regards,
Ravi
"Raymond Mak [MSFT]" wrote:
> Hi Ravi,
> Not sure if I fully understand your situation but you can definitely drop
> the subscription for just the table that you want to drop using
> sp_dropsubscription (See Books Online). The UI (SEM & SMSS) doesn't usually
> allow you to perform such article-level operation however.
> HTH
> -Raymond
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:EED2FB1D-7481-4027-B418-55BD7A97322E@.microsoft.com...
>
>
|||Ravi,
I used this to change a column on a replicated table. It is almost exactly
the script you'll need:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
alter table tEmployees alter column Forename varchar(100) null
exec sp_addarticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.source_table = 'tEmployees'
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Ravi,
I keep forgetting that folks may be using Merge replication in which case it
is not possible (read: no supported way) to drop a single article without
reinitializing the entire subscription. But in case you are using
transactional\snapshot replication, I think here is what you need to do:
1) Call sp_dropsubscription to drop subscriptions on the article you want to
drop
2) Call sp_droparticle to drop the article you want to drop
3) Call sp_addarticle to add the new table to your publication
4) Call sp_addsubscrption to add subscription to your new table (this is not
necessary if syspublications.immediate_sync = 1)
5) Run snapshot agent to generate snapshot for the new article, run
distribution agent to apply it
You probably know about this already but dropping subscription on an article
doesn't remove data that has already been replicated to the subscriber. Of
course, you should be able to manually drop the table easily at the
subscriber if that is what you want.
-Raymond
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:CBE581C0-3897-4A40-A740-CB38D33D61C5@.microsoft.com...[vbcol=seagreen]
> Raymond thanks for the reply, all i ma trying to do is drop one table from
> existing publication and subscription.
> and if possible add another table to same publication and subscription...
> Regards,
> Ravi
> "Raymond Mak [MSFT]" wrote:
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Thursday, March 29, 2012
dropping replication
Hi,
Is there any way to drop a single replication. one subscriber server was taken down without dropping first. Now I have a pull at publisher which is in state of reinitialized and waiting for the server to be back on.
I don not want to drop all replication and create them again. So I wonder if there is anyway to just drop that single replication belong to the taken off server.
Thanks for the help in advance.Yes, just execute 'sp_dropsubscription' with the correct parameters in the publication.
Just for future references, the pull subscription should be dropped in the subscriber before you take the server down.|||ty.
Edit:
according to BOL.
sp_dropsubscription is used in snapshot and transactional replication.
but this is merge.|||Did you mention it was a Merge?|||yup it is merge replication. I am sorry I didn't mention on first msg.
Is there any way to drop a single replication. one subscriber server was taken down without dropping first. Now I have a pull at publisher which is in state of reinitialized and waiting for the server to be back on.
I don not want to drop all replication and create them again. So I wonder if there is anyway to just drop that single replication belong to the taken off server.
Thanks for the help in advance.Yes, just execute 'sp_dropsubscription' with the correct parameters in the publication.
Just for future references, the pull subscription should be dropped in the subscriber before you take the server down.|||ty.
Edit:
according to BOL.
sp_dropsubscription is used in snapshot and transactional replication.
but this is merge.|||Did you mention it was a Merge?|||yup it is merge replication. I am sorry I didn't mention on first msg.
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
left over after replication was removed?
Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?
|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>
|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
|||Have you seen this article?
http://support.microsoft.com/default...b;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>|||Have you seen this article?
http://support.microsoft.com/defaul...kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...[vbcol=seagreen]
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>
Dropping Multiple system tables
What is the best way to drop the 100's of %conlict% tables from a database
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
left over after replication was removed?Highlight them in EM and hit DELETE?
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> What is the best way to drop the 100's of %conlict% tables from a database
> left over after replication was removed?|||EM doesn't allow you to delete "system" tables which is what they are.
"Michael C#" wrote:
> Highlight them in EM and hit DELETE?
> "Monte" <Monte@.discussions.microsoft.com> wrote in message
> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
> > What is the best way to drop the 100's of %conlict% tables from a database
> > left over after replication was removed?
>
>|||Hence the question mark. Sorry.
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>|||Have you seen this article?
http://support.microsoft.com/default.aspx?scid=kb;en-us;324401 and
http://www.mcse.ms/archive95-2004-7-876589.html
"Monte" <Monte@.discussions.microsoft.com> wrote in message
news:ADD9FBFA-DA36-4EC1-B9C5-617A85002977@.microsoft.com...
> EM doesn't allow you to delete "system" tables which is what they are.
> "Michael C#" wrote:
>> Highlight them in EM and hit DELETE?
>> "Monte" <Monte@.discussions.microsoft.com> wrote in message
>> news:A96DEE48-66D3-4133-BE1E-FDAAF51B2AF5@.microsoft.com...
>> > What is the best way to drop the 100's of %conlict% tables from a
>> > database
>> > left over after replication was removed?
>>
dropping default constraints
Hello
We've got a product which uses merge replication with anonymous pullsubscriptions.
At most custome sites it's running on SQL Server 2000, a few with SQL 2005, which is running wich replication compatibility level 80 due to .
As it happens, db schema changes. So I have to drop a column with a default constraint. First the constraint, then the column.
This works excellently on the publisher - but not on the subscriber
The schema script 'exec sp_repldropcolumn '[dbo].[role_modul_rmd]', 'rmd_modul_enabled', 1' could not be propagated to the subscriber. (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL-2147201001)
Hilfe abrufen: http://help/MSSQL_REPL-2147201001
The object 'DF__role_modu__rmd_m__3119DB2C' is dependent on column 'rmd_modul_enabled'. (Quelle: MSSQLServer, Fehlernummer: 5074)
Hilfe abrufen: http://help/5074
ALTER TABLE DROP COLUMN rmd_modul_enabled failed because one or more objects access this column. (Quelle: MSSQLServer, Fehlernummer: 4922)
Hilfe abrufen: http://help/4922
What can bi done?
Thanks for your advice
Aline
We had this exact same problem.
First to recover your replication you can go in and drop the column manually on the subscribers. Then replication should recover.
This happens because replication does not deliver the commands in the correct order, it tries to drop the column first before droping the constraint which it cannot do. The only solution is to drop the constraint first then wait for everyone to replicate then try dropping the column.
Martin
Tuesday, March 27, 2012
dropping default constraints
Hello
We've got a product which uses merge replication with anonymous pullsubscriptions.
At most custome sites it's running on SQL Server 2000, a few with SQL 2005, which is running wich replication compatibility level 80 due to .
As it happens, db schema changes. So I have to drop a column with a default constraint. First the constraint, then the column.
This works excellently on the publisher - but not on the subscriber
The schema script 'exec sp_repldropcolumn '[dbo].[role_modul_rmd]', 'rmd_modul_enabled', 1' could not be propagated to the subscriber. (Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL-2147201001)
Hilfe abrufen: http://help/MSSQL_REPL-2147201001
The object 'DF__role_modu__rmd_m__3119DB2C' is dependent on column 'rmd_modul_enabled'. (Quelle: MSSQLServer, Fehlernummer: 5074)
Hilfe abrufen: http://help/5074
ALTER TABLE DROP COLUMN rmd_modul_enabled failed because one or more objects access this column. (Quelle: MSSQLServer, Fehlernummer: 4922)
Hilfe abrufen: http://help/4922
What can bi done?
Thanks for your advice
Aline
We had this exact same problem.
First to recover your replication you can go in and drop the column manually on the subscribers. Then replication should recover.
This happens because replication does not deliver the commands in the correct order, it tries to drop the column first before droping the constraint which it cannot do. The only solution is to drop the constraint first then wait for everyone to replicate then try dropping the column.
Martin
dropping database that had replication
Usually I use Enterprise Manager to remove the Merge Replication info before
trying to drop a database.
Now I used sp_dropmergepublication and successfully dropped the merge
publications associated with a database. When I try to do a DROP DATABASE
though, I receive :
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'xxxxxx' because it is being used for replication.
I cannot find what stored procedure(s) or other process I need to do to
remove any remaining replication info so the drop database will work.
TIA,
Doug
Doug,
please try sp_removedbreplication 'dbname'
failing that, sp_dboption 'dbname', 'merge publish','false'
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
trying to drop a database.
Now I used sp_dropmergepublication and successfully dropped the merge
publications associated with a database. When I try to do a DROP DATABASE
though, I receive :
Server: Msg 3724, Level 16, State 3, Line 1
Cannot drop the database 'xxxxxx' because it is being used for replication.
I cannot find what stored procedure(s) or other process I need to do to
remove any remaining replication info so the drop database will work.
TIA,
Doug
Doug,
please try sp_removedbreplication 'dbname'
failing that, sp_dboption 'dbname', 'merge publish','false'
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
dropping articles in a running merge replication
hi all, i need to drop some tables from a merge replication, i have a
publisher that is also the distrubuitor, and 2 subscribers, i have 2 tables
that i no longer need to replicate, my articles are set to on name conflits
leave the current table unchanged. Whats the best method of dropping the
tables i dont need, if i have to drop the subscriptions, can my clients at
the subscribers continue to use there system ?. Down time is a serious issue
to my clients lol..
Thanks Andrew
can't be done. You will have to drop the subscribers and do a nosync. Any
work your subscribers are doing will be lost.
Hilary Cotter
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u8yAO4HKFHA.724@.TK2MSFTNGP10.phx.gbl...
> hi all, i need to drop some tables from a merge replication, i have a
> publisher that is also the distrubuitor, and 2 subscribers, i have 2
tables
> that i no longer need to replicate, my articles are set to on name
conflits
> leave the current table unchanged. Whats the best method of dropping the
> tables i dont need, if i have to drop the subscriptions, can my clients at
> the subscribers continue to use there system ?. Down time is a serious
issue
> to my clients lol..
> Thanks Andrew
>
sql
publisher that is also the distrubuitor, and 2 subscribers, i have 2 tables
that i no longer need to replicate, my articles are set to on name conflits
leave the current table unchanged. Whats the best method of dropping the
tables i dont need, if i have to drop the subscriptions, can my clients at
the subscribers continue to use there system ?. Down time is a serious issue
to my clients lol..
Thanks Andrew
can't be done. You will have to drop the subscribers and do a nosync. Any
work your subscribers are doing will be lost.
Hilary Cotter
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
"andrew bourne" <andrewbourne@.vardells.com> wrote in message
news:u8yAO4HKFHA.724@.TK2MSFTNGP10.phx.gbl...
> hi all, i need to drop some tables from a merge replication, i have a
> publisher that is also the distrubuitor, and 2 subscribers, i have 2
tables
> that i no longer need to replicate, my articles are set to on name
conflits
> leave the current table unchanged. Whats the best method of dropping the
> tables i dont need, if i have to drop the subscriptions, can my clients at
> the subscribers continue to use there system ?. Down time is a serious
issue
> to my clients lol..
> Thanks Andrew
>
sql
Dropping Articles (Replication) Can it be done
Could any of you kind Gentlemen/Ladies tell me :-
Is there a way to drop Articles within a Publication without having to kill the Subscription ?
SQL Server 2000 SP3
Merge Publication of Articles Include Views,Stored Procedures & Tables
Many Thanks
GWYou can not drop any articles or columns if the publication has any subscriptions. You must first drop the subscription(s).|||Thanks For Trying JS but
I don't want to kill the Subscription as it means halting Production
and
you can drop columns in SQL 2k without dropping the Subscription
GW|||-IF- you have replication setup using stored procedures and not verb based, you can be vewwy sneaky and blank out the code within the sprocs and recompile them.
wahh-lahh
this, effectively does the same thing- (and works)
cheers,
isaac|||Lol Thanks Isaac
I don't know what verb based replication is but hear whot u r saying about modifying the SP's
In this case I will drop the subscriptions
Maybe next time delve into the SP's
Thanks
GW|||I thought you'd like that little trick :)
when you're creating the publication, you have the option of doing verb based or sproc based replication.
verb based just means that the distributor is issuing INSERT/UPDATE/DELETE commands directly against the server, instead of proxying them through stored procedures.
I prefer the sproc route because it gives you a lot of flexibility without having to rely on MS's row-based filtering stuff in the wizards.
I also heartily recommend using the "script to sql" option in the right-click context menu of the publication entry within e-mangler (and i mean that lovingly).
After you observe the code, you'll find that replication is much simpler than what the manuals make it out to be (which is usually the case).
at any rate, I can now recreate my publication from scratch pointed to a separate distributor, and setup 5 separate push-subscriptions in about 30 seconds through ISQL. This also allows you to keep your production config and setup scripts checked nicely into VSS where it belongs! ;)
good luck,
-isaac|||I will look further into the verb based stuff when the time comes.
I agree the SQL Scripting Option is a bonus in many situations & I use it all the time !
All Hail the Learning Curve
Thanks again M8y
Is there a way to drop Articles within a Publication without having to kill the Subscription ?
SQL Server 2000 SP3
Merge Publication of Articles Include Views,Stored Procedures & Tables
Many Thanks
GWYou can not drop any articles or columns if the publication has any subscriptions. You must first drop the subscription(s).|||Thanks For Trying JS but
I don't want to kill the Subscription as it means halting Production
and
you can drop columns in SQL 2k without dropping the Subscription
GW|||-IF- you have replication setup using stored procedures and not verb based, you can be vewwy sneaky and blank out the code within the sprocs and recompile them.
wahh-lahh
this, effectively does the same thing- (and works)
cheers,
isaac|||Lol Thanks Isaac
I don't know what verb based replication is but hear whot u r saying about modifying the SP's
In this case I will drop the subscriptions
Maybe next time delve into the SP's
Thanks
GW|||I thought you'd like that little trick :)
when you're creating the publication, you have the option of doing verb based or sproc based replication.
verb based just means that the distributor is issuing INSERT/UPDATE/DELETE commands directly against the server, instead of proxying them through stored procedures.
I prefer the sproc route because it gives you a lot of flexibility without having to rely on MS's row-based filtering stuff in the wizards.
I also heartily recommend using the "script to sql" option in the right-click context menu of the publication entry within e-mangler (and i mean that lovingly).
After you observe the code, you'll find that replication is much simpler than what the manuals make it out to be (which is usually the case).
at any rate, I can now recreate my publication from scratch pointed to a separate distributor, and setup 5 separate push-subscriptions in about 30 seconds through ISQL. This also allows you to keep your production config and setup scripts checked nicely into VSS where it belongs! ;)
good luck,
-isaac|||I will look further into the verb based stuff when the time comes.
I agree the SQL Scripting Option is a bonus in many situations & I use it all the time !
All Hail the Learning Curve
Thanks again M8y
Dropping article on transaction publications.
In 2005 transactional replication, The following procedure worked (without dropping the subscription) when I dropped an article from a replicated database:
Drop article: On Publication Properties, uncheck the article (table, stored procedure or function).
Create a new snapshot.
Synchronize the push subscription.
DROP the article on the Publication and Subscriber databases.
Replication still works!
However, the following article says the subscription needs to be dropped and re-created when an article is dropped from publication: http://msdn2.microsoft.com/en-us/library/ms152493.aspx (Adding Articles to and Dropping Articles from Existing Publications ). For transactional publications, articles can be dropped with no special considerations prior to subscriptions being created. If an article is dropped after one or more subscriptions is created, the subscriptions must be dropped, recreated, and synchronized.
Under what conditions is dropping the subscription and recreating it absolutely necessary? I do not want to include this extra step.
Linda
Hi Linda, the documentation is correct. If you try to drop the artice via TSQL stored proc sp_droparticle, it will raise an error saying you you cannot drop the article because you have existing subscription. What I think BOL should say is that "the subscriptions to the article must be dropped before you can drop the article".
This is what the UI is doing - it will first drop the article from all existing subscriptions (via sp_dropsubscription), then drop the article from the publication and then invalidate the snapshot. So for existing subscriptions, there isn't anything else you need to do, but if you add a new subscription afterwards, you have to regenerate a new snapshot.
I hope this is a little more clear.
Dropping article
Hi,
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis
- hadn't
appreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik
I have setup merge replication to run between two SQL 2k (SP3) across the internet. But while selecting the articles via EM I forgot to select "Identity Resolver" for a particular table. And this is creating havoc in that table.
I have to solve this by dropping the article. Or is there any other way?
But if I try to drop the article it says that there is an active subscription for this publication. Would this mean that I will have to drop my entire publication and create the entire thing again?
Can someone help me with this?
Thank you.
Regards,
Karthik
Karthik,
it is not mandatory to reinitialize - you can drop the subscription to a particular article using sp_dropsubscription then drop the article using sp_droparticle, then sp_addarticle to readd then sp_refreshsubscriptions.
Incidentally, what is the error message you see?
Regards,
Paul Ibison
|||Hi Paul,
Thank you for the quick response.
I see a primary key violation for a column that has IDENTITY property set and marked as "YES NOT FOR REPLICATION".
So according to you I do the following -
1. Run sp_dropsubscription at the publisher.
2. Run sp_droparticle
3. Run sp_addarticle
4. Run sp_refreshsubscriptions
I am not all that experienced in Replication. So I still have a few queries on what you have said. I would like to be really clear before I do something as this is the production database.
If I drop a subscription from the publisher, will it not mean that I would need to initialize via the snapshots again?
And what does sp_refreshsubscriptions do actually? I did look at BOL, but didn't understand too much.
Regards,
Karthik.
|||Karthik,
apologies - I just reread your post and noticed that you are using merge replication. In this case my route won't work - it only works for snapshot and transactional. What you can do though is to script out your publication (inc subscriptions),delete the
publication (and prevent users from editing data on the subscriber), make necessary changes to the table, then recreate the publication. For each of the other articles you set @.sync_type to 'none' to establish that the table already exists on the subscrib
er. For the table that you want the identity value, you'll need to have @.sync_type set to 'automatic'.
Alternatively you can have several publications to separate the articles if this is a common issue.
As another point, I'd mention that you can be sure to avoid these PK violations if you set the increment of the identity column in such a way as there can never be an overlap eg publisher uses even numbers and subscriber odd ones. There is an article by M
ichael Hotek on this (http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
I did generate a script of both the publisher and subscribers. But nowhere could I see a @.sync_type. I searched the entire text file.
May be I forgot to specify that I have a pull subscription.
Do let me know if my posting part of the script file would help.
Thank you once again.
Regards,
Karthik
|||Karthik,
it's a parameter off sp_addmergepullsubscription or sp_addmergesubscription.
However, unfortunately it is not valid on a per article basis
- hadn'tappreciated this subtle difference between transactional and merge, so you'd
have to set @.sync_type set to 'automatic' for the complete subscription. In
your case you might be able to make the required table modification to the
table on the publisher and subscriber then run this. As it involves identity
column changes whose ranges are saved to system tables I suspect this is not
viable and would advise another route:
(a) drop the publication! Add 2 new publications - one with the old tables
apart from the problemmatic one, one with the problemmatic table. For the
first one have @.sync_type set to 'none' (subscriber has schema) and have
@.sync_type set to 'automatic' for the second.
(b) if this is not feasible (due to PK-FK constraints between articles),
then go down the initial route of a complete recreation of the publication.
Sorry it's not easier advice.
Regards,
Paul Ibison
|||Hi Paul,
What you seemed to have mentioned seems quite complicated. I would like to stall it as much as possible. Anyways, may be I am trying to go about this the wrong way as I didn't specify why I want to drop the article.
Is there any way I can specify idenity ranges for an article after it has been published? What I need to do basically is, specify that my table "FTPFile" has a range of 1-1000 from my publisher with a threshold of 80%.
Is there any way of doing this?
Thank you once again!
Regards,
Karthik.
|||Karthik,
the only sp wich might be relevant is:
sp_adjustpublisheridentityrange
From BOL: For a publication which has the auto identity range enabled, the
Distribution Agent or Merge Agent is responsible for automatically adjusting
the identity range in a publication based on its threshold value. However,
if for some reason the Distribution Agent or Merge Agent has not been run
for a period of time, and identity range resource have been consumed heavily
to the point of threshold, users can call sp_adjustpublisheridentityrange to
allocate a new range of values for a Publisher.
The identity ranges are held in a system table and the infrastructure is
also implemented using check constraints, but I would not advise you to
change these manually.
My recommendation would be to set it up again and ensure there can't be any
problems by following Michael Hotek's example: eg publisher uses even
numbers and subscriber odd ones. There is an article on this
(http://www.mssqlserver.com/replicati...h_identity.asp).
HTH,
Paul Ibison
|||Hi Paul,
Thank you for all the help.
I was able to fix the problem as suggested in the link.
Thank you once again.
Regards,
Karthik
Dropping and Creating of a Table.
I have what, hopefully, is a simple question. Do any of the methods
of replication for SQL 2000 support the dropping and creating of a
table and have it replicated to the remote system?
Brandon,
I'm assuming you're talking about an existing publication. In transactional
this is possible using sp_addarticle, sp_addsubscription,
sp_refreshsubscriptions and the corresponding drop commands for the
converse. As far as I recall, snapshot also behaves this way but not merge.
I'll test this when I get a moment.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
of replication for SQL 2000 support the dropping and creating of a
table and have it replicated to the remote system?
Brandon,
I'm assuming you're talking about an existing publication. In transactional
this is possible using sp_addarticle, sp_addsubscription,
sp_refreshsubscriptions and the corresponding drop commands for the
converse. As far as I recall, snapshot also behaves this way but not merge.
I'll test this when I get a moment.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
dropping a trigger
Is there any precautions needed to be made when dropping
u/i/d triggers on table involved in replication.
Thanks,
DonaldDonald,
I assume you mean these are your own triggers, and not the triggers used in
Merge replication? Don't drop any replication triggers!
If these are user-defined triggers, they will not be replicated anyway, so
there should be no harm in dropping them. However, you might consider
disabling them first, before dropping, just to make sure everything works.
Do this on a test system first.
Ron
--
Ron Talmage
SQL Server MVP
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:2e5e01c49fda$452d7370$a501280a@.phx.gbl...
> Is there any precautions needed to be made when dropping
> u/i/d triggers on table involved in replication.
> Thanks,
> Donald
u/i/d triggers on table involved in replication.
Thanks,
DonaldDonald,
I assume you mean these are your own triggers, and not the triggers used in
Merge replication? Don't drop any replication triggers!
If these are user-defined triggers, they will not be replicated anyway, so
there should be no harm in dropping them. However, you might consider
disabling them first, before dropping, just to make sure everything works.
Do this on a test system first.
Ron
--
Ron Talmage
SQL Server MVP
"Donald" <anonymous@.discussions.microsoft.com> wrote in message
news:2e5e01c49fda$452d7370$a501280a@.phx.gbl...
> Is there any precautions needed to be made when dropping
> u/i/d triggers on table involved in replication.
> Thanks,
> Donald
Sunday, March 25, 2012
Dropping a table from replication
Transaction replication:
Please help. I am new to replication.
I have setup Transaction replication between two databases. The replication
is running.
I now need to drop a replicate table in the source database but it would not
let me.
I don't want to remove the whole replication inorder to drop a table.
Can you show me how to do it step by step?
Thank You.
RClick the Publication/ Generate SQL Script/ Script the steps to delete.../
Preview/ grab the sp_dropsubscription and sp_droparticle for just the table
you need/ run in QA.
"Sam" <skhou@.computability.ab.ca> wrote in message
news:eaEoIuzKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Transaction replication:
> Please help. I am new to replication.
> I have setup Transaction replication between two databases. The
replication
> is running.
> I now need to drop a replicate table in the source database but it would
not
> let me.
> I don't want to remove the whole replication inorder to drop a table.
> Can you show me how to do it step by step?
> Thank You.
>
Please help. I am new to replication.
I have setup Transaction replication between two databases. The replication
is running.
I now need to drop a replicate table in the source database but it would not
let me.
I don't want to remove the whole replication inorder to drop a table.
Can you show me how to do it step by step?
Thank You.
RClick the Publication/ Generate SQL Script/ Script the steps to delete.../
Preview/ grab the sp_dropsubscription and sp_droparticle for just the table
you need/ run in QA.
"Sam" <skhou@.computability.ab.ca> wrote in message
news:eaEoIuzKFHA.1284@.TK2MSFTNGP14.phx.gbl...
> Transaction replication:
> Please help. I am new to replication.
> I have setup Transaction replication between two databases. The
replication
> is running.
> I now need to drop a replicate table in the source database but it would
not
> let me.
> I don't want to remove the whole replication inorder to drop a table.
> Can you show me how to do it step by step?
> Thank You.
>
Dropping a table from merge replication
Guys,
Merge replication server A to server B with NOSYNC option. (identities odd
at Server A and even at Server B)
I want to remove a table from merge replication.
Regards
Javed
Use sp_dropmergearticle
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Javed Iqbal" <javediqbal98@.hotmail.com> wrote in message news:%23GyRlsm7FHA.3752@.tk2msftngp13.phx.gbl...
Guys,
Merge replication server A to server B with NOSYNC option. (identities odd
at Server A and even at Server B)
I want to remove a table from merge replication.
Regards
Javed
|||......provided the snapshot hasn't yet been run - otherwise you'll have to drop the subscriptions first and reinitialize (nosync again so not too arduous).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I have set up merge replication.
Server A = Publisher
Server B = Subscriber
Snapshot = NOSYNC
The identities are manually handled ie: Odd on Server A and Even on Server B.
Due to some reason I had to remove on table from my Publication. For this i dropped subscription then removed table from publication.
Mean while I allowed user to connect to publisher and change data. After say 20 minutes i again enabled to subscription with NOSYNC.
I got an error for OBSOLETE SNAPSHOT. so I ran the snap shopt again. and since then merge agent is running fine.
My concern is between that 20 minutes data changed is not propagated to the subscriber. How do i sync my database for those records?
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ewVZUro7FHA.252@.TK2MSFTNGP15.phx.gbl...
.....provided the snapshot hasn't yet been run - otherwise you'll have to drop the subscriptions first and reinitialize (nosync again so not too arduous).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the extra changes were inserts you could run sp_addtabletocontents to include the rows then resynchronise. If they are general inserts and updates, you can use sp_mergedummyupdate for each single row. It might be difficult to find these changed rows, and something like datacompare could be used for this to decide what to do.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Merge replication server A to server B with NOSYNC option. (identities odd
at Server A and even at Server B)
I want to remove a table from merge replication.
Regards
Javed
Use sp_dropmergearticle
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Javed Iqbal" <javediqbal98@.hotmail.com> wrote in message news:%23GyRlsm7FHA.3752@.tk2msftngp13.phx.gbl...
Guys,
Merge replication server A to server B with NOSYNC option. (identities odd
at Server A and even at Server B)
I want to remove a table from merge replication.
Regards
Javed
|||......provided the snapshot hasn't yet been run - otherwise you'll have to drop the subscriptions first and reinitialize (nosync again so not too arduous).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul,
I have set up merge replication.
Server A = Publisher
Server B = Subscriber
Snapshot = NOSYNC
The identities are manually handled ie: Odd on Server A and Even on Server B.
Due to some reason I had to remove on table from my Publication. For this i dropped subscription then removed table from publication.
Mean while I allowed user to connect to publisher and change data. After say 20 minutes i again enabled to subscription with NOSYNC.
I got an error for OBSOLETE SNAPSHOT. so I ran the snap shopt again. and since then merge agent is running fine.
My concern is between that 20 minutes data changed is not propagated to the subscriber. How do i sync my database for those records?
Regards
Javed
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:ewVZUro7FHA.252@.TK2MSFTNGP15.phx.gbl...
.....provided the snapshot hasn't yet been run - otherwise you'll have to drop the subscriptions first and reinitialize (nosync again so not too arduous).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||If the extra changes were inserts you could run sp_addtabletocontents to include the rows then resynchronise. If they are general inserts and updates, you can use sp_mergedummyupdate for each single row. It might be difficult to find these changed rows, and something like datacompare could be used for this to decide what to do.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Droping a table in subscrier !
Hello,
I had a merge replication that was replicating a table, and i deleted it,
now it doesn't let me drop that replicated table in my subscriber database.
It says that it is being used for replication.
I even stoped and started my server, but no success.
Any help !!!!!!!!
Thanks,
Mathew
Matthew,
have a look at using sp_MSunmarkreplinfo.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
I had a merge replication that was replicating a table, and i deleted it,
now it doesn't let me drop that replicated table in my subscriber database.
It says that it is being used for replication.
I even stoped and started my server, but no success.
Any help !!!!!!!!
Thanks,
Mathew
Matthew,
have a look at using sp_MSunmarkreplinfo.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Monday, March 19, 2012
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...
>
>
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 replication Pull job without Subscription publication databas
Publisher/Distribution job was removed but on Subscriver was not, since then
we are not able to remobe Pull job without Subscription publication database
on Subscriver server, any help how to remove it?
Identify the job by its name and then use sp_dropjob to remove it.
"Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
> Publisher/Distribution job was removed but on Subscriver was not, since
> then
> we are not able to remobe Pull job without Subscription publication
> database
> on Subscriver server, any help how to remove it?
|||Thanks, job was removed
"Hilary Cotter" wrote:
> Identify the job by its name and then use sp_dropjob to remove it.
> "Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
> news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
>
>
we are not able to remobe Pull job without Subscription publication database
on Subscriver server, any help how to remove it?
Identify the job by its name and then use sp_dropjob to remove it.
"Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
> Publisher/Distribution job was removed but on Subscriver was not, since
> then
> we are not able to remobe Pull job without Subscription publication
> database
> on Subscriver server, any help how to remove it?
|||Thanks, job was removed
"Hilary Cotter" wrote:
> Identify the job by its name and then use sp_dropjob to remove it.
> "Jose Milhazes" <Jose Milhazes@.discussions.microsoft.com> wrote in message
> news:FFB75B9A-C4EF-42CA-8B27-313E70972490@.microsoft.com...
>
>
drop replication
Here was my problem.
I have a database with replication. All I need to do was restore the
database from a backup (totally different data from another server).
The restore process was messed up because I was not aware there is
replication in place. So the database status is always loading. I have
to use sp_removedb and then restore the db.
But the publication is still there (with a push subscription). Can not
do anything with Enterprise manager either. So I did a
sp_dropReplication but I later noticed that I have to do
sp_dropsubscription first.
Now I am stuck:
I can not drop the subscription (complaining error 20026 can not find
publication)
and I can not create publication either (compaling there is already a
subscriptin)
Anyone just tell how to get rid of this and I can start over.
Thanks
John
Hey
If u hav droped the publication already dont wory forget it
and continue.
The other way is in the publication DB u hav some tables
which keep tracks of the subcription info just clear that
rows also. or just drop the subscription DB also
And other thing what u hav done is u hav droped the DB
If the DB is droped then automatically publication is deleted
and related information tables are also deleted which holds
the subscription information
I really dont know what is stoping u to continue to restore DB
from the backup and dont get panic and start the
restore process
-Cheers - Prax
Q. John Chen wrote:
> Here was my problem.
> I have a database with replication. All I need to do was restore the
> database from a backup (totally different data from another server).
> The restore process was messed up because I was not aware there is
> replication in place. So the database status is always loading. I have
> to use sp_removedb and then restore the db.
> But the publication is still there (with a push subscription). Can not
> do anything with Enterprise manager either. So I did a
> sp_dropReplication but I later noticed that I have to do
> sp_dropsubscription first.
> Now I am stuck:
> I can not drop the subscription (complaining error 20026 can not find
> publication)
> and I can not create publication either (compaling there is already a
> subscriptin)
> Anyone just tell how to get rid of this and I can start over.
> Thanks
> John
|||Hi,
The MS document says to do sp_dropReplication AFTER
sp_dropSubscription.
I dropped publication first so the subscriptions became orphant.
I fixed it by manaully deleting entry in distribution..MSsubscriptions
table. After that I was able to re-create publication and then the
subscription.
Thanks
John
prakashcn@.hotmail.com wrote:[vbcol=seagreen]
> Hey
> If u hav droped the publication already dont wory forget it
> and continue.
> The other way is in the publication DB u hav some tables
> which keep tracks of the subcription info just clear that
> rows also. or just drop the subscription DB also
> And other thing what u hav done is u hav droped the DB
> If the DB is droped then automatically publication is deleted
> and related information tables are also deleted which holds
> the subscription information
> I really dont know what is stoping u to continue to restore DB
> from the backup and dont get panic and start the
> restore process
> -Cheers - Prax
>
> Q. John Chen wrote:
I have a database with replication. All I need to do was restore the
database from a backup (totally different data from another server).
The restore process was messed up because I was not aware there is
replication in place. So the database status is always loading. I have
to use sp_removedb and then restore the db.
But the publication is still there (with a push subscription). Can not
do anything with Enterprise manager either. So I did a
sp_dropReplication but I later noticed that I have to do
sp_dropsubscription first.
Now I am stuck:
I can not drop the subscription (complaining error 20026 can not find
publication)
and I can not create publication either (compaling there is already a
subscriptin)
Anyone just tell how to get rid of this and I can start over.
Thanks
John
Hey
If u hav droped the publication already dont wory forget it
and continue.
The other way is in the publication DB u hav some tables
which keep tracks of the subcription info just clear that
rows also. or just drop the subscription DB also
And other thing what u hav done is u hav droped the DB
If the DB is droped then automatically publication is deleted
and related information tables are also deleted which holds
the subscription information
I really dont know what is stoping u to continue to restore DB
from the backup and dont get panic and start the
restore process
-Cheers - Prax
Q. John Chen wrote:
> Here was my problem.
> I have a database with replication. All I need to do was restore the
> database from a backup (totally different data from another server).
> The restore process was messed up because I was not aware there is
> replication in place. So the database status is always loading. I have
> to use sp_removedb and then restore the db.
> But the publication is still there (with a push subscription). Can not
> do anything with Enterprise manager either. So I did a
> sp_dropReplication but I later noticed that I have to do
> sp_dropsubscription first.
> Now I am stuck:
> I can not drop the subscription (complaining error 20026 can not find
> publication)
> and I can not create publication either (compaling there is already a
> subscriptin)
> Anyone just tell how to get rid of this and I can start over.
> Thanks
> John
|||Hi,
The MS document says to do sp_dropReplication AFTER
sp_dropSubscription.
I dropped publication first so the subscriptions became orphant.
I fixed it by manaully deleting entry in distribution..MSsubscriptions
table. After that I was able to re-create publication and then the
subscription.
Thanks
John
prakashcn@.hotmail.com wrote:[vbcol=seagreen]
> Hey
> If u hav droped the publication already dont wory forget it
> and continue.
> The other way is in the publication DB u hav some tables
> which keep tracks of the subcription info just clear that
> rows also. or just drop the subscription DB also
> And other thing what u hav done is u hav droped the DB
> If the DB is droped then automatically publication is deleted
> and related information tables are also deleted which holds
> the subscription information
> I really dont know what is stoping u to continue to restore DB
> from the backup and dont get panic and start the
> restore process
> -Cheers - Prax
>
> Q. John Chen wrote:
Subscribe to:
Posts (Atom)