6

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:

http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlconnection.connectionstring.aspx

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?

3
  • 2
    I believe the Failover Partner property in the connection string is only used with Database Mirroring.
    – Jon Seigel
    Feb 26, 2013 at 14:07
  • 1
    Correct, Failover Partner should not be used with AlwaysOn Availability Groups.
    – Brent Ozar
    Feb 26, 2013 at 14:42
  • Hey Tom, Brent updated his answer. Notice I added in your information from your below answer into the question. That's the preferred update mechanism. At this point we probably need to open a new question if you have more questions.
    – jcolebrand
    Feb 27, 2013 at 21:53

1 Answer 1

7

There's two parts to this question:

First, can you use the Failover Partner connection string tip with AlwaysOn Availability Groups? No. AlwaysOn AG's "Listener" technology is the replacement. Have your connection strings point to the listener name and they'll always get the primary replica. (For the next part of this answer, I'm assuming you're using the listener name - if you're not, start, heh.)

Second, why do some queries fail to connect to the listener? This has to do with the number of DNS entries for the listener. All possible subnets for the listener will be in DNS at all times. If you've got a listener in 192.168.1.X and another in 192.168.100.x, both listeners will always be in DNS. By default, your clients will try connecting to each of the DNS entries serially, and not always in numeric order. If you've got a 30-second connection timeout, it's possible that your app will only try one of the IPs and then fail before it has the time to try the second one.

If you want to try connecting to all possible IPs simultaneously, check out the MultiSubnetFailover = True options for the SQL Server client as described here: http://msdn.microsoft.com/en-us/library/hh205662.aspx

Otherwise, you'll need to increase your connection timeout to account for the multiple IPs.

Update Feb 27: the question added, "I was therefore trying to come up with a way to avoid manual intervention for these (replicated) DBs should a failure of the replica occur and the other databases which are part of an AG failover."

Ooo, unfortunately, no, if the databases aren't part of the Availability Group, you're going to be doing manual work in order to fail over. One popular option is to use a DNS record, and just repoint the DNS record at whatever server is currently hosting the primary copy of the databases.

2
  • 1
    "Otherwise, you'll need to increase your connection timeout to account for the multiple IPs." Or if you will never have a client that can utilize MultiSubnetFailover you should set the RegisterAllProvidersIP property of the cluster resource (the AG) to 0 to ensure that only the active IP address is published to DNS. Feb 26, 2013 at 15:33
  • Hi Brent, thanks for the reply, yes I was afraid a manual process may be involved. My manager isn't too happy so I may have to write something to change the web.configs, but thats another tale. Cheers Tom.
    – Tom
    Feb 28, 2013 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.