Thursday, November 3, 2011

0 Replication (2) Snapshot Replication

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

  1. use [AdventureWorks]
  2. exec sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'true'
  3. GO

Run sp_dboption to confirm the replication is being enable

image

Create the Publication

  1. use [AdventureWorks]
  2. exec sp_addpublication @publication = N'RepSnapshot1', @description = N'Snapshot publication of database ''AdventureWorks'' from Publisher ''SG11\MSSQL2008R2''.', @sync_method = N'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'snapshot', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1
  3. GO

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

image_thumb[13]

sp_helppublication can be used to exam the detail information

  1. use [AdventureWorks]
  2. exec sp_helppublication

image

Create the snapshot agent

  1. use [AdventureWorks]
  2. EXEC sp_addpublication_snapshot
  3.     @publication = N'RepSnapshot1',
  4.     @job_login = N'SG11\PO', @job_password = 'XXX',
  5.     @publisher_security_mode = 1;

This will create the snapshot agent job. use sp_help_job to check the job detail.

  1. USE [msdb]
  2. EXEC msdb.dbo.sp_help_job

image

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.

  1.  
  2. use [AdventureWorks]
  3. exec sp_addarticle @publication = N'RepSnapshot1', @article = N'Department', @source_owner = N'HumanResources', @source_object = N'Department', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Department', @destination_owner = N'HumanResources', @vertical_partition = N'true', @filter_clause = N'[DepartmentID] > 10'
  4.  
  5. -- Adding the article's partition column(s)
  6. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Department', @column = N'DepartmentID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  7. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Department', @column = N'Name', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  8. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Department', @column = N'GroupName', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  9.  
  10. -- Adding the article filter
  11. exec sp_articlefilter @publication = N'RepSnapshot1', @article = N'Department', @filter_name = N'FLTR_Department_1__57', @filter_clause = N'[DepartmentID] > 10', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  12.  
  13. -- Adding the article synchronization object
  14. exec sp_articleview @publication = N'RepSnapshot1', @article = N'Department', @view_name = N'SYNC_Department_1__57', @filter_clause = N'[DepartmentID] > 10', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  15. GO
  16.  
  17.  
  18.  
  19.  
  20. use [AdventureWorks]
  21. exec sp_addarticle @publication = N'RepSnapshot1', @article = N'Employee', @source_owner = N'HumanResources', @source_object = N'Employee', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', @destination_table = N'Employee', @destination_owner = N'HumanResources', @vertical_partition = N'true'
  22.  
  23. -- Adding the article's partition column(s)
  24. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'EmployeeID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  25. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'LoginID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  26. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'Title', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  27. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'BirthDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  28. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'SalariedFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  29. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'VacationHours', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  30. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'SickLeaveHours', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  31. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'CurrentFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  32. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  33. exec sp_articlecolumn @publication = N'RepSnapshot1', @article = N'Employee', @column = N'ModifiedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  34.  
  35. -- Adding the article synchronization object
  36. exec sp_articleview @publication = N'RepSnapshot1', @article = N'Employee', @view_name = N'SYNC_Employee_1__57', @filter_clause = null, @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  37. GO

Use sp_helparticle to check the article detail

  1. use [AdventureWorks]
  2. go
  3. sp_helparticle @publication = N'RepSnapshot1'

image

Create the subscriptions & the distribution agent job

  1. use [AdventureWorks]
  2. exec sp_addsubscription @publication = N'RepSnapshot1', @subscriber = N'SG1\SQLEXPRESS2008R2', @destination_db = N'ADW_REP', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0
  3.  
  4. exec sp_addpushsubscription_agent @publication = N'RepSnapshot1', @subscriber = N'SG1\SQLEXPRESS2008R2', @subscriber_db = N'ADW_REP', @job_login = N'SG11\PO', @job_password = 'XXX', @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20111101, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
  5. GO

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

  1. USE [msdb]
  2. EXEC msdb.dbo.sp_help_job  
  3. go
  4. use [AdventureWorks]
  5. go
  6. sp_helpsubscription

 

Drop Replication

  1. 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.
  2. drop publication:sp_droppublication, similar as sp_dropsubscription, once all publications are being drop. the job will be removed automatically.
  3. If database is no longer participate with replication, disable it via sp_replicationdboption
  4. Clean up the subscription from subscriber
  1. use [AdventureWorks]
  2. go
  3. EXEC sp_dropsubscription
  4.   @publication = 'RepSnapshot1',
  5.   @article = N'all',@subscriber = 'SG1\SQLEXPRESS2008R2'
  6. EXEC sp_droppublication @publication = N'RepSnapshot1'
  7. EXEC sp_replicationdboption
  8.   @dbname =  [AdventureWorks],
  9.   @optname = N'publish',
  10.   @value = N'false';

Run below on the subscriber

  1. USE [ADW_REP]
  2. sp_subscription_cleanup @publisher = N'SG11\MSSQL2008R2', @publisher_db='AdventureWorks',@publication='RepSnapshot1'

If you want to remove the replication from the server completely, you need to run below as well

  1. drop distribution Publisher: sp_dropdistpublisher 
  2. drop distributor db: sp_dropdistributiondb
  3. drop distribution database: sp_dropdistributor
  1. use master;
  2. exec sp_dropdistpublisher  'SG11\MSSQL2008R2'
  3. exec sp_dropdistributiondb 'distribution'
  4. exec 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.

  1. use [master]
  2. exec sp_dropdistributor @no_checks = 1

 

Other issues

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.

image_thumb16

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.

  1. sp_droparticle  @publication = N'RepSnapshot1',@article = 'Employee' ,@force_invalidate_snapshot =1

 

Reference

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates