This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(195.2, 63%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
Hello, We are planning to upgrade SQL 2019 to SQL 2022. As with SQL 2022, we can add master & msdb databases to the SQL Alwayson Availability group, so that local SQL logins along with AD logins and SQL jobs can be synced between the primary and secondary replica's of AAG, that's the primary reason we are thinking about upgrading to SQL 2022. As we really need that feature in our environment. Wondering if any one is doing the same in your environment. Appreciate your feedback. Also please refer me to any links I can use in setting up for adding master & msdb databases to SQL 2022 AAG. Thanks.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 89%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
Hi @SQLDev
Did these answers help you? If the answer is useful, please click Accept Answer to let others with similar questions quickly find answers. If you have any questions, please feel free to let me know.
Best regards,
Aniya
Hi @SQLDev
I am using Contained Availability Groups in SQL Server 2022. But note that you have to re-create the AG with the contained option after the upgrade. They are quite good actually but you have to consider what I have mentioned
Hope it helps
Regards
Javier
In addition to what Javier says: notice that you don't mirror master and msdb of the instance, rather a contained AG has its own master and msdb. This can cause some confusion. If you connect directly to an instance, you will see the master and msdb of that instance. The master and msdb of the AG, will appear as AGname_master and AGname_msdb. If you connect to the listener for the AG, these databases will be your master and msdb.
In difference to Javier, I have not used contained AG in production, but only played with them in my lab. (My professional role is a systems developer, not a DBA.)
Thanks for quick responses. So the master & msdb databases will be recreated using Contained Availability Groups after upgrading to 2022. Can the user databases be part of that Contained Availability Groups as well or does those user databases be in a regular availability groups like we had in previous versions?
when you create a new Contained Availability Group (CAG) it will create a custom version of master and msdb which will be visible within that CAG only. a user database can be part of only one AG or CAG
To illustrate, this is how the database dropdown looks like when I connect to my instance, AGGE2_
When I connect to the listener for the contained AG, I see this:
It does take some time wrap your head around how contained AGs work, and I definitely recommend that you read the topic Contained Availability Groups, which Javier also linked to. One thing which is very important is that although a user may have one set of permissions in the master of the AG, and another on the instance, the AG is not a security boundary.
(By the way, you had posted a followup question as an Answer. Answers are only for answers to he original question. Therefore I converted your Answer to a Comment.)
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 79%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Yes, SQL Server 2022, a new feature called Contained Availability Groups was introduced. This is essentially a unique form of Availability Group that handles server-level elements required by databases within an Availability Group to maintain consistency across all replicas. These elements can include logins or SQL Server Agent jobs. By utilizing Contained Availability Groups, the need to recreate and remove server-level objects across secondary replicas is eliminated.
Since logins and SQL Server Agent jobs are created in the master and msdb databases, respectively, Contained Availability Groups will have their own copies of master and msdb. The server-level objects that exist within the Contained Availability Groups are limited within its boundaries. What that means is only the logins and SQL Server Agent jobs created within the context of the Contained Availability Groups will be created in those system databases.
It will be look like below-
Thank you!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(230.39999999999998, 32%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERG%3C/text%3E%3C/svg%3E)
I have two servers running SQL Server 2019 in a primary-secondary Always On configuration, and I want to upgrade them to SQL Server 2022. If I upgrade them separately, will Always On remain uninterrupted?
My plan is to first upgrade the secondary server to SQL Server 2022, then failover the primary role to it and upgrade the original primary server. Will my servers continue running without downtime during this process