Oracle to SQL database migration

The Client

A global media firm supporting big clients all around the world. With thousands of staff, supported by a small I.T. team, reliability and support for their key business systems is crucial.

The Problem

The client had standardised on Microsoft SQL server as their database provider, with all business applications storing their backend data there. This allowed the I.T. team to monitor the performance easily, maintain the databases, and ensure that a proper backup regime was in place.

Only a single application, the company intranet, did not follow this pattern since this system (written half in ASP.NET and half in classic ASP) was not well architectured, and the effort of changing the data access was deemed to risky.

With critical business data stored in both though, this resulted in 2 lots of licenses and 2 lots of expertise, and 2 lots of maintenance.

The Solution

Their intranet was successfully updated – and performance was improved at the same time. The update was performed in a tight timescale and with minimal disruption to the business.

Database conversion

The backend data for the entire intranet system was migrated from Oracle to Microsoft SQL Server. All existing data structures were preserved and stored procedures were converted. An analysis of the original Oracle databases allowed a strong set of tests to be established to ensure the reliability and integrity of the resulting SQL database.

Code Conversion

Code-bases of both Intranet systems were also converted - either by replacing data access components, or providing adapters where necessary to allow the existing code to continue functioning efficiently with the minimal number of changes.

A full set of component and integration tests were designed and implemented against both the unchanged, and updated systems to ensure that the Intranet was functionally identical after the update and with no issues.

Performance

The performance of the new databases was also analysed and improved. Inefficient indexes were identified, complicated key generation eliminated, and in some cases data structures were normalised - so that the implementation of the new databases also resulted in faster performance for the users.

The Technology

The final system combined an ASP with an ASP.NET frontend, and housed the data in the Microsoft SQL Server database.