Loading...

中文 日本語
By: Dervish

Migrating data from SQL Server to Oracle Database is a top priority for businesses looking to scale, enhance security, and break free from ecosystem limitations. But with different business sizes, budgets, and technical capabilities, there’s no one-size-fits-all approach—so we’re covering both free and enterprise-grade methods to fit your needs.

First, a quick overview of the databases:

  • SQL Server: Microsoft’s RDBMS, praised for its user-friendly interface and tight integration with Microsoft tools (SSIS, SSAS, .NET). It’s perfect for small to mid-sized businesses leveraging Windows ecosystems but struggles with cross-platform scalability and mission-critical uptime demands.
  • Oracle Database: Oracle’s ORDBMS, a leader in enterprise data management. It offers unmatched scalability (supports 100+ hardware platforms), 24/7 high availability (via RAC), and advanced security features—making it ideal for large organizations, hybrid clouds, and mission-critical applications.

In this step-by-step guide, we’ll first walk you through essential pre-migration preparations to avoid common pitfalls (e.g., data loss, compatibility issues). Then, we’ll detail two proven migration methods: Oracle SQL Developer (free, for small-scale migrations) and i2Stream (enterprise-grade, for zero-downtime, large-scale transfers). Finally, we’ll compare the tools to help you choose the best path for your business.

How to Achieve SQL Server to Oracle Database Migration

Why Migrate from SQL Server to Oracle Database?

Now that you have a basic understanding of SQL Server and Oracle, let’s explore the core benefits that drive organizations to switch from SQL Server to Oracle:

  • Scalability & High Availability: Oracle Database offers advanced features like Real Application Clustering (RAC) and Automatic Storage Management (ASM), enabling seamless scaling to handle growing data volumes and 24/7 uptime—critical for mission-critical applications.
  • Cross-Platform Compatibility: Unlike SQL Server, which is tightly integrated with the Microsoft ecosystem, Oracle runs on over 100 hardware platforms and 20+ operating systems (Windows, Linux, macOS, Unix), providing greater flexibility for hybrid and multi-cloud environments.
  • Advanced Security & Compliance: Oracle includes built-in security features such as data encryption (at rest and in transit), fine-grained access control, and compliance certifications (GDPR, HIPAA, SOC 2), helping businesses meet strict regulatory requirements.
  • Cost Efficiency in the Long Run: While initial setup may require investment, Oracle’s scalability reduces the need for frequent infrastructure upgrades, and its unified data management capabilities eliminate silos, lowering long-term maintenance costs.

How to Migrate SQL Server to Oracle Database [2 Methods]

With thorough preparation complete, it’s time to choose a migration method that aligns with your business needs, budget, and technical capabilities. Below are two proven methods—one free and suitable for small to mid-sized businesses, and the other is specifically designed for large-scale migration between heterogeneous databases.

Method 1: Migration Using Oracle SQL Developer

Oracle SQL Developer is a free, integrated development environment (IDE) designed for Oracle Database management. It offers built-in migration tools to simplify SQL Server to Oracle transitions, making it ideal for small to mid-sized businesses with limited budgets. Below is the optimized step-by-step workflow:

Preparations

  • Environment & Tool Confirmation
    • Operating System: Windows Server 2016/2019/2022, Linux, macOS X (must be compatible with Oracle version)
    • Oracle Database Version: 11gR2, 12C, 18c, 19c, 20c
    • Tool Installation: Download Oracle SQL Developer 22.2.1 (launch sqldeveloper.exe on Windows, sqldeveloper.sh on Linux), and extract the mssqlservermigration.zip package to your working directory.
  • Pre-Migration Backup
    • SQL Server Backup: Open SSMS → Right-click the target database → Select Tasks > Back Up to create a full backup.
    • Oracle Backup: Use RMAN or Oracle SQL Developer, combined with i2Backup (supports automatic backups and ransomware protection) to create a backup, ensuring rollback capability.

Core Migration Operations

Step 1: Create MWREP User

  • Open Oracle SQL Developer → Go to View > Connectionsto bring up the Connections panel.
  • Right-click Connections→ Select New Connection to configure a connection for the system user:
    • Connection Name: system_orcl(customizable)
    • Username/Password: system/ [your system password] (check “Save Password”)
    • Hostname/Port/SID: localhost(or actual hostname) / 1521 / orcl
  • Click “Test” → Click “Connect” if the connection is successful.

Oracle SQL Developer-Connect

  • In the automatically opened SQL Worksheet, execute the following script (specify tablespaces for precise permissions):

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

Opened SQL Worksheet
  • Press F5 to execute the script. A message indicating “User MWREP created” confirms completion.

