Call to action for Microsoft. Contained Availability Groups came out in SQL 2022 and they definitely have their use. But there were some artifacts left behind that need some fixing. Namely when you use DBMail while in the Availability Group jobs or operations. Let’s see what there is left.
First, here is the link to the Feedback Item that is out there for voting to get Microsoft to fix this issue. There has already been an issue fixed with the msdb proc to activate dbmail in a Contained AG ([dbo].[sp_sysmail_activate]).
What is the Issue?
The issue is that within a Contained AG the master and msdb are actually named AGNAME_master and AGNAME_msdb which in the procedures mentioned use the following code which uses the original msdb database instead of the AGNAME_msdb database.
|
1 2 |
-- sp_RunMailQuery is used to run a query result into the mail message SET @mailDbName = db_name() -- which will return msdb instead of AGNAME_msdb |
The fix is to have this code instead so that it uses the real database name since it is in a contained availability group which makes the AGNAME_msdb look like msdb when connected to the AG Listener. See below.
|
1 2 3 4 |
-- Note: we cannot use DB_NAME() here since this SP could be running in the context -- of a contained AG: in that case, we really want to fetch the physical name of the DB SELECT @mailDbName = physical_database_name FROM sys.databases WHERE database_id = DB_ID() SET @mailDbId = DB_ID() |
Notice how the database name instead of just being DB_NAME() it is using the physical_database_name that is in sys.databases for this DB_ID()? This is the fix. You can make the fix yourself until Microsoft gets to it in the feedback item.
If you have not applied the latest SQL 2022 CU to your servers and you run a Contained Availability Group, then you should look to update your server so that you have the most up to date fixes in SQL Server. So far this fix is not in a CU, but the previous one for dbo.sp_sysmail_activate has been fixed in one of the CUs previously.
Pingback: Contained Availability Groups and Database Mail – Curated SQL