September 28, 2023 at 8:31 pm
Good afternoon!
Is there any type of documentation or process for failing over a CMS server? Do I have to remove them from Primary and add them to secondary (new primary)? This is cumbersome work. Let's assume it's HA and failed over every 2 weeks. Or assume a DR failed over for over a month.
Please advise.
Thank you in advance.
¤ §unshine ¤
September 29, 2023 at 8:36 am
CMS data is stored in MSDB.
As MSDB isn't part of an AG, setting up a CMS on an AOAG is impossible (I mean you could register it against the listener but on failover all the info is not there as the MSDB's don't sync), and I even guess now in SQL2022 with contained system databases within an AG that the CMS stuff isn't part of that contained MSDB as it isn't pertinent to the operation of the AG.
So yeah, if you have a CMS and you use a listener to connect to the CMS, then when it fails over the info wont be there, so you will need to ensure you use the standalone replica name to connect to the CMS, find a way to sync the CMS data between the replicas in the AG periodically.
September 29, 2023 at 5:34 pm
Why do you need your CMS to failover? Unless the entire server is unavailable, then you can always connect to the server where your CMS is located.
You can automate the process contained in this article:
https://www.sqlservercentral.com/blogs/centralized-management-server-101
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 4, 2023 at 5:16 pm
My implementation was really simple, I mostly only scheduled jobs in it. My CMS was an FCI for local HA and I used dba tools to copy the jobs to a DR CMS so they would be there available in the case I ended up operating out of DR for an extended period that would need to make job changes. It also synchronized my scale out SSIS jobs that could fail over transparently to the new master at DR and the SSIS catalog database was kept in the AAG, replicated to DR.
All the CMS jobs had something in their name to indicate they were a centrally managed job as well as the database and server group they ran against. If it needed to be switched over, we would just need to pull the target servers out of the original master (this is going back a few years, struggling to remember the terminology of functions we never used) and then register the DR server into the new CMS and edit jobs to target the remaining DR SQL server for all the centrally managed jobs.
It would have been a manual pain to reconfigure all those jobs, but our primary datacenter was so robust that it was our assumption anything big enough to get is to failover to DR would cause a very long change freeze and was unlikely to need to make any changes.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply