How to use SQL Profiler against an Azure SQL Server database

One of the first tool I am using when I start working on a project I don't know too much about, project connected to a database, is the SQL Server Profiler.

To understand the code implemented behind some functionality, I am adding a database trace and monitor how the database is hit by the application running the mentioned functionality. In this way I can quickly identify the queries or stored procedures executed at the database level and perform a search in the code for these objects; once I have found them, I am monitoring the stack calls and get familiarized with the functions called in the app.

Note: To do this, you need to request Alter Trace permission for your database account.

Recently, I wanted to do the same activity, this time against a SQL Server database that resides in the Azure Cloud. I was surprised to see that this functionality has been deprecated in Azure SQL Database.

Note:

When you try to connect to a Azure SQL Database from SQL server profiler, it incorrectly throws a misleading error message as follows:

• In order to run a trace against SQL Server, you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

The message should have explained that Azure SQL Database is not supported by SQL Server profiler.

Doing a bit of research, I have discovered that you are still able to use this functionality if you install Azure Data Studio from this link:

https://docs.microsoft.com/en-us/sql/azure-data-studio/extensions/sql-server-profiler-extension?view=sql-server-ver15

Once you start the azuredatastudio.exe, you can install the SQL Server Profiler extension:

To open the extensions manager and access the available extensions, select the extensions icon, or select Extensions in the View menu (above image).

You can start the profiler from View->Command Palette->Launch Profiler (above image).

I have connected to SQL Server using the SQL Login created in Azure SQL Server.

Note: You may need to associate your database server to your Azure account/subscription (especially if you have a MSDN subscription via your work setup).

A simple select statement from a table it is seen below within this tool:

Entity Framework SQL Trace using MiniProfiler

Install the following libraries and add the code from below:

dotnet add package MiniProfiler.AspNetCore.Mvc --version 4.1.0
dotnet add package MiniProfiler.EntityFrameworkCore --version 4.1.0

Modify Startup.cs

public void ConfigureServices(IServiceCollection services)
{    
    services.AddMemoryCache();
    services.AddEntityFrameworkSqlite().AddDbContext<DatabaseContext>();
    services.AddMiniProfiler(options => options.RouteBasePath =       "/profiler").AddEntityFramework();
    services.AddControllers();
}

Then navigate to your API profiler results at [your-api-url]/profiler/results-index

You can open the APIs on by one and see the SQL code that you can copy and execute against your database.

##