Redundancy Control For Postgre SQL SQueueL Khurrum A

Redundancy Control For Postgre. SQL S-Queue-L Khurrum A Mujeeb, Adam Abu Hijleh, Adam Ali Stephen Mc. Donald, Wisam Zaghal CISC 322 - Fall 2010

Overview - Motivation for Redundancy Control - SAAM Analysis - Stakeholders & Interests - Possible Implementations of Redundancy based on architecture design - Comparison of designs - Our chosen architecture - Redundancy Subsystem - Use Case - Risks and limitations of Redundancy Implementation - Effects on Concurrency and Team Issues - Limitations - Lessons Learned - Conclusion

What is Redundancy? Redundancy is having two or more independent components, be it processes or data, with the exact same purpose Lets say we have an employee database which contains 2 rows of the following data Emp num 12345 Emp John Name Age 25 25

Motivation for Redundancy Control - Increase server reliability & availability by decreasing the chances of a complete server failure - - Current implementation uses a “hot standby” server in case of failover - - If one server crashes queries still get processed as if there was no crash Asynchronous – Secondary server data is not sync’d in real time, but is updated when needed or at regular intervals Read only queries New Implementation increases reliability and availability while sacrificing performance and increased cost - Synchronous – Secondary server must be updated concurrently Read/Write queries allowed on both servers

Motivation: Stakeholders Interests Stakeholder Non-Functional Requirements Postgre. SQL Developers Maintainability End Users of Postgre. SQL Reliability, Availibility, Performance, Usability, Security Commit. Fest Reviewers Testability Network Administrators of Backend Server Reliability, Security, Scalability

Potential Conceptual Architecture for Redundancy Control – Layered Architecture • Client communicates with the redundancy control layer which in turn communicates with Postgres – Object Oriented Architecture • All subsystems communicate directly with each other

Comparative Advantages - Layered Approach Advantages: - Greater security and testability, and, in the event of the redundancy subsystem crashing, maintains data integrity - Object-Oriented Approach Advantages: - Better performance and availability

Selected Architecture for Redundancy Control

Impacted Subsystems - All subsystems affected, due to Redundancy Control acting as a “link” between the upper and lower layer

Redundancy Control Subsystem Legend Components Depends on External subsystems Depend on Subsystems

Testing Impact of Enhancement Regression Testing – make sure software does not become less effective that in the past Functionality tests - black box testing - test every time a feature is added, changed or extended Failure tests - examples that have caused failures in the past - before correcting failure, find out what caused it and save it - re run on all future versions Operational Tests - ensure existing/intended functionality/behavior not lost - catches accidental or unintentional changes

Library Interface Client Request to Log in Logged in Redundancy control Server Requested Server 1 Server 2 Backend 1 Backend 2 Server Requested Server spawned Use Case: Server 1 Fails Server and communication channel created Query Sent Query Sent Server 1 Not Responding Executed Query Returned Executed Query Returned Legend: Function Call Components User Duration of running component Data Flow

Comparison of Potential Risks & Limitations: • Further expenses are required to introduce an additional servers • Backend Servers must be Identical Risks: • Entire system is reliant upon the Redundancy Control subsystem • No failover in the case when both servers are down

Concurrency & Team Issues • Submissions of new enhancements have to added to next Commit. Fest • New team to manage redundancy control, test the code frequently and make sure there are no bugs • Further personnel • Concurrency controls remain the same as currently implemented

Limitations - Due to the lack of knowledge about SQL Database Management Systems within the group, coming up with an enhancement was very challenging - Determining what Postgres has implemented with regards to data backup systems - We had to assume that our new implementation could be easily integrated in a layered architecture

Lessons Learned – Currently, the majority of SQL Database Systems have an asynchronous redundancy feature available, as synchronous is very expensive to maintain and set-up – Understanding the difference between synchronous and asynchronous was crucial before suggesting alternatives

Conclusions - We have decided to implement Redundancy Control, utilizing three machines; one for the client communication and redundancy control, and one of the two backends - We are doing this utilizing a layered architecture - The main goal of our implementation is to INCREASE reliability, with a small reduction in performance, and an increased financial cost