Changing Database vendors is not an easy task, but a lot of people do it and for multiple reasons including better pricing or features. Microsoft provides a free tool called the SQL Server Migration Assistant (SSMA), which can assist with your database migration needs. While SSMA offers comprehensive support for database schema migration, the application code required manual updates to support the new database back end. Today Microsoft is introducing the Data Access Migration Toolkit, which is designed to help close this gap.

What is the Data Access Migration Toolkit?

Making changes to the application code is a significant challenge during database migration. How should this be addressed? We could add support for source code analysis in SSMA. However, the people editing the application are not necessarily the same people that are addressing the database schema, so they may not be using the same tool set. We could roll out new tool solely for this purpose, but that would introduce a learning curve for application developers. Instead, we have decided to build on existing developer tools that everybody knows and loves – Visual Studio Code! The Data Access Migration Toolkit is an extension for Visual Studio Code that allows you to analyze your Java source code and detect data access API calls and queries, providing you with a single-pane view of what needs to be addressed to support the new database back end.

There are a lot of variables in migrations, such as different programming languages and database engines. However, we had to start somewhere, so we decided to focus on Java applications accessing Oracle databases as our first scenario.

Getting started

The Data Access Migration Toolkit extension is free and available through the Visual Studio Marketplace. To install the toolkit, just open Visual Studio Code, navigate to Extensions tab, search for Data Access Migration Toolkit, select it in the list, and then select Install. The process should not take long to complete.

When the installation is complete, reload Visual Studio Code and you are all set to analyze your app!

Using the extension

To start analyzing your Java source code, you first need to load it in the workspace. If you are already familiar Visual Studio Code – good! If not, then just navigate to the Explorer tab and select Open Folder. If you don’t have the necessary extensions to work with Java code, Visual Studio Code will prompt you to install them.

The Data Access Migration Toolkit relies on the redhat.java extension for analysis, so make you have this installed. This extension is part of the Java Extension Pack, which is the most common setup.

After your folder is loaded and ready to use, just run the Data access: Analyze workspace command (press ++P and start typing “Data acc...”).

Select the command, and then give it some time to scan through your workspace. Depending on the size of your project, this may take a few minutes. After the analysis is done, the Problems pane will list all locations of interest that have been identified.

The command will also generate a report calling out the places in your code base that may need to be updated when migrating to a different database back end.

Currently the extension detects the following:

• JDBC and Java Persistence API calls
• Connection strings
• String literals that look like a database query

The extension analyzes all relevant files, including XML, JSON, and arbitrary Java properties files, in your workspace. In our example, all data access artifacts existed in .java files. Let’s explore what needs to be changed.

The first thing identified is the connection string to the database. Click on that problem, and you are taken to the location where the connection string is defined.

In this case, just change the connection string to use the SQL Server driver, and then move on. After you update the connection string and save the file – the extension will automatically reanalyze the contents and update the problems pane with the most recent scan results.

The next problem identified points to the DriverManager.getConnection(..) API call:

In this case, review the connection information and make sure everything is pointing to the SQL Server. Luckily, in our case the connection string is defined somewhere else (a constant field that we already fixed), so no actions are required. However, we still want to ensure this problem is resolved, so we can suppress the issue by adding a special damt.ignore-next-line comment directly before it:

We specify api-reference in brackets since this is the warning type that we want to suppress.

Moving on to the final two problems, notice that they are on the same line:

The first one is a Connection.prepareStatement(..) call and the second one is the actual query. At times you may have queries coming from user input or loaded from an external file, so query detection alone will not be a reliable approach. To be on the safe side and ensure that you detect all potential problems, the Data Access Migration Toolkit also marks calls to data access APIs for your review so that you can make sure that the queries being used are compatible with new database back end. In this case we have a query right there, so we can suppress the API warning right away and focus on the query.

If you used SQL Server Migration Assistant to migrate your database back end from Oracle to SQL Server, then you can use the statement conversion feature to convert queries within your migration project. For this walk-through, we will modify the query to be compatible with SQL Server ourselves – all we need to do is use ‘+’ for string concatenation: 

Notice that the suppression attribute has two warning types: api-reference and query. I already mentioned that there is nothing to do about prepareStatement(..) call in this case, but the query is already fixed, so why would we suppress the warning? That’s where it gets tricky – some queries look very similar between Oracle and SQL Server, so the extension cannot tell whether a query will work with your new database back end. As a result, after you modify the query it is recommended to mark it as fixed by adding the suppression comment next to it – this will help you track the progress as you move along.

After you are done – save the file, and the extension will reanalyze the file and clear all the warnings!

What’s next?

If you are in the process of migrating your database – go and try it yourself today:

• Get Visual Studio Code.
• Install the Data Access Migration Toolkit extension.

This is just a first step in our journey to simplify application code remediation when you migrate to SQL Server, so stay tuned – there is more to come!