Replication Demystified by 140oI15Q

VIEWS: 7 PAGES: 30

									Replication Demystified
                     Arie D. Jones
       Senior Microsoft Consultant
      Perpetual Technologies, Inc.
               February 26th, 2008
    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.
    Types of Replication
   3 Types of Replication
    › Snapshot
    › Merge
    › Transactional
   Non-Homogenous
    › Example: Oracle Publisher
    Replication Architecture
   3 main pieces
    › Publisher
    › Distributor
    › Subscriber
    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
    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.
    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.
    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
    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
    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’
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
  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.
Merge Replication
     Merge Replication
                   Publication Table




Msmerge_contents                          Msmerge_tombstone


                     Msmerge_genhistory
 GenHistory
Column name   Data type          Description
guidsrc       uniqueidentifier   The global identifier of the
                                 changes identified by
                                 generation at the
                                 Subscriber.
pubid         uniqueidentifier   The publication identifier.
generation    bigint             The generation value.
art_nick      int                The nickname for the
                                 article.
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
                                 table.
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
                                 generation
    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
    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
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
  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
    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
    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
      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!
    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/ms151869.aspx
 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@perptech.com
 Questions & Open Discussion

								
To top