Make your own free website on Tripod.com

Case Study: Migrating from Oracle to SQL Server at AIM Healthcare Services, Inc.
Last updated: November 15th '04 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New


This month's 2 click survey:
Is .NET important for a database professional?


Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW




 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
Related SQL Server and Oracle articles:  
Migrating from Oracle to SQL Server - T-SQL, PL/SQL differences Oracle Books (8i, 9i) for Database Professionals
SQL Server 2000 for the Oracle DBA SQL Server FAQ on programming, administration, replication and tools
SQL Server DBA and database developer interview questions SQL Server administration best practices
Related books:
Inside SQL Server 2000 Beginning Oracle Programming Expert One on One: Oracle

CASE STUDY


AIM Healthcare Services, Inc.

Increased capacity and reduced TCO by migrating a mission-critical database from Oracle to Microsoft SQL Server

Industry: Healthcare
Products: DB Best Migration Analyzer, DB Best SQL Converter


Summary

DB Best provided AIM Healthcare with the DB Best SQL Converter™ plus critical consulting services to quickly and cost-effectively migrate their claims-processing database from Oracle to Microsoft SQL Server. This enabled AIM Healthcare to significantly increase the capacity of their claims processing database while simultaneously reducing their operating costs and consolidating their data center around a single database software vendor.


Problem: Claims processing capacity

AIM Healthcare’s claims processing database was rapidly reaching the physical limits of its underlying platform, so migration to a new hardware/software platform was necessary.


Solution: Migrate to higher capacity platform

AIM Healthcare migrated their claims processing database to a higher capacity infrastructure to support the explosive growth of the claims database.
Results
  • AIM Healthcare significantly increased the capacity of it’s claims processing system.


  • Their operational costs were reduced by migrating to lower cost DMBS software and hardware.


  • The cost of the migration project will be recouped within the first full year of operation.



Benefits
  • The DB Best Migration Analyzer “provided an accurate assessment of the effort that would be required to migrate from Oracle to SQL Server.”


  • The DB Best SQL Converter “was a tremendous migration aid, automatically converting 99% of our database code. The Converter combined with DB Best Services allowed us to migrate this highly complex database in a most economical manner.”


  • “We increased the capacity of our claims processing system and simultaneously lowered our operational costs. This is a big win for us!”


DBBest.com logo AIM Inc. logo

THE BUSINESS CHALLENGE
AIM Healthcare Services, Inc. (AIM) is a leading innovator in technology-driven applications and services for the healthcare industry. AIM significantly impacts the financial well-being of most major healthcare entities in the country. It is a claims cost management consulting firm with over 700 employees nation-wide and 1,500 payor clients including Blue Cross/ Shield, Indemnity, HMO’s, Self-Insured and Medicaid Programs. In support of AIM’s claims processing activities, it has developed a large, sophisticated claims processing database that is loaded each night with new insurance claims, and is accessed interactively during the day to resolve claims issues. As the database grew, it was rapidly approaching space and CPU power limits needed to keep up with the demand. This was the catalyst for deciding how to upgrade the infrastructure – remain on Oracle and replace the existing computers with a more powerful computer architecture, or replace both the database system and the hardware with a more economical infrastructure. Whether AIM stayed with Oracle or decided on Microsoft SQL Server, either choice would involve a migration, although migrating to a new Oracle DBMS would be more straightforward.


THE DB BEST SOLUTION
If AIM could find a way to rapidly and safely migrate to SQL Server, then they would be able to simultaneously scale the hardware architecture to one that would meet their future needs as well as reduce the ongoing operating costs of the infrastructure. By using the DB Best Migration Analyzer to analyze the Oracle-based database, AIM was able to understand the migration challenge very clearly. The information provided by the assessment included the following:

  • Number of objects in the database by type, e.g. AIM’s database included


  • over 600 tables


  • over 450 stored procedures


  • over 150 functions


  • over 1000 exception blocks


  • over 115,000 lines of code


  • Percentage of the database that the SQL Converter would convert automatically


  • Specific code constructs that present a conversion problem


  • Estimated effort to manually convert this database: approximately 5300 staff-hours
