Hi All:
I have setup Merge Replication (Push) & we are using SQL2000 sp3.
After testing a few things, I disabled the Publication. This did not remove
Subscription on the other Server.
I have a task stuck in Subcriptions, under Replication on SQL.
It Shows the Task as 'Running' Status and Last Action as 'Syncronization in
Progress'
Now I am not able to delete the table in the subscriber also, as it says
'This table has been setup from Replication'
Please suggest.
Thanks,
Anukul
You can use sp_removedbreplication 'dbname' on publisher and subscriber to
remove the system tables associated with replication.
If needed, there is a stored procedure to remove the replication setting on
a table called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Alternatively, setting replinfo to 0 in sysobjects for the particular table
should do it.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks Paul:
I did try this procedure & was able to remove the system tables, including
the Job I mentioned. But it was still not letting us alter the structure or
drop the tables on which Replication was setup.
Please let me know what am I doing wrong.
Regards,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OHL64kGAFHA.1396@.tk2msftngp13.phx.gbl...
> You can use sp_removedbreplication 'dbname' on publisher and subscriber to
> remove the system tables associated with replication.
> If needed, there is a stored procedure to remove the replication setting
on
> a table called sp_MSunmarkreplinfo which takes a tablename as a parameter.
> Alternatively, setting replinfo to 0 in sysobjects for the particular
table
> should do it.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Anukul,
did you try sp_MSunmarkreplinfo?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
I did try that one out also & BTW I am now able to remove Subscriber.
Thanks a Lot for ur help.
Regards,
Anukul
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:155e01c5023b$3917e340$a501280a@.phx.gbl...
> Anukul,
> did you try sp_MSunmarkreplinfo?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I would be interested in hearing from the community if anyone is having the
same type of problems on SQL 2005. We added a force option to sp_removed
which more aggressively drops replication objects. Additionally, we now drop
any rowguid columns that were added in merge.
Philip Vaughn
Program Manager
SQL Server Replication
This message is provided "AS IS" with no warranties, and confers no rights.
"Anukul" <anukuls@.yahoo.com> wrote in message
news:OCJxPYmAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Hi Paul:
> I did try that one out also & BTW I am now able to remove Subscriber.
> Thanks a Lot for ur help.
> Regards,
> Anukul
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:155e01c5023b$3917e340$a501280a@.phx.gbl...
>
Showing posts with label publication. Show all posts
Showing posts with label publication. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
How to remove a push subscription?
I have a publication within a transactional replication with a push
subscription. I had deleted the subscription on the Publisher through the EM.
And Publisher EM showed the subscription is removed. But when I checked the
Subscriber EM, the subscription is still there under the
Replication\Subscriptions, and it seems there is no way to remove it on
Subscriber EM. Can someone please help me on this? How do I remove it on the
subscriber?
Thanks,
Hi,
to dtop a pull subscription you can use:
sp_droppullsubscription system stored procedures.
hope it works fine with you.
SaeedA
"FYK" wrote:
> I have a publication within a transactional replication with a push
> subscription. I had deleted the subscription on the Publisher through the EM.
> And Publisher EM showed the subscription is removed. But when I checked the
> Subscriber EM, the subscription is still there under the
> Replication\Subscriptions, and it seems there is no way to remove it on
> Subscriber EM. Can someone please help me on this? How do I remove it on the
> subscriber?
> Thanks,
|||Hi,
You can use :
sp_dropsubscription, you can see BOL for more details
SaeedA
"FYK" wrote:
> I have a publication within a transactional replication with a push
> subscription. I had deleted the subscription on the Publisher through the EM.
> And Publisher EM showed the subscription is removed. But when I checked the
> Subscriber EM, the subscription is still there under the
> Replication\Subscriptions, and it seems there is no way to remove it on
> Subscriber EM. Can someone please help me on this? How do I remove it on the
> subscriber?
> Thanks,
subscription. I had deleted the subscription on the Publisher through the EM.
And Publisher EM showed the subscription is removed. But when I checked the
Subscriber EM, the subscription is still there under the
Replication\Subscriptions, and it seems there is no way to remove it on
Subscriber EM. Can someone please help me on this? How do I remove it on the
subscriber?
Thanks,
Hi,
to dtop a pull subscription you can use:
sp_droppullsubscription system stored procedures.
hope it works fine with you.
SaeedA
"FYK" wrote:
> I have a publication within a transactional replication with a push
> subscription. I had deleted the subscription on the Publisher through the EM.
> And Publisher EM showed the subscription is removed. But when I checked the
> Subscriber EM, the subscription is still there under the
> Replication\Subscriptions, and it seems there is no way to remove it on
> Subscriber EM. Can someone please help me on this? How do I remove it on the
> subscriber?
> Thanks,
|||Hi,
You can use :
sp_dropsubscription, you can see BOL for more details
SaeedA
"FYK" wrote:
> I have a publication within a transactional replication with a push
> subscription. I had deleted the subscription on the Publisher through the EM.
> And Publisher EM showed the subscription is removed. But when I checked the
> Subscriber EM, the subscription is still there under the
> Replication\Subscriptions, and it seems there is no way to remove it on
> Subscriber EM. Can someone please help me on this? How do I remove it on the
> subscriber?
> Thanks,
Friday, March 23, 2012
How to reinitialize a table in transactional publication.
I have set-up replication using transactional publication and I have
configured subscription to be near continuous.
I have this issue. When setting up subscription I have specified that the
subscription database already had data. So when the snapshot agent is
initialized it does not recreate the tables on the subscription database.
This is ok. But, now if I was to re-initialize only one of the tables, how
do I achieve this without breaking replication.
-Nags
You can't really do this.
If you start with a no-sync, you can't re-initialize. Only subscriptions
that were set for automatic synchronization can be re-initialized.
What I do in situations like this is to drop the article, and then recreate
it in a new publication.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables,
how
> do I achieve this without breaking replication.
> -Nags
>
|||Can we drop an article once the tables are published and also subscribed ?
I thought that once there is a subscription on a publication (at least in
transactional publication) we cannot drop the article.
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> You can't really do this.
> If you start with a no-sync, you can't re-initialize. Only subscriptions
> that were set for automatic synchronization can be re-initialized.
> What I do in situations like this is to drop the article, and then
recreate[vbcol=seagreen]
> it in a new publication.
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
database.
> how
>
|||You will have to drop the subscription for both automatic and nosync
subscriptions, then you can drop the article.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can we drop an article once the tables are published and also subscribed ?
> I thought that once there is a subscription on a publication (at least in
> transactional publication) we cannot drop the article.
> -Nags
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> recreate
> the
> database.
tables,
>
|||But, I do not want to break replication
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eg$wjYkIEHA.3536@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You will have to drop the subscription for both automatic and nosync
> subscriptions, then you can drop the article.
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...
?[vbcol=seagreen]
in[vbcol=seagreen]
subscriptions[vbcol=seagreen]
that[vbcol=seagreen]
is
> tables,
>
|||Nags wrote:
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables, how
> do I achieve this without breaking replication.
> -Nags
>
You are not very well..
However, there are several tricks you can try:
a) you can afford having no transaction on the source table for a
certain time
=> put a trigger for insert / update / delete on the table which
always raise error and rollback
=> wait the log reader agent and the distribution agent have purged
any pending transaction on this table
=> bulk copy your table from publisher to subscriber
=> remove the trigger
b) you can also create a new publication on the table ( I never tried to
publish twice a table on the same database, but you can always create a
"mirror" table on the publishing database that you keep in sync with
triggers )
On the subscriber, subscribe to this new publication and wait for
synchronisation
Now, here is the trick:
On the publisher you have articles A and A2 which you know are in sync
On the subscriber you have tables Arep and A2rep and you know that A2rep
is in sync
So, stop the log reader agent.
Wait long enough so that the distributing agents tell "no more transactions"
Copy A2rep into Arep
restart log reader agent.
Now Arep is sync'ed and you can drop all the A2 stuff.
If you are not familiar with these operations, you might make a
rehearsal on a test server before trying your production server!
configured subscription to be near continuous.
I have this issue. When setting up subscription I have specified that the
subscription database already had data. So when the snapshot agent is
initialized it does not recreate the tables on the subscription database.
This is ok. But, now if I was to re-initialize only one of the tables, how
do I achieve this without breaking replication.
-Nags
You can't really do this.
If you start with a no-sync, you can't re-initialize. Only subscriptions
that were set for automatic synchronization can be re-initialized.
What I do in situations like this is to drop the article, and then recreate
it in a new publication.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables,
how
> do I achieve this without breaking replication.
> -Nags
>
|||Can we drop an article once the tables are published and also subscribed ?
I thought that once there is a subscription on a publication (at least in
transactional publication) we cannot drop the article.
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> You can't really do this.
> If you start with a no-sync, you can't re-initialize. Only subscriptions
> that were set for automatic synchronization can be re-initialized.
> What I do in situations like this is to drop the article, and then
recreate[vbcol=seagreen]
> it in a new publication.
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eQMRViYIEHA.828@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
database.
> how
>
|||You will have to drop the subscription for both automatic and nosync
subscriptions, then you can drop the article.
"Nags" <nags@.DontSpamMe.com> wrote in message
news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Can we drop an article once the tables are published and also subscribed ?
> I thought that once there is a subscription on a publication (at least in
> transactional publication) we cannot drop the article.
> -Nags
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OIi$glcIEHA.3528@.TK2MSFTNGP09.phx.gbl...
> recreate
> the
> database.
tables,
>
|||But, I do not want to break replication
-Nags
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eg$wjYkIEHA.3536@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You will have to drop the subscription for both automatic and nosync
> subscriptions, then you can drop the article.
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:uSGXCLhIEHA.3440@.TK2MSFTNGP09.phx.gbl...
?[vbcol=seagreen]
in[vbcol=seagreen]
subscriptions[vbcol=seagreen]
that[vbcol=seagreen]
is
> tables,
>
|||Nags wrote:
> I have set-up replication using transactional publication and I have
> configured subscription to be near continuous.
> I have this issue. When setting up subscription I have specified that the
> subscription database already had data. So when the snapshot agent is
> initialized it does not recreate the tables on the subscription database.
> This is ok. But, now if I was to re-initialize only one of the tables, how
> do I achieve this without breaking replication.
> -Nags
>
You are not very well..
However, there are several tricks you can try:
a) you can afford having no transaction on the source table for a
certain time
=> put a trigger for insert / update / delete on the table which
always raise error and rollback
=> wait the log reader agent and the distribution agent have purged
any pending transaction on this table
=> bulk copy your table from publisher to subscriber
=> remove the trigger
b) you can also create a new publication on the table ( I never tried to
publish twice a table on the same database, but you can always create a
"mirror" table on the publishing database that you keep in sync with
triggers )
On the subscriber, subscribe to this new publication and wait for
synchronisation
Now, here is the trick:
On the publisher you have articles A and A2 which you know are in sync
On the subscriber you have tables Arep and A2rep and you know that A2rep
is in sync
So, stop the log reader agent.
Wait long enough so that the distributing agents tell "no more transactions"
Copy A2rep into Arep
restart log reader agent.
Now Arep is sync'ed and you can drop all the A2 stuff.
If you are not familiar with these operations, you might make a
rehearsal on a test server before trying your production server!
Monday, March 12, 2012
How to Reboot a Publication server ?
We have to add some hardware and I was wondering if someone could provide a stepwise tip to do it .
It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.
Please explain a little .
ThanksStop Distribution Agent, make CHECKPOINT on Publisher and shut it down. You also could just shut down publisher but it is not a good practise.|||Thanks . I guess I don't have to worry about Log reader agents ! They will die once publisher is out ?
It is a 3 Server Environment Pub/Dist/Sub
All three running under SQL2K
Here is what I am planning.
a. Uncheck the Enable in Distributed Agent for each replication
b. Wait for all replications to complete
c. Shut down Publisher ? Or do I have to do something else before that.
Please explain a little .
ThanksStop Distribution Agent, make CHECKPOINT on Publisher and shut it down. You also could just shut down publisher but it is not a good practise.|||Thanks . I guess I don't have to worry about Log reader agents ! They will die once publisher is out ?
Subscribe to:
Posts (Atom)