Converting Oracle to SQL Server Using SSMA – Case Study

Converting Oracle to SQL Server Using SSMA – Case Study

Client’s Challenge

Our large Australian resource exporting client was heavily reliant on a shipping application that used Oracle as its primary database. The Company had earmarked Microsoft as its preferred direction including SQL Server. It had a skilled internal team that was able to administer the Microsoft servers and databases. Administering and maintaining the single Oracle database had become a key concern with all other applications using SQL Server. The concern was magnified when the client was informed that the database was soon to be out of support and that several of the products used including Advanced Replication were being deprecated and would need to be replaced by an expensive alternative.

An independent audit had also earmarked the Oracle based application as a major risk. A move from Oracle to SQL Server (if possible) would reduce the license cost, ease the maintenance and reduce the support cost by being able to use the internal support team.

MGA was engaged to assist in determining if the migration to SQL Server was possible with minimal risk to the Business that relied heavily on this application. The client had an aggressive timeline that required the migration from Oracle to SQL Server to complete within 6 weeks. The short timeframe was due to an obligation to meet compliance of all databases being at a supported release. The Company required a functionally correct, robust, highly available and highly performant system post the migration. They required minimal downtime during the cutover.

The application used Oracle Tables, Indexes, Views, Triggers, Functions, Procedures and Scheduled Jobs. It also used Oracle Advanced Replication.

MGA Approach

We assigned a team of 2 persons that were highly skilled at Oracle and SQL Server and knew the differences between the two in great detail. SQL Server Migration Assistant for Oracle (SSMA) was used to convert the Oracle objects and data to SQL Server.

We performed various checks in the Oracle database prior to running SSMA. We are aware of the differences in the two products and were keen to see how SSMA would convert them. The following list shows some of the major differences between Oracle and SQL Server and we were keen to see how SSMA would handle them.

SQL Server does NOT have:

  • packages
  • sub partitions
  • ROW level triggers and BEFORE triggers
  • DUAL table
  • Same Dictionary tables and views
  • Database links (SQL has a linked server)
  • Various data types e.g. BFILE, CLOB and BLOB datatypes (SQL has VARBINARY)
  • Many functions including NVL, TO_DATE, DECODE and others
  • DBMS procedures e.g. DBMS_JOBS and DBMS_SHEDULER (SQL has SQL Agent Jobs)
  • NULLS in Unique indexes
  • Oracle Advanced Replication, Advanced Queues and Materialized Views

If the source database contains any of those in the list, the post conversion result in SQL Server needs to be looked at carefully to ensure it has converted OK.

Another check that we made was for any invalid Oracle Objects. There was no point in migrating objects which are already known not to work.

SQL Server Migration Assistant for Oracle (SSMA) Effectiveness

SSMA is a tool provided to automatically convert and Oracle database to a SQL Server database. The question was “How much of the conversion is fully automated”? and “How do we identify the parts that are not working as expected and may cause issues?”

In this case study, we estimated that SSMA was able to perform around 80% of the conversion. The data portion of the conversion was fine, but issues occurred with triggers and other objects stored within the Oracle database that required similar logic in the SQL Server database. The process of migrating the logic requires a thorough understanding of how it is constructed in Oracle PL/SQL and how to rewrite the logic in Microsoft T-SQL to exactly retain the logic’s intention. Our highly skilled staff members where able perform this over numerous occurrences, in an economical timeframe.

Some items could not be converted by SSMA including User Logins, Scheduled Jobs and Database links. These items need to be set up manually by the team performing the migration.

Adjustments Made After SSMA Migration

This section shows the adjustments that were required to be made to make the application work after the SSMA migration.

Note that these changes were in addition to the User Creation, Database Link Creation and Scheduled Jobs which had to be performed by the migration team.

Triggers

  • SQL Server doesn’t have before triggers and SSMA generates INSTEAD OF triggers using cursors.
    • Tested OK and no action required
  • SSMA generates various triggers where there are logic issues inside the trigger. The application functionality was broken.
    • Fixed and tested logic issues
  • When the Oracle trigger has a single “AFTER INSERT, UPDATE, DELETE”, SSMA will create 3 separate triggers with the same trigger content and an additional variable to categorise the Insert / Update / Delete
    • Tested OK and no action required
  • Some basic before update row triggers were created as row triggers with cursors and some code was commented.
    • Required consolidation of the triggers
  • Functionality differs between Oracle UPDATE () and SQL UPDATE () – SQL Server’s UPDATE () will return TRUE even if the column is touched with the same value but Oracle doesn’t return TRUE. This broke the application functionality
    • Fixed the Triggers by adding a conditional statement to compare the previous & current values
  • Triggers with DBMS_REPUTIL.FROM_REMOTE, even though SQL Server has NOT FOR REPLICATION keyword, SSMA didn’t convert the scripts and commented the statement block
    • Included the NOT FOR REPLICATION

Stored Procedures

  • Converted SQL Server Procedure returns 0 instead of NULL. Oracle Procedure returns NULL
  • Fixed the logical issue inside the stored procedure
  • Oracle Exception trapping – SSMA converted the script with raise exception without masking
  • Commented the Raise Exception on SQL Stored Procedure

Conclusion

While SQL Server Migration Assistant is a valuable tool in migration from Oracle to SQL Server, it can only typically help with perhaps 80% of the overall migration. It does a great job of converting data types and moving data, but lacks finesse when it comes to migrating the logic contained in stored procedures, functions and triggers. Logic migration is a task that often holds customers back as it requires deep knowledge of both PL/SQL and T-SQL and is very much a manual process, meaning that many customers are unable to realise the cost and support benefits of moving to SQL Server.

Our customer achieved all of its requirements, including a migration that was achieved within the 6 weeks. But meeting the tight delivery timeframe required experts in both technology platforms. MGA had the in depth expertise of both Oracle and SQL Server to achieve this highly successful outcome. Not having the extensive skills in both would have made the migration particularly difficult.

»