SQL Server to Amazon Redshift Online Presentation

Watch this demo to see how Ispirer Migration and Modernization Toolkit automatically converts Microsoft SQL Server to Amazon Redshift.


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 the database conversion from Microsoft SQL Server to Amazon Redshift.

Basically there are 4 tool called:

• 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 contained in scripts.

• Ispirer SQLWays Wizard – a GUI tool designed particularly for the database migration. Works with a source database using the ODBC connection.

• Ispirer Migration Studio – a GUI tool oriented specifically to the conversion of a separate application or database script. Works with one script at a time.

• Ispirer Migration Commander – a GUI tool designed for the conversion of multiple files containing application or database code. Works with several scripts at a time.

So let’s start this trial migration from Microsoft SQL Server to Amazon Redshift with the help of the tool called Ispirer SQLWays Wizard. This is a fully featured tool developed exactly for a database migration.

First of all we have prepared Microsoft SQL Server database with the list of the objects and an empty Amazon RedShift database where database objects will be loaded from Microsoft SQL Server.

When we start Ispirer SQLWays Wizard we see a Welcome page. Here you need to specify a license and a project directory where a set of files required for the project is stored. Well, I’ve got it selected here, we can click next.

To migrate from Microsoft SQL Server to Amazon RedShift database, you need to tune the ODBC DSN for Microsoft SQL Server and specify it 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 system tables of Microsoft SQL Server.

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 Amazon RedShift database is specified here. Also we need to provide other credentials such as Host, port, database, user and a password for Amazon RedShift database for support of definitions and data load using native utilities of this database.

Also to be able to make an import of data into Amazon Redshift database you need to specify a path to Amazon S3 folder where files with the data will be uploaded and specify Access key ID and Secret access key.

All the objects that SQLWays Wizard manages to extract from the Microsoft SQL Server database are depicted in a tree view on the “Specify Database Objects or Query” page. Here you can select all the objects required for the migration.

Then you can specify different options that will help you to customize Microsoft SQL Server to Amazon RedShift conversion, like option that controls the schema name conversion, reserved words conversion, data types conversion, etc.

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, such as SQL files with the converted code, TXT files with the data, BAT or SH files.

On the “Specify Import Options” page you need to specify Bin directory. Bin Directory is a path to a folder where PSQL.exe utility is stored on your local machine. Our tool will use this path to generate correct import commands.

Then we need to initiate conversion process by clicking “Start Conversion” button and our tool will extract the information about the objects specified for conversion and generate converted scripts.

When the conversion is done, we need to copy all the TXT files – files with the extracted data, and upload them to the Amazon S3 folder.

And then we initiate import by clicking “Start Import” button and our tool will load the tool from these files into the Redshift database.

As you can see all the Microsoft SQL Server to Amazon RedShift migration process is performed in two steps. Each of them has separate logging information.

For the Export process there are the sqlways.log file with the information about the objects read from the Microsoft SQL Server database, their number, number of rows of data and the size of the data per each extracted table. At the end of the export log file there is the summary information per each object.

Similar information but in a 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, 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 Wizard generates a sqlways_imp.log file that contains information about which objects have been loaded and whether there are any issues during the import process or no.

Now, let’s make selects from the views from Microsoft SQL Server database. We can see that it returned some data. Let’s make the same selects from our Redshift database. We can see that the objects have been created in the database and we can see that the data returned by this view are the same.

Let’s call another view. We can see that it also returned some data.

Let’s execute it in the Amazon Redshift database. We can see that it also returned some data from the select view which are also the same. So it means that the objects have been migrated correctly.

This is all information I’ve got for this demo. The information about the peculiarities of Microsoft SQL Server to Amazon RedShift migration and the information about other migration directions you can find on our web site www.ispirer.com as well as in our next demos.

Thank you very much for your attention. Bye.