1 / 47

Module 4: Implementing Transactional Replication

Module 4: Implementing Transactional Replication. Overview. Understanding Transactional Replication Architecture Replicating with Transactional Replication Implementing Updatable Transactional Replication Facilitating Transactional Replication by Using Stored Procedures

keziah
Download Presentation

Module 4: Implementing Transactional Replication

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Module 4:Implementing Transactional Replication

  2. Overview • Understanding Transactional Replication Architecture • Replicating with Transactional Replication • Implementing Updatable Transactional Replication • Facilitating Transactional Replication by Using Stored Procedures • Troubleshooting Transactional Replication • Tuning Transactional Replication

  3. Lesson: Understanding Transactional Replication Architecture • How Transactional Replication Moves Data • The Role of the Log Reader Agent • How the Log Reader Agent Reads the Transaction Log • How the Log Reader Agent Changes Transaction Syntax • The Role of the Distribution Agent • Independent and Shared Agents • Concurrent Snapshot Generation

  4. How Transactional Replication Moves Data Snapshot ensures synchronized table schema and data Log Reader Agent scans transaction log Log Reader Agent copies transactions marked for replication to Distributor Distribution Agent moves transactions from Distributor to Subscribers

  5. 1 The Log Reader Agent is created when the first transactional publication is created on the Publisher Each time the Log Reader Agent runs: Log Reader Agent The Log Reader Agent looks in the distribution database for the last LSN read 2 1 3 2 distribution distribution The Log Reader Agent reads the transaction log of the publication database starting with the next LSN 3 Transaction log LSN: 18 LSN: 18 The Role of the Log Reader Agent 1 The Log Reader Agent is created when the first transactional publication is created on the Publisher Each time the Log Reader Agent runs: Created Log Reader Agent The Log Reader Agent looks in the distribution database for the last LSN read 2 The Log Reader Agent reads the transaction log of the publishing database starting with the next LSN 3 Transaction log

  6. LSN:************** 17: UPDATELSN:**************18: XXXXX LSN:************** 19: UPDATE LSN:************** 20: UPDATE LSN:************** 21: INSERTLSN:************** 22: DELETELSN:************** 23: UPDATELSN:************** 24: INSERTLSN:************** 25: INSERTLSN:************** 26: UPDATE LSN:************** 27: INSERT LSN:************** 28: INSERT 2 1 3 UPDATEUPDATEINSERTUPDATE … How the Log Reader Agent Reads the Transaction Log Finds the next LSN 1 2 Starts reading transactions Ignores transactions related to nonpublished objects 3 Copies the published transactions to the distribution database 4 5 Marks the log as transactions read to here Anything prior to the mark can be truncated 6 LSN:************** 29: XXXXX

  7. INSERT INTO Emp VALUES (57, 'Bremer', 'Ted') May convert to a stored procedure CALL sp_MSins_Emp (57, 'Bremer', 'Ted') UPDATE Emp SET Phone = '5559521' WHERE LastName = 'Bremer' AND FirstName = 'Ted' 2 1 3 Uses primary key CALL sp_MSupd_Emp (57, '5559521') UPDATE XYZ SET … DELETE XYZ …INSERT XYZ … May convert to an DELETE/INSERT How the Log Reader Agent Changes Transaction Syntax

  8. distribution database subscribing database subscribing database 2 1 3 The Role of the Distribution Agent Distribution Agent DTS Data Pump Transformable subscriptions only DTS Package

  9. subscribing database subscribing database Independent and Shared Agents Independent Agent Shared Agent Independent Agent

  10. 2 1 3 Tablesprocsviews Tablesprocsviews SQL Concurrent Snapshot Generation Marks the log 1 Publishing Database Copies the tables 2 Marks the log 3 Transaction log 4 X Checks the log between the marks for transactions marked for replication Distributor X INSERT… INSERT… Subscribing Database Applies the initial snapshot 5 6 Applies the transactions copied from the marked portion of the log INSERT…

  11. Lesson: Replicating with Transactional Replication • Designing Tables for Transactional Replication • Replicating text, ntext, and image Data • Replicating Horizontally Filtered Data • Overhead of Filtering for Transactional Replication

  12. Y Related row 2 1 3 INSERT X, Y, Z Designing Tables for Transactional Replication Use NOT FOR REPLICATION for relationships Y Related row Replication Replication INSERT X, Y, Z Insert on Publisher checks the relationship It is not necessary to check again on the Subscriber

  13. Practice: Designing Tables for Transactional Replication • Adding syntax • ALTER TABLE and CLUSTERED INDEX • ALTER TABLE and FOREIGN KEY • CREATE TRIGGER

  14. Replicating text, ntext, and image Data • Reinitialize the publication if nonlogged operations have occurred • Use the WITH LOG option with text operations • Use max text repl size with ODBC subscribers

  15. Replicating Horizontally Filtered Data CREATE PROCEDURE CustFilter FOR REPLICATION AS IF EXISTS ( SELECT Country FROM Customers WHERE Country IN (SELECT Country FROM Promotion (NOLOCK)) RETURN 1 ELSE RETURN 0

  16. Overhead of Filtering in Transactional Replication • Number of filters • Every row tested against every filter • Each filter is evaluated independently • Complexity • Complexity determines overhead • Joins, functions, comparisons, and indexes contribute to overhead

  17. Lesson: Implementing Updatable Transactional Replication • What Are Updating Subscriptions? • Limitations of Updating Subscriptions • How Subscribers Using Immediate Updating Subscriptions Update Tables • Schema Alterations for Subscribers Using Immediate Updating Subscriptions • Choosing a Queue Type • How Queued Updating Subscriptions Update Tables (SQL Server 2000 queue) • How Queued Updating Subscriptions Update Tables (MSMQ) • Managing Conflicts with Subscribers Using Queued Updating Subscriptions • Implementing Immediate Updating with Failover to Queued Updating • Managing Identity Values for Updating Subscriptions

  18. 2 1 3 What Are Updating Subscriptions? Publisher Reliable Connection Intermittent Connection Queue Reader Agent Queue Distributor Subscriber using immediate updating subscription Subscriber using queued updating subscription

  19. Limitations of Updating Subscriptions • No merge replication • No republishing replicated data at the Subscriber • All text and image data should be treated as read-only • Cannot disable the updating subscription • Cannot use INSERT statements without a column list • Cannot combine with transformable subscriptions • Cannot use pre-existing data at Subscribers

  20. How Subscribers Using Immediate Updating Subscriptions Update Tables Publisher updates table and forwards transactions Subscriber starts transaction and connects with MS DTC MS DTC completes data modification with two-phase commit (2PC) Publisher distributes update to other Subscribers according to schedule

  21. Schema Alterations for Subscribers Using Immediate Updating Subscriptions • MSrepl_tran_version • Added by wizard if needed • Include vertical filter • Add to column list • System-defined triggers • Added automatically when subscribers enabled • Make calls to MS DTC • Avoiding a trigger-firing loop • Fire user-defined triggers after immediate updating triggers

  22. Choosing a Queue Type • SQL Server 2000 queue • Works with all SQL Server platforms • Easy to set up • No additional components to install • Faster • Microsoft Message Queue (MSMQ) • Centralized queue administration and monitoring • Propagate changes when Subscriber is not running SQL Server • Does not require available Publisher when Subscriber reconnects

  23. SQL Server 2000 Queue: Publisher updates table and forwards transactions using typical transactional replication Subscriber trigger places copy of new transactions in MSreplication_queue SQL Server Queue Reader Agent checks for new rows to be moved to Distributor queue SQL Server Queue Reader Agent uses MS DTC to move rows to Publisher Publisher distributes update to other Subscribers How Queued Updating Subscriptions Update Tables (SQL Server 2000 Queue)

  24. How Queued Updating Subscriptions Update Tables (MSMQ) MSMQ: Publisher updates table and forwards transactions using typical transactional replication MSMQ places transaction in MSMQ-managed queue MSMQ service on Subscriber moves row to MSMQ service on Distributor by using MS DTC MSMQ service on Distributor connects to Publisher and updates Subscriber table by using MS DTC Publisher distributes update to other Subscribers

  25. Managing Conflicts with Subscribers Using Queued Updating Subscriptions • How SQL Server detects conflicts • Choosing a conflict resolver • Keep change made at Publisher • Keep change made at Subscriber • Reinitialize the subscription

  26. 4. 3. 2. 1. 2 1 3 1. 2. 3. 4. Implementing Immediate Updating with Failover to Queued Updating Immediate updating Queue Using immediate updating 1 Connection is unavailable 2 Switch to queuing 3 When connection is available – empty the queue 4 Resume immediate updating 5

  27. Managing Identity Values for Updating Subscriptions • Methods for managing identity values • Use SQL Server 2000 automatic management • Use the NOT FOR REPLICATION option • Use a primary key other than the identity column • Identity values and immediate updating • Publisher controls identity values • You cannot assign identity ranges • Identity values and queued updating • Assigned by the Subscriber • Identity values and queued updating failover • Assign identity ranges to Subscribers • Inserted rows generate identity values from the range

  28. Lesson: Facilitating Transactional Replication by Using Stored Procedures • Understanding Custom Stored Procedures for Replication • Creating Custom Stored Procedures • Implementing Custom Stored Procedures

  29. Understanding Custom Stored Procedures for Replication • Benefits of custom stored procedures • Improved performance • Less data passed over the network • Command types • CALL • MCALL • XCALL • SQL • NONE

  30. Creating Custom Stored Procedures • sp_scriptinsproc • sp_scriptdelproc • sp_scriptupdproc • sp_scriptmappedupdproc • sp_scriptdynamicupdproc • sp_scriptpublicationcustomprocs

  31. Implementing Custom Stored Procedures

  32. Lesson: Troubleshooting Transactional Replication • Troubleshooting by Using the Distribution and Log Reader Agents • Troubleshooting by Using Stored Procedures • Skipping Errors to Prevent Replication Halts • Reinitializing Subscriptions • Troubleshooting Steps • Troubleshooting Guidelines

  33. Troubleshooting by Using the Distribution and Log Reader Agents Distribution Agent "C:\Program Files\Microsoft SQL Server\80\COM\distrib" -Publisher [VANCOUVER] -PublisherDB [Northwind] -Publication [CustomersTransactional] -DistributorSecurityMode 1 Log Reader Agent "C:\Program Files\Microsoft SQL Server\80\COM\logread" -Publisher [VANCOUVER] -PublisherDB [Northwind] -DistributorSecurityMode 1

  34. Troubleshooting by Using Stored Procedures • sp_browsereplcmds – Commands waiting at Distributor • sp_replshowcmds – Commands waiting at Publisher • sp_repltrans – LSNs in transaction log • sp_replflush – Stop acting as the Log Reader Agent • sp_repldone – System use only • sp_replqueuemonitor – Lists queue messages • DBCC OPENTRAN – Returns oldest LSNs • sp_replcmds – System use only

  35. Practice: Troubleshooting by Using Stored Procedures • Create a push subscription and subscribe • Identify commands at Distributor • Identify commands at Publisher

  36. Skipping Errors to Prevent Replication Halts

  37. Reinitializing Subscriptions

  38. Troubleshooting Steps • Check Replication Monitor • Change -BatchCommitSize • Disable Cleanup • Rerun distribution • Check MSreplication_subscriptions • Identify the row • Narrow the query • Map the sequence number • Display commands

  39. Troubleshooting Guidelines • Use SQL error logs • Run DBCC checks • Check for blocking issues • Analyze index and query performance on subscribing tables • View application and System Event Viewer logs • Check triggers

  40. Lesson: Tuning Transactional Replication • Identifying Subscriber Factors That Degrade Performance • Improving Performance Through Design and Configuration • Improving Performance by Using Appropriate Subscription Types and Scheduling • Improving Performance by Using Agent Properties

  41. Identifying Subscriber Factors That Degrade Performance • Subscriber computer factors • Subscription database or SQL Server setup factors • Network speed or connection factors • Additional factors

  42. Improving Performance Through Design and Configuration • Design factors • Use custom stored procedures • Avoid individual row evaluation • Publish as indexed views instead of filtered tables • Avoid the update of unique constrained columns • Configuration factors • Set the distribution database to a fixed size • Configure the Distributor on a dedicated server

  43. Improving Performance by Using Appropriate Subscription Types and Scheduling • Subscription factors • Use push and pull subscriptions appropriately • Use anonymous subscriptions appropriately • Scheduling factors • Run agents continuously • Reduce distribution frequency • Schedule snapshots during periods of low activity

  44. Improving Performance by Using Agent Properties • Property of both the Log Reader Agent and Distribution Agent • Use -PollingInterval parameter to adjust latency • Log Reader Agent Properties • Increase -ReadBatchSize parameter • Retain -ReadBatchThreshold default • Adjust the -MaxCmdsInTran setting • Distribution Agent Properties • Increase -CommitBatchSize and-CommitBatchThreshold defaults

  45. Review • Understanding Transactional Replication Architecture • Replicating with Transactional Replication • Implementing Updatable Transactional Replication • Facilitating Transactional Replication by Using Stored Procedures • Troubleshooting Transactional Replication • Tuning Transactional Replication

  46. Lab 4: Implementing Transactional Replication • Exercise 1: Designing a Transactional Replication Solution

  47. Course Evaluation

More Related