Microsoft SQL Server to PostgreSQL Online Presentation


Demo presentation text:

Hello! This is a demo about the migration tools provided by Ispirer Systems for the SQL Server to PostgreSQL migration.

First of all I’d like to mention that Ispirer Systems provides tools and services for automating the databases and applications migration processes for a variety of conversion directions. The list of tools supplied for such kind of automation is as follows:

• SQLWays Wizard – a GUI tool that is designed to simplify the database conversion process tuning for the sqlways command-line tool. Works with the source database using the ODBC connection.

• SQLWays Studio – a GUI tool that is designed to simplify the database or application script conversion process tuning for the sqlways command-line tool. Works with one script at a time.

• SQLWays Commander - a GUI tool that is designed to simplify the database or application scripts conversion process tuning for the sqlways command-line tool. Works with several scripts at a time.

• Sqlways.exe – a command line tool that can be run in batch mode and can perform the automatic conversion of the database using the ODBC connection or the database and application code provided in scripts.

In this demo I will show you the Microsoft SQL Server to PostgreSQL migration using SQLWays Wizard, SQLWays Studio and SQLWays Commander Tools mentioned in the list provided.

Let’s start from the SQLWays Wizard.

This is the tool that can connect to the SQL Server database using the ODBC connection only. So, to migrate from Microsoft SQL Server to PostgreSQL database , you need to provide the ODBC DSN for SQL Server on the “Choose a Source Database” page. Also you need to specify a user and a password with the appropriate rights for making selects from systems tables from SQL Server database.

On a “Choose a Target Database” page you need to choose the target database from the list of the supported databases. As you can see the PostgreSQL database is specified here. Also you need to provide other credentials for the PostgreSQL database for a support of definitions and data load using native utilities of this database.

All the objects that SQLWays manages to extract from the source database it depicts in a tree view on the “Specify Database Objects or Query” page. Here you can select all the objects required for the migration.

After that you can specify different options for automatic Microsoft SQL Server to PostgreSQL conversion, like option that controls the schema name conversion, reserved words conversion, data types conversion, identifier names conversion and etc. Also there are options that can help you to run the definitions and data migration in separate processes.

On the “Specify Export File Options” page you need to provide the directory, which serves as an intermediate repository for the whole migration process and stores all the automatically generated files, like SQL files with the converted definitions of the objects, TEXT files with the data prepared to be loaded into the target database, BAT or SH files with the commands for native utilities to load both definitions and data on Windows or on Unix like OS.

All the Microsoft SQL Server to PostgreSQL migration process is performed in two steps. Each of them has separate logg information.

For the Export process there are provided the sqlways.log files with the information about the: objects read from the SQL Server database, their number, number of rows of data and the size of the data per each table extracted, number of lines of code (LOC) per each business logics objects, like views, synonyms, stored procedures, functions, triggers, packages and etc. At the end of the export log file there is provided the summary information per each object type regarding the number of objects, the common size of data, number of rows of data or lines of code, depending on the object type, the export status regarding whether there were any failures or no during the export process and the Total information for the whole database export.

Similar information but in more readable format is provided in the sqlways_report.html file. There is a summary table with the complete export status and statistics per each object type and there are also tables with the list of objects and more detailed statistics for each object extracted from the source database.

As for the import process, SQLWays generates a sqlways_imp.log file for the import into the target database that contains information about what object is being loaded and whether there are any issues during the import process or no.

Now let’s turn to the SQLWays Studio and SQLWays Commander tools that are designed for database scripts and application scripts conversion purposes.

In SQLWays Studio to convert from Microsoft SQL Server database to PostgreSQL all you need to do is to provide the conversion direction using the “Source” and “Target” options, place the source SQL Server code to the left pane of the tool and Run the conversion. At the bottom of the tool you can see the log of the conversion process that shows if there are any errors during the script conversion from SQL Server to PostgreSQL database. After the conversion you can compare both source and target scripts in one tool side-by-side for your convenience.

In SQLWays Commander there are also two panes which give you the opportunity to specify the file or files or the whole directory for the conversion. So, to convert from Microsoft SQL Server to PostgreSQL you just specify the source and target from the list of supported databases, specify the files for conversion and click Run. When you specify the directory for the conversion you also need to provide the template for the files that you would like to convert from Microsoft SQL Server to PostgreSQL database and provide the target directory. At the bottom there is generated the conversion log with the information about conversion issues if any.

After the conversion by the double clicking file in the left pane and in the right pane you can compare the results of the conversion side by side like in SQLWays Studio.

Now let’s turn to the results of the conversion process made by SQLWays Wizard utility. So we did the conversion of a MSSQL to PG database. This is our DEMO database from Microsoft SQL Server database into PostgreSQL database. As you can see both databases exist now so we have a source and a target. Now after the conversion process you need to cheek one of the stored procedures, one of the functions how they were actually converted is the conversion result correct or not. So lte’s turn to a simple example with one stored procedure called SP-convert style that we run with just some representation of a date time Value. we click execute and we receive result that it is converted to the different styles. Now let’s run the same conversion for the converted stored procedure in PostgreSQL database and see whether the results are the same or not after the conversion process. So we need to click run here and we can see that the result of the converted stored procedure is the same as in the source.

So this is all information I’ve got for this demo all information about the peculiarities of Microsoft SQL Server to PostgreSQL migration and the information about other tools that Ispirer Systems for the databases migration support will be described at our next demos.

Thank you very much for your attention. Bye.

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

Let's do it together!

Learn more