Assessing an application’s Data Access Layer using SQL Server Data Migration Assistant

When upgrading to a higher version of SQL Server, or when migrating to Azure, there can be certain compatibility issues between versions of SQL Server that can throw a spanner in your works. Fortunately, the Data Migration Assistant tool helps you to easily identify where the issues lie within your environment. Here’s what Microsoft say about the Data Migration Assistant:

Data Migration Assistant helps you upgrade to a modern data platform by detecting compatibility issues that can affect database functionality when you:

• Upgrade to a new version of SQL Server
• Migrate to Azure SQL Database
• Migrate to Azure SQL Managed Instance

DMA recommends performance and reliability improvements for your target environment and allows you to move your schema, data, and uncontained objects from your source server to your target server.

But what about the issues that lie outside of your environment? What about the application’s data access layer – those T-SQL statements that are ‘passed in’ directly from the application? The DMA can help with those too! But there isn’t a great deal documented about it.

DMA v.4.3 and later includes the ability to read from and assess Extended Events. First, you must create and start an extended event to capture the completed T-SQL batches. Make sure the folder you specify for the capture already exists:

---Create the session

CREATE EVENT SESSION [DatalayerSession] ON SERVER

ADD EVENT sqlserver.sql_batch_completed(

   ACTION (sqlserver.sql_text,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id))

ADD TARGET package0.asynchronous_file_target(SET filename=N'C:\DSP\Extended Events\DatalayerSession.xel')

WITH (MAX_MEMORY=2048 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=3 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

go

---Start the session

ALTER EVENT SESSION [DatalayerSession]

     ON SERVER

   STATE = START;

Leave the session to run for the duration that represents your peak workload.

For this example, I ran the following query that has an ORDER BY specifying an ordinal column number as the sort column:

select [blog_date], [blog_title] from [dbo].[blogs] order by 1

This is not recommended and can cause problems under certain use cases in SQL 90 compatibility mode and above, and so typically gets flagged by the DMA.

Once your extended events session is complete, run the DMA Assessment as normal. Select the database(s) to assess, and then in the bottom right corner, enter the folder path containing the captured extended event file.

And voila! The DMA detects the issue in the statement that was passed in, where from, and even advises on the recommended fix:

If you’re considering upgrading or modernising your SQL Server estate, our highly accredited SQL Server DBAs can help you create bespoke solutions to meet your needs.

Discover more about our SQL Server Upgrade services here and don't hesitate to get in touch with the team if you have any questions!