User Created

Step 2: Create Migration Repository

  • Right-click Connections→ Select New Connection to configure a connection for the MWREP user:
    • Connection Name: mwrep_orcl(customizable)
    • Username/Password: mwrep / mwrep(check “Save Password”)
    • Other configurations are the same as system_orcl(consistent hostname/port/SID).
  • Click “Connect” after the connection test is successful.
  • Right-click the mwrep_orcl connection → Select Migration Repository > Associate Migration Repository → Click “OK” after the setup is complete (automatically creates migration tables and PL/SQL packages).

Select Migration Repository

Step 3: Capture SQL Server Exported Files (Offline Mode, Suitable for Large Files)

  • First, export SQL Server data via SSMS (refer to the essence of the first article):
    • Open SSMS → Right-click the target database → Select Tasks > Export Data.
    • Data Source: Select “SQL Server Native Client”; Destination: Select “Flat File Destination”.
    • Check the tables/views to be exported → Complete the wizard to generate a flat file, ultimately obtaining ocp(xxxx is the version, e.g., 2005).
  • Load the file in Oracle SQL Developer:
    • Path: Migration > Third-Party Database Offline Capture > Load Database Capture Script Output.
    • Browse to the extracted mssqlservermigrationdirectory and select ocp.
    • Expand Captured Models > SQLServerxxxx > dboto confirm that tables, views, and other objects are captured.

Third-Party Database Offline Capture

Step 4: Configure Migration Preferences (Avoid Syntax/Identifier Conflicts)

  • Path: Tools > Preferences > Migration > Identifier Options.
  • Mandatorily check “Is Quoted Identifier On” (resolves differences in quote rules between SQL Server and Oracle).
  • Switch to the Migration > Capture/Convert/Generatetab, keep the default configurations (or fine-tune according to business needs), and click “OK”.

Step 5: Convert to Oracle-Compatible Model (Automatic Data Type Mapping)

  • Right-click the captured SQLServerxxxxunder Captured Models → Select Convert to Oracle Model.

Captured Models 

  • In the Set Data Map window, review the data type mapping (e.g., SQL Server’s int → Oracle’s NUMBER), and click “Apply” after confirmation.
  • Expand Converted Models> Converted:SQLServerxxxx > dbo Northwind to verify the converted tables, stored procedures, and other objects (mark complex objects for manual repair).

dbo Northwind

Step 6: Generate and Execute Oracle Database Object Scripts

  • Right-click Converted:SQLServerxxxx → Select Generate to automatically create DDL scripts (for creating tables, indexes, constraints, etc.).
  • In the script panel, select the system_orcl connection from the drop-down menu.
  • Press F5 to execute the script. After a successful execution, create a new business user connection:
    • Right-click Connections → Select New Connection; set the connection name to dbo Northwind-migrated orcl.
    • Username/Password: dbo Northwind / [corresponding password]; other configurations are the same as before.
    • After the connection test is successful, expand Tables to confirm that the table structure is created (no data yet).

Generate and Execute Oracle Database Object Scripts

Step 7: Configure Offline Data Movement Preferences

  • Path: Tools > Preferences > Migration > Data Move Options.
  • Configure the following fixed formats (essence of the third article to ensure date/time compatibility):
    • Date Mask: Mon dd yyyy HH:mi:ssAM (e.g., “Jan 01 2025 12:00:00AM”)
    • Timestamp Mask: Mon dd yyyy HH:mi:ss:ff3AM (accurate to milliseconds)
  • Click “OK” to save the configuration.

Configure Offline Data Movement Preferences

Step 8: Analysis and Estimation

  • Generate Migration Report: Go to Migration Reports → View Migration Summary (success/failure statistics), Migration Details (specific object status), and Automatic Name Changes (name change records) respectively.
  • Estimate Manual Tasks:
    • Repair complex objects that failed automatic conversion (e.g., nested stored procedures, custom functions).
    • Verify the impact of name changes on applications (e.g., field name truncation).
    • Reserve 1–2 working days (estimated based on 100 complex objects).

Step 9: Import Data

  • Open Command Prompt → Navigate to the data file directory: cd <mssqlservermigration extraction path>/Data.
  • Execute the batch file: oracle_ctl(internally uses Oracle’s sqlldr tool to load data).
  • Return to Oracle SQL Developer → Refresh the dbo Northwind-migrated orcl

Import Data

