SQL Server to Oracle Migration Guide

In the world of business today, the significance of customer data, sales records, or inventory statistics cannot be overstated. It's not a secret that modern businesses operate on the lifeblood that is data. A multitude of database management systems (DBMS), including Oracle, MSSQL, DB2, MySQL, PostgreSQL, and MongoDB are available for businesses to choose from. Among these, Oracle and SQL Server emerge as the most popular options. This article mainly focuses on the critical features of Oracle and SQL Server databases, delves into the ways how to migrate SQL Server to Oracle, and provides an overview of efficient tools for smooth transitions with step-by-step guides on working with them.

Microsoft SQL Server overview

SQL Server, a relational database management system, was brought to life by Microsoft in 1889 to contend with Oracle and MySQL databases. Like its counterparts, SQL Server rests on the SQL standard programming language, used widely for interacting with relational databases. SQL Server is tied to Transact-SQL, also known as T-SQL, which represents Microsoft's adaptation of SQL incorporating a set of proprietary programming features.

Key Features of Microsoft SQL Server

• Data analysis capabilities. Microsoft SQL Server equips businesses with analytical support to scrutinize stored and managed data to facilitate decision-making. An Online Analytical Processing tool, part of Microsoft SQL Server Analysis, merges seamlessly with SQL Server, offering valuable data insights.
• Comprehensive integration options. Packed with Microsoft SQL Server Integration services (SSIS), SQL Server presents a multitude of possibilities for data loading, data transformation, and data extraction. SSIS is an Extract, Transform, and Load (ETL) tool, tailored for large-scale data integration and transformation operations.
• Robust security measures. SQL Server protects itself with a range of encryption techniques, making the database resistant to unauthorized access. With its advanced security features, SQL Server ensures a safe environment to guard your data against potential threats.

Oracle Overview

Oracle Database, often referred to as Oracle RDBMS or just Oracle, is a relational database management system. Conceived by Lawrence Ellison and his team of engineers in 1977, it has since become a staple in the tech industry for its ability to store, manage, and extract data efficiently. Its uniqueness lies in its pioneering approach to enterprise grid computing and data warehousing, setting it apart in the realm of database management. Oracle operates on the PL/SQL language foundation, making it highly productive for programmers to query, manipulate, and modify data within a database.

Switching from T-SQL to Oracle PL/SQL can be a daunting task if migrating manually. However, Ispirer Toolkit can handle it easily with no need to adjust the resulting code. Check out how the tool automates this process: SQL Server to Oracle migration

Oracle DB runs on most major platforms, including Windows, UNIX, Linux and macOS. The Oracle database is supported on multiple operating systems, including IBM AIX, HP-UX, Linux, Microsoft Windows Server, Solaris, SunOS and macOS.

Oracle key features

An Oracle database offers the following features to meet the requirements of robust database management:

• Performance. Features such as Oracle Advanced Compression, Oracle Database In-Memory, Oracle Real Application Testing, and Oracle Times Ten Application-Tier Database Cache are included to provide supreme system performance enhancements. Their main objective is to bring system performance to the highest possible level.
• Scalability. Oracle database offers scalability features such as Real Application Clusters (RAC) and Portability that permit its adjustment based on usage. In a clustered environment, it provides functions like rolling instance migrations, application continuity maintenance, quality of service management, and seamless upgrades.
• Availability. OracleDB ensures that the database is always online and accessible, thanks to its 24/7 operational status. This persistent availability is supported by Oracle Data Guard functionality and its Real Application Cluster (RAC) mode. Even in the case of a failure, data remains accessible on other cluster nodes, ensuring continual system operation.
• Backup and Recovery. Oracle is engineered with robust recovery features, enabling the retrieval of data from most types of failures. The system is designed to quickly restore the database after a failure, ensuring high availability. Meanwhile, unaffected data remains accessible while the affected segments undergo recovery.
• Security. Oracle employs a robust security mechanism to prevent unauthorized database access. It leverages Oracle Advanced Security features providing solutions like Transparent Data Encryption (TDE) and Data Redaction for data protection. TDE supports data encryption at its origin and during export, whereas Redaction is conducted at the application level. Additional security features include Oracle Database Vault which manages user privileges and Oracle Label Security.

SQL Server to Oracle Conversion Tools

