SQL Server Replication HILARY COTTER SQL SERVER MVP

SQL Server Replication HILARY COTTER SQL SERVER MVP HILARY. COTTER@GMAIL. COM

Who am I? SQL Server MVP – 14 years 20 year IT veteran Author/coauthor – 6 books Authors Microsoft courseware Answer 20, 000 questions on Microsoft forums Some of them correctly Specializes in replication, full-text search, service broker, performance tuning

Clients

Agenda Replication Types Performance Tuning Troubleshooting Monitoring

Terms Publisher Distributor Subscriber Gateway Article Schema and data Schema only (table, indexed views, functions, stored procedures) Stored procedure execution

Replication Types Snapshot Transactional with updateable subscribers Bi-Directional Transactional Replication Peer to Peer Merge Sync Services

Replication Use Cases Used to Distribute/Aggregate data or schema only objects # 1 use case – offloading reporting Move data closer to consumer Avoiding network hop of linked server Creating Islands of data Replicating to SQL CE/SQL Compact Load Balancing/Scale out reads Peer to Peer Bi-Directional Transactional

What is new in SQL 2012/2014 Replication plays nicely with Always. ON if you are using a remote distributor. Performance improvements/Bug Fixes New Extended Events Sync Services no longer part of the install Ability to replicate to the Cloud https: //channel 9. msdn. com/Shows/Data-Exposed/Azure. SQL-DB-Transactional-Replication

Replication Extended Events Name logreader_apply_filter_proc logreader_process_text_ptr logreader_process_filestream_info logreader_add_compensation_range logreader_add_eor logreader_add_tran_info logreader_process_text_info

Snapshot Replication Point in time image of data sent to subscriber Best fit when the majority of the data changes at one time Catalogs Price lists Poor use cases Tables don’t have PKs No real time synchronization requirements

Transactional Replication Replicates transactions from publisher to subscriber in a transactional context Store and Forward mechanism Tlog read asynchronously Commands constructed and written to distribution database Markers placed in log saying Transaction X read LSN – log sequence numbers correlates to Xact. Sequence in profiler xact_seqno in msrepl_transactions Transactional boundaries are respected when applying commands on the subscribe 20 rows updated on publisher 20 rows read by log reader and written to distribution database 20 rows applied on subscriber in a transaction. Upon failure the 20 row application is rolled back

Demo

Transaction Replication Can Replicate execution of a stored procedure Serializable -- SET TRANSACTION ISOLATION LEVEL Serializable; Begin tran 1 Exec Proc 1 Begin tran 2 Exec proc 2 -- 2 is committed before 1 Commit tran 2 Commit tran 1

Updateable Subscribers Designed for the case where the majority of the DML originates at the Publisher Immediate - MS DTC – linked server Contrast with merge where it is anywhere committed at publisher before subscriber Queued Uses queue reader Long standing bug in Replication Scripting adds a queue reader for all transactional replication types

Updateable Subscribers Immediate with queued failover If Publisher goes down Published tables go readonly Deprecated in SQL 2012 Can still use it through stored procedures Don’t use it Small user base

Bi-Directional Transactional Two way replication No guid key required Publisher is also a subscriber sp_addsubscription @loopback_detection=true Faster than merge and p 2 p Only scalable to 2 nodes Can do three Great for scaling read outs

Bi-Directional Transactional demo

Bi-Directional Transactional Caveats Swapping data Identity seeding Downtime The longer the downtime the greater the probability of duplicate data

Peer to Peer (P 2 P) EE Feature only Bi-directional Uses a mesh technology Each node can replicate to any other nodes in the topology. A node can drop off and come back on the topology and automatically synchronize SQL 2008 has conflict detection No conflict resolution Smaller feature set the Transactional Replication No custom stored procedures Network saturated at 10 nodes

P 2 P demo

Merge Replication Designed for frequently disconnected clients which need to bi-directionally replicate Ideal for POS applications Uses a tracking column (rowguid) to uniquely identify rows across a replication topology Uses tracking triggers to do change detection Merge agent enumerates changes occurring between the publisher and subscriber between synchronizations and downloads changes to either side. Should a change to a row occur on both sides of a merge replication topology the lineage column on msmerge_contents – sync cookie

Merge Replication Conflicts Error Changes to same row (row level tracking) or column (column level tracking) Conflict Priority Server/Global Client/Local Determines who conflicts are persisted. Server – first to publisher wins and persists Client – determined by assigned priority Publisher changes always persist

Merge Replication Conflict resolution Microsoft SQL Server Additive Conflict Resolver Microsoft SQL Server Averaging Conflict Resolver Microsoft SQL Server DATETIME (Earlier Wins) Conflict Resolver Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver Microsoft SQL Server Download Only Conflict Resolver Microsoft SQL Server Maximum Conflict Resolver Microsoft SQL Server Merge Text Columns Conflict Resolver Microsoft SQL Server Minimum Conflict Resolver Microsoft SQL Server Priority Column Resolver Microsoft SQL Server Subscriber Always Wins Conflict Resolver Microsoft SQL Server Upload Only Conflict Resolver Microsoft SQLServer Stored Procedure Resolver

Merge Replication Web Synchronization Secure transport Rich Filtering Can filter vertically and horizontally Changes to parent rows will cause motion in child rows. For example if you are filtering on region, and a sales person gets a new region all the child rows belonging to that region will be replicated down to the sales person’s subscription. Termed a partition Precomputed Partitions are evaluated at run time (when changes happen) as opposed to filter/sync time Can result in very short syncs, but sometimes depending on the hierarchies in the filters can result in longer DML time

Business Logic Resolver . Net class that allows you to Handle Merge Errors Conflicts Update Insert Delete Do things on each synchronization Overhead Stored Procedure Resolver

Performance Tuning transactional 1400 1200 1000 800 History. Verbose. Level=1 600 History. Verbose. Level=2 400 200 The affect of History. Verbose. Level and Output. Verbose. Level settings on a workload of 10, 000 singleton inserts 0 1 2 3

Performance Tuning transactional 1400 1200 1000 800 History. Verbose. Level=1 600 400 200 History. Verbose. Level=2 The affect of History. Verbose. Level and Output. Verbose. Level settings on a workload of 100 transactions of 100 singleton inserts. 0 1 2 3

Performance Tuning The affect of Worker. Time with varying settings of Commit. Batch. Size and Commit. Batch. Threshold for a workload of 10000 singleton inserts on the Distribution Agent. 18000 16000 14000 12000 Commit. Batch. Size=10 10000 Commit. Batch. Size=100 8000 Commit. Batch. Size=10000 6000 4000 2000 0 10 10000

Merge Replication Maximize Generations per batch Upload. Generations. Per. Batch Download. Generations. Per. Batch Upload. Read. Changes. Per. Batch Download. Read. Changes. Per. Batch Upload. Write. Changes. Per. Batch Download. Write. Changes. Per. Batch Minimize Conflicts Minimize Concurrent Merge Agents

Troubleshooting Enable agents for logging Run from the command line Break up publication For Transactional Replication sp_replcounters sp_browsereplcmds sp_setsubscriptionxactseqno For Merge Replication – tracking changes sp_showlineage sp_showcolv sp_mergedummyupdate sp_showpendingchanges

Troubleshooting Limit number of concurrent merge syncs sp_changemergepublication 'My. Publication', 'max_concurrent_merge', '10‘ Start. Queue. Timeout Reindex merge system tables nightly

Replication and Mirroring Need a remote distributor Need to use trace flag 1448

Replication and AGs Configure your AG Configure your remote distributor Configure your Publication Use Sp_redirect_publisher to have your publisher use the Listener name

Questions