Thursday, November 3, 2011

0 Replication (3) Transaction Replication

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

  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_thumb

CREATE LOG AGENT

  1. use [AdventureWorks]
  2. exec [AdventureWorks].sys.sp_addlogreader_agent @job_login = N'SG11\PO', @job_password = '0920643709', @publisher_security_mode = 1, @job_name = null
  3. GO

Use msdb.dbo.sp_help_job  to exam the job detail

Create the Publication

  1. EXEC sp_addpublication
  2.     @publication = N'RepTran1',
  3.     @status = N'active',
  4.     @allow_push = N'true',
  5.     @allow_pull = N'true',
  6.     @independent_agent = N'true';

After you run sp_addpublication, you will see the publication has been add to the SSMS object explorer.  The article is not yet defined.image

sp_helppublication can be used to exam the detail information

  1. use [AdventureWorks]
  2. exec sp_helppublication

Create the snapshot agent

  1. use [AdventureWorks]
  2. EXEC sp_addpublication_snapshot
  3.     @publication =N'RepTran1',
  4.     @job_login =  N'SG11\PO',
  5.     @job_password = 'xxx',
  6.     @frequency_type=1,
  7.     @publisher_security_mode = 1;
  8. GO

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

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.

  1. use [AdventureWorks]
  2. exec sp_addarticle @publication = N'RepTran1', @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 = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Department', @destination_owner = N'HumanResources', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_HumanResourcesDepartment', @del_cmd = N'CALL sp_MSdel_HumanResourcesDepartment', @upd_cmd = N'SCALL sp_MSupd_HumanResourcesDepartment', @filter_clause = N'[DepartmentID] > 100'
  3.  
  4. -- Adding the article filter
  5. exec sp_articlefilter @publication = N'RepTran1', @article = N'Department', @filter_name = N'FLTR_Department_1__59', @filter_clause = N'[DepartmentID] > 100', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  6.  
  7. -- Adding the article synchronization object
  8. exec sp_articleview @publication = N'RepTran1', @article = N'Department', @view_name = N'SYNC_Department_1__59', @filter_clause = N'[DepartmentID] > 100', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
  9. GO

we can Use sp_helparticle to check the article detail

Create the subscriptions & the distribution agent job

  1. use [AdventureWorks]
  2. exec sp_addsubscription @publication = N'RepTran1', @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. exec sp_addpushsubscription_agent @publication = N'RepTran1', @subscriber = N'SG1\SQLEXPRESS2008R2', @subscriber_db = N'ADW_REP', @job_login = N'SG11\PO', @job_password = '0920643709', @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 = 20111103, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
  4. GO

At this point, you will have 3 SQL Agent jobs being setup:

  1. Snapshot agent job: only use to initialize the subscriber
  2. Log reader agent job: will read the subsequent transaction and send to the distributor
  3. Distributor agent job: will deliver the transaction to the subscriber

SSMS has a GUI tools to monitor the replication status

image

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.

image

Publisher to Distributor –> Log reader agent job

Distributor to Subscriber –> Distributor agent job

image

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 = 'RepTran1',
  5.   @article = N'all',@subscriber = 'SG1\SQLEXPRESS2008R2'
  6. EXEC sp_droppublication @publication = N'RepTran1'
  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

0 comments:

Post a Comment

 

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