How to Migrate Oracle Database to SQL Server?

Alex Kirpichny

Alexandr Kirpichny

Chief Product Officer, Ispirer Systems

Sooner or later, company owners face the need to migrate databases. And the choice of a new RDBMS most often falls on the most common - Oracle and Microsoft SQL Server. According to statista, both of these databases are among the top 5 most popular RDBMS in the world. Why is that?

Company executives choose Oracle and SQL Server not just because they are long-standing players with a significant market share, but due to many advantages that made them desirable in the first place. Among the main reasons for choosing these databases are performance, compatibility with external environments, availability of in-house expertise, and future development plans. But no matter how cool the database is, circumstances often put companies in a position when switching to a new solution is required.

Why Migrate Oracle to SQL Server?

As a matter of fact, Oracle is a great platform. However, it involves some significant cons. Oracle customers are often subject to restrictive licensing practices and once business is running on an Oracle database, it can be extremely difficult to get off it. Besides, many organizations struggle with updating or modernizing their data implementation. Many companies use Oracle databases in their IT technology stack and are still using the on-premise database version. However, as data grows exponentially, their data environments are struggling to cope with the growing load - which requires more processing power and increased storage, which in turn increases the cost of data storage and complicates the data management task.

Benefits of SQL Server

Microsoft SQL Server brings to the table a number of benefits, which make this platform a popular choice for customers.

SQL Server tools and applications

Microsoft owns a large number of tools and solutions that greatly simplify numerous processes, including development, problem solving, software support, etc. Among them:

  • SQL Server Management Studio
  • Azure Data Studio
  • SQL Server Data Tools
  • SQL Server Profiler

For instance, companies that have a data warehouse running on the Microsoft SQL Server platform can easily integrate it with Analysis Services and Power BI without having to buy any third-party software. Everything is available on the Microsoft Data platform.

Multiple SQL Server editions

MS SQL Server comes in several editions to cater to the needs of corporate enterprises and remote users. Different editions vary in features and price range. Therefore, organizations can choose the version suitable for their operational needs. The editions include:

  • Enterprise. This edition is usually for larger enterprises with greater data storage requirements. It provides data warehousing and web-enabled databases. Enterprise-grade SQL server offers the essential features an organization expects.
  • Standard. Standard SQL Server edition is best for small- and medium-scale businesses. Moreover, it can be used for branch offices and small web servers as a back-end database. The standard version has no user limits.
  • Express. Express SQL server edition is free of cost, has limited user capacity, and includes fewer features compared to Standard and Enterprise SQL Server editions.
  • Developer. The developer SQL server edition works and functions exactly like an enterprise SQL edition. The only difference is that the license is used for testing and development purposes. This edition is generally used by developers to build and test applications on top of the SQL server.

Easy Installation

Microsoft SQL is easy to use and can be installed via setup wizard. Unlike other database servers requiring extensive command-line configurations, SQL Server offers a user-friendly installation interface. Besides the one-click installation process, it comes with a readable GUI along with lots of instructions. The prerequisite updates are downloaded by the installation wizard automatically, which reduces manual workloads. Automatic updates not only reduce the maintenance cost but also help to ensure that you use the latest version of your database. Analytical and database services can be installed separately later.

Data recovery options

Data security is always a critical point when choosing a database. Fortunately, Microsoft SQL Server has a number of features that allow you to recover data. In fact, Microsoft may not have implemented the function of restoring individual tables yet, but you can always recover the database completely. You can rely on Microsoft to protect your data as it offers using log files, caching, and backups.

Flawless Security

Because of highly secure and sophisticated encryption algorithms (for instance, DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, and others) that are used in the SQL Server database, it is almost impossible to break the security layers of the database. To prevent the risk of attacks, SQL Server offers additional security features, which makes it an invincible wall to keep your data safe.

Thorough documentation and Microsoft Premier support

