Resizing operations - getting notifications on Managed Instance & regular SQL DB

How to monitor the progress of a resizing operation. (see previous post for Elastic Pool changes)

Managed Instance:

•  Get-AzSqlInstanceOperation -ResourceGroupName "myrgnamehere" -managedinstancename "myservernamehere"

Azure SQL Database, not elastic pool. 

•  get-azsqldatabaseactivity -ResourceGroupName "myrgnamehere" -servername "myservernamehere" -databasename "dbnamehere"

Note that instead of IN_PROGRESS, for both of these the state is "InProgress". Sloppy, Microsoft, sloppy.

Oh, but at least they start reporting info shortly after it starts!

$activity = (the above)

$latest_activity = $activity|sort -Property StartTime -Descending |select -first 1 #unsure if I need this, but what the heck, still works

if ($latest_activity.state -ne "INPROGRESS" -and $latest_activity.StartTime -gt [datetime]"2025/02/13 19:00:00")

{

#notification here

}

Azure MI - Failed email -2147467259

A job step on an Azure Managed Instance that calls msdb.dbo.sp_send_dbmail (or possibly calling a linked server) can fail with error -2147467259.

The fix is to put "EXECUTE AS LOGIN = 'SQLAGENTUSER' " at the front of the code inside the job step. What's happening is that the Login that's used by the agent (ContainerAdministrator) is lacking perms (MSDB for email, but also linked server).

Azure SQL Managed Instance - a simple Extended Event to track logins

 This is a barebones Xevent (Extended Event) . I'm not using blob storage, just the existing "eh, you have a couple hundred meg worth of ring buffer you can use". But I needed to see who was using one of our dev instances. I thought we'd moved everything over to the Azure SQL DBs, since there's a ton of reasons to do so.

Here's some quick off-the-cuff reasons why I prefer Azure SQL DB to Managed Instance:

1. Spins up/down FAST.  Seconds to minutes. Have a 20gb database? Takes 2 minutes. 30gb? 3.
2. More availability! Technically more servers!
3. Serverless options! Allows you to just pay for the price of storage, not compute. Doubly useful for QA/Dev boxes that aren't always used. Also allows us to auto scaleup/down as traffic changes.
4. Works with our super-limited networking.

Yes, there are downsides. CLR, Replication, Agent, Mail, others. But the wins of using Azure SQL DB are very tangible.  < / soapbox > 

Anyhow, script:

@@

/* 

CREATE EVENT SESSION [Logins] ON SERVER 

ADD EVENT sqlserver.login(

ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name,sqlserver.username))

ADD TARGET package0.ring_buffer(SET max_memory = 4096)  --max_event_limit threw error!

go

ALTER EVENT SESSION [Logins] 

ON SERVER 

STATE = start ;

GO*/

if object_id('tempdb..#xevent') is not null

    DROP TABLE #xevent

CREATE TABLE #xevent (target_data XML)

INSERT INTO #xevent (    target_data)

SELECT target_data

 FROM sys.dm_xe_session_targets        AS st

    INNER JOIN sys.dm_xe_sessions       AS se

         ON CAST(se.address AS BINARY(8)) = CAST(st.event_session_address AS BINARY(8))

WHERE

se.name = 'Logins'

DECLARE @count VARCHAR(10) 

SELECT @count = target_data.value('/RingBufferTarget[1]/@eventCount','varchar(10)') 

FROM #xevent --null means it's not on or not "installed"

PRINT 'eventCount = ' + @count

;with events_cte as(

SELECT 

ed.c.value('(@timestamp)[1]', 'DATETIMEOFFSET') at TIME ZONE 'Central Standard Time' AS err_timestamp,

--xevents.event_data.value('(RingBufferTarget/event/@timestamp)[1]', 'datetime2')) AS [err_timestamp],

ed.c.value('(action[@name="client_app_name"]/value)[1]', 'nvarchar(max)') AS client_app_name,

ed.c.value('(action[@name="client_hostname"]/value)[1]', 'nvarchar(max)') AS client_hostname,

ed.c.value('(action[@name="database_id"]/value)[1]', 'int') AS database_id,

ed.c.value('(action[@name="database_name (Action)"]/value)[1]', 'nvarchar(max)') AS nt_username,

ed.c.value('(action[@name="username"]/value)[1]', 'nvarchar(max)') AS username

, ed.c.query('.') AS event_data  --we leave this off since it makes this run SO MUCH LONGER.

FROM #xevent a

cross apply (select CAST(target_data as XML) as event_data) AS xevents

CROSS APPLY event_data.nodes('/RingBufferTarget/event') AS ed(c)

)

SELECT *

--, events_cte.event_data

from events_cte 

order by err_timestamp desc;

@@

Xquery - a surprising difference between Azure SQL Database and Azure Managed Instance with Quotes

I'm moving processes into Azure.  Given that it's me, probably poorly. But I'm using this as an opportunity to improve things where possible, specifically looking at moving some processes to Azure SQL Database. 

Why? I love them for several reasons: 

• Simpler
• Cheaper
• Quick to create
• Quick to modify the instance (seconds, not hours)

Yes, there are still gotchas (Elastic Jobs, no cross-db queries, no CLR, can't replicate from it), but overall? Soooo nice.

---

I was trying to get some Xquery parsing working, and ran into a weird error:

"CONDITIONAL failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'"

Weird.  Doubly so because this code has worked on-prem for a decade, and the Managed Instance is running it now.  And because the SP specifically has a SET QUOTED_IDENTIFIER ON; 

Okay, let's take alook online.

https://stackoverflow.com/questions/47091181/jobs-failing-due-to-incorrect-settings-quoted-identifier

Yeah, that's the problem.  And indeed, my code has similar xquery to the example:

set @myval = @xml.[value]('(*/A[B="C"]/D[E="F" or G="H"]/I)[1]', 'varchar (35)');

What's the fix? Use two single-quotes instead of the double quote.

set @myval = @xml.[value]('(*/A[B=''C'']/D[E=''F'' or G=''H'']/I)[1]', 'varchar (35)');