Every database professional knows Oracle as extremely powerful DBMS enriched with ultimate range of features and capabilities. At the same time, it has strict licensing policy and very high cost of ownership. Many advanced options like Database Encryption or Dynamic Data Masking are subject of extra fee. On the other hand, SQL Server provides similar options out of the box when using the Enterprise Edition. This is the most significant reason to migrate a database from Oracle to Microsoft SQL Server.
Database migration is hard task that can take much time and effort when doing manually. To simplify and automate this procedure one of migration tools can be used. Let us find out basic requirements for this kind of software products.
It is expected that Oracle to SQL Server migration tool support all recent versions of both database management systems and work with Unix, Linux and Windows Oracle. Support for Azure SQL, cloud variation of Microsoft SQL DBMS would be an extra advantage.
Other requirements for Oracle to SQL Server converter:
- Comprehensive conversion of table definitions, primary keys, indexes and constraints
- Support for all Oracle data types including LOBs, spatial, JSON and XML
- Option to migrateOracle views into MS SQL format
- Option to overwrite, merge or synchronize Oracle data with an existing SQL Server table
- Support for command line arguments
- Unicode support
- Intuitive user-friendly interface
One of such tools to migrate Oracle to SQL Server has been developed by Microsoft and is known as SQL Server Migration Assistant (SSMA). The algorithm of SSMA working cycle is:
- Connect to Oracle instance as data source
- Connect to SQL Server as data destination
- Convert Oracle database objects into T-SQL creation statements
- Run generated statements
- Migrate data
With all features and capabilities listed above, SSMA has some limitations that should be considered when planning Oracle to SQL Server migration:
- It does not support particular versions of MS SQL Express Edition
- It required some intermediate components like Oracle Client Provider or the OLEDB provider
- The tool creates service database ‘ssmatesterdb’ on the target server
Alternative tool is Oracle to SQL Server converter developed by Intelligent Converters, solution provider for database migration and synchronization since 2001. Besides all capabilities listed above It also provides some extra advantages:
- When the target SQL Server or Azure SQL does not allow remote connections, the converter can export Oracle tables into T-SQL script file containing data definition (DDL) statements to create tables with all indexes and constraints and to load the data. Later the person who is responsible for database migration can import that script file to SQL Server or Azure SQLvia the standard client tools.
- Some projects may require partial data migration or merging data from multiple tables into the one. For those cases Oracle to SQL Server converter provides option to convert result of user defined SELECT queries that were run onto the source database.
- Sometimes it may be required to change column name or type in the resulting table.For those purposesthe migration tool provides feature known as “custom column mapping”. It allows to change name, type and any other attributes for every column in SQL Server or Azure SQL table as well as exclude certain columns from migration.
Please visit the official vendor’s site to get more information about their Oracle to SQL Server converter.