Microsoft offers detailed documentation for its products and a large community of people who are ready to help on any issue. It provides you with access to a network of experts from support teams. You can rest assured that there will always be help to resolve any issue.

Oracle to SQL Server migration challenges

Before making a decision to migrate Oracle Database to SQL Server it is necessary to understand all the issues you may face during the transition. The main migration challenges are the following:

1. Packages

Oracle allows you to bundle procedures, but this cannot be done in MS SQL Server. A particular difficulty lies in the fact that packages in an Oracle database can contain batch variables that are available in any package object and retain their values during the session. Alternatively, in order to convert Oracle to SQL Server, you can use a temporary table with as many columns and types as the batch variables need.

2. Functions

The use of functions in both databases has a number of limitations. However, target SQL Server has more of them, and therefore, some of the functions should be converted into procedures, which leads to the need to change all their calls or refactor the code.

3. ROWID

In the source Oracle database, all tables have a ROWID pseudo-column, which contains the "address" of each row by which it can be identified. MSSQL Server does not have a direct equivalent for such unique identifiers of table rows. Therefore, it will be necessary to resolve all cases when positioning on this feature is used.

4. String operations

In Oracle, null and empty quotes are considered to be synonyms for an empty string. This is not the case for MS SQL Server. Accordingly, it is necessary to supplement all checks for null with a check for empty quotes. Also, when connecting strings (concatenation), the behavior in various DBMS differs.

Here is how the conversion of a procedure that contains a check for null and a string connection can look like:

OracleSQL Server
  1.  
  2.   CREATE OR REPLACE PROCEDURE empty_str (in_str varchar2) 
  3.  IS 
  4.    out_str varchar2(100);
  5.  BEGIN 
  6.    if in_str is null then
  7.       DBMS_OUTPUT.PUT_LINE('in_str is null');
  8.    end if;
  9.  
  10.    out_str := 'Hi, '||in_str;
  11.    DBMS_OUTPUT.PUT_LINE('out_str = '||out_str);
  12.  END;
  13.  
  1.  
  2. 	  CREATE OR ALTER PROCEDURE empty_str @IN_STR VARCHAR(MAX)
  3.  AS
  4.  BEGIN
  5.     DECLARE @OUT_STR VARCHAR(100)
  6.     if (@IN_STR is null OR  @IN_STR = '')
  7.        PRINT 'in_str is null'
  8.  
  9.     SET @OUT_STR = CONCAT('Hi, ',@IN_STR)
  10.     PRINT CONCAT('out_str = ',@OUT_STR)
  11.  END
  12.  

5. Differences in variable types

Not all Oracle types have a definite match in SQL Server. This applies both to the presence of types (for instance, "type is table of") and the features of their internal storage and use. For instance, fractions of seconds in time in Oracle can be stored with an accuracy of up to 9 decimal places, and in MS SQL Server only up to 7, etc. The solution to this complicated task requires special skills and expertise.

6. Hierarchy

In Oracle there are many specific constructions for organizing hierarchical queries that are absent in MS SQL Server, which complicates queries conversion a lot. In most cases, they can be rewritten using CTE (Common Table Expression), but it looks more cumbersome. Some tools can perform such a conversion automatically, which is suitable for most use cases.

7. System information

There are also differences in the storage of meta information about the structure of tables and other objects, users, rights, connections, event logs, etc. As a rule, everything related to the migration of system objects is difficult to automate and requires manual adjustments.

As a matter of fact, in order to migrate an Oracle database to SQL Server you should meet a number of challenges. To ensure a smooth process with no pitfalls we recommend using automated conversion tools to effectively resolve potential issues and minimize the impact of the human factor.

Oracle to SQL Server Migration Tools

SQL Server Migration Assistant for Oracle (SSMA)