Data migration from SQL Server to Oracle can be a daunting task for a business. Custom coding is one of the methods to accomplish this, which entails generating unique PL/SQL scripts to facilitate the migration process. However, given its potential for errors and the considerable time required, many businesses opt for other solutions, such as data replication software. These database replication utilities assist in navigating the intricate process of converting data types and transferring data among diverse database systems.

There are several tools in the market that are widely used to transition both a database schema and its data from SQL Server to Oracle. They simplify the complex process and ensure a smooth transition with minimal disruptions to operations.

• SQL Server Integration Services (SSIS). An intrinsic part of the SQL Server, this platform offers various data integration services, including a tool designed for ETL processes within a data warehouse.
• Oracle SQL Developer. This is a pre-packaged integrated development environment from the house of Oracle. It is geared towards capturing and transforming data from SQL Server into a format compatible with Oracle, along with generating necessary DDL statements.
• Oracle GoldenGate. It is a high-end software solution that offers the capability to duplicate, modify, and filter data as it moves from one database to another. This feature is an additional option for Oracle's Enterprise Edition Database, providing a real-time data mesh service. GoldenGate works by replicating data between source and target databases to keep it highly available for real-time analysis using CDC.
• Ispirer Toolkit. A comprehensive, automated solution for migrating databases from SQL Server to Oracle. Built around a smart migration core, this database migration tool automates the entire process of database migration, converting the complete database schema and importing data into the Oracle database.

Migrate Data from SQL Server to Oracle

Migrating data from SQL Server to Oracle is a considerable challenge that organizations face when they choose to change their database systems. The complexity arises due to the inherent differences in the data structures, syntax, and other elements between these two systems. This migration process involves steps like data extraction from SQL Server, transformation of that data to make it compatible with Oracle, and then loading it into the Oracle database. It's critical to ensure data integrity, consistency, and security during this migration. Identifying the right tools as per the specific needs of the migration, and having a well-planned approach, can make this task more controllable and less prone to errors.

How to migrate SQL Server to Oracle?

For those looking to convert SQL Server to Oracle, the SQL Developer and Ispirer Toolkit come in handy. We'll explore both tools in detail below. These tools facilitate a smooth transition from SQL Server to Oracle. Let's walk through the process of using SQL Developer to migrate an MSSQL database to Oracle.

Step 1. Creating the mwrep User

To create a new database user, perform the steps below:

Note: If you already have a system_orcl connection and a mwrep user, you can skip these steps.

• Launch Oracle SQL Developer through the desktop shortcut.
• Navigate to the View section and choose Connections.
• Choose a New Connection. A screen will appear requesting you to specify a new database connection.
• Provide a name in the Connection Name field, for instance, system orcl. Fill out the Username and Password fields with the system and your password. To remember your password, ensure the Save Password box is ticked. Specify your hostname in the Hostname field, and input orcl in the SID field. Select Test.
• Inspect the connection status located on the lower left corner, above the Help button. It should read Success. Validate the connection by clicking Connect and then close the window.
• The saved connection will now appear under the Connections section in the Connections tab.
• Expand the system orcl connection. Keep in mind that a SQL Worksheet will automatically launch upon establishing a connection. This SQL Worksheet can be used to execute SQL queries against your newly created connection.
• To set up a user for the migration repository, enter the following command in the SQL Worksheet.

CREATE USER MWREP IDENTIFIED BY mwrep DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP; GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

• Execute the script by clicking on the “Run Script (F5)” icon.
• The mwrep user will be created.

Step 2. Creating the Migration Repository

Before you can transition from Microsoft SQL Server to Oracle, it's crucial to establish a repository to contain the necessary tables and PL/SQL packages. If you have an existing migration repository and a connection established with mwrep orcl, you can bypass these steps.

• The first step involves setting up a connection with the mwrep user, an essential prerequisite for creating the repository. To do this, navigate to the Connections tab, right-click on Connections, and select New Connection. A prompt will appear asking you to establish a new database link. To view this tab, you may need to go to View > Connections.
• When defining your connection, use mwrep orcl (or a name that suits your requirements) in the Connection Name field, and mwrep as the Username and Password. The password can be saved by ticking the Save Password box. Provide the hostname in the Hostname section, and use orcl for the SID. To validate the connection, click Test.
• You can see the connection's status in the lower left corner, just above the Help button. If successful, the status will read 'Success'. Maintain this connection by clicking Connect, and then close the window.
• After the connection has been saved, it will be displayed under the Connections section of the Connections tab.
• To associate the Migration Repository, right-click the mwrep orcl connection, and select Migration Repository > Associate Migration Repository.
• A window that tracks the progress will appear.
• Once the process is completed, click Close.
• Finally, confirm the process by clicking OK.

