Site icon Information2 | Data Management & Recovery Pioneer

[4 Methods] How to Migrate Oracle to SQL Server Step-by-Step

Dylan

Table of Content

Why Migrate from Oracle to SQL Server?

Oracle and SQL Server are both prominent database management systems. Sometimes, organizations want to migrate from Oracle to SQL Server for many purposes.

Here are the most common reasons:

No matter what reason you want to convert Oracle to SQL Server, refer to this article to know more information and detailed steps to implement the conversion.

Oracle to SQL Server Migration Challenges

You can check the technical differences between Oracle and SQL Server databases.

Features Oracle SQL Server
Procedural Language PL/SQL T-SQL
Data Types NUMBER, VARCHAR2, CLOB, etc. INT, NVARCHAR, TEXT, etc.
Schema/User Model Each user has a schema Schemas exist within a database
Transaction Control Implicit commits in DDL Explicit commits required
Sequences Separate objects Built-in IDENTITY columns

Next part, we will give 4 effective ways to transfer Oracle data to SQL Server. 

Method 1. Migrate Oracle Database to SQL Server using SSMA

SQL Server Migration Assistant or SSMA is a free Microsoft tool that automates another database platform to SQL Server. It will not only transfer the data but also convert Oracle database schema like tables, views, procedures, etc. to SQL Server.

Before getting started, please check the requirements and compatibility::✎…
Supported OS: Windows 10 / 11 (64-bit) or Windows Server 2016/2019/2022
Architecture: 64-bit only (x64)
Oracle Source Compatibility: Oracle 9i(legacy only), Oracle 10g, Oracle 11g, Oracle 12c(12.1, 12.2), Oracle 18c, Oracle 19c, Oracle 21c.
SQL Server Target Compatibility: SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019, SQL Server 2022.

Step 1. Install and Configure SSMA

1. Download and install SSMA for Oracle from Microsoft’s site.

2. Open the msi file. Click “Run” if you get a security warning.

3. Install the SSMA client and SSMA Extension Pack on the QL Server machine. Then follow the installer prompts to finish the installation.

Step 2. Create a New SSMA Project

1. Launch SSMA for Oracle, click “file” > “New Project”.

2. Enter

Step 3: Connect to the Oracle Database

In Oracle Metadata Explorer, click “Connect to Oracle” on the top menu.