Step 10: Comprehensive Testing and Deployment

  • Structure Verification: Confirm that tables, indexes, foreign keys, views, and other objects are complete and consistent with the source database.
  • Data Verification: Sampling and comparing key fields (e.g., order amount, user ID) to ensure no loss or corruption.
  • Security Verification: Verify that the dbo Northwind user has normal permissions (read/write/execution) and that access control (e.g., IP whitelist) is functioning.
  • Application Testing: Launch business applications and test CRUD operations (e.g., order submission, data query) for compatibility with Oracle.
  • Performance Testing: Identify query delays and concurrency bottlenecks (refer to Article 1) and optimize Oracle parameters (e.g., memory allocation).
  • Deployment and Launch: After passing all tests, switch business traffic to the new Oracle database and monitor stability for 24 hours.

Method 2: Migrate SQL Server to Oracle with i2Stream [Hot]

Oracle SQL Developer is suitable for small-scale, offline migrations with simple structures. It is free but has limited functionality.

If you need a smooth and seamless migration from SQL Server to Oracle, I recommend trying i2Stream from information2 Software.

Designed for enterprise-level production environments, it addresses core challenges such as zero downtime, real-time processing, heterogeneous compatibility, and high-volume/low-bandwidth scenarios. It provides end-to-end migration and continuous data protection capabilities, making it ideal for scenarios demanding high business continuity and data consistency.

FREE Trial for 60-Day
  • Seamless Heterogeneous Compatibility

Covers full-stack cross-platform deployment (Windows/Linux, x86/ARM) and supports all versions of SQL Server & Oracle (10g-19c, including CDB/PDB). Enables hybrid cloud bidirectional sync, eliminating OS/database version barriers.

  • Non-Disruptive Migration & Fast Sync

Combines full initial sync + real-time incremental replication—no downtime, table locking, or business interruption. Multi-threaded concurrency and 5+ levels of data compression cut through large datasets and bandwidth constraints.

  • Complete Data Integrity

Syncs DDL/DML operations (table creation, column modification, data CRUD) without limitations. Built-in real-time data consistency checks prevent loss or discrepancies between SQL Server and Oracle.

  • Intuitive Monitoring & Alerts

Web-based dashboard tracks sync progress, latency, CPU/memory usage, and network traffic. Customizable second-level delay alerts and multi-channel notifications (email/SMS) ensure migration visibility.

  • Lightweight & Flexible Deployment

Agent-free setup with low resource consumption on the SQL Server source. Supports multi-task concurrency on a single node, adapting to on-premises, cloud, or cross-region migration scenarios.

You can watch this demo to learn the detailed steps for migrating databases with i2Stream. If you’d like more information, please contact us.

Oracle SQL Developer vs. i2Stream: Which Should You Choose?

Having detailed both migration methods, it’s important to compare them to determine which aligns best with your business scenario. The table below summarizes the core differences:

Factor

Oracle SQL Developer

i2Stream

Best For

Small/mid-sized businesses, simple migrations, limited budgets

Enterprises, large-scale migrations, minimal downtime, mission-critical data

Automation

Partial (manual scripting for complex objects)

Full (zero-code, end-to-end automation)

Downtime

Moderate (requires planned downtime)

Minimal (CDC support for real-time sync)

Support

Community forums, limited Oracle support

24/7 enterprise support from Information2 Software

Complexity

Steeper learning curve (requires Oracle/SQL expertise)

User-friendly (intuitive UI, no coding required)

Conclusion

Choosing the right migration method is the key to export data from SQL Server to Oracle Database. For small to mid-sized businesses with limited budgets, Oracle SQL Developer offers a free, reliable solution—provided you have the technical expertise to handle manual adjustments. For enterprises prioritizing minimal downtime, automation, and enterprise support, Information2 Software is the optimal choice, delivering end-to-end migration with zero code and 100% data integrity.

Ready to start your migration? Request a free trial of i2Stream to experience automated SQL Server to Oracle migration, or download Oracle SQL Developer to begin with the free method. Whichever tool you choose, following the pre-migration checklist and post-migration testing steps will ensure a smooth and successful transition.

{{ author_info.name }}
{{author_info.introduction || "No brief introduction for now"}}

More Related Articles

{{item.title}}
{{item.excerpt}}
Read Article
Table of Contents:
Stay Updated on Latest Tips
Subscribe to our newsletter for the latest insights, news, exclusive content. You can unsubscribe at any time.
Subscribe
Ready to Enhance Business Data Security?
Start a 60-day free trial or view demo to see how Info2Soft protects enterprise data.
Free Trial Contact Sales
Table of Contents:
{{ country.name }}
Please fill out the form and submit it, our customer service representative will contact you soon.
By submitting this form, I confirm that I have read and agree to the Privacy Notice.
{{ isSubmitting ? 'Submitting...' : 'Submit' }}