[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

To Stop Replication Temporarily.

SQL2K

I want to stop replication (Transactional) temporarily. I don't want to delete any publication /subscription. I just want to stop or disable it.
So that when I enable then everything should be as it was bebore stopping/disabling.
0
Mateen
Asked:
Mateen
  • 3
  • 2
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
TRY Stopping the distribution agent
0
 
MateenAuthor Commented:
Hi
I stopped snapshot agents, logreader agents. distributor agents.

and above all I stopped SQLServerAgent.

then I tried

alter table quality disable trigger all

I receive the error.

Could not do so because the table quailty is being used in replication.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Actually, I am not so experienced in replication, Hope someone else will help you
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MateenAuthor Commented:
ok
0
 
imran_fastCommented:
>alter table quality disable trigger all

if you stop replication and then make any schema changes it wont work.
because this table exists as an article inside your publication you have to drop the article from that table if you want to do that.

one more thing it is not recommended to have trigger at the pulisher and subscriber if you want to do so you have to create the trigger with [ NOT FOR REPLICATION ]  clause.

come to your question it seems you want to disable all triggers from the table quantity generate the script for those and delete those triggers using the enterprse manager from quantity table and later on after you accomplish what you want add them. deleting trigger from publisher wont effect your replication. but diabling will.

0
 
MateenAuthor Commented:
Hi imran_fast

Thanks for your detailed answer.

Now I have got everything right.

Now my only question Is it possible to stop replication keeping publications/subscriptios etc intact?
0
 
imran_fastCommented:
yes keep the log reader running and stop  the agent which is same as  publication name (stop synchronizing)
it will keep on logging all the changes  once you start synchronizing it will apply all the changes to the subscriber.
Note: make the subscribtion expires to never (by default i think it is 48 hourse) otherwise if you don't sync for 48 hours you have to apply the snaphot again or it will become useless.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now