How to Migrate Oracle Database to SQL Server in 2 Ways?

Oracle Database and Microsoft SQL Server are two leading databases that arrest the attention of global users and are ranked top 4 in the world’s most popular databases list.

Oracle Database is highly compatible with various platforms and applications and is supported by mainstream software and hardware vendors, the community, and Oracle. The database also provides strong security and privacy functions like authentication and data encryption.

But every rose has its thorns. The users of Oracle Database face problems such as extensive SQL knowledge, expensive licenses, or high hardware requirements.

Unlike Oracle Database, MS SQL Server is easier to install and configure, and updates automatically. Table partition, database encryption, or dynamic masking that charge users additionally in Oracle, are coming out of the box in SQL Server Enterprise Edition.

Therefore, some users turn from Oracle to SQL Server instead. Generally, there are two official Oracle to SQL Server converters: SQL Server Migration Assistant (SSMA) and SQL Server Integration Services (SSIS). 

Way 1: Migrate Oracle Database to SQL Server with SSMA

Prerequisites:

• Download and install SQL Server, Oracle Client, and SSMA on the target server.

• Connection to source and target databases.

• Connection Oracle permission for SSMA:

  Connect to the Oracle Database: CONNECT and SELECT ANY DICTIONARY

  Load all objects in the schema: same as above.

  Discover all referenced procedures: SELECT ANY TABLE, SELECT ANY SEQUENCE, CREATE ANY PROCEDURE, CREATE ANY TRIGGER, and CREATE ANY TYPE.

  Additional permissions for SSMA features.

Compatibility:

With the latest SSMA version 9.2.

• Supported OS: Windows Server 2008 R2, 2012 R2, 2012, 2016, Windows 7, 8, 8.1, 10

• Supported targets: SQL Server 2012, 2014, 2016, 2017, and 2019 on Windows and Linux; Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

• Requirements for Oracle Client:

  Microsoft Windows Installer 3.1 or above.

  Microsoft .NET Framework 4.7.2 or later.

  Oracle Client 9.0 or above, the same or higher version than the Oracle database version.

  4 GB RAM.

Pre-immigration:

Download and install Oracle Client and SSMA

Oracle Client

1. Download the latest Oracle Client version.

2. Click and Run setup.exe from the downloaded files.

3. Choose the Instant Client installation type in the Oracle Universal Installer window.

4. Specify the installation location for the Oracle Client, then you will see the summary, click Install.

SSMA

1. Open the msi file based on x86/64 bits in the downloaded SSMA folder.

2. Click Run anyway if you receive a Security Warning.

3. Click Next in the SSMA for the Oracle Setup window.

4. Check I accept the agreement and click Next.

5. Choose the setup type, then click Install.

Identify current data sources and feature details

1. Open the Microsoft Assessment and Planning (MAP) Toolkit.

2. On the Overview page, choose Create/Select database. Under this option, choose to Create an inventory database, then provide a name and description for the database, and click OK.

3. Choose Collect inventory data to open the wizard. Choose Oracle in the wizard and click Next.

4. Choose the computer search option, and click Next.

5. Input the current credentials or create new ones for the system exploration, and click Next. Then set the order for the credentials. Click Next.

6. Enter credentials for each computer you want to find. For each computer or device, you can use different credentials, or you can choose it from the Computers list.

7. Check all your choices and click Finish.

8. See the Data Collection summary report after it finishes, then Close. Choose Options to generate a report.

Connect to the source Oracle instance

1. Start SSMA, and navigate to File> New Project.

2. Enter the Name and Location for the new project, and choose a target SQL Server from the dropdown list. Click OK.

3. Choose Connect to Oracle, fill in the connection info, and click Connect.

4. On the Filter objects window, choose the source Oracle schemas and click OK.

5. On the Oracle Metadata Explorer page, choose the Oracle schemas you’re running, choose to Create Report to produce an HTML report, or you can choose to Create Report at the top right.

