Informix Replication and Availability Offerings

1. Informix Replication and Availability Offerings

2. Agenda • Why Replicate? • Enterprise Replication • Flexible Grid • Continuous Availability • Updates on secondary • Connection Manager • Continuous Log Restore

3. Terminology • Replication • Replication is the process of sharing information so as to ensure consistency between redundant resources. • Enterprise replication • Enterprise Replication is an asynchronous, log-based tool for replicating data between IBM Informix Dynamic Server database servers. • HDR • Mechanism of marinating a live copy of database on another instance by means of applying the logical logs. • Hot Backup • Hot backup is a backup performed on data while its accessed and modified actively. • Log snooping • Process of extracting the records from log stream. • Concurrency • A mechanism to ensure that transactions are performed concurrently without the violating the data integrity.

4. Replication

5. High Availability Provide a hot backup to avoid downtime due to system failure Secondary Capacity Relief Offload some work ontosecondary systems (reporting/analysis) ReportingDatabase Workload Partitioning Different datum ‘owned’ in different locations (warehouses) Why Replicate?

6. Enterprise Replication • Uses • Workload partitioning. • Capacity relief. • Flexible and Scalable • Subset of data. • Supports update anywhere • Very low latency. • Synchronizes local withglobal data. • Integrated • Compatible with all other Informix availability solutions. • Secure data communications.

7. ER Strengths • Flexible • Choose which Columns to Replicate. • Choose where to Replicate. • Supports Update anywhere • Conflicting updates resolved by: • Timestamp, Stored Procedure, Always Apply. • Completely implemented in the Server • No additional products to buy. • Based on log snooping rather than transaction based • Support for heterogeneous OS, Informix versions, and hardware

8. Informix Flexible Grid • What does Informix Flexible Grid (Grid) provide? • The ability to mix hardware, software, and versions of Informix in the Grid. • Centralized, simultaneous administration of servers and databases in the Grid. • Workload balancing across nodes in the Grid. • Rolling upgrades. • Instance cloning. • Selective data replication if desired. • Virtually eliminate downtime while providing uninterrupted data services. • The ability to create small to massively sized grids easily.

9. Informix Flexible Grid Connection Manager for a grid Solaris AIX Linux Windows Flexible Grid Fewer DBAs Balance workloads Re-use current HW Avoid platform lock-in Scales globally Easily Managed Secondary Secondary Connection Manager for a Cluster Connection Manager for a Cluster

10. Informix Flexible Grid • Informix Flexible Grid (Grid) replication is an enhancement to ER replication • Specific syntax for Grid vs “regular” ER configuration and administration • While ER is heterogeneous in terms of H/W and Informix version, Grid requires all instances to be on Informix 11.70 or higher: • Grid is still heterogeneous from a H/W perspective though • An Informix 11.70 instance can be in a ER cluster, a Grid cluster, or both at the same time!!! • Flexibility • Provides backwards compatibility while allowing for on-going, forward looking environment changes / adjustments

11. Informix Flexible Grid • Technically, Informix Flexible Grid provides the ability to: • Replicate data using ER without a primary key**** • Create ER replication as part of a create table DDL statement • Replicate DDL statements across multiple nodes • create table, create index, create procedure . . . • Make instance changes across all members of the Grid • Add / Drop logical logs, chunks, dbspaces, update $ONCONFIG, etc. • Support the oncmsm connection agent against Grid clusters • Replicate the execution of a statement rather than just the results of the statement executed somewhere else**** • Helpful if you have triggers that execute locally on each node • Turn on/off ER replication within the transaction and not just at the start of the transaction**** **** Has data consistency implications, reviewed later

12. HDR Replication • Uses: • High availability: takeover from primary. • Capacity relief: distribute workload. • Secondary available for Read-only queries. • Simple to administer. • Integrated • Compatible with all other Informix availability solutions. Primary server Secondary server

13. Strengths of HDR • Easy setup • Just backup the primary and restore on the secondary • No significant configuration required • Secondary can be used for dirty reads • Provides failover to secondary • Automatic failover when DRAUTO is set • Stable code • Has been part of the product since version 6 • Integrates easily with ER

14. Remote Standalone Secondary • New type of secondary – RSS nodes • Can have 0 to N RSS nodes • Can coexist with HDR secondary • Uses: • Reporting • Web Applications • Additional backup in case primary fails • Can write to these as well with one configuration parameter change. • Similarities with HDR secondary node: • Receive logs from Primary • Has its own set of disks to manage • Primary performance does not affected RSS • RSS performance does not affect primary • Differences with HDR secondary node: • Can only be promoted to HDR secondary, not primary • Can only be updated asynchronously • Only manual failover supported Replication to Multiple Remote Secondary Nodes Primary Node Secondary Node RSS #1 RSS #2

15. Usage of RSS: Additional Capacity Customer needs to add additional capacity for its web applications. Adding additional RSS nodes may be the answer. Applications Primary Server Secondary Servers

16. Usage of RSS – Availability with Poor Network Latency RSS uses a fully duplexed communication protocol. This allows RSS to be used in places where network communication is slow or not always reliable. Dallas Customer in Dallas wants to provide copies of the database in remote locations, but knows there is a high latency between the sites. Memphis New Orleans

