Was working with a client who had an existing Availability Group, but did not have the RegisterAllProvidersIP setting enabled on their cluster. So, recent changes to their connection strings to take advantage of MultiSubnetFailover to minimize failover re-connection delay wasn’t helping. Multisubnet failover needs to see both Listener IP’s to help, so RegisterAllProviderIP’s is required to be ON.
Here’s why.
With RegisterAllProvidersIP disabled on the client access point (the cluster network), only one IP address is made available for the listener. When enabled, all site IPs for the listener are simultaneously listed. Connection strings using MultiSubnetFailover will try out all IPs simultaneously, providing for the fastest possible transition after an availability group failover.
Caveat remains that any connection strings that aren’t or can’t use MultiSubnetFailover will have problems. So RegisterAllProvidersIP should be enabled only when MultiSubnetFailover can be used in all application connection strings. More information here. You really do need to understand the applications and their connection strings here, this has little to do with the SQL Server and more to do with apps and DNS aliases.
In the case of the client above, enabling RegisterAllProvidersIP on an existing, in-use cluster is possible, but does require a brief outage.
Here is the script to use via PowerShell.
Comments guide along the way.
nslookup AAGListenerName #With RegisterAllProvidersIP = 0, this will only return one IP, the IP of the current primary.
#You'll get a warning, all changes will take effect until ClusterNetworkName is taken offline and then online again.
nslookup AAGListenerName #This should now return Two IPs, for both the primary and secondary. Now, MultiSubnetFailover can help.
UPDATE: Thanks to a commenter, updated the 0 to 1 in the set-clusterparameter line.
Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?
Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.
Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.