6. View the report or open it in Excel to get the inventory of Oracle objects and details of schema conversions. The report's default placement is the report folder inside of SSMAProjects.

Validate data types

1. Choose Tools> Project Settings> Type Mapping.

2. On the Oracle Metadata Explorer pane, choose the table name to change the type mapping for each table.

Convert schema

1. Right-click the node and choose to add a statement for dynamic or specialized queries conversion optionally.

2. Click the Connect to SQL Server tab, then input the connection info and click Connect.

3. On the Oracle Metadata Explorer page, click the schema that you’re operating, choose Convert Schema, or choose Convert Shema at the top right.

4. Compare the converted objects to the original ones for identifying and solving problems.

5. Choose Review results and see errors on the Error list page.

6. Save the project locally by choosing File > Save Project for an offline exercise in schema remediation.

Migrate Oracle Database to SQL Server

1. On the SQL Server Metadata Explorer page, right-click the database and choose to Synchronize with Database to publish the Oracle schema to SQL Server.

2. See the mapping of the source and target projects.

3. On the Oracle Metadata Explorer page, right-click the source schema/object and choose Migrate Data or choose Migrate Data at the top right. Check the database name for the migration of the whole database, and expand the Tables under the database to choose individual tables.

4. On the Migrate Data page, input the connection details of both databases and see the Data Migration Report after the process.

5. Use SQL Server Management Studio to connect SQL Server instance for validation.

Way 2: Migrate Oracle Database to SQL Server with SSIS

1. Create a new data flow job and include a new Oracle source and SQL Server ADO.NET destination.

2. Create a new connection manager.

   In the Connection Manager window, right-click for New Connection> then in the type menu, choose OracleOCI> configure connection details;

   Update the PATH variable with native DLLs included. Enter the details of the Port, User, Password, and Service Name for the Oracle database.

3. Set up the Oracle source.

   Double-click the Oracle source, in the Connection Manager menu, and choose the previous one.

   Specify the data extraction query; 

   SELECT CompanyName, City FROM Customers WHERE Country = xx

   Close Oracle Source and connect it to ADO.NET Destination.

4. Set up SQL Server destination.

   Open ADD.NET Destination to add a New Connection, then input the details of the server and database;

   In the Data access mode pane, choose table or view;

   In the Table or View pane, choose the table to populate.

   On the Mappings page, set up any desired properties.

5. Run the project. Oracle data is now in the SQL Server instance after the task.

Centralized Management for Multi-Database

Some database users of Oracle, SQL Server, or both of them may look for a compatible data solution to manage them efficiently since the importance of backup cannot be more emphasized.

Vinchin Backup & Recovery backs up, restores, and intensively manages backups of multiple databases including Oracle Database, MySQL, SQL Server, PostgreSQL, Postgres Pro, and MariaDB, 10+ virtual platforms, NAS, and physical servers with a single web-based console.

With the backup solution, you can:

Automate database backup: make backup easier under preset schedules and job status email notifications.

Choose from various strategies: full, incremental, differential, and archive log backup types are listed (vary with databases) for selection. Reduces database size to save storage, while enabling retention policy to keep data per day/restore point for automatic data management.

Restore database: recover database backups to the original or a new one for data retrieval from backups or an offsite backup copy.

Anti-ransomware protection: protect all data backups and Vinchin backup servers against malware attacks with backup storage protection, data encryption, and an offsite backup copy.

Care for the solution? Here’s a 60-day free trial of the Enterprise version below.

Sum up

Oracle and SQL Server databases have their own advantages and disadvantages, users can choose one or move to another according to business needs and IT infrastructure. Microsoft launches two Oracle to SQL Server migration tools: SQL Server Migration Assistant (SSMA) and SQL Server Integration Services (SSIS).

I introduced every step of how to convert Oracle to SQL Server (see how to migrate SQL Server to Oracle) and hope this blog will help you. Let's also back up these databases and manage them together for sampled administration with Vinchin Backup & Recovery.