Step 3. Capturing the Microsoft SQL Server Exported Files

Here are the steps to transfer scripts from Microsoft SQL Server over to Oracle SQL Developer:

• Navigate to the Migration menu, select Third-Party Database Offline Capture, then click Load Database Capture Script Output.
• The Sqlserver2016.ocp file is located within the Capture directory and can be easily accessed.
• The items are currently being captured. Close the window once the capture process is complete.
• The tab titled Captured Models will show SQLServer2016. Click to expand SQLServer2016.
• Expand dbo to see all the captured objects included in the list.

Step 4. Checking Conversion Preferences

Make sure to examine the conversion settings at this point. To do this, adhere to the following procedure:

• To get to Settings, navigate to Tools.
• Once you've expanded Migration, choose Identifier Options.
• Make sure that On Mode for Quoted Identifier is selected. Press OK to confirm.

Step 5. Converting to the Oracle Model

To migrate the captured model into the Oracle schema, adhere to these guidelines:

• On the captured model labeled SQLServer2016, right-click and select the 'Convert to Oracle Model' option.
• Upon doing so, a 'Set Data Map' window will surface, showing the Source Data Type and its corresponding conversion in the Oracle Model. Press the 'Apply' button.
• Await the completion of the conversion process. Once completed, click on 'Close'.
• Navigate to the 'Converted Models' section and expand 'Converted:SQLServer2016'.
• To view the converted items, expand the 'dbo Northwind' section.

Step 6. Generating and Executing the Script to Create the Oracle Database Objects

Here's how to draft an SQL script with DDL statements to create objects in an Oracle Database:

• For the converted SQL Server 2016, prompt the Generate option from the menu.
• The creation of Oracle SQL is in progress. Click on “Close” upon completion.
• The SQL script is on the screen. Select system orcl from the drop-down menu next to the right icon.
• Select “Run Script” (F5).
• The results of the script operation are displayed.
• After successful execution of your scripts, you can initiate a connection for the dbo Northwind user. Simply right-click on Connection and opt for New Connection.
• In the Connection Name field, input dbo northwind-migrated orcl (or another descriptive name). In the Username and Password fields, enter dbo Northwind. Tick the Save Password box. In the Hostname field, enter hostname>, and orcl in the SID field. Click on Test.
• Check the connection status at the bottom left corner (above the Help button). Success should be indicated. Click on Connect to maintain the connection. Dismiss the window.
• Expand the connection to dbo Northwind-migrated orcl.
• Expand Tables.
• The Oracle database tables, post-conversion, are outlined here. Select the required table.
• Click on the Data tab. Note that the table is currently empty. Later in this guide, you will transfer the data.

Step 7. Checking Offline Data Move Preferences

In order to reference the correct format it is essential to set a date format mask for the Offline Data Move scripts, particularly the Oracle SQL*Loader control files. Follow the steps:

• To access Preferences, choose Tools.
• Select Data Move Options after expanding Migration.
• Check that the Date Mask format corresponds to the following:
  Mon dd yyyy HH:mi:ssAM
• Make sure the Timestamp Mask is the same as what is listed below.
  Mon dd yyyy HH:mi:ss:ff3AM.
• Confirm by clicking OK.

Step 8. Analysis and Estimation

Transitioning from Microsoft SQL Server to an Oracle database may not be as complex as one might anticipate. That being said, it is crucial to understand that a complete automatic migration of all objects and syntax is not always feasible and requires manual involvement.

The estimated time needed for manual tasks can be determined by analyzing the Capture Model and determining the quantity, kind, and complexity of objects.

You can use the reports below to estimate your tasks and projects.

• Migration Reports> Migration Summary
• Migration Reports> Migration Details
• Migration Reports> Automatic Name Changes

Time estimate for:

• Manually fix each object that couldn’t be converted.
• Verify and evaluate each item.
• Application modifications brought on by name changes

Step 9. Migrating the Data

For data import, follow the guidelines below:

• Run the commands at a DOS command prompt:

‹prompt› cd ‹location where files are›
‹prompt› oracle_ctl

You unzipped the files, and they are now in the Data directory. A bat file called oracle ctl contains instructions for loading the data. The data is loaded using sqlldr.

