Replication Demystified

1
Replication Demystified

• Arie D. Jones
• Senior Microsoft Consultant
• Perpetual Technologies, Inc.
• February 26th, 2008

2
Speaker Background

• Principal Microsoft Consultant for Perpetual
  Technologies
• 20 years of programming experience
• 11 years of experience with SQL
• Lead author SQL Functions book
• Etc.

3
Types of Replication

• 3 Types of Replication
• Snapshot
• Merge
• Transactional
• Non-Homogenous
• Example Oracle Publisher

4
Replication Architecture

• 3 main pieces
• Publisher
• Distributor
• Subscriber

5
Publication

• Publication is the articles from the database
  that you are publishing via replication
• You can pick and chose which items you wish to
  replicate.
• Publications can be modified in the future
• Publications also allow horizontal partitioning
  of data

6
Distributor

• Distributor is the brain-box of the replication
  process
• Keeps track of subscribers, their publications,
  whom had updated, whom has not,etc.
• Normally the place to find out where problems
  exist within the replication process.

7
Subscriber

• The database that will receive the replicated
  data.
• It may also replicate data back as in the case of
  2 way merge or transactional replication.

8
Agents

• Distribution Agent
• Merge Agent
• Agents run at the distributor for push
  subscriptions
• Agents run at the subscriber for pull
  subscriptions
• Retention Period period of time for data
  expiration
• Only applies to period since last data sent from
  publication

9
Secondary Agents

• Snapshot Agent
• Executes on the distributor to extract snapshot
• Log Reader Agent
• Used with Transactional replication to extarct
  committed transactions
• Queue Reader Agent
• Used to queue the updates for transactional and
  snapshot replication
• Distribution Agent
• Does all the work of dispatching to subscribers

10
Snapshot Replication

• Possibly the easiest to implement and understand.
• A snapshot of the database is taken and applied
  to subscribers at a regular interval
• Good for database that do not change that
  often(data-wise)
• Basically, replicating the whole database image

11
Snapshot Replication
12
Snapshot Replication

• Parts of Snapshot Replication
• Snapshot agent runs at Distributor
• Snapshot Files created
• Schema Files .sch
• Bulk Copy Program(BCP) Files - .bcp
• Makes entries into 2 tables
• MSRepl_Commands
• MSRepl_Transactions

13
Merge Replication

• Possibly the most complex version of replication.
  
• Used when data will be updated by the subscriber
  and merged with the publisher
• Uses a system of trigger to log the change
  activity.

14
Merge Replication
15
Merge Replication
Publication Table
Deletes
Inserts/Updates
Msmerge_tombstone
Msmerge_contents
Msmerge_genhistory
16
GenHistory
17
Merge Replication

• Merge Agent handles the replication process
• Conflicts are detected using lineage column of
  the MSmerge_contents table
• Conflicts are resolved and the losing version of
  a row is logged
• Msmerge_conflicts_info
• Info is cleaned up based upon retention period

18
Transactional Replication

• One of the most misunderstood
• Replicates transactions to the subscribers
• Distribution Agent handles replication
• Gets transactions via LogReader process
• Transactions are stored in Msrepl_Commands and
  Msrepl_transactions
• Transactions are sent in batches to the
  subscriber

19
Transactional Replication
20
Transactional Replication

• Msrepl_commands Msrepl_transactions tell you
  which transactions have been sent
• Transactions are nothing more than stored
  procedure execution
• Use sp_scriptpublicationcustomprocs
• If a batch fails then the Agent will try to
  determine why.

21
Oracle Publishers

• Can support the following objects
• Tables
• Index-organized tables
• Indexes
• Materialized views(treated as tables)
• These are not (even though the may be part of
  your tables)
• Defaults
• Check constraints
• Foreign Keys
• Function-based indexes

22
Oracle Publishers

• Occurs in a similar manner to Merge Replication
• Row-level triggers installed on published tables
• Sequences are maintained by the table HREPL_seq
• Changes are then compiled from the article log
  tables and placed in the Msrepl_commands and
  Msrepl_transactions tables on the distributor

23
Oracle Publishers

• Be careful
• Data sizing in Oracle is slightly different from
  SQL Server
• LOBs
• LOB column do not fire triggers on updates
• LOB must be deleted and then inserted or
• LOB update must contain another non-LOB column

24
Monitoring Replication

• 2 prudent ways
• Write your own scripts
• Use Replication Monitor

25
Replication Monitor
26
Replication Monitor
27
Replication Monitor
28
Replication Monitor

• Things to remember
• Throughput is not necessarily a hard-fast
  analysis
• Agent properties can be changed
• Agent changes take place the next time the
  particular agent is fired off
• Alerts can be set but be careful!

29
Troubleshooting Replication

• Troubleshooting can be achieved via custom code
  and some stored procedures
• sp_browsereplcmds
• sp_replshowcmds
• sp_replcmds
• sp_replcounters
• sp_repldone
• Complete list can be found here
• http//technet.microsoft.com/en-us/library/ms15186
  9.aspx

30
Thank You!

• Slides can be found on my blog
• http//www.programmersedge.com
• Look at our other events
• http//events.perptech.com
• Email arie.jones_at_perptech.com
• Questions Open Discussion