Microsoft SQL Server to Oracle Online Presentation


Demo presentation text:

Hello,

This is the demo about the tools provided by Ispirer Systems for database or scripts migrations. All of them are graphical tools based on a command line tool called Ispirer Migration and Modernization Engine. This demo is particularly devoted to Microsoft SQL Server to Oracle database conversion.

For this conversion we will use the following tools:

• Ispirer Migration and Modernization Engine – a command line tool that can be run in batch mode and can perform the automatic conversion of a database or database and application code provided in scripts.

• Ispirer SQLWays Wizard – a GUI tool that is designed to simplify the database conversion process tuning for Ispirer Migration and Modernization Engine. Works with a source database using the ODBC connection.

• Ispirer Migration Studio – a GUI tool that is designed to simplify the database or application scripts conversion process tuning for Ispirer Migration and Modernization Engine. Works with one script at a time.

• Ispirer Migration Commander – a GUI tool that is designed to simplify the database or application scripts conversion tuning for Ispirer Migration and Modernization Engine, Works with several scripts at a time.

For this demo, we have prepared a database in Microsoft SQL Server. You can see its contents open in Microsoft SQL Server Management Studio. For the migration purposes we have prepared an empty database on Oracle server called MSSQL2ORA.

Let’s convert this database using Ispirer MnMTK 2015.

For this purpose we have Ispirer SQLWays Wizard which you can see on your screen. This tool works with a source database using the ODBC connection. And for a target Oracle database it creates commands for native tools. When you start Ispirer SQLWays Wizard you see Ispirer SQLWays Wizard Welcome page. Here you need to specify a license that you have for this product and a project directory where a set of files required for project information like data type mappings, identifier mappings, objects selected for the conversion, options that you selected during the tuning up of the migration process is stored.

Well, I’ve got it selected here, we can click next. On «Choose a source database» page you need to specify a user or system DSN name for ODBC connection for Microsoft SQL Server database, a user name and a password. Also I’d like to mention that it is recommended to choose a user that has appropriate rights to work with system tables in Microsoft SQL Server because a lot of additional information is extracted from Microsoft SQL Server for a conversion of business logic from Microsoft SQL Server to Oracle.

On «Choose a target database» page, you need to specify Oracle as a target from the list of the supported databases.

To make an import you need to specify:

Port

Service name

The user name and the password that you are going to use to load the objects to Oracle.

On «Specify database objects or query» page you can see all the objects of the source database specified in a tree view: each table is specified in its own schema and in its own object type in the source tree view for Microsoft SQL Server.

In the target pane you can see the tree view of the selected objects. As I’m using the project directory I’ve got all the objects selected here so we can click next.

On «Specify DDL and data options» page there is a set of options that helps you with tuning up the conversion process for Microsoft SQL Server to Oracle. The most commonly used option is «Omit schema names in SQL scripts» that helps you with controlling the way the schema names are converted from Microsoft SQL Server to Oracle.

You can specify yes if you would like to remove all schema names from the conversion from Microsoft SQL Server to Oracle. There is also an option called «Replace reserved words» that controls the reserved words conversion. You can see it here.

Also I would like to mention an option called data type mapping that can be global or local. Global data type mapping can be changed for the whole database conversion.

Local data type mapping means that you can change the data type mapping for some specific column in some specific table. During the conversion the local data type mapping has a higher priority than the global data type mapping.

Also I’d like to show you the option that controls the way DDL and Data are converted. They can be run in separate processes. For example, I have a lot of data in Microsoft SQL Server and I don’t want to convert the data in the first run, I just want to check the conversion from Microsoft SQL Server to Oracle. For this purpose you need to open «Data options» tab and uncheck «Export data» option, in this case data will not be converted. I’ve performed the DDL conversion and now I want to check the data conversion, so I open «DDL options» tab, select «Generation options» instead of «Global data type mapping» and uncheck «Generate DDL». Now only data is converted.