17. Usage of RSS – Bunker Backup Customer currently has their primary and secondary in the same location and is worried about losing them in a disaster. They would like to have an additional backup of their system available in a remote location for disaster recovery. Using HDR to provide High Availability is a proven choice. Additional disaster availability is provided by using RSS to replicate to a secure ‘bunker’.

18. HDR with Multiple Shared Disk Secondary Nodes Primary SDS #1 Shared Disk SDS #2 SDS #3 Blade Server Shared Disk Mirror Shared Disk Secondary • SDS nodes share disks with the primary: • Can have 0 to N SDS nodes. • Uses: • Adjust capacity online as demand changes. • Does not duplicate disk space. • Features: • Doesn’t require any specialized hardware. • Simple to setup. • Can coexist with ER. • Can coexist with HDR and RSS secondary nodes. • Similarities with HDR secondary node: • Dirty reads allowed on SDS nodes. • The primary can failover to any SDS node.

19. SDS Usage: Capacity as Needed Web Applications Analytic Applications Shared Disk Primary Shared Disk Primary SDS #1 SDS #1 SDS #2 SDS #2 SDS #3 SDS #3 Blade Server A Blade Server B

20. Updates on Secondary • Allows updating activity to be performed from the secondary node. • Allows the customers to take better advantage of their investment.

21. Updates on Secondary • Supports a DML operation (insert, update, and delete) on the secondary node. • Uses optimistic concurrency to avoid updating a stale copy of the row. • Works on HDR secondary, RSS nodes, and SDS nodes. • Works with the basic data types, UDTs (those that store data in the server), logged smart BLOBs, and partition BLOBs. • Supports temp tables- both explicit and implicit. • Works with ER.

22. Optimistic Concurrency and Writes on the Secondary • We did not implement a distributed lock manager. • Added support for row versioning • CREATE TABLE …. WITH VERCOLSALTER TABLE …. [ADD]/[DROP] VERCOLS • Creates a shadow column consisting of an insert checksum value (ifx_insert_checksum) and update version column (ifx_row_version). • If it is determined that the before image on the secondary is different than the current image on the primary, then the write operation is not allowed and an EVERCONFLICT (-7350) error is returned. • If table does not use VERCOLS, then a before image of the row is used to verify that the update is not being attempted on a stale row.

23. Committed Reads on the Secondary • The secondary node supports ‘Committed Read’ and ‘Last Committed Read’ isolation levels. • This is implemented as a locally committed read, not a globally committed read. • The Read on the secondary node will not return an uncommitted read. • However the row could be in the process of being updated.

24. HDR Traffic HDR Secondary Primary SDS Shared Disk Blade Server A <New Orleans> Building-A SDS Shared Disk SDS Mirror Blade Server D <New Orleans> Building-B Blade Server C <Denver> Availability – The Complete Picture RSS Traffic HDR Secondary Client Apps Blade Server B Disk Client Apps <Memphis> RSS Client Apps Disk

25. Enterprise Replication Replication – The Complete Picture • Any node within the Enterprise Replication can also be a cluster. • Not only do the nodes within the cluster automatically realign, but so does the ER connections. • This provides for the ability to not only provide multiple levels of availability, but also the integration of multiple systems. Web Sales Inventory Accounting

26. Connection Manager • Maintains knowledge of all nodes within the cluster. • Records addition/removal of nodes. • Monitors type of node. • Monitors workload of nodes. • Routes the client application to target node.

27. Connection Manager • Works on the class of service concept by resolving the following requirements: • Connect to the best possible secondary. • Connect to the current primary. • Connect to the SDS node or primary with the most free CPU cycles. • Connect to either the HDR primary or the HDR secondary. • Connect to an SDS node or HDR secondary, if any are currently active, otherwise it connects to the primary. • Multiple connection managers can exist so failover is possible. • Supports Informix 11.7 replicate set level Grid/ER operations as well.

28. Automatic Failover of Primary Node • Quickly detects the failure of the current primary. • Part of the connection manager binary. • Gets confirmation through alternate paths before performing a failover. • Performs a failover using admin API interface. • There is failover for the arbitrator just as there is failover for the connection manager. • Supports proxy connections thru a firewall.

29. Continuous Log Restore

30. onbar –b –l ontape -a Log backup device Preparing Remote Standby System Remote Standby Primary Physical backup Physical Restore RestoreLogs

31. Continuous log restore option with ontape/onbar • With continuous log restore option, server will suspend log restore: ontape -l -C Roll forward should start with log number 7 Log restore suspended at log number 7 • Log restore can be restarted again with ontape command. ontape -l -C Roll forward should start with log number 8 Log restore suspended at log number 10 • Recovery mode is terminated by ontape –l command

32. Resources • The Online Informix Information Center • http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp • IBM Informix DeveloperWorks Technical Articles • http://www.ibm.com/developerworks/db2/products/informix/index.html • IBM DeveloperWorks Informix Blogs • http://www-128.ibm.com/developerworks/blogs/page/roundrep (Informix Replication) • http://www-128.ibm.com/developerworks/blogs/page/gbowerman(Informix Application Development) • http://www-128.ibm.com/developerworks/blogs/page/idsteam(Informix Experts Blog)