SQL Server Migration Assistant for Oracle or SSMA is a free tool to simplify the database conversion process to Microsoft SQL Server. Currently the tool supports migration from DB2, MySQL, Oracle, SAP ASE and Microsoft Access. SSMA automates assessment analysis, schema conversion, SQL statement conversion, data migration and testing. Though SSMA has a number of pros, which include:

  • SQL code comparison between the original database and the target one.
  • Once the conversion is complete, it generates a detailed report to identify possible errors.
  • Transparent process, allowing users to track the migration status.

However there are significant cons of SSMA:

  • Requires knowledge of Python, or C#.
  • Able to convert only plain SQL code with no intricacies.
  • Lacks additional toolsets and visualization features compared to premium products.

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS) is a component of SQL Server database that can be used for various migration tasks. SSIS is a fast and flexible data warehousing tool used for data extraction, loading and transformation like cleaning, aggregating, merging data, etc. It simplifies the data moving process from one database to another. SSIS can extract data from a wide range of databases like SQL Server databases, Oracle and DB2, etc.

Some advantages of SSIS include:

  • Ease and speed of implementation
  • Offers real-time, message-based capabilities
  • Easy integration with SQL Server and Visual Studio

The disadvantages are as follows:

  • Supports data conversion only, cannot convert SQL code
  • SSIS lacks support for alternative data integration styles
  • Poor integration with other products

Completely automated migration with Ispirer Toolkit

Ispirer Toolkit is a solution that converts both databases and applications automatically. As the topic of this article is migration of Oracle to SQL Server, we will focus mainly on the process of migrating databases.

SQLWays Wizard, which is a part of Ispirer Toolkit, is designed for heterogeneous database migration. It automatically migrates data, as well as the entire Oracle schema, including tables, indexes, stored procedures, functions, triggers, views, etc.

In order to migrate an Oracle database to SQL Server it is necessary to follow three phases: configuration, conversion and import. The configuration stage includes enabling the necessary settings to ensure the highest possible level of automation. A set of settings is determined in accordance with the specifics of your Oracle database. The toolkit contains a ton of them, including schema settings, data types mapping, ROWID_FORMAT, HINT, CASE_SENSITIVE_COLLATION and many others. During the Conversion stage the tool connects to the source Oracle database using an ODBC connection, extracts all the required information about the objects that were specified for the Conversion process, and converts them. During the Import stage SQLWays Wizard connects to the target database using the target database client and its command-line native utilities (sqlcmd.exe for SQL Server). Using these native utilities, it loads all the converted SQL code and extracted data into the target database.

It is worth noting that there are code structures that are hard to automate. In such a case, customers get the opportunity to customize the SQLWays Wizard in accordance with the source Oracle database. This approach makes the tool as flexible as possible even for databases complicated by a large number of non-amendable code structures. It increases the automation level and, accordingly, greatly reduces migration time.

Ispirer Toolkit stands out from the competition in a number of benefits:

1. Speed. Due to the smart migration core of the solution, automation streamlines the migration from Oracle as a source and saves your time for more important business tasks.

2. Reliability. Ispirer Toolkit is an automated solution which means it eradicates the risk of human error and provides an error-free modernization process.

3. Security. What is more, the toolkit works with read-only privileges, so you don’t need to worry about data security. Flexibility. In terms of flexibility, Ispirer Toolkit is a perfect solution. Customizing the tool according to your database is the best decision for high quality automated Oracle migration.

4. Efficiency. The toolkit is a comprehensive solution as it migrates huge and complex databases and applications.

Conclusion

In our article, we reviewed the main advantages of the SQL Server database, the complexity of migration from the Oracle database and compiled a list of the most popular tools for migration.

The highlighted tools above are some of the best and most popular migration tools to help organizations implement cross-platform database migration from the Oracle database to SQL Server. While every tool has its unique advantages and features, it’s advisable to leverage Ispirer Toolkit. It’s not only a reliable tool but also a highly automated solution which helps to save a great deal of time and effort. Moreover, with its 30-days free trial period you can try out the effectiveness of automated Oracle to SQL Server migration.

Discover more about Ispirer products and solutions!

Find Out More