Applications typically connect and persist data to a database. The data access layer of the application provides simplified access to this data. Data Migration Assistant (DMA) has enabled users to assess their databases and related objects. The latest version of DMA (v5.0) introduces support for analyzing database connectivity and embedded SQL queries in the application code.
Consider the simple C# code segment below:
In this case, you can see that the application is using a SQL query to get the name of an employee.
As an application owner, I need to be able to identify the various databases that the application can connect to and the queries embedded in the application’s data access layer. In addition, I need to identify any changes required to modernize the application to Azure Data services.
To enable this assessment, we recently introduced the Data Access Migration Toolkit (DAMT), a Visual Studio Code extension. The latest version (v 0.2) of this extension adds support for .Net applications and T-SQL dialect.
Download and install VS Code from http://code.visualstudio.com/download.
Enable the Data Access Migration Toolkit extension from the Extensions Marketplace.
Open the application project in Visual Studio Code.
Start the extension console (Ctrl-Shft-P), and then run the Data Access: Analyze Workspace command.
Select the SQL Server dialect.
At the end of the analysis, the command produces a report of SQL connectivity commands and queries.
Review the report for data connectivity components and for SQL queries embedded in the application code. These are highlighted in the code as well.
These queries can be analyzed through DMA for compatibility and feature parity issues based on the target SQL platform.
To assess the application’s data layer, export the report as json file.
In this case, the generated file is:
Data Migration Assistant enables assessing the queries identified in the application within the context of modernizing the database to Azure Data platform.
Start Data Migration Assistant and create and assessment project.
Select source SQL instance.
Select the database the application is connecting to.
To facilitate data access assessment, DMA introduces the ability to include json files with application queries. Next, we’ll include the json file we crafted earlier with the application queries.
Select the database and browse to the json file exported from Data Access Migration Toolkit to include the queries from the application for the assessment.
Start the assessment.
Review the assessment report. The generated report will include any compatibility or feature parity issues detected in the application queries as shown below.
Now, in addition having the database perspective of the migration, users also have a view from the application perspective.