Transfer Oracle database to SQL Server


Oracle offers the most advanced database management system coming with strict licensing policy and expensive pricing plans. Also, Oracle specialists have higher salaries than other database professionals. These reasons make many organizations migrate Oracle databases to other systems like Microsoft SQL Server to reduce cost of hosting and maintenance.

Advantages of SQL Server over Oracle

Microsoft SQL Server started its history in 1989 from SyBase as a parent system. Since then it has been improved a lot and has become one of the most popular DBMS. Below are the key benefits offered by SQL Server:

  • Simple Deployment:Microsoft offers fully automated setup of the databases management system using intuitive setup wizards that guide users through the installation and deploy all required components.
  • Good performance: The recent versions of SQL Server are supplied with in-built compression and security. Also, it offers enhanced performance of data storage and better integration with Microsoft Office applications.
  • Ultimate security:The recent versions of MS SQL offer advanced access control,sophisticated privilege system and password management that demands secure passwords with frequent regular updates.
  • Lower Cost:Microsoft provides recurrent automatic security updates/patches for their SQL Server DBMS at no extra charges which substantially reduce maintenance costs.

Migration Methods

  1. 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. Process the CSV data to comply with data format accepted bySQL Server if necessary. Then import the data into previously created MS SQL tables
    5. Export your Oracle views, triggers and the stored procedures to SQL statements and plain text source code
    6. Convert extracted SQL statements and the source code as Microsoft SQL format. After that load resulting entries to the target SQL Server.
  1. Using SQL Migration Assistant for Oracle: Microsoft’s SQL Migration Assistant can help in moving Oracle database to SQL Server or Azure SQL. Manual post-processing is still required to complete the migration process. First step is to migrate schemas of the database. Configure the appropriate types mapping using the related tab of the Migration Assistant for Oracle. Then apply the migrated schemas to MS SQL database.After these steps are completed, the destination database is ready for data migration. Remember this method does not handle database logic enclosed into views, triggers and stored procedures.
  1. Commercial migration tools:In order to migrate large and complex database it is advisable to use special tools that are known for their reliability and high performance. Most of these tools have been designed to automate database migration with just a few clicks of a mouse buttons. One popular tool to automate database migration is Oracle to SQL Server offered by Intelligent Converters software company. This tool can convert the following entries:
  • Table definitions
  • Data
  • Indexes &constraints
  • Views

Oracle to SQL Server converter can also filter data to migrate via user defined SELECT-statements. This flexible and powerful feature can be used to migrate partial data, to rename columns or tables or to merge several tables into a single one.