SharePlex Q3FY20 - Active-Active Replication: Considerations for High Availability

Statement-level triggers There is no good method to isolate the change from a particular user. Statementlevel triggers that change replicated data must be avoided. Update jobs Consider where to run update jobs, since running jobs on multiple servers can cause collision. Make sure your failover process handles enabling and disabling of jobs; otherwise, conflicts may occur. Application deployment (DDL) In a downtime scenario, application and DDL changes are made at the same time on one system. • A column is added, and then the application is updated to use the column. • When a column is removed, first stop updates to the database, drop the column, and then update the application. In a no-downtime, active-active scenario, you can use SharePlex to propagate the changes. SharePlex lets you choose either to replicate DDLs or to control the DDL yourself. Full control can be obtained without downtime; however, the deployment of changes has to be split into two phases and the steps depend on whether the transaction is an add or delete: • When using a DDL to add a column or table, do not add data (the application should not utilize the new column). This is to avoid replication of the new column data to the target server(s) because the column does not yet exist on the target server. The second phase is when all servers are updated with the new column, allowing the application to use the new column. • When using a DDL to drop a column or table, the first phase is to modify the application to not use the column and then update all systems to use the same application version. The second phase is to drop the column on each server. Network connectivity Application routing should be in place for both normal and outage situations. Connections from customer requests should have some routing factor, such as geographic or IP persistence. A secondary mechanism should also be in place in case of outages. When you use multiple servers, the application needs to know 3 that it must connect to a different box when there is an outage on one server. SharePlex will utilize the network connections to send data between the servers. Network bandwidth considerations for replication should be determined. In addition, network isolation and routing for SharePlex activities must be considered as well. If network bandwidth is not taken into consideration, a backlog could occur. Conflict resolution In data replication, conflicts can occur on three types of operations: INSERT, UPDATE and DELETE. • INSERT – With active-active replication, collisions on an INSERT operation should not occur if the database design is correct; that is, it considers UNIQUE key violations. In most cases, UNIQUE keys are generated by a SEQUENCE statement. There are mechanisms for handling sequence generation in a peer-to-peer environment to ensure uniqueness. • DELETE – Collisions on a DELETE operation will be ignored since the DELETE operation is the end cycle for the data. • UPDATE – The only conflict that has proven to be problematic occurs with the UPDATE operation. Here are some methods that organizations are using to resolve UPDATE conflicts: Timestamp resolution – The update that occurred last should be the one that wins. This means that the server ignores UPDATES with an older timestamp than the last UPDATE for the same row. If the timestamp on the UPDATE is newer than the last one applied to that row then the record will be applied. Host resolution – In an active-active environment, you can dictate the priority of operations based upon where the data is originated. In this scenario, the data from a lower-priority host will be ignored if there is a conflict. If the data came from a server with higher priority, it will be applied. Business logic resolution – A record’s fate depends on the business logic applied. Since everyone’s business logic is different, the mechanism for resolving conflict in this scenario should be provided by the organization’s own code. SharePlex for Oracle has built-in conflictresolution procedures for timestamp resolution and host resolution. It also Application routing should be in place for both normal and outage situations. provides the ability to write business logic conflict resolution procedures using PL/SQL. With the power of PL/SQL, organizations can apply any business logic to resolve the data. Remember that the goal is to resolve the conflict as well as to bring the data back in sync on all nodes. Conflict avoidance Avoiding conflicts is cheaper than resolving them, of course; in fact, if you can avoid conflicts, the integrity of the data is preserved without any cost. Sometimes, conflict resolution on the target cannot be done automatically due to the complexity of the logic. In this case, avoiding conflicts is the best scenario. Here are some methods for avoiding conflicts: • In web-based applications, you can persist one connection throughout the transaction. This will isolate changes from one customer to one server, reducing conflicts. • In some applications, the isolation can be done by the login: you can isolate a user’s changes to a certain machine, avoiding conflict altogether. 4 Avoidance mechanisms cannot always prevent all conflicts, but they can reduce the conflicts to a small, manageable number. By using conflict avoidance, you may narrow down your actual conflicts to a handful of tables; then conflictresolution procedures need to be applied to only those tables. Conclusion Organizations strive to minimize outages, with goals as stringent as just six minutes of unscheduled downtime a year. Activeactive replication scales the application over multiple servers to eliminate ALL outages—scheduled and unscheduled— if implemented properly. That means considering a variety of factors, including unique key collisions, scheduling of update jobs, and methods of conflict resolution and conflict avoidance. Understanding the key considerations detailed in this paper, and using a powerful data replication solution like SharePlex for Oracle, will enable you to easily achieve your uptime goals with active-active replication. SharePlex for Oracle has built-in conflictresolution procedures for timestamp resolution and host resolution.