How to Start an XEvents Trace on a Read Scale-Out Azure SQL Managed Instance

It took me more than half hour to figure out how to start an XEvents trace on a read-scale out instance of Azure SQL Managed Instance. It’s hard to monitor read scale-out instances, so tracing is desirable! I started with a simple trace of sql_statement_completed. Hopefully this saves other folks some time.

Architecture review: what are read scale-out instances in Managed Instance?

Read scale-out instances are provided when you use the Business Critical tier for Azure SQL Managed Instance. These use an availability-groups like technology to keep databases fresh in a read-only format.

You can offload queries to this read-only replica with near realtime data. However, the instances are tricky to monitor: most 3rd party monitoring systems have no idea what these are, and they don’t emit performance counters like a “normal” instance.

If you have configured a Failover Group and the failover partner is in the Business Critical service tier, that partner will also have read scale-out instances.

Want to use an event file target? Buckle up

I typically like to create both an event file target and a ring buffer target if the trace may run for a while.

There’s a few steps to set up a Managed Instance to work with event file targets. You can reuse this configuration for multiple XEvents traces, if that’s a comfort.

Follow the steps in Create an event session with an event_file target in Azure Storage to do the following in the Azure Portal:

• Identify or create the Azure Storage Account you want to use
• Create a container/bucket in the storage account where your trace files will be written
• Create a SAS token for the container

After this, the article instructs you to run a TSQL command against your Managed Instance to store the SAS token in a server-scoped credential.

Where to run this step: The Managed Instance which is the “base” of the read scale-out instance. This might be your primary writable replica, or it might be the failover partner, or it might be both. (It is NOT the read scale-out instance. Connect with ApplicationIntent=ReadWrite.)

If you aren’t sure if this may have been done before, check if a credential has been set up in your Managed Instance with the following query:

use master;
GO
SELECT * FROM sys.credentials;
GO

The credential name will be something like:

https://exampleaccount4xe.blob.core.windows.net/xe-example-container

The identity will be ‘SHARED ACCESS SIGNATURE’.

Create the trace definition on the primary replica or failover partner – SSMS will get you halfway there

Where to run this step: The Managed Instance which is the “base” of the read scale-out instance. This might be your primary writable replica, or it might be the failover partner, or it might be both. (It is NOT the read scale-out instance. Connect with ApplicationIntent=ReadWrite.)

SQL Server Management Studio (SSMS) only partially supports creating XEvents sessions. You can use the built in wizard to configure the session, but when you go to configure where to write the event file target it will thing the url for the location is an error.

So you need to script out the definition to TSQL, put the URL into the TSQL, and execute it in SQL to create the XEvents trace.

Is this a stack of workarounds in a trench coat? Absolutely.

Make sure the trace definition syncs to the read scale-out

Your trace now should exist on the Managed Instance where you created it (a primary replica or a failover partner).

If you now connect to your read scale-out instance (ApplicationIntent=READONLY), you should see in Object Explorer that the definition has synced over.

I had to open a second instance of SSMS to get Object Explorer to have connections to both an instance and its read scale-out partner at the same time.

Start the trace on the primary replica or failover partner, then on the read scale-out instance

At the time I wrote this, the documentation claimed:

An extended event session on a read-only replica that is based on a session definition from the primary replica can be started and stopped independently of the session on the primary replica.

I didn’t find that to be true. If I attempted to start the XEvents trace on the read scale-out replica at this point, I got the error:

Msg 3906, Level 16, State 2, Line 1 Failed to update database “master” because the database is read-only.

In order to get the trace started on the read scale-out instance, I had to do a little dance:

• Start the trace on the primary replica or failover partner
• Start the trace on the read scale-out partner

I put in a PR to update the docs, so hopefully someone verifies this and figures out if it’s the same or different for Azure SQL Database.

Stop the trace on the primary replica or failover partner

If you don’t need the trace to run on the primary replica or failover partner, you can stop it there. But don’t drop it– you need to leave it there as long as you want to run it on the read scale-out instance.

Hopefully nobody comes along and cleans it up.

Verify your trace is working

Is it writing to the targets and picking up data? After all this, I sure hope so.

The good news is that you can now open the results of event-file traces inside SSMS, like a normal XEvents trace. This isa recent improvement which is VERY nice– you used to have to download the files from Azure Storage, which required stopping a trace and other bother. It’s much more convenient to work with trace data in SSMS now for Azure SQL Managed Instance.