Stabilizing a Legacy System

Dymeng was contacted by a New York based legal firm to help solve numerous critical issues stemming from a legacy custom system from many years prior. The goal was to stabilize and secure the existing system, then increase its integrity to lay the groundwork for future business growth plans. Dymeng was able to achieve this through extensive experience in data management, legacy systems work and data integrity management, successfully transforming a brittle system into a base foundation for future growth.

About the Client 

Our client, a legal firm based in Long Island, NY, has been operational for more than 35 years, offering legal service plans to various unions and groups throughout NY and surrounding states. With a team of over 50 lawyers, associates, counsels and management staff, they handle hundreds of thousands of members eligible for their services via service plans, as well as non-plan based cases. Their legal practice areas include Personal Injury, Estate Planning, Business & IP, Matrimonial, Family and Adoption, Immigration Law, Criminal and Civil Litigation, among others. 

Challenges & Objectives

The firm sought technical help for two primary reasons: the first to stabilize an existing, legacy system that was prone to breakage and had many processes contributing to low integrity data, and the second to position their technology landscape for future growth plans.

The legacy system was built in the ‘90s using Microsoft Access as a backend database and ASP (Classic) as the front end system. It had not received any regular maintenance or updates for nearly 10 years, and many subsystems were non-functional or functioned incorrectly. Furthermore, there were concerns about data security and disaster recovery. Database backups were haphazard and recovery plans were generally absent. The legacy system’s data stored sensitive personal information in unencrypted formats and hadn’t been initially designed from the ground up for security and integrity. The front end internal website lacked input sanitization and validation, contributing greatly to a lack of data integrity. Additionally, automated data import processes were completely broken or functioned incorrectly, resulting in some 100k+ duplicate member records that had to be cleaned up and and further prevented. Due to these integrity issues, reporting was unreliable, making it difficult to make objective business decisions, and compliance with their plan contracts was difficult.

The goal then was to address all of these problems to result in a stabilized, high integrity data system, which could then be used as the basis to evolve the firm further.

Dymeng’s Solutions

Once reviewed and planned, the first order of business – as is typically the case for working with legacy systems – was to secure the existing database in terms of operational availability. That is, making sure that we could get backups, restore backups, and control the availability of the database, without making any changes to it (yet). Because the plan called for a migration from Microsoft Access to Microsoft’s SQL Server as a more robust database engine, Dymeng implemented a quick and easy disaster recovery plan for the existing database to secure it prior to the migration, ensuring we could work in the existing technical space while having mitigated the primary risks involved with legacy database operations.

Once the legacy database was secured and workable, we set out to implement the next steps: a two-phase, parallel effort to a) migrate the database to a more robust database engine, and b) implement a true disaster recovery and data integrity plan for the new database engine. This would serve as a long-term home for clean data and enable the eventual replacement and evolution of their legacy applications that the database served.

With our experience in database systems and the complexities and risk-prone nature of working with legacy systems, Dymeng was able to complete a full conversion of database engines from Microsoft Access to SQL Server with no adverse effects. This, in turn, allowed for the implementation of a robust disaster recovery system for the database, including regular backups, transaction log shipping, secure offsite storage and recovery processes, all fully automated and monitored.

Having completed the migration of the database to its new permanent home and swaddled it in layers of security and disaster recovery, our next task was to clean up sensitive data and general integrity issues (such as the 100k+ duplicate member records). We reviewed the database to identify all areas containing sensitive data, which was then reviewed with the client. Many of the sensitive data columns were deemed to be outdated and no longer of use to the company and were subsequently removed. Others were placed into specialized secure tables within the database and encrypted. To solve the data integrity issues, Dymeng performed a series of data queries to identify the extent of integrity issues, as well as examining the incoming data channels to identify the major offenders. In this case, the cleanup process was laborious and had to be done by people with business history: thus Dymeng created a quick internal tool for comparison and merging of member records that could be run either in batch or individual modes. In addition, the critical input paths that were responsible for the integrity issues were isolated and updated accordingly: input sanitization and validations were implemented, and guards against auto-importing of duplicate data put into place.

Results of Efforts

What began as a critically brittle database with problems of security, outages, integrity and general operational stability was able to be completely transformed to a robust, high integrity, stable and secure database platform.

Dymeng was able to perform these critical operations with minimal risk to operations, within a four-week time period. The project completion came in just under the initial estimate and budget, and just under the estimated timeframe with no unexpected side-effects or complications.

The resultant data system offered numerous advantages:

Due to High Integrity Data:

• Increased confidence in internal company reporting for business decisions
• Compliance reporting processes for their clients was vastly improved, including more detailed report deliverables and an 85% reduction in time required to generate deliverable reports
• Call staff were able to respond to member call-ins approximately 50% faster due to not having to wade through bad data
• Overall member experiences improved due to better data tracking
• Overall departmental waste time reduced by approximately 30%

Due to Stability, Risk Management and Security:

• Business peace of mind due to system stability and modernization
• Increased data security and breach prevention and mitigation
• A guaranteed 1-hour business time loss for data through a robust disaster recovery implementation (previous recovery time was unknown, presumably days or longer)

In addition, a primary advantage of this work allowed the client to focus their efforts on forward-looking initiatives. Since then, Dymeng has continued to play an integral part as their solutions provider for many of their technology needs.