New to AG - have inherited my first existing system. Have spent quite some time reading MS docs and watching a few training videos but a) concepts seem blurred (terminology similar between different technologies) and b) all this reading raises more questions than answers.
My question is essentially how to proceed with taking over and managing this AG, and how to prepare for, and carry out, some failovers in order to upgrade the OS on the servers?
- There are 3 replicas - SQL01, SQL02, SQl04. SQL01 is currently primary.
Firstly - is there a good script to give me an overview of AG configuration and health? I understand the console in SSMS gives me an overview, but is there no script that can automate the task and be used to detect or report on servers lagging behind, etc?
Secondly, how can I check whether any secondary has been set up for read-only routing, and/or backups? I believe backups are run only against SQL01, so what happens if/when there is a failover making SQL02 the primary?
Thirdly and probably related - if the AG exists to route queries to a given node, I can understand that all databases are kept in sync so that applications can continue querying and any failover between nodes should be transparent to the application, but if backups (or mailing of query/report results) are run by scheduled Agent jobs, how are these handled after failover? Is this something manually kept in sync by the DBA?
Fourth, along similar lines - is it conceivable that there are differences between databases across two nodes. ie. I'm not talking about differences that will synchronise automatically; I mean might someone have made a change - such as adding a trigger to a table - on a replica that won't have replicated across all nodes?
Fifth - to start thinking about manual failovers, what things should be checked ahead of failover? I saw a question here talking about checking how quorum is formed - do I need some kind of report on Quorum setup before I fail over? If so, what am I looking for?
Sixth, another question here mentions checking the is_failover_ready column on sys.dm_hadr_database_cluster_states - but when I query that table, I'm told "invalid object name". What gives?
Seventh To the specifics of OS upgrade, what is the basic approach - Should I, say, take SQL04 off the AG (or pause syncing), then upgrade its OS, restart the machine, then re-add to the AG / continue syncing? If so, how? And how do I know at the end of everything that all is ok? What would be the "rollback plan" if something doesn't work out as hoped?
Eighth Continuing with the OS update, do I do the same thing for SQL02, then failover from SQL01 to SQL02, upgrade SQL01 OS then fail back? Again, how to determine each step is "successful"?
Ninth - what happens if I initiate a manual failover but SQL01 is in the middle of backing up 20 or so databases? Is there something to check for me ahead of time, on how database backups might be affected, or something to check after failover, to ensure database backups are still functioning as required, post failover? We have full nightly backups on all databases plus partial and incremental backups - but only on some databases.
Tenth - similarly, what if automated jobs are running at the time of failover? These might be Agent jobs updating data or running queries for mailout, or they might be a third-party BI system pulling data for ETL prior to import into the BI end. Is it safe to presume the BI systems will continue receiving their data post-failover, or should I be checking how they're making their connections to the DB first? (Is it possible they are connecting to SQL01 specifically (or any other node really), in spite of the AG, so that when SQL01 goes down for the OS upgrade, the BI extract fails?
Like I said, have put a fair amount of reading into AGs overall, but not easy to find these big picture answers :/