Tags: AWS Schema Conversion Tool, code conversion
A typical database migration project includes not only the schema conversion and data migration but also the application remediation. We are talking about one or many applications interacting with the source database. To wrap up the database migration project, you will need to convert the SQL code used in the application and then reconnect it to the new database. AWS Schema Conversion Tool (SCT) helps to automate this process and simplifies required efforts estimation for manual SQL code conversion.
Check the following video to see the application conversion project in use and find out how the AWS Schema Conversion Tool helped our DBAs during migration projects.
Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your migration to Amazon Redshift.
Now let us explore the application conversion feature of AWS Schema Conversion Tool in details.
App conversion background
First and foremost, you should realize that AWS SCT converts only the SQL code from the application but not the application code itself. Schema Conversion Tool parses the original application code and extracts SQL statements from the code.
After AWS SCT completes parsing of the source application code, you are able to explore or edit the extracted SQL statements. Then you can convert the code, edit the result of the conversion if neсessary, and finally apply the changes to the target application’s code.
Each database may interact with one or several applications, so you may add one or more applications to the database migration project. Still, access to the source project files of all these applications is compulsory.
In our practice various errors may occur during SQL code extraction and conversion. You might want to explore these errors directly in the application conversion window or in the specific Application Assessment Report. Just like the Assessment Report created for the whole database migration project, this Application Assessment Report includes general recommendations as well as estimates of the effort it might take to manually convert the SQL code that can not be converted automatically.
Application Conversion Workflow
Let’s explore the basics of the application conversion process in AWS Schema Conversion Tool.
1 Create a new application conversion project
The application conversion project is to be created only in the frameworks of database migration project. Moreover, you should specify the target database (SCT must know the type of environment you are migrating to). Also, you ought to establish connection to the source database.
In the New Application Conversion Project wizard, you should specify the project language of the application. Amazon claims that SCT supports all types of the applications but the best results are reached with the Java, C++ and C# application projects.
In addition, you have to specify the application folder, SQL parameter style for target database and the source database schema. It is really important that Application Migration project connects to the correct database schema at the source. If it is not done, conversion might be incorrect or produce errors.
Now the selected application will appear in the metadata tree of the source database. You can find it in the Application section at the bottom part of the metadata tree. So, you can proceed to the next step, which is all about extracting the SQL code from the source application.
2 Analyze & Convert SQL code
Open the application conversion window and select the whole application or the required nodes that you want to convert. Press the Analyze button from the Actions menu to analyze the application code and extract SQL script from the code.
AWS SCT parses the initial application code and displays the extracted SQL code in the bottom part of the window. If any errors occur during SQL code extraction, corresponding warning icons appear.
You can export the results of the extracted SQL statements into .csv file. This is a very useful feature for large amount of source files. It is activated by clicking the Save button at the bottom window where extracted SQL is shown. Our migration team uses this feature in database migration projects and finds it very helpful when the number of statements is very high.
You can select a SQL statement and press the Convert button to convert it. Additionally, you can convert the specified file or the whole project. The converted code will be displayed on the right side of the application window.
Keep in mind that you can edit the extracted code or the converted code before applying the changes to the target application’s code.
3 Assessment Report
Errors, issues, and other problems are an integral part of any application conversion project. Therefore, you may want to use the Application Assessment Report to explore the list of issues that occurred during SQL code extraction and conversion.
Press Actions — Create Report to create the Application Assessment Report (AAR) for the selected application. The Application Assessment Report includes recommendations on manual conversion of the SQL statements with issues. Also, the AAR provides estimates on how long it will take to perform the manual conversion.
After creating an Application Assessment Report, you can switch to the Action Items view at the top menu of the application window. You can explore the issues directly in the application window and see exactly where they appear in this mode.
Finally, the Application Assessment Report can be saved as PDF or CSV file.
4 Apply Converted Code
Applying the converted code to the target application project should be carried out in two sure steps. At first, press the Apply button to insert the converted code to the target application. You can still edit it here.
In the end, don’t forget to press the Save button at the top right corner of the application window to save the target application project code. Now the changes will be saved in the application project files.
Observations
We have used the application converter built in the AWS Schema Conversion Tool in a number of database migration projects. The key benefit of the application and its most valuable contribution relates to the automatic extraction of SQL statements from the application code.
The automatic extraction of SQL code may not always work perfectly but at the same time, you have a chance to highlight the SQL statements manually to make sure that they are extracted correctly. When we are dealing with big projects with a lot of code, it is simply impossible to review all SQL statements manually. Furthermore, the AWS SCT application converter shows exactly whether the statement requires conversion or not.
Another important thing that we have noticed is that SCT uses its own format to display the converted code. In this respect, it is not that easy to compare the original and converted code, even if the converter has not made any changes to it. The thing is that we have noticed that the same code fragments may be formatted in one string by the application converter instead of, for example, 5 code strings in the original application (see the example below).
Despite all above mentioned, AWS SCT proved to be a great help for databases and related applications conversion. What’s even more, you should bear in mind that application converter in AWS SCT may come handy or even irreplaceable for the developers who are not too familiar with SQL statements. If you simply want to make sure the converted SQL code is correctly executed in the new target environment, SCT will give you the proofs for that!
Please check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your migration to Amazon Redshift.
Related AWS SCT Posts
- Migrating an Oracle to MySQL Database using the AWS Schema Conversion Tool
- AWS Schema Conversion Tool: Offline mode
- AWS Schema Conversion Tool: Connecting to Oracle Source Database
- Selecting target database type for migration using Assessment Report in AWS Schema Conversion Tool
- Troubleshooting database migration issues using AWS Schema Conversion Tool Assessment Report
- Converting Objects during Database Migration using AWS Schema Conversion Tool
- Applying Converted Code to Target Database
- General Approach to Migration of Data Warehouses to Amazon Redshift
- AWS Schema Conversion Tool: Specific Features of Migrating Data Warehouses to Redshift
- First look at AWS Schema Conversion Tool with tighter DMS integration
- Optimizing your Redshift Storage with AWS Schema Conversion Tool
- Extracting Data from Warehouses with Data Migration Agents in AWS Schema Conversion Tool
- Using AWS Schema Conversion Tool Extension Pack for OLTP databases
- Using AWS Schema Conversion Tool Extension Pack for Data Warehouses
