Take snapshot from publisher’s article and BCP to the subscriber. The subscriber wont not have most up to date data as it data only refresh upon the job completed. SQL 2008 has enhance the parallel process which allow agent to script the objects and BCP at the same time.
Before creating the snapshot publisher, the Distributor and distributor database must be pre configured.
Create snapshot publisher
Set up database for publication
Create the Publication
After you run sp_addpublication, you will see the publication has been add to the SSMS object explorer. At this point, the snapshot agent is not being set nor the article
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 2 tables. HumanResources.Department and HumanResources.Employee. On the HumanResources.Department table, I have add the filter only publish DepartmentID > 10.
Use sp_helparticle to check the article detail
Create the subscriptions & the distribution agent job
At this point, you will have 2 SQL Agent jobs being setup, one for the snapshot agent and the other one is for distributing the snapshot to the subscriber.
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.
How to resolve the “No replicated transactions are available.” within snapshot replication.
After the first snapshot has been initialized. whenever you kick off the job and launch replication monitor, you may see the the message “No replicated transactions are available.”. Sometimes, even the publisher has already update the data, but it does not get deliver in the timely manner. It is because the only time the new data will be update to the subscription is when the snapshot is being created. If there is no snapshot, there wont be any transaction. In the event to resolve this is to instead of setting the snapshot replication run continuingly. we step it to run in the small interval. ex: 5 mins. You can consider to set this interval for both snapshot job and distributor job.
Change article after snapshot is being generate
If you try to change the article after snapshot is being generated, you will encounter
Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.
How to: Disable Publishing and Distribution (Replication Transact-SQL Programming) http://msdn.microsoft.com/en-us/library/ms147921.aspx
How to manually remove a replication in SQL Server 2000 or in SQL Server 2005 http://support.microsoft.com/kb/324401