Had an issue with a SQL Server Always On Failover Cluster Instance.

Lots of things happened at once that caused this thing to fail, It was not a production Always On cluster but one used for Load and Performance testing so it isn't monitored as closely as it should have been.

A bunch of windows patches were applied, than the SAN Storage had an issue, the end result was a mess and we had to delete the availability groups and reset them up, in the process of doing so we received this error:

Create failed for Availability group {name here}.

Failed to create, join or add replicat to availability group {name here}, because node {Server} is a possible owner for both replica {replica instance} and {primary instance}.  If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again (Microsoft SQL Server, Error: 19405).

This is in a bit of a complicated environment, where we are using Always On, to off-load read-only queries with the Read-Intent attribute.  So it's a 3 node cluster, with a SQL Server Fail over cluster on nodes A and B, and a Stand-alone Instance on node C where we host the replica's to handle the read-only queries.  Works Great, till you screw the environment over with patching and san maintenance all at the same time.

This error means exactly what is says, but it took me a while to figure out that some how the sql server instance in cluster manager, was marked as having a preferred owner of all 3 nodes.  Which isn't possible as the SQL FCI was only able to be nodes A and B.  Simply unchecking the 3rd node fixed the issue, though it threw a sub-sequent error that the availability group already existed, so we had to go in and manually run a tsql command to delete the availability group on both the primary and replica instances.

There is always a need for reporting, but remembering those requirements is almost always left out, and always there is a desire to use SQL Server Reporting Services (SSRS) or SQL Server Integration Services (SSIS) and sometimes in combination with SQL Agent to get these done.

Sometimes there are environmental or local policies that prevent the use of those technologies, but it's still easy to get done.  Attached here is a simple script for sending the results of a query via Email.  It could easily be enhanced to take the query, subject and to lines as attachment so it could be used generically for different results.  Scheduling can easily be done with windows task scheduler.

This example uses vb script, just rename it to have .VBS extension, but could easily be done in power shell as well.

Email_Query.txt (5.08 KB)