A key factor provided by the assessment report was the fact that the DB Best SQL Converter could automatically convert 99% of the database code from PL/SQL to T-SQL.

“We needed to see if we could quickly and safely migrate to Microsoft SQL Server. We had good Oracle skills, but with little SQL Server and almost no migration experience this would take us too long without DB Best’s software and database expertise.”

Based on the assessment report, analysis of several automatic conversions, and the fact that DB Best Services was able to commit to 100% conversion within two weeks and to work with AIM’s IT staff to QA the converted code, the decision was made to migrate to SQL Server. DB Best was selected as the software and services provider to assist with the migration.

THE RESULT
AIM Healthcare was able to migrate to the new platform and take an important step towards the realization of their vision to “reduce the cost of healthcare and simplify healthcare information management.” Although 99% of the code conversion was accomplished automatically, the project was still quite demanding because of the size and complexity of the database code and its extensive use of dynamic SQL which could not be directly converted by the SQL Converter. A significant effort was made to review and modify the code, eliminating some constructs that didn’t perform as expected and removing some obsolete code. Also, since Oracle provides rich support for exceptions and SQL Server 2000 doesn’t support exceptions, it was necessary to provide alternate implementations for this part of the code. However, given a code pattern to be changed, the SQL Converter was able to emit the revised behavior during the conversion whenever that pattern was detected in the Oracle-based business logic. The fact that these changes could be applied to all applicable instances, represents another advantage of the SQL Converter.

Besides the conversion/migration process, considerable attention was paid to testing the resultant SQL Server database. Clearly, before going live with the new database it was necessary to insure that its behavior would be identical to the original database. During this QA period the old and new databases were run in parallel. Nightly updates to the Oracle database were also applied to the SQL Server database. Instances of the applications that ran against Oracle were developed to run against SQL Server and the interactive users were also turned loose on the new database. The testing process, which took about four months of part-time effort by AIM’s IT staff, was the longest running activity of the migration project. The team leader felt that more up front analysis and planning might have shortened this phase of the project. In addition, if DB Best’s newly developed Migration Tester was available to support this QA phase, we estimate that it could have shortened the testing phase by close to 50%. Designed specifically to address the testing phase, the Migration Tester automates the generation of test data, and invokes corresponding procedures on the two databases. It then compares the results and flags those procedures/functions whose return values or database changes are not equivalent.

"The ability to automate nearly 100% of the code conversion to another database system makes the previously prohibitive process (in cost, time, and risk) a viable option for any enterprise."

By using DB Best’s software and services, AIM saved over half a million dollars on the project, as compared to manual conversion. These savings are based on the estimate provided by DB Best’s Migration Analyzer. This estimate is produced by assessing each object’s complexity and, assuming expert Oracle and SQL Server consultants, produces a conservative estimate of the labor required to convert the database manually. In this case, the manual conversion effort was estimated to be 5300 hours, so at $100 per hour for consulting, the cost would be approximately $530K. Thus, with DB Best’s price at less than 10% of the manual cost for the software and services to achieve 100% conversion, the project was completed much more rapidly than if done by hand, and at an enormous saving.

To sum up, AIM was able to reduce their operational costs by moving to a less costly infrastructure while at the same time significantly increasing the capacity of their claims processing database. Moreover, given the in-depth assessment provided by the DB Best Migration Analyzer, the comprehensive code conversion capability of the DB Best SQL Converter, combined with the expertise of DB Best’s consultants, the conversion project had minimal impact on AIM’s ongoing business operations.


DBBest logo
DB Best Technologies
550 Lakeside Dr., Suite 7
Sunnyvale, California
Phone: 408-530-8829
Web: www.dbbest.com
E-mail: info@dbbest.com

AIM Corporate Headquarters
1021 Windcross Court
Franklin TN 37067-2678

Disclaimer, terms of use and privacy policy
Copyright © 1997 - 2004 Narayana Vyas Kondreddi. All rights reserved.