1 / 48

Chat with the Lab Replication in the IBM Informix Dynamic Server

Chat with the Lab Replication in the IBM Informix Dynamic Server. Agenda. What is Enterprise Replication How is Enterprise Replication different from HDR Internal Overview of HDR/ER Recent Improvements in Enterprise Replication (10.x) Troubleshooting Enterprise Replication.

sona
Download Presentation

Chat with the Lab Replication in the IBM Informix Dynamic Server

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. Chat with the LabReplication in the IBM Informix Dynamic Server

  2. Agenda • What is Enterprise Replication • How is Enterprise Replication different from HDR • Internal Overview of HDR/ER • Recent Improvements in Enterprise Replication (10.x) • Troubleshooting Enterprise Replication

  3. IDS Enterprise Replication (ER) • Log based, Transaction oriented replication • Asynchronous, Homogeneous (IDS 7.22+ only) • Primary/Target + Update anywhere • Consolidation, Dissemination, Workload partitioning • Tightly coupled with the server • Web and command line administration

  4. ER History • Initial Release: 7.22 in 12/1996 • Version I - 7.22 - 7.30 releases • Version II (7.31 & 9.2x) • Queue and NIF redesign, Hierarchical Routing • Version III (9.3) • Extensibility, Increased parallelism, Smart blob queuing, In-place alter to add/drop CRCOLS, Serial Col Primary Key Support, … • Version IV (9.4) • ER/HDR support, Large transaction support, Complex Type support, Performance enhancements, Network Encryption • Version V (10.x) • Templates, Alter Support, Resync Support, Mastered Replicates, Shadow Replicates

  5. How HDR and ER differ? Provides single primary and single secondary Allows configurable source(s)/target(s) Primary and secondary must run thesame executables and have similardisk layout Source/target do not have to be thesame Secondary restricted to report processing Allows full usage of both source/target Simple to set up and administer Setup and administration more complex Primary and secondary are mirror images Source and target can be totally different Does not support blobspace blobs Supports blobspace blobs Replication can be synchronous Replication is asynchronous Primary purpose is for highavailability Primary purpose is for data distribution HDR ER

  6. HDR – How it works LogRecvr AcctTable AcctTable Logical Log Buffer Recovery Buffer drsecapply Reception Buffer HDR Buffer Logical Logs Written to Disk Logical Logs Written to Disk DRINTERVAL Sets maximum time lag in seconds for HDR buffer transmission Set to ‘-1’ for synchronous. Primary Secondary

  7. ER – how it works Global Catalog syscdr Database Regroups transaction and performs evaluation Database Target apply threads Grouper Data Synch Snoopy AckQueue Logical Log NIF Send Queue ReceiveQueue Spool Transmits Txn to targets Source Target Transmits Txn to targets

  8. ER Setup Issues • Row is identified by its primary key • SQLHOSTS issues • LogSize issue • Conflict Resolution issues • Topology • Scope issues • Stable Queue

  9. SQL host changes for ER Group Name Label Type Server Service Options CDRID – any number between1 and 32768 Must be unique withinreplication domain. srv1tcp1 ontlitcp dallas port1 srv1tcp2 ontlitcp dallas port2 srv1shm onipcshm dallas srv1shm1 srv2tcp1 ontlitcp houston cdr2 srv2shm onipcshm houston srv2shm srv1_g group - - i=1 srv1tcp1 ontlitcp dallas port1 g=srv1_g srv1shm onipcshm dallas srv1shm1 srv2_g group - - i=2 srv2tcp1 ontlitcp houston cdr2 g=srv2_g srv2shm onipcshm houston srv2shm g=srv2_g NO!!!ER groups should only containTCP connections.

  10. SQL host changes for ER with HDR (9.4) Configure HDR pair as members Of the same ER group Label Type Server Service Options srv1tcp1 ontlitcp dallas port1 srv1tcp2 ontlitcp dallas port2 srv1shm onipcshm dallas srv1shm1 srv2tcp1 ontlitcp houston cdr2 srv2shm onipcshm houston srv2shm srv1_g group - - i=1 hdrPrim ontlitcp dallas port1 g=srv1_g dhrSec ontlitcp ftworth port1 g=srv1_g srv1shm onipcshm dallas srv1shm1 srv2_g group - - i=2 srv2tcp1 ontlitcp houston cdr2 g=srv2_g

  11. Snoopy/Replay/DDRBLOCK Log Number 1 2 3 4 5 6 7 8 Unique id 1214 1215 1216 1217 1210 1211 1212 1213 Replay position Snoopy position Current position Log needs position Replay position DDR BLOCK zone Advancing Log Positions Snoopy position Log needs position Current position

  12. Work Done to Avoid DDRBLOCK state • Spooling Threads • Starts spooling much earlier • CDR_MAX_DYNAMIC_LOGS (9.4) • Allows ER to dynamically add a log rather than enter a DRBLOCK state • -1 Add logs infinitely • 0 Turn off • >0 Number of logs ER is allowed to dynamically create

  13. What is Conflict Resolution? A B Very Fast Network Sally enters a new order on ‘A’ Fast Fingered Fredcan update an orderfaster than can bedelivered to ‘C’ on the ‘Buggy Network’ Slow Network C What to do when the originalorder finally arrives from A after Fred’s update has been applied?Who Wins???

  14. Conflict Resolution • Method to determine if the current version or a just received version of the row should ‘win’ • Ignore • Row must be applied as is • Timestamp • Most recent update wins • Upsert processing • Stored Procedure • User written stored procedure is invoked • Always Apply (10.0) • Like Ignore but performs upserts • Requires CRCOLS (shadow columns) • CDRTIME, CDRSERVER • Create table ... With CRCOLS

  15. What if Fred Deleted the order??? A B Very Fast Network Fast Fingered Fredcan also delete an orderfaster than can bedelivered to ‘C’ on the ‘Buggy Network’ Slow Network C OppsWhat to do when the originalorder finally arrives from A?? Deleted Row Tables

  16. Routing - All Roots B A C

  17. Routing - Hierarchial A (root) B (non-root) C (non-root) B2 (leaf) C1 (leaf) B1 (leaf) C2 (leaf)

  18. Routing - Hub/Spoke B (leaf) E (leaf) A (root) D (leaf) C (leaf)

  19. Routing vrs Replication • The Server Definitions define the topology and routing of the ER domain • The Replicate Definitions define which tables are replicated and where the targets are located • Any table can be replicated to any other server within the replication domain

  20. Things to think about Scope • Transaction scope is really “All or Nothing” • If one row fails within the transaction and you are in transaction scope, then all of the rows fail. • The transaction is always applied as a transaction. • The transaction is NOT rolled-back on the source • Triggers are normally not fired on the target • Firing triggers can be a way to replicate a procedure rather than replicate a table change • Timed Based replication is not a good thing

  21. Smart Blob Queue Setup (9.3+) • Stable storage moved table space blobs to smart blob space • Defined by CDR_QDATA_SBSPACE in onconfig • Can be logged or non-logged • 9.4 can have multiple entries forCDR_QDATA_SBSPACE • Can have both logged and unlogged smartblob queue • Small transactions use logged smartblob space • Large transactions use unlogged smartblob space • If using with HDR, then only logged smartblob space used

  22. ER Encryption Configuration • ENCRYPT_CDR • 0 Encryption Turned Off • 1 Encrypt if peer can decrypt • 2 Encryption must be used • ENCRYPT_CIPHERS • List of to use or to reject • Default is all without using ECB mode • ENCRYPT_SWITCH <CipherTime>,<KeyTime> • Time between renegotiations

  23. New Stuff

  24. New Features in 10.x • Mastered Replicates • Shadow Replicates • Template Support • Allows multi-table deployment with minimal adminstration • Table Alter Support • Support add/modify/drop of column, attach/detach of fragment • Sync Support • Supports multi-node – update anywhere syncronization • Infrastructure • Mastered Replicates • Shadow Replicates • Other • Always Apply, Ignore Deletes

  25. Mastered Replicate • A new replicate type in IDS 10.00 • Traditional replicate are called classic replicate now • A master replicate is a replicate that stores dictionary information in CDR database • Guarantees data integrity • check column type • Can be used to verify column name (--name=y) • Enables table generation on participants • Also allow user to perform alter operations on replicated tables • “Select *” is converted into “select col1, col2, col3…”

  26. Master Replicate (example….) cdr define repl SharkRepl \–S row –C always –M Miami_g –name=y \ “DB@Miami_g:userid.Sharks” “select * from Sharks” \“DB@Charleston_g:userid.Sharks” “select * from Sharks”

  27. Shadow Replicate A B C

  28. Shadow Replicate • Functions as a ‘special case’ replicate • Used in alter to seamlessly swap one replicate definition for another (remastering) • Used in Sync to provide targeted replication cdr define replicate shadowReplicate -M masterNode –m primaryReplicate –S row –C always …

  29. Re-mastering • Seamlessly swaps one replicate definition for another • Uses shadow replicates internally • cdr remaster –M Miami_g SharkRepl “select * from Sharks” • A replicate should be re-master after a replicated table is altered on all nodes

  30. Template in Enterprise Replication • Ease of ER administration and setup • ER Template feature is an extension to the replication set concept • Mass deployment with multiple tables • Easy to define and deploy replicate • Can create tables and deploy on new node • Can provide initial data Sync • Easy command line interface • Eliminates many schema related errors

  31. Enterprise Replication Template • The ER Template feature has two main commands and two other supporting commands. • Define template • Realize template • List template • Delete templete

  32. Define Template • Define a template on participants (tables) • Creates the empty master replicates internally and also a replset on the specified tables • Master replicate is based on tables definition taken from master node cdr define template SharkReplTemp –S row –C timestamp –M NewYork_g –d DB --all

  33. Realize Template • Realize operation Instantiate a template on one or many server • Table on each server matches master definition • Additional columns at any participants are ignored • Realize operation can create tables • Tables can be synced at realize time cdr realize template SharkReplTemp Miami_g Charleston_g NewYork_g • Replication is started by default

  34. Things to Consider With Templates • A template needs to be defined from the non-leaf server just as a replicate set and replicates • Always we need to have direct connectivity between –c server and the Master node • Always we need to specify a group name i.e. g_<server name> as the master node. • Initial Sync using a template requires that all servers should be always directly connected during sync.

  35. Alter table/fragment support on ER table • This feature provides alter support for tables being replicated (10.00) • add/drop default values • add/drop SQL checks • add/drop fragments • attach/detach fragments • add/drop columns • recluster indexes • alter non replicated columns • modify replicated column

  36. Alter on Replication Table • Table defined using master replicate can be altered • During alter • ER is notified of alter of a replicated table • ER spools the queue to avoid having to snoop any old log records • ER blocks any ER activity on the table (CDR Alter Mode) • Normal alter logic performed • ER is notified that the alter is complete and rebuilds dictionary information • Table is verified to ensure that replication can resume • If replication column is altered, it is verified • Alter mode can be set/unset manually through CDR CLI or implicitly through SQL alter statement itself. cdr alter [-c server] –o|-f <tables>

  37. Alter table/fragment Restrictions • ER must be in active state for altering a replicated table except in scenarios where adding/dropping check constraints and default values. • Alter operations are supported only on tables defined with mastered replicates. • Rename table operation is not supported • Rename column operation is not supported • Drop table operation is not supported

  38. Resync feature in 10.00 • To bring a newly participating table up-to-date with the ongoing replication. • Repair a replicated table if replication was stopped or failed for some reason.

  39. Sync in a multi-node environment A B C

  40. Using sync option • Sync tables when starting replicate cdr start replicate repl1 –S NewYork_g • Sync tables when template is realized cdr realize template –c Dallas_g SharkReplTemp –S NewYork_g “DB@Charleston_g” “DB@NewYork_g”

  41. Repair using sync option • By defining and running a ‘repair job’ • Repair is defined on active replicate • Repair entire table • Repair part of a table

  42. Repairing a replicate • Repair a replicate $ cdr define repair <jobname> -r <replicate name> -b <blocksize> -e <extra target row option> -S <source server name> < target server name> extratargetrow option can be one of : delete, keep, merge (default is delete)

  43. Repairing a set • Repair a replicate set $ cdr define repair [ -c <connect server > ] <jobname> -R <replicate set name> -b <blocksize> -e <extra target row option> -S <source server name> < target server name> CDR determines the order of replicate jobs internally based on the referential constraints between the tables in the replset on the target server. NOTE: If the source or leaf of a repair job is a leaf server then ‘connect server’ has to be a non-leaf server

  44. Running a Repair job • Starting a Repair job $ cdr start repair [ -c <connect server> ] jobname The ‘connect server’ has to be the source server of the job. • Stopping a Repair job $ cdr stop repair [ -c <connect server> ] jobname This will stop the scanning of source table for the repair job. But forwarding of data to the target server will continue. ‘start repair’ command on the same job will resume scanning.

  45. How repair job work … • Generates stored procedures and triggers to: • Scan the source data • Handle the extra target row options – including cascade deletes if the option is delete • Cleanup the rows in the internal tables and populate the violations table for errors. • Do dummy updates on the source data to replicate to the target.

  46. ATS & RIS Repair Command syntax: $ cdr repair ats <ats_filename> [-C] $ cdr repair ris <ris_filename> [-C] -C option does only check for the existence of failed rows on target and source.

  47. ATS & RIS Repair How It Works: • If the failed operation is ‘delete’ then do a ‘local’ delete of those row(s) on the target server. • If the failed operation is ‘insert’ or ‘update’ • If the row(s) are found on the ‘source server’ of the failed transaction then do a ‘dummy update’ on those row(s) • If the row(s) are NOT found then do a ‘local’ delete on the target server.

  48. In Closing - Questions???

More Related