Home
%3CLINGO-SUB%20id%3D%22lingo-sub-991011%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Data%20Migration%20Assistant%20to%20assess%20an%20application%E2%80%99s%20data%20access%20layer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-991011%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20for%20Sharing%2C%20Awesome%20blogpost%26nbsp%3B%3CIMG%20class%3D%22lia-deferred-image%20lia-image-emoji%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fhtml%2Fimages%2Femoticons%2Fcool_40x40.gif%22%20alt%3D%22%3Acool%3A%22%20title%3D%22%3Acool%3A%22%20%2F%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-990430%22%20slang%3D%22en-US%22%3EUsing%20Data%20Migration%20Assistant%20to%20assess%20an%20application%E2%80%99s%20data%20access%20layer%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-990430%22%20slang%3D%22en-US%22%3E%3CP%3EApplications%20typically%20connect%20and%20persist%20data%20to%20a%20database.%20The%20data%20access%20layer%20of%20the%20application%20provides%20simplified%20access%20to%20this%20data.%20Data%20Migration%20Assistant%20(DMA)%20has%20enabled%20users%20to%20assess%20their%20databases%20and%20related%20objects.%20The%20latest%20version%20of%20DMA%20(v5.0)%20introduces%20support%20for%20analyzing%20database%20connectivity%20and%20embedded%20SQL%20queries%20in%20the%20application%20code.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConsider%20the%20simple%20C%23%20code%20segment%20below%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155520i8E9EAE549C04FE3C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20you%20can%20see%20that%20the%20application%20is%20using%20a%20SQL%20query%20to%20get%20the%20name%20of%20an%20employee.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155519i34B66556A45BF55E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20application%20owner%2C%20I%20need%20to%20be%20able%20to%20identify%20the%20various%20databases%20that%20the%20application%20can%20connect%20to%20and%20the%20queries%20embedded%20in%20the%20application%E2%80%99s%20data%20access%20layer.%20In%20addition%2C%20I%20need%20to%20identify%20any%20changes%20required%20to%20modernize%20the%20application%20to%20Azure%20Data%20services.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20enable%20this%20assessment%2C%20we%20recently%20introduced%20the%20Data%20Access%20Migration%20Toolkit%20(DAMT)%2C%20a%20Visual%20Studio%20Code%20extension.%20The%20latest%20version%20(v%200.2)%20of%20this%20extension%20adds%20support%20for%20.Net%20applications%20and%20T-SQL%20dialect.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDownload%20and%20install%20VS%20Code%20from%20%3CA%20href%3D%22http%3A%2F%2Fcode.visualstudio.com%2Fdownload%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fcode.visualstudio.com%2Fdownload%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnable%20the%20Data%20Access%20Migration%20Toolkit%20extension%20from%20the%20Extensions%20Marketplace.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155521iF9175432735C5AA6%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOpen%20the%20application%20project%20in%20Visual%20Studio%20Code.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155522iE39CA9BE50869BD1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStart%20the%20extension%20console%20(Ctrl-Shft-P)%2C%20and%20then%20run%20the%20Data%20Access%3A%20Analyze%20Workspace%20command.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155523i993966697402FB30%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20SQL%20Server%20dialect.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155524iAD05099FC68D10B0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_5.png%22%20title%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAt%20the%20end%20of%20the%20analysis%2C%20the%20command%20produces%20a%20report%20of%20SQL%20connectivity%20commands%20and%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155525i4547CF91CD01C749%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_6.png%22%20title%3D%22clipboard_image_6.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EReview%20the%20report%20for%20data%20connectivity%20components%20and%20for%20SQL%20queries%20embedded%20in%20the%20application%20code.%20These%20are%20highlighted%20in%20the%20code%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155527iA3E33EFD793F96EB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThese%20queries%20can%20be%20analyzed%20through%20DMA%20for%20compatibility%20and%20feature%20parity%20issues%20based%20on%20the%20target%20SQL%20platform.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20assess%20the%20application%E2%80%99s%20data%20layer%2C%20export%20the%20report%20as%20json%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155528i4D11BE12CB3F6820%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20this%20case%2C%20the%20generated%20file%20is%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155526i752B0DB4E22FEB7F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20Migration%20Assistant%20enables%20assessing%20the%20queries%20identified%20in%20the%20application%20within%20the%20context%20of%20modernizing%20the%20database%20to%20Azure%20Data%20platform.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStart%20Data%20Migration%20Assistant%20and%20create%20and%20assessment%20project.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155529i163EFD1B277A3FF7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_3.png%22%20title%3D%22clipboard_image_3.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20source%20SQL%20instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155530iCCE9F08F00ED05FA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_4.png%22%20title%3D%22clipboard_image_4.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20database%20the%20application%20is%20connecting%20to.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155531i82F0EED48B5B0245%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_5.png%22%20title%3D%22clipboard_image_5.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20facilitate%20data%20access%20assessment%2C%20DMA%20introduces%20the%20ability%20to%20include%20json%20files%20with%20application%20queries.%20Next%2C%20we%E2%80%99ll%20include%20the%20json%20file%20we%20crafted%20earlier%20with%20the%20application%20queries.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20database%20and%20browse%20to%20the%20json%20file%20exported%20from%20Data%20Access%20Migration%20Toolkit%20to%20include%20the%20queries%20from%20the%20application%20for%20the%20assessment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155533iA073804B11E38B51%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_6.png%22%20title%3D%22clipboard_image_6.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EStart%20the%20assessment.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155532iF353AC8E3F457F1B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_7.png%22%20title%3D%22clipboard_image_7.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EReview%20the%20assessment%20report.%20The%20generated%20report%20will%20include%20any%20compatibility%20or%20feature%20parity%20issues%20detected%20in%20the%20application%20queries%20as%20shown%20below.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F155534iD8A6163A8D0B64D4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22clipboard_image_8.png%22%20title%3D%22clipboard_image_8.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENow%2C%20in%20addition%20having%20the%20database%20perspective%20of%20the%20migration%2C%20users%20also%20have%20a%20view%20from%20the%20application%20perspective.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-990430%22%20slang%3D%22en-US%22%3E%3CP%3EApplications%20typically%20connect%20and%20persist%20data%20to%20a%20database.%20The%20data%20access%20layer%20of%20the%20application%20provides%20simplified%20access%20to%20this%20data.%20Data%20Migration%20Assistant%20(DMA)%20has%20enabled%20users%20to%20assess%20their%20databases%20and%20related%20objects.%20The%20latest%20version%20of%20DMA%20(v5.0)%20introduces%20support%20for%20analyzing%20database%20connectivity%20and%20embedded%20SQL%20queries%20in%20the%20application%20code.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-990430%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDAMT%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDMA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

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:

 

