1 / 30

Replication Demystified

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

addo
Download Presentation

Replication Demystified

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. 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 Your logo here

  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/ms151869.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@perptech.com • Questions & Open Discussion

More Related