Failover Compatibility
Overview
The data replicates. Nothing else does.
Logins, server role membership, agent jobs, linked servers, operators, Database Mail profiles and certificates all live at instance scope. An availability group can be perfectly synchronized, with a send queue of zero and every replica healthy, and still fail over into an outage, because the secondary was built by hand eighteen months ago and has been drifting from the primary ever since.
The failover works. The databases come online. Then the overnight jobs do not run, the application cannot log in, the linked server the nightly extract needs is not there, and nobody is paged about any of it because the operator does not exist on this node.
This page is the pre-flight for that. It is run before a planned failover, before patching, and after building a new replica.
The Availability Groups report answers the other half of the question: how far behind each replica is, and whether the failover itself would succeed. The two sit next to each other in the report arrows for that reason.
Where to find it
| Route | How |
|---|---|
| Server Overview | The Availability Groups panel, Check Failover on the right of its header |
| Panel right-click | Right-click the Availability Groups panel → Open Failover Compatibility |
| Server tree | Right-click the server → Instance Level Reports → Failover Compatibility |
| Command palette | Ctrl+K, then type failover |
The Availability Groups panel is only drawn for an instance that is in a group, so the link on it only appears where it means something. The tree item is not gated the same way: gating it would mean asking every registered server whether it is in a group every time a right-click menu is built. Open it on an instance with no group and the page says so.
Requirements
- SQL Server 2012 or newer, on both replicas. The availability views do not exist before it.
VIEW SERVER STATEon both, for the group DMVs and the Agent service state.VIEW ANY DEFINITIONor better, for the logins, roles and permissions.- msdb access on both, for the jobs, operators and mail. A connection that is not
sysadminsees only the Agent jobs it owns, and the page says so in the footer rather than reporting every other job as missing. - The query timeout is 120 seconds per replica.
Every one of those can be absent and the page still answers. A category that could not be read is drawn as not readable, which is deliberately not the same as saying it matched.
The other replica has to be one of your connections
This is the most important thing to understand about the page, and the one thing it will not do for you.
It never opens a connection to a server you have not registered. Every replica in sys.availability_replicas is matched against the instances already in your server tree, three ways, in this order:
- A pairing you set by hand from the row menu.
- The replica name as SQL Server reports it.
- The name each registered connection reports for itself, from
SERVERPROPERTY('ServerName').
The third pass is what makes this work in a real environment. replica_server_name is the cluster node name, or NODE\INSTANCE, while the connection you actually use may be an FQDN, an IP address, a client alias or the listener. Matching on the string alone would report “not registered” for servers that are registered.
A replica that matches nothing is listed with the reason and nothing is read from it:
SQLNODE3 is a replica of this group and is not one of your connections, so there is nothing here to read it with. Add it from the tree and this page will compare against it.
Every registered replica is read when the page opens, not just the one selected. Switching the target in the toolbar is then a redraw rather than another two round trips, and the matrix view has everything it needs. Refresh is what goes back to the servers.
What it compares
Eight categories, in one round trip per replica.
| Category | Compared on | What it costs after a failover |
|---|---|---|
| Logins | Name, SID, type, disabled, password hash, default database, policy | An application that cannot log in, or database users left orphaned by a SID that does not match |
| Server roles | Fixed role membership, user-defined roles, server-level GRANT and DENY |
A login that connects and then cannot do its job |
| Agent jobs | Enabled, steps, schedules, proxies, owner, category, notify operator | Work that stops happening, or starts happening twice |
| Linked servers | Provider, data source, data access, RPC out, login mappings | The nightly extract fails with a name it does not recognise |
| Operators | Email, enabled, on-duty hours, and which alerts notify them | The alerting goes quiet, and nobody is told that it has |
| Database Mail | Profiles, accounts, default profiles, Database Mail XPs, the Agent’s mail profile | Every job that ends by sending mail stops sending it |
| Instance environment | Build, edition, collation, Agent service state and startup type | The failover looks like it worked and the databases will not open |
| Certificates | Name and thumbprint, and how many group databases each holds open | An encrypted database that does not come online at all |
The SID is the one that catches people
A login that exists on both replicas, under the same name, enabled, with the same password, and a different SID looks like nothing is wrong. Every database user inside the group maps to its login by SID, so the moment the group moves, every one of them is orphaned.
That case is blocking on its own, and the page says how many users it affects:
The SID differs, so the 14 database users in the group that map to this login would all be orphaned by a failover.
Counting those users needs a read of each group database, so it is only done from the primary. A secondary that is not readable would refuse every one of those reads, and an empty answer read as “nothing depends on this login” would turn the most serious finding on the page into nothing at all.
A job that runs on both replicas
A job present and identical on both sides is not automatically correct. If it touches a group database and nothing in it checks which replica it is on, both copies fire, and the copy on the secondary fails every time because the database is not writable there. After a failover the two swap over and it keeps happening.
That is its own finding, marked on both, and it is the only thing on the page that is true of two servers at once. Guard the first step with sys.fn_hadr_is_primary_replica and it goes away.
The verdicts
| Verdict | Meaning |
|---|---|
| Blocking | After a failover something is down or unreachable |
| Degraded | It comes up, but something stops happening |
| Cosmetic | A difference that costs nothing. Counted, never in the headline |
| Not readable | No permission, no connection, or nothing registered to read |
Objects that are present on both and identical are counted in the chart and never listed. A page that printed two thousand matching logins would bury the six that do not match.
The headline is built from the counts, so it cannot say something the grid underneath it contradicts:
Not safe to fail over to SQLNODE2: 3 blocking differences and 11 degraded differences.
The three views
| View | Shape | Question |
|---|---|---|
| Ledger | One lane per category, diverging from a centre line | What would this failover lose, and what would arrive? |
| Matrix | Categories against every replica | Which replica was never built to match? |
| Verdict | The whole comparison as one bar | Is this safe, in one picture? |
The ledger is diverging rather than stacked because the answer has two halves that mean opposite things. A stacked bar adds a login the target is missing to a job the target has spare and calls the total eleven, which is not a number anybody can act on. Split at the line, reading straight down the left margin gives the whole of what breaks.
Matched objects are the gap at the centre, so a category with nothing wrong draws as nothing at all.
Clicking a lane filters the grid to that category. Clicking a legend swatch filters to that verdict. Clicking the same thing again clears it, so there is no filter you can get stuck behind. On the matrix, clicking a cell moves the whole page on to that replica.
Fixing what is out of sync
Every fix on this page runs on the target replica, not on the instance the page is connected to. Everything it compares is an instance level object and every one of them is missing from, or wrong on, the target. The confirm dialog names the server before anything happens.
| Action | Where |
|---|---|
| Apply the fix for the selected rows | Row right-click menu |
| Script the fix for the selected rows | Row right-click menu |
| Create every missing object | Toolbar, and the page menu |
| Script every fix | Toolbar, and the page menu |
One row selected and forty rows selected land in the same place. The selection is the scope, and the confirm dialog counts and names what it is about to do.
What can be applied, and what cannot
| Category | Applied | Left to you |
|---|---|---|
| Logins | CREATE LOGIN with the original SID and password hash, ALTER LOGIN for state |
Dropping a login. Repairing a SID, which needs a drop first |
| Server roles | CREATE SERVER ROLE, ALTER SERVER ROLE ADD MEMBER, GRANT and DENY |
Revoking anything |
| Agent jobs | The job rebuilt from the source with its steps and schedules | A job that exists and differs. Rewriting it would throw away its history |
| Linked servers | Script only | The remote password, which is not readable from the instance |
| Operators | sp_add_operator, sp_update_operator, and the alert notifications |
Nothing |
| Database Mail | Profiles, profile accounts, default profiles, the sp_configure enable |
The SMTP password on an account |
| Certificates | Script only | Moving a private key through a file, which is not a button press |
A SQL login is recreated from its password hash, which is readable here, because the login exists on the source. That is the difference between this page and Orphan Users, where the login is gone and the password is genuinely unrecoverable.
What the batch button will not do
Three exclusions, named in the confirm dialog rather than left to be discovered:
- Nothing is dropped, revoked or disabled.
- Nothing that already exists is changed. Only missing objects are created, so a job whose steps differ is never rewritten under somebody.
- Nothing carrying a secret the instance cannot supply is run. A batch with a placeholder in it is not a batch to set going on another server unattended.
All three are still one row-menu item away. The scope is a decision, not a hidden rule.
The ignore list
A comparison of two live instances is noisy in a way a single instance report never is. A job that deliberately runs on one node only, a login that belongs to a reporting tool that only ever talks to the secondary: without a way to say yes, I know, the page is read once and never again.
Stop reporting on the row menu puts an object on the list, and the footer says how many are on it. Report the ignored objects again clears it. The list is about the object rather than about one reading of it, so a job ignored while it was missing stays ignored when it turns up with a different schedule.
Related reports
| Report | Why |
|---|---|
| Availability Groups | How far behind each replica is, and whether the failover itself would succeed |
| Logins | What the instance actually has, and who is a sysadmin |
| Orphan Users | The other half of the SID story: users whose login is already gone |
| Linked Servers | What each link reaches and whether it still answers |
| Failed Jobs | Whether the jobs that do exist are working |
| TDE Status | Which databases are encrypted, and by what |
| Migration Planner | The same question about a server that does not exist yet |
Frequently asked questions
Why does it say a replica is not registered when I can clearly connect to it? The name in sys.availability_replicas is the cluster node name, and your connection may use an FQDN, an alias or the listener. The page probes each registered instance for the name it reports for itself, and if that still does not find it you can pair the two by hand from the row menu.
Why does it not just connect to the other replica for me? Because connecting somewhere you did not ask for is not something a monitoring tool should do quietly. The Availability Groups report follows the same rule.
Why is every job on the secondary listed as missing? Because the connection to it is not sysadmin, and SQL Server shows a non-sysadmin only the Agent jobs it owns. The footer says so when that is the case.
Can it fail over for me? No. It says whether a failover is safe and puts the differences right. Moving the group is done where you can watch it.
Why is a stopped Agent a finding when the Agent is stopped on both? Because two stopped Agents are not a match, they are two instances that run no job. It is judged rather than compared, and so is a build number.
Why is a login only on the secondary not an error? Because nothing breaks when the group moves. It is worth knowing and it is cosmetic. A DENY CONNECT SQL that exists only there is a different matter, and that one is blocking.
Does the compare read every job’s T-SQL? No. The step commands are hashed on the server and only the hashes are compared, so a step that differs is reported without moving the whole of msdb across the wire. The full text is fetched only for the job you ask to script or create.