We have a situation where we are using replicated databases in an environment that's making use of Availability Groups. As we wanted no manual intervention when failing over, we decided to set up subscriptions to write replicated data to both of our replica servers. As these databases will only ever be read from, this architecture should be ok.
To further configure this and after reading this article by Brent Ozar, we set the connection strings in our apps that used said databases with a failover partner such that if we lost our primary replica then our app would still work.
After a recent test, failover and fail back, we have now started receiving some intermittent errors from our clients along the lines of:
System.InvalidOperationException: Server xxx, database xxx is not configured for database mirroring.
At present I am confused as to why this error is appearing. There has been some suggestions of adding a connection timeout to the connection string, however that doesn't make sense to me as if the primary timed out then reading from the secondary should work, and in our case is valid as it's an exact copy.
If anyone has any ideas I would be grateful.
EDIT: Further to this I have now removed the failover partner from the connection string and left it pointing at the DB on the replica, however we get errors of:
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.)
Its like the client has cached the secondary server somewhere?
Edit: in response to Brent's answer
Hi Brent thanks for that, however I wasn't clear in my initial description. The replicated databases are not part of an availability group but instead I have a subscription on each node because, as I understand it, replicated DBs are not well supported in AGs.
I was therefore trying to come up with a way to avoid manual intervention for these DBs should a failure of the replica occur and the other databases which are part of an AG failover.
I was thinking that while Node A was up all connections would go to that server, but if Node A failed then connections would go to Node B when using Failover Partner. However this does not seem to be the case and actually according to this article:
If you specify a failover partner but the failover partner server is not configured for database mirroring and the primary server (specified with the Server keyword) is not available, then the connection will fail.
This Failover Partner configuration seems not to be supported for how I am attempting to use it, but leaves me with a situation where manual intervention is required for any failover :( unless I am missing something?