clipboard_image_0.png

 

In this case, you can see that the application is using a SQL query to get the name of an employee.

 

clipboard_image_1.png

 

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.

clipboard_image_2.png

 

Open the application project in Visual Studio Code.

 

clipboard_image_3.png

 

Start the extension console (Ctrl-Shft-P), and then run the Data Access: Analyze Workspace command.

 

clipboard_image_4.png

 

Select the SQL Server dialect.

 

clipboard_image_5.png

 

At the end of the analysis, the command produces a report of SQL connectivity commands and queries.

 

clipboard_image_6.png

 

Review the report for data connectivity components and for SQL queries embedded in the application code. These are highlighted in the code as well.

 

clipboard_image_0.png

 

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.

 

clipboard_image_1.png

 

In this case, the generated file is:

 

clipboard_image_2.png

 

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.

 

clipboard_image_3.png

 

Select source SQL instance.

 

clipboard_image_4.png

 

Select the database the application is connecting to.

 

clipboard_image_5.png

 

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.

 

clipboard_image_6.png

 

Start the assessment.

 

clipboard_image_7.png

 

Review the assessment report. The generated report will include any compatibility or feature parity issues detected in the application queries as shown below.

 

clipboard_image_8.png

 

Now, in addition having the database perspective of the migration, users also have a view from the application perspective.

1 Comment

Thank you for Sharing, Awesome blogpost :cool: