SQL Server 2019 Cumulative Update 2 is out, and Microsoft snuck in a few things that they didn’t mention in the release notes.
Contained Availability Groups continue to get new investments. In the lead-up to SQL Server 2019’s release, we got a few presentations at various conferences where Microsoft folks said we’d get the ability to put system databases inside a Contained Availability Group. However, things got a little weird leading up to the release, and right now, the only official references I can find are dedicated to Big Data Clusters only. Microsoft published a KB article about some of the fixes in CU2 for contained AGs, but they’ve already pulled the KB articles from public view. Fortunately, Bing and Google still have it cached:
Hmm. I get a little nervous when Cumulative Update documentation pages get updated after their release. Either the CU has a fix for it, or the fix didn’t work, or Microsoft isn’t ready to talk about the feature yet.
Anyhoo, in CU2, there are a few new related CAG goodies:
- sys.dm_exec_sessions has a new column for contained_availability_group_id
- 2 new entries in sys.configurations:
- 1593 – version high part of SQL Server – version high part of SQL Server that model database copied for
- 1594 – version low part of SQL Server – version low part of SQL Server that model database copied for
- New message 47147: Creating contained availability group ‘%.*ls’ failed. When creating contained availability group, neither master database nor msdb database can be included in the CREATE AVAILABILITY GROUP statement. They will be automatically included in the availability group. Remove master database name and msdb database name in CREATE AVAILABILITY GROUP statement and retry the operation.
- New message 47148: Cannot join database ‘%.*ls’ to contained availability group ‘%.*ls’. Before joining other databases to contained availability group, contained availability group master database has to be joined and recovered. Make sure contained availability group master database has been joined and recovered, then retry the operation.
2 new undocumented DMVs: sys.dm_db_data_pool_nodes and sys.dm_db_storage_pool_nodes. Both are empty in my SQL Server 2019 testbeds, and I don’t know whether they refer to Big Data Clusters or something in the cloud.
1 Linux DMV now shows up in Windows: I’m not sure if this is intentional, but sys.dm_pal_net_stats is now shipping with the Windows version.
Some CU2 KB articles have been pulled. For example, there was a KB article about Accelerated Database Recovery silently corrupting data, but that KB article now returns a 404. Fortunately, Bing and Google have caches of it:
Again, I don’t know whether Microsoft pulling the KB article means that they haven’t fixed the bug after all, or that they’re embarrassed about the bug. Either way…this isn’t a great look.
12 Comments. Leave new
I’m so stoked for system DBs (read: msdb) finally/hopefully coming to availability groups, I didn’t even clean the Cheeto dust off my fingers before writing this comment.
Someone bring me my asthma inhaler, posthaste!!
I’ve identified a problem with CU2. I have no Availability Groups configured, but after installing it, the SQL Agent starts polling the database engine every second for AG information. Using SQL Profiler I see the following two queries executing repeatedly by the application SQLAgent – Contained AG:
SELECT N’Testing Connection…’
select ag.name from sys.availability_groups AS ag INNER JOIN sys.dm_hadr_availability_replica_states AS ars ON ag.group_id = ars.group_id where ag.is_contained = 1 and ars.is_local = 1 and ars.role = 1
Steve – OK, cool, thanks for the heads up. I’d open a call to Microsoft support.
Brent – you’re not running a BDC right? the DMV situation for BDC-specific stuff is frightfully sparse at the moment so I’m hopeful that they’re actually adding some features for that (the datapool and storagepool DMVs seem promising.) I’ll probably upgrade my dev/test cluster soon (but not too soon, it seems like this release might be a little shakey at the moment) — thanks for the heads up!!
I am definitely not running a BDC, heh.
We seem to have experienced a problem with CU2. When a SQL job schedule is altered, the SQL server agent stops working.
We had to revert to CU1 to fix it, and are opening a ticket with MS support on it.
Yeah, a few folks reported that at https://DBA.StackExchange.com and https://SQLServerUpdates.com too.
We have same problem here, when Enabling/Disabling Jobs. After a few minutes afterwards the Agent stops working and sp_help_jobs runs into a timeout if you try to run it. after a restart of the Agent Service sp_help_jobs takes < 1 sec.
I am having the same problem with SQL Agent freezing after installing SQL Server 2019 CU2. I currently have an open ticket with Microsoft.
I’ve found that scripting the jobs then deleting and recreating them prevents the issue recurring, at least it does on the 10 instances of SQL Server 2019 I’ve upgraded so far.
Yea, we saw that on one of our test instances too.
On that it seemed to be a single job, using a deprecated way of updating its schedule, that caused the agent to stall.
That wasn’t the case on production though.
I believe Microsoft are looking at few different scenarios which can cause this to happen, with the number of jobs being a factor (we have quite a few jobs).
I contacted microsoft and was told they are already working on a fix on CU3.