Fortune 500 Mission Critical Application

One of SQLstream's customers is a well-known Fortune 500 company with a mission critical application responsible for managing and scheduling the release of valuable information content both on the web and through a number of other routes to market.

Value Added Information Publishing Business

One of the core businesses of this customer is the collection, creation, management and dissemination of premium information content to business and consumer users across the US. They have a major challenge maintaining their information base and have adopted industry standard RDBMS database servers to store and manage both the content and the release schedule and process.

Content Creation Process

Large teams of content creators, gatherers, and analysts assemble, create, prepare and edit the content through a home-developed mission-critical application that has evolved over time with an increasingly complicated and unwieldy data model and database. In order to offload this central database, they have replicated the data to a number of satellite database servers which feed the needs of specific applications such as accounting, compliancy checking and other planning and data warehousing reporting applications. By offloading the data from the central database server, the expensive on-demand queries are performed on separate systems from the central server dishing out the data to paying external customers.

Key Challenges – Downstream Data Dependencies

One of their challenges has to do with the reuse of the same information prepared by their analysts and content creators for a number of different external applications. In their existing application, the data are copied and edited to meet each specific application need. Their difficulties start when the core data changes. Some news arrives, or perhaps data suppliers make modifications or corrections to the data they originally supplied, and our customer then is faced with the task of tracking down all of their published (and to be published) analyses that are impacted by the changes.

Real-time Business Pressures

As their business has becoming increasingly real-time, the volume of these changes has grown to the point that has started to endanger the perceived quality of their information feeds by their customers. This in turn increases the chance their customers will turn to other competitive suppliers of similar information. They decided to create a whole application and data model that would elegantly address all of the challenges that they faced:

• Higher transaction and querying performance
• Better management and isolation of data in a single place for safe editing
• Publishing multiple views of the same underlying data according to application need
• Maintaining a single “golden source” that all other application views depend upon

Big Bang Migration

They designed the new data model and started coding the new application. The problem was that the new application had a radically different data model and database technologies, and had radically new application functionality but it still needed to support all of the existing mission critical functionality. That meant that the new application would have to be built as a single large development project with a “big bang” transition over to the new system. Unfortunately, and perhaps predictably, the “big bang” approach failed. There was just too much complexity in terms of application logic and critically needed functionality to get a stable and usable system at first release. The project also was clearly heavily overrunning on both money and time budget. They took the painful decision to abandon the project and to try and struggle on with their existing system, adding major new hardware upgrades to try and alleviate some of the performance issues. The process issues of moving to near real-time were not alleviated by hardware. More people had to be hired, and the working day was extended to allow all the data changes to be propagated across the database, tracking down all of the downstream dependencies by hand. This was slow, painful and becoming increasingly expensive and unworkable.

Second Attempt

Of course, the business drivers did not go away, so the customer was forced to face the same problem a little over one year later. This time the pain was acute. The decision was taken to create their new application iteratively and incrementally and to run it in parallel with the old application. That way, over time, the new application could replace the old, once the new was able to meet all existing needs and proven to be stable and reliable.

Parallel Running

The key remaining issue was maintaining both the new and the old applications' consistency. How should they do this? There were a number of choices examined:

• Have all existing users double enter the data – in both the old and the new applications
• Hire a new team of data entry clerks to manually insert data from one system to the next
• Try to evolve the database from the old to the new updating both old and new apps at the same time in a series of “mini bangs”
• Write a suite of software to perform the updates
• To find a flexible data mediation and migration technology to solve the problem – the SQLstream solution presented

Solution Choices

The first two options proved unworkable. Their existing users could barely keep on top of the schedule operating just a single system. The savings of using the new system were not sufficient to overcome the pain of maintaining two systems. For the second option, the cost of training new users alone would be prohibitive, and would only be needed for a stop-gap solution. The fourth option was again prohibitively expensive and an internal project to do just that before as part of the “big bang” failure had, well, failed. So it was not politically acceptable to repeat the error.

The customer initially decided to try to evolve the databases and data models. It seemed lower risk if expensive. They set off with good intentions, meanwhile SQLstream prototyped its solution. The customer ended up abandoning the database evolution approach. On paper it looked good. In reality, the performance issues and issues of continually creating completely new data models and reloading and moving all of the data across from one model to the next turned out to be show-stoppers.

Low risk, High Return Option

The SQLstream solution proved in the end to be the low cost, low risk, high return option. SQLstream's RDBMS Change Data Capture module completely automated the tasks of capturing all transactions on either database and converting them into streams of RAM messages. The transactions were either insertions, deletions, updates or upserts (update unless not present in which case insert new record).

These SQLstream transaction streams were then mediated through a series of SQLstream queries in order to perform the necessary inter data model translations.

SQLstream queries enabled:

• Grouping of data according to transaction window
• Eliminating redundant transactions (edit, delete,upsert) and reducing the transactions to the minimum needed by combining multiple operations into a single operation
• Mediating between data types and field names (trivial)
• Aggregating multiple transactions over time to create a single destination transaction
• Splitting up a single transaction into multiple destination transactions
• Managing the consistency of the data across these complex intermediation transactions

Illustration By Example

Of course, we cannot share the customer data, so we instead illustrate the techniques with an imaginary application which we can readily demonstrate to all interested parties.

Consider two applications for managing orders that coming in from the web or over the phone. The original evolving application Southwind is to be replaced by a newer Northwind application that presents a more normalized data model. In Southwind web orders and phone orders are handled in separate tables whereas in the Northwind they are normalized into a single table, where customer data are separated into a separate customer details table.

The SQLstream RDBMS Change Data Capture and Data Migration solution easily solves the problem of maintaining bi-directional mediation of transactions and synchronizing across different data models. Any transaction performed on one data base and data model is instantaneously translated and mediated into the equivalent transaction or sets of transactions on the other. Both databases are continuously updated and maintained in a mutually consistent manner.

SQLstream instruments the changes to each database and data model and generates RAM message streams capturing those transactions. SQLstream's RAMMS then pipes the message streams through a pipeline of queries that transform and intermediate the data. SQLstream's RDBMS Change Data Capture solution manages the transactions and integrity for all changes made over both databases.

Benefits of the SQLstream Solution

The SQLstream solution takes the pain out of the whole database and data model intermediation process and offers a number of clear benefits:

• Very low cost of development and solution deployment through high-level RAM and SQL declarative processing
• Familiar SQL enables existing SQL skillsets to be leveraged to manage and maintain the system
• Easy to add new transformation and changes
• Easy to use the same solution to populate additional data stores and data caches
• Allows seamless coexistence of both new and old databases, data models and applications
• Enables painless migration over time from one application to a radically different application
• New application can evolve over time in safe, bite-sized steps independently from the existing, proven application
• Completely insulates the old application and database from the new application and database
• Allows concurrent use and integrates the new application with the new so that new capabilities can be added over time to enhance the combined solution
• Eventually, when the new application is sufficiently rich and stable, the old application can be completely dropped.

Performance Considerations

The SQLstream solution is able to process many hundreds of committed transaction per second across both databases while maintaining transactional integrity.

Contacts