Snapshot agent will take snapshot from publisher’s article and BCP to the subscriber. Log agent will read al the subsequent transaction and deliver to the distributor. Distributor agent will apply the transaction to the subscriber.
Before creating the Transaction publisher, the Distributor and distributor database must be pre configured.
Create Transaction publisher
Set up database for publication
CREATE LOG AGENT
Use msdb.dbo.sp_help_job to exam the job detail
Create the Publication
sp_helppublication can be used to exam the detail information
Create the snapshot agent
This will create the snapshot agent job. use sp_help_job to check the job detail.
Set up the article
Below example, I have only publish 1 table. HumanResources.Department On the HumanResources.Department table, I have add the filter only publish DepartmentID > 100.
we can Use sp_helparticle to check the article detail
Create the subscriptions & the distribution agent job
At this point, you will have 3 SQL Agent jobs being setup:
- Snapshot agent job: only use to initialize the subscriber
- Log reader agent job: will read the subsequent transaction and send to the distributor
- Distributor agent job: will deliver the transaction to the subscriber
SSMS has a GUI tools to monitor the replication status
In the agent tab, you can see the snapshot agent status, Click the All subscriptions, you can see more detail about the publisher, distributor and subscriber.
Publisher to Distributor –> Log reader agent job
Distributor to Subscriber –> Distributor agent job
We can use sp_helpsubscription and sp_help_job to view the detail
- drop subscription: sp_dropsubscription to drop all subscription or just subscription for one article. Even the subscription is being drop. It just mean the the target table wont get the update but the existing data will remain intact. Once all the subscriptions are being drop. the job will be removed automatically. If not, we can use msdb.dbo.sp_delete_job to manually remove it.
- drop publication:sp_droppublication, similar as sp_dropsubscription, once all publications are being drop. the job will be removed automatically.
- If database is no longer participate with replication, disable it via sp_replicationdboption
- Clean up the subscription from subscriber
Run below on the subscriber
If you want to remove the replication from the server completely, you need to run below as well
- drop distribution Publisher: sp_dropdistpublisher
- drop distributor db: sp_dropdistributiondb
- drop distribution database: sp_dropdistributor
As alternative, we can use sp_dropdistributor with no_checks =1 . this will drop publishing and distribution objects prior to uninstalling the distributor. The distribution database will be drop too. The related sql agent job will be drop also. This wont remove subscription from subscriber. therefore sp_subscription_cleanup is still required.