Oracle to Microsoft SQL Server Database Migration Demo

Note this is an old version of Oracle to SQL Server Migration Demo! We have prepared a new one in June 2016. Watch it here: https://youtu.be/RUnrpwg7PRU

---

Closed Caption:


Hello!
This is a demo about
Oracle to Microsoft's SQL server conversion
using tools provided by Ispirer Systems. So there is a set of tools that our company provides
for making an Oracle to SQL Server migration.
And these tools have one common tool called
SQLWAYS
and of all of them have just different graphical interfaces and the ways they are used for maintaining the conversion process
First of all I would like to mention the SQLWays Wizard tool
that helps you in doing the database conversion
from oracle to SQL server database
both of definitions and data.
So when you start it you trap into a Welcome to the SQLWays Wizard page
where you need to enter the license and where you can specify a project directory
for storing up the information
about the project you are doing.
So, the information that is stored here
is about the objects that you specify for the conversion for this particular project
the options that you specify as for the conversion, the mappings, data type
mappings or identifier mappings
that you specify for this conversion.
So and this information we store in two files in a sqlways.xml file
and sqlways.ini file for particular project.
So I've got one project selected and I can move next
On a "Choose a source database page
you need to specify the USER or SYSTEM DSN for connection to an Oracle database.
This is ODBC DSN as our tool works using ODBC connection only with a source database.
So you have to install an Oracle client and tune up a connection to an Oracle database.
And you need to specify appropriate username and password
Because our tool uses additional information from system views from Oracle database
about the objects about the datatypes used for columns and etc. when doing the conversion.
OK. Let's move next.
On a "Choose a Target Database" page you need to specify a target database as Microsoft SQL Server.
The target version that you are converting into.
So you can see 2012 SQL Server database is also supported in our tool, but I am using 2008 as a target version of MS SQL Server database.
The server name is required, the database name, the username and a password
With aproppriate rights for doing load in MS SQL Server database.
Also on the Advanced page are recommended to specify bin directory.
where the native tools like BCP and SQLCMD reside that our tool uses for maintainig data and definitions load into MS SQL Server database.
So let's move next.
At this point on the "Specify a Database Objects or Query" you can specify the set of objects for the conversion.
You can see them in a tree view on a left pane. So each object is depicted in it's own schema,
in its own object type tree.
and the whole database is provided in a tree view for your convinience.
So using arrow buttons you can choose an objects for conversion.
or you can use the filtering option to specify the exact match of the objects you would like to convert.
For example if I would like to convert everything from schema ORA
So I am adding this schema here.
Now I have all the objects all the schemas that start with ORA.
So this helps me in a specification of the objects that I would like to make a conversion of.
As I'm using a project file so I have a set of objects selected for conversion and you can see them here.
So there are some tables, views, stored procedures, synonyms, functions, packages,
I am not showing you the conversion of user accounts, defined types and sequenses.
But actually sequences conversion is supported especially in SQL Server 2012
where the support of this objects has been added recently.
So we have selected the objects.
Let's click next.
In our tool there are lots of options
for maintains the conversion process.
I will talk about just a few of them.
First option is ommit schema name in SQL scripts
You know that the are differences in the way that Oracle and SQL server stores the objects
and treat the schemas, the number of schemas of the database and etc.
The number of databases on one server. All this is differently in both Oracle and SQL server database.
So using options that control the schema names conversion
you can tune up the conversion process of objects from different schemas in Oracle into schemas in SQL Server database.
And using some additional options in SQLWays tool you can convert schema names to database names and etc. in our tool.
Also SQLWays supports reserved words conversion if it traps into reserved word in identifier names
when doing the migration from Oracle database to SQL Server database.
It encloses such words and such identifiers in square brackets for SQL Server database.
You can change this behavior by changing the reserver word parsing in a replace reserved word option.
Like this for example.
In this case no square brackets provided only "_r" is added to each reserved word in target database.
Also in our tool there are two types of data types mapping
First one is a global data type mapping. You can see it here when you click tables.
This data type mapping is a predefined datatype mapping for the whole database conversion.
You can change the target data type
so and can change the way the data types are converted
during Oracle to SQL Server migration.
Also there is so called local data type mapping.
To find it you need to click one of the tables
and go to DDL Options
and here you can see a list of columns and their target data types.
You can change the data type here and it will be changed for this exact column only in the whole database.
Also our tool provides an opportunity to make a conversion separately of schema and data.
This is especialy useful if you have a large Oracle database and you would like to convert schema only.
So just go to tables.
Go to data option step.
And you can see export data options here. This option controls whether to convert the data or no.
If I check it, the check box export data, so I will
convert the data from Oracle the SQL server database
Also I can ommit schema conversion
For this purpose I need to go to DDL option step, choose a SQL scripts from drop down box and uncheck Generate DDL.
In this case only data is converted.
OK. Let's move next.
There are a lot of different options in our tool.
So if you have a need in some specific option for your project we can let you know whether we have such option in our tool
or we need some time to implement it.
On "Specify Export File Options" page need to specify export directory.
This is a intermediate directory, repository,
for all the generated files and
SQLWays uses them for maintaining the import process into MS SQL Server database.
This is the one export directory I have and the option run in trace mode.
This option controls the way the SQL files are created and the trace information that is generated by our tool.
So for sql files it generates commented source in each file for the object, for each object that is being migrated from Oracle to SQL Server.
So in one file you have an opportunity to see the commented source and it's automatically converted equivalent
for Oracle to SQL Server migration
On the "Specify Import Options" page for SQL server we are usually using the first option
This is when you have a SQL server client installed on your current computer.
So you have all the tools like SQLCMD and BCP tools installed as well and you can
make an import from local system
to remote SQL Server database.
So we are using this first option for this purpose.
Some information about the conversion you're going to perform.
Number of objects.
So at this point on a "Migration Status" page you can see that our tool connects to an Oracle database.
And extracts information about each table one by one
So there are some data in some tables.
Some objects were specified as not valid.
So this information our tool depicts as well.

