title

Assess an application’s data access layer with Data Migration Assistant

description

Learn how to use Data Migration Assistant to assess an application’s data access layer.

ms.date

01/23/2020

ms.prod

sql

ms.prod_service

dma

ms.reviewer

ms.technology

dma

ms.topic

conceptual

keywords

helpviewer_keywords

Data Migration Assistant, Assess

ms.assetid

author

HJToland3

ms.author

rajpo

ms.custom

seo-lt-2019

Assess an app's data access layer with Data Migration Assistant

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 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.

Assess an app with Data Access Migration Toolkit

To enable this assessment, we recently introduced the Data Access Migration Toolkit (DAMT), a Visual Studio Code extension. The latest version of this extension (v 0.2) adds support for .Net applications and T-SQL dialect.

Download and install VS Code from here.
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, which appear highlighted.

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 then create an assessment project.
Select the source SQL Server instance.
Select the database to which the application is connecting.

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.