There are a lot of different options that help you with tuning up the migration in our tool. If you need some specific options, please contact us and we will be glad to assist you.

On «Specify export file options» page there is an option «Export directory» which is used as an intermediate repository for all the files which our tool generates during the conversion from Microsoft SQL Server to Oracle, and I’ve got it specified here. There is also «Run in trace mode» option that allows us to see the commented source and the equivalent.

On «Specify import options» page there are two main options that actually control the import process. The first one is «Import to local or remote target database will be executed from the local system» which means that you have an Oracle client installed on your current computer and you can run the import process from your current computer from this location.

And there is another option that helps you with providing the information about the remote system where you would like to run the import process. So you can make an export and then move all the files generated by Ispirer MnMTK 2015 to a remote computer which can be on Windows or on Unix.

I’m using this computer locally so I will make export and then import into Oracle System from this current location.

In case the functions are converted, the status is provided as well. The import process has started so you can see it here on the same «Migration status» page. There is also a «View export» page and SQLWays log file that depicts export process.

Here you can see the command that is actually run by Ispirer SQLWays Wizard in batch mode. Ispirer SQLWays Wizard is just a wrapper for the command line tool.

The same information about the export process can be reviewed in SQLWays_report.html file.

You can see it here. There is an object summary page, volume of data for tables, lines of code for business logic objects, total summary information. Information per each object type (stored procedures, functions, triggers) and the information about the modifications applied to the conversion process which is usually a reference to the addition of some parameters, return statements and etc.

As to the import process, there is sqlways_imp.log file that depicts import process into Oracle system and actually this is the information provided by Oracle server to our tool so there is no control of the import process for SQLWays Wizard. It just initiates the import of commands one by one into Oracle database.

As to the files generated for this process, you can see that for tables an SQL file, a DDL file with a commented source in its equivalent in the same file are generated.

There is also a batch file with specification for maintaining the load of definitions and data into the target database.

As to stored procedures and business logic objects, you can see the SQL file that contains the source and it’s equivalent in one file and the bat file with commands which execute the SQL statement in the SQL file.

This is how Ispirer SQLWays Wizard works. You have seen the files it provides after the conversion process.

Now let’s check the conversion results. Let’s open Sqldeveloper utility in Oracle and check whether the objects were converted to Oracle. In order to check the validity of the converted business logic we need to run one of the stored procedures in Microsoft SQL Server and Oracle and compare the results. Let’s choose a stored procedure for the conversion for this purpose. As you can see the procedure contains cursor statements and loop statements. Let’s run it and check the result. Now let’s run the same SP in the target database. As you can see the results are the same in both databases. So the conversion was successful.

Now let’s open Ispirer Migration Commander and Ispirer Migration Studio which are designed to help you with maintaining the conversion of scripts from Microsoft SQL Server to Oracle.

For example, Ispirer Migration Studio works with one script at a time. Let’s add some SQL code in the left pane. Let’s make a conversion of the code from the left to the right pane. We specify the source and the target and click run. You can see the command line in the raw which is provided here for the conversion from Microsoft SQL Server to Oracle.

The result of the conversion can be compared in both left and right panes for your convenience.

As to Ispirer Migration Commander, the idea is the same: it converts scripts, but the difference is that it can convert multiple scripts at a time. So for example we’ve got several scripts in our source.

So let’s convert them. You can specify a file or files or the whole directory.

Now let’s run the conversion.

You can see that again the same command line is used for maintaining the conversion from one database to the other and you can see how the conversion is performed. Files are converted from Microsoft SQL Server to Oracle one by one.

Now let’s see the results of the conversion. We can open the SQL file and compare the source and it’s equivalent in Oracle.

This is all information about the tools that our company provides for the database migration from Microsoft SQL Server to Oracle.

It is just a review of the tools and the features that they support so if you need additional information please contact us, and we will be glad to assist you in your migration projects.

Thank you very much for your attention. Bye.

Do you want to move from Microsoft SQL Server to Oracle?

Let's do it together!

Learn more