Step 10. Testing and Deployment

The complexity of the database to be transferred can significantly impact the testing phase of a migration project. Presently, Oracle does not provide automatic tools necessary for migrated database validation. Therefore, the preparation and incorporation of this stage into the project plan before the beginning of the migration is crucial.

Your project plan needs to include the following:

• Verify Database Structure
• Verify Database Security
• Verify Data
• Verify the Logical Correctness of Views
• Test Application

SQL Server to Oracle with Ispirer Toolkit

Ispirer Toolkit includes two tools for automated migration of both databases and applications: SQLWays Wizard for databases and NglFly Wizard for apps. Based on an intelligent core both tools handle migration of legacy programming languages and outdated databases with up to 100% automation. It has a free demo license that allows users to assess the quality of automated migration within 30 days.

Further we are going to go through the steps of migrating the database with SQLWays Wizard once the toolkit is already installed.

Step 1. Select the project directory.

For the toolkit that has already been successfully registered with a valid license, you can specify the project directory. In this directory SQLWays Wizard will generate a project file that will store information about the current migration process. Also on this page you need to specify the Export Directory. The tool puts all the generated files to this directory, such as sql files with converted SQL objects, txt files with data. Bat or sh files with commands are used to import all the files to the target database. To proceed, press “Next”.

Step 2. Choose a source database

Select or create a corresponding ODBC Data Source Name (DSN) that will allow to connect to the database intended for migration. You also need to specify the user and password for the connection, that will be tested after clicking the “Next >” button.

Step 3. Specifying a target database

On this page you are to specify the Oracle database, the server and the name of the database itself, as well as the user and password. Also here you need to specify a path to the Bin Directory. It is a folder that contains the target database native utilities.

Step 4. Selecting objects

Select all the objects that need to be converted by drag-and-drop from the left column to the right one. SQLWays Wizard automatically converts all the types of SQL objects, including tables, triggers, functions, views, stored procedures, sequences, etc.

Step 5. Specify conversion options

At this step, you can modify the conversion options of DDL and Data. You can specify different options that can be used to optimize the conversion process. In the objects tree, items (database objects) of different levels and types have different available options (e.g. mappings for data types and column names, formats, files, schemas and different types of database objects, etc.).

Step 6. Review the summary page

Review all the settings of SQLWays Wizard.

Step 7. Run the migration

SQLWays Wizard runs the process of migration in 2 stages: Conversion and Import. During the conversion phase, the tool extracts information about the objects chosen for this migration, converts them and generates the appropriate set of files. During the import phase, the tool tries to create all the converted objects and data in the target database.

Press “Start conversion” to run the conversion process. Information about the conversion process will be displayed dynamically in the “Conversion output” text field. Once the conversion is complete, you can run the import process to create the converted objects in the target database. To do this, click the “Start Import” button. If you want to view information about the import output, please switch to the “Import output” tab.

SQLWays Wizard has an option of starting import automatically. It can be useful for your conversion projects, if you see that it may take a long time due to the large number of objects and their complexity. To enable the option, click the checkbox “Start Import Automatically”.

Step 8. Migration results

On the last page of SQLWays Wizard, on the corresponding tabs you can view the Summary, Export and Import reports and statistics. After clicking the “Finish” button the migration configuration will be saved for future launches, and the wizard will be closed.

As you delve into the intricacies of the tool, you'll find that it is user-friendly, as its vast array of settings caters to both simple and complicated projects. This impressive balance of ease-of-use and adaptability makes the tool a robust solution for database migration tasks, turning a daunting task into a well-orchestrated process, and a complex procedure into a seamless, enjoyable experience.

Conclusion

This article explains the key features of MSSQL and Oracle databases, provides an overview of the most popular tools for migration from SQL Server to Oracle and ways to implement such a transition with the most popular solutions.

Ispirer Toolkit is a robust automated tool for managing database and application migrations. This reliable solution provides a number of benefits, for example:

• Streamlined migration. Do we need to mention that migrating using the tool is 2-3 times faster than doing it manually?
• Error-free migration. Automation eradicates the risk of human error, which makes the projects secure.
• Flexible transition. With its customizable features, the tool enhances the level of automation for migrating even the most complex source systems.

Try Ispirer Toolkit for free with a 30-days trial period and check the effectiveness of automated SQL Server to Oracle migration.