Transfer Oracle database to SQL Server

Published On December 6, 2017 | By Robbie Garner | Programming

Oracle software is the most powerful database management system and because of its popularity, it has a strict licensing policy side by side with good pricing plans.Naturally, Oracle developers and administrators charge more for their work than other database professionals. These reasons are enough to force many companies to move their databases to other systems like SQL in order to cut the cost of maintenance of their data.

Advantages of SQL Server over Oracle

Sybase, the variation of Microsoft SQL started in 1989, has improved a lot and over the years it has become one of the best database management systems. Let’s look at the benefits it offers:-

  • Easy and Simple Installation:

    Microsoft’s idea of a fully automated setup of databases paid off. The company supplied system with easy-to-use setup wizards that guide even novice users through the entire installation process and install all the necessary components.

  • Good performance:

    The latest SQL Server comes with inbuilt compression and security, enhanced performance in data collection and better integration with the popular Microsoft Office.

  • Top-notch security:

    The latest versions of MS SQL have solid access control, permission settings and password management that demands secure passwords along with frequent renewal requests.

  • Lower Cost:

    Modern Microsoft SQL database management systems also perform recurrent automatic security updates/patches at no extra charges – which substantially reduce maintenance costs. In addition, there are no additional charges for all the DBMS which includes disk partition system, reporting and security tools.

 

Migration Methods

Manual Processing of Data. This process includes the following steps:-

  1. Export Oracle definitions of table, indexes &constraints as SQL DDL statements
  2. Convert SQL DDL statements to MS SQL format.After that load them to your server
  3. Export Oracle data as CSV files to an intermediate storage
  4. If necessary, convert the CSV file if they are not accepted by SQL Server. After that import the data into your MS SQL database
  5. Export your Oracle views, triggers, and the stored procedures to SQL statements and plain text source code
  6. Convert resulting SQL statements and the source code as Microsoft SQL format. After that load them to your target server

2. Using SQL Migration Assistant for Oracle:

Microsoft’sSQL Migration Assistant can help in moving Oracle database to SQL Server or Azure SQL. It still needs some manual steps to complete the migration process. Firstly, convert database schemas. To do this you must set up types mapping using the corresponding tab of SQL Server Migration Assistant for Oracle. Then you should apply the converted schemas to your target database.It’s only after completing these steps the destination database will be ready for data migration. This method does not process such database objects as views, triggers & sequences.

3. Commercial software/tools:

In order to migrate a large and complex database, it is advisable to use systems/tools that are known for their reliability and high performance. Most of these systems allow the transfer of entire database a few clicks of a mouse. One popular tool you can use is Oracle to SQL created by Intelligent Converters. This tool can convert the following:-

  • Table definitions
  • Data
  • Indexes & constraints
  • Views

This tool can also convert the result of SELECT- statements that are defined by the user. This feature can also be used in filtering data, renaming of columns or tables and merging multiple tables into a single table.

Like this Article? Share it!

About The Author

Leave a Reply