2. Provide the following information and click “Connect”:

    3. Now the SSMA connects to Oracle and lists all schemas available. You can select the one you want to migrate and click “OK”.

    Then SSMA will load the Oracle schema and object. Go to next step to connect to a SQL Server.

    Step 4: Connect to SQL Server Target

    1. In SQL Server Metadata Explorer page, click “Connect to SQL Server” on the top menu.

    2. In the pop-up window, provide

      3. Click “Connect” button

      Step 5. Get a Report

      1. In the Oracle Metadata Explorer, choose “Create Report” > “Assessment Report”.

      2. Then you will see: Conversion success rate, list of incompatible objects, Estimated manual work required.

      3. Review the report carefully, you may need to fix some issues manually if it notes.

      Step 6. Convert Oracle Schema to SQL Server

      1. In the Oracle Metadata Explorer, right-click the schema you want and choose “Convert Schema”.

      2. Then SSMA will translates Oracle data types to SQL Server equivalents, converts PL/SQL code to T-SQL, and it will display conversion issues in the output window.

      3. Review converted schema in the SQL Server Metadata Explorer pane.

      Step 7. Synchronize Schema to SQL Server

      1. On the SQL Server Metadatabase Explorer, right-click your target SQL Server database > “Synchronize with Database”.

      2. SSMA executes CREATE TABLE, CREATE VIEW, and other DDL scripts to build the schema on the SQL Server.

      3. Confirm that the new tables and objects appear in SQL Server Management Studio (SSMS).

      Step 8. Migrate Data

      1. In Oracle Metadata Explorer, Right-click the “tables” or “entire schema” and choose “Migrate Data”.

      2. SSMA will extract data from Oracle and insert it into SQL Server using efficient build load. Then wait for the process to get finished.

      Step 9. Validate Migration

      Once data migration completes:

      1. Compare row accounts between Oracle and SQL Server:

      — SQL Server

      SELECT COUNT(*) FROM dbo.Customers;

      — Oracle

      SELECT COUNT(*) FROM Customers;

      2. Validate using SQL Server Management Studio:

      Primary/foreign keys.

      Null and default values.

      Indexes and constraints.

      Sample data and reports.

      3. Run test queries or application scripts to verify business logic.

      Method 2. Convert Oracle to SQL using Customer Scripts (for small databases)

      For small databases, developers can convert Oracle to SQL manually using T-SQL scripts and SQL Server Integration Services (SSIS). However, this approach requires deep expertise and rigorous testing.

      When to Use Custom Scripts:
      Your Oracle database is small (<10GB)
      You have specific customization needs
      You want full control over data transformation.
      You’re migrating only selected tables or modules, not the entire database.

      Step 1: Assess and Export Your Oracle Schema

      Before writing any conversion script, start by analyzing your Oracle database structure:

      1. List all database objects — tables, indexes, views, stored procedures, triggers, and sequences.

      2. Export the schema using Oracle SQL Developer or expdp (Data Pump export).

      3. Generate DDL (Data Definition Language) scripts from Oracle:

      SELECT dbms_metadata.get_ddl(‘TABLE’, table_name) FROM user_tables;

      4. Save these DDL scripts — you’ll use them as a reference for recreating objects in SQL Server.

      Step 2: Map Oracle Data Types to SQL Server Types

      Oracle and SQL Server use different data types. Manual conversion requires explicit mapping to ensure data accuracy.

      Oracle Data Type

      SQL Server Equivalent

      VARCHAR2(n)

      VARCHAR(n)

      NVARCHAR2(n)

      NVARCHAR(n)

      NUMBER

      INT, BIGINT, or DECIMAL

      DATE

      DATETIME or DATE

      CLOB

      TEXT or VARCHAR(MAX)

      BLOB

      VARBINARY(MAX)

      When writing custom scripts, define each column explicitly to match SQL Server’s syntax.

      Step 3: Create SQL Server Schema Manually

      Once mappings are ready, create a new database and schema in SQL Server:

      CREATE DATABASE CompanyDB;

      GO

      USE CompanyDB;

      GO

      CREATE SCHEMA hr;

      GO

      Then, recreate tables, indexes, and constraints using your converted scripts. This ensures clean control over structure and indexing strategy during the Oracle to SQL conversion.

      Step 4: Export Data from Oracle

      Export your Oracle table data into flat files (CSV or TSV) for easier loading:

      Using SQL*Plus or SQL Developer:

      SPOOL employees.csv

      SELECT * FROM employees;

      SPOOL OFF;

      Alternatively, use expdp for data-only exports. Keep in mind to handle nulls, date formats, and special characters properly to prevent issues during import.

      Step 5: Import Data into SQL Server

      Use SQL Server Management Studio (SSMS) or bcp (Bulk Copy Program) to load data into your SQL Server tables.

      Example using bcp:

      bcp CompanyDB.dbo.employees in “C:\data\employees.csv” -c -t, -S localhost -U sa -P yourpassword

      Or, use SQL Server Integration Services (SSIS) to automate multiple table imports in batches.

      Step 6: Convert and Rewrite PL/SQL Code

      Oracle’s procedural code (PL/SQL) differs from SQL Server’s T-SQL. You’ll need to rewrite triggers, stored procedures, and functions manually.

      Step 7: Validate and Test the Migration

      After data import and code conversion:

      1. Verify record counts between Oracle and SQL Server.

      2. Check integrity constraints and relationships.

      3. Run application-level tests to confirm functionality.

      4. Use SQL Server’s Query Store or Profiler to identify performance issues.

      Step 8: Optimize and Finalize

      Once data and schema are verified:

      Method 3. Best Way to Migrate Data from Oracle to SQL Server

      For organizations looking for a faster, more automated, and enterprise-grade solution to migrate from Oracle to SQL Server, Info2Soft’s i2Migration offers a powerful, all-in-one platform designed specifically for database migration and synchronization.

      i2Migration is Info2Soft’s next-generation database migration and conversion tool that supports seamless transitions between major database platforms, including Oracle, SQL Server, MySQL, PostgreSQL, and more. It automates complex conversion tasks, reduces manual scripting, and minimizes downtime, making it a trusted Oracle to SQL Server converter for businesses of all sizes.

      Key Features of Info2Soft’s i2Migration:

      FREE Trial for 60-Day
      Secure Download

      Method 4. Continuously Replicate Oracle to SQL Server

      For businesses that need real-time data synchronization instead of a one-time migration, Info2Soft’s i2Stream offers a powerful and flexible solution. Rather than performing a traditional Oracle to SQL migration once, i2Stream enables continuous replication, keeping your Oracle and SQL Server databases fully synchronized with minimal latency.

      Key features of i2Stream for Oracle to SQL Replication

      FREE Trial for 60-Day
      Secure Download

      Conlusion

      Migrating from Oracle to SQL Server is a strategic move that helps organizations reduce costs, modernize infrastructure, and integrate seamlessly with Microsoft technologies. However, the process requires careful planning and the right tools to address differences in data types, schema structures, and procedural code.

      This article explored the main challenges of Oracle to SQL Server migration and outlined several proven methods to complete the transition successfully.

       

      Exit mobile version