OK. The export has ended and now we can see the import process into SQL Server database
OK. So we can see that import is much faster than the export.
So now after making the export and import process we can use the export directory
And here we can see a set of files generated for the makings an import into MS SQL server database.
At first let's see the batch files generated. You can see a SQLCMD command for loading the definitions of objects into MS SQL Server.
And the BCP command line to load the data into a Microsoft SQL server database
After the import process you'll see an error file
for each table.
This error file will contain all the rows of data that were not loaded into SQL server database.
And if they are empty so there are no errors
during the data load into the Microsoft SQL Server database.
We can see a format file is generated as well by our tool automatically to support data load into target database.
Text file with data is provided as well.
Also SQL files are generated for definitions of objects, for tables and you can see a commented source in Oracle syntax
in the same file and it's equivalent in MS SQL Server database.
And NUMBER(10,0) with assume that some trigger is specified for "Categories", a table, that alters the value of the categoryid column
with some sequence value, that is why it has been converted to the identity value in MS SQL Server database 2008.
And if there are some primary keys, check constraints, foreign key constraints they are also depicted in the SQL files.
Indexes with the source commented and it's equivalent for target database.
Practically the same information is generated for trigger, and you can see the SQL files and batch files for such objects.
And here for example we have a trigger. In Oracle database "for each row" trigger.
It's commented source with some exceptions inside that has been converted into a "For each statement" trigger in SQL Server database.
so you can see all this files are generated. Some packages. Let's see some packages examples.
Like the package with a function and procedure inside.
So and the idea is to remove packages for target database and the name of the package is added to the name of a function or a stored procedure
That are declared inside the package body
in Oracle database. So you can see the name of the package and a name of a function
as the name of create function in SQL server database and for procedures the same.
OK. In addition to all those files that are generated our tool also provide the sqlways.log file with a command line options
that our SQLWays Wizard graphical tool uses to run a SQLWays command line tool.
The information about all the objects extracted from the source database, the workflow for the export process
and the information above the export process for business logic objects in the overall statistics
for each object type
and the total information for the whole migration process.
Also information about the expert process can be found in sqlways_report.HTML file.
You can see it here. It's in a more readable format here provided. You can see the object summary information table.
With all the required statistical information.
It's like a high level business information about objects inside,
whether they were parsed or no. Time that was required to make an export process.
Number of objects, number of lines of code, size of the date and per each object type their is generated separate table to provide
information and statistical information regarding the export process.
For example as for packages also there is a feature to identify it as the objects inside the packages.
So we can count the number of functions and stored procedures inside each package.
A number of lines of code that were used for their creation and if there is some modifications
inside the business logic objects.
Like function provides here, you can see these modifications in a separate table
As for the import process there is SQLWays_imp.log file that depicts an import into the Microsoft SQL server database.
And you can see this information here
Ok. So, this is actually the way SQLWays Wizard works, so we can finish it work, we have made an export and import
of the Oracle database into SQL Server database.
How about the scripts conversion.
For this purpose we have two tools.
First one is a SQLWays Studio, you can see it here, it works with one script at the time, you can provide some statements here.
For example in a left pane. Provide the source and target from the list of supported source and target databases.
And run the conversion.
So SQLWays Studio is also a graphical wrapper for a command line tool called SQLWays and you can see sqlways log
Even the command line is used to run each conversion.
So in this tool you can see in one window left pane and right for the source and for its automatically converted equivalent.
You can see that there are exceptions inside begin end blocks, so they are converted to begin try and to try catch blocks in SQL Server.
So while Studio works with just one script at a time
There is a SQLWays Commander tool that can work with multiple scripts.
So it looks like pretty the same like studio but
in left pane you can specify the files that you would like to convert the scripts or you can specify a folder for conversion.
so you can specify a folder and you need to specify the source and target the same way as in Studio.
And when you click RUN you need to provide a file mask for the files that you would like to convert, this can be text files or SQL files
as it is provides here.
The target directory is the one that is opened in a right pane.
So, we go up.
and we go up here
You can see there was a conversion of a procedure from a source folder to procedures
in a target folder.
So, this tool works the same way as Studio, to see the converted result we just need to click the source and target
and compare both the source and a target scripts conversion.
and in the bottom of this window in SQLWays commander in log view we can see again the command
that was used to build the script conversion in this case.
So lots of different features, conversion features are supported in our SQLWays tool especially
for Oracle to SQL Server conversion
So the conversion of sequences, packages, row type, exception blocks,
all kinds of business logic conversion from Oracle to SQL Server database are supported in our tool
And we are ready to extend it to support any of your particular project.
So this is all I have for now.
Thank you very much for your attention.
Bye.