Replication Demystified by 140oI15Q


									Replication Demystified
                     Arie D. Jones
       Senior Microsoft Consultant
      Perpetual Technologies, Inc.
               February 26th, 2008
    Speaker Background
   Principal Microsoft
    Consultant for Perpetual
   20 years of programming
   11 years of experience
    with SQL
   Lead author ‘SQL
    Functions’ book
   Etc.
    Types of Replication
   3 Types of Replication
    › Snapshot
    › Merge
    › Transactional
   Non-Homogenous
    › Example: Oracle Publisher
    Replication Architecture
   3 main pieces
    › Publisher
    › Distributor
    › Subscriber
 Publication is the articles from the
  database that you are ‘publishing’ via
 You can pick and chose which items you
  wish to replicate.
 Publications can be modified in the
 Publications also allow horizontal
  partitioning of data
 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
 The database that will receive the
  replicated data.
 It may also replicate data back as in the
  case of 2 way merge or transactional
 Distribution Agent
 Merge Agent
 Agents run at the distributor for push
 Agents run at the subscriber for pull
 Retention Period: period of time for data
    › Only applies to period since last data sent
     from publication
    Secondary Agents
   Snapshot Agent
    › Executes on the distributor to extract
   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
    Snapshot Replication
 Possibly the easiest to implement and
 A snapshot of the database is taken and
  applied to subscribers at a regular
 Good for database that do not change
  that often(data-wise)
 Basically, replicating the whole
  database ‘image’
Snapshot Replication
    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
    Merge Replication
 Possibly the most complex version of
 Used when data will be updated by the
  subscriber and ‘merged’ with the
 Uses a system of trigger to ‘log’ the
  change activity.
Merge Replication
     Merge Replication
                   Publication Table

Msmerge_contents                          Msmerge_tombstone

Column name   Data type          Description
guidsrc       uniqueidentifier   The global identifier of the
                                 changes identified by
                                 generation at the
pubid         uniqueidentifier   The publication identifier.
generation    bigint             The generation value.
art_nick      int                The nickname for the
nicknames     varbinary(1001)    A list of nicknames of
                                 other Subscribers that are
                                 known to already have
                                 this generation.
coldate       datetime           Date when current
                                 generation is added to the
genstatus     tinyint            The status of the
                                 generation as follows:
                                 0 = Open.
                                 1 = Closed.
                                 2 = Closed and originated
                                 at another Subscriber.
changecount   int                The number of changes
                                 reflected in a given
    Merge Replication
 Merge Agent handles the replication
 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
    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
Transactional Replication
    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.
    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
    Oracle Publishers
 Occurs in a similar manner to Merge
 Row-level triggers installed on published
 Sequences are maintained by the table
 Changes are then compiled from the
  article log tables and placed in the
  Msrepl_commands and
  Msrepl_transactions tables on the
    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
    Monitoring Replication
   2 prudent ways
    › Write your own scripts
    › Use Replication Monitor
Replication Monitor
Replication Monitor

          Your logo here
Replication Monitor
    Replication Monitor
   Things to remember
    › Throughput is not necessarily a hard-fast
    › 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!
    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
 Thank You!
   Slides can be found on my blog
   Look at our other events
 Email:
 Questions & Open Discussion

To top