June 28, 2018 at 11:55 pm
Hi all,
I have several SQL instances, few with Always on AG activated and few with out Always on AG group.
I have tried writing connection string which is working on both type of SQL instances. Below is the connection string i used in .net application
Provider=SQLNCLI11.1;User Id=sa;Password=*********;Initial Catalog=TEST1;Data Source=DC01;MultiSubnetFailover=true;
This connection string works well with the SQL instances where Always on is not activated. But I found an article warning not to use "MultiSubnetfailover" parameter ON non SQL always on instances (refer below URL for the same).
https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-2017
It says “Specifying MultiSubnetFailover=Yes when connecting to something other than an availability group listener or Failover Cluster Instance may result in a negative performance impact, and is not supported.”
Can anyone please let me know How far it is safe to use this parameter in SQL instances with out Always on feature activated?
- Prasad
July 3, 2018 at 7:31 pm
Deeptiprasad - Thursday, June 28, 2018 11:55 PMHi all,I have several SQL instances, few with Always on AG activated and few with out Always on AG group.
I have tried writing connection string which is working on both type of SQL instances. Below is the connection string i used in .net application
Provider=SQLNCLI11.1;User Id=sa;Password=*********;Initial Catalog=TEST1;Data Source=DC01;MultiSubnetFailover=true;
This connection string works well with the SQL instances where Always on is not activated. But I found an article warning not to use "MultiSubnetfailover" parameter ON non SQL always on instances (refer below URL for the same).
https://docs.microsoft.com/en-us/sql/relational-databases/native-client/features/sql-server-native-client-support-for-high-availability-disaster-recovery?view=sql-server-2017It says “Specifying MultiSubnetFailover=Yes when connecting to something other than an availability group listener or Failover Cluster Instance may result in a negative performance impact, and is not supported.â€
Can anyone please let me know How far it is safe to use this parameter in SQL instances with out Always on feature activated?
- Prasad
I don't know and it's likely that it may not have been around long enough for people to see issues with using multisubnetfailover when not needed.
If you are changing the data source is there a reason you can't or wouldn't want to change the multisubnetfailover at the same time?
Sue
July 4, 2018 at 5:02 am
Why are you using sa? If you are connecting to the listener, why do you need to specify MultiSubnetFailover=true?
July 4, 2018 at 5:17 am
@rick, Could you please give pointers on what are the scenarios where we specify MultiSubnetFailover=true?
@Sue_H, We need this generic connection string which can be used across applications so that it will be used if multiple subnets are available and it won't be used if single subnet is present.
Correct me if you think i am following a wrong approach.
July 4, 2018 at 6:10 am
Deeptiprasad - Wednesday, July 4, 2018 5:17 AM@rick, Could you please give pointers on what are the scenarios where we specify MultiSubnetFailover=true?
@Sue_H, We need this generic connection string which can be used across applications so that it will be used if multiple subnets are available and it won't be used if single subnet is present.Correct me if you think i am following a wrong approach.
How can you have a single generic connection string and you connect to different instances, some of which are always on and some are not?
You connect to the different instances and you don't change the data source in the connection string?
Sue
July 4, 2018 at 6:12 am
As Sue says, you shouldn't really use a generic connection string.
July 4, 2018 at 11:05 am
Beatrix Kiddo - Wednesday, July 4, 2018 6:12 AMAs Sue says, you shouldn't really use a generic connection string.
I've seen that question before and it's never made any sense. If you use the same instance/data source, you would be connecting to the same instance every time.
I've always wondered if it was more that they need to have the application layer using logic to decide which connection string to use. Doing that is fairly common.
Sue
July 4, 2018 at 9:39 pm
Sue_H - Wednesday, July 4, 2018 11:05 AMBeatrix Kiddo - Wednesday, July 4, 2018 6:12 AMAs Sue says, you shouldn't really use a generic connection string.I've seen that question before and it's never made any sense. If you use the same instance/data source, you would be connecting to the same instance every time.
I've always wondered if it was more that they need to have the application layer using logic to decide which connection string to use. Doing that is fairly common.Sue
@sue, i think i was not clear in explaining u the issue in first place..let me rephrase it.
i have one installer which installs multiple applications. And during installation it puts the connection string at various places such as environment variables, registry and few application property files. Once installed I can not expect clients/users to go and change connection string @ several places.
Now the initial installer i can ship to multiple clients. few clients are willing to configure always on feature and few are not. So my intention is my installer should put a generic connection string which will work for all my clients irrespective of whether they r using sql always on with multisubnetfailover parameter or not.
I hope now i am clear in explaining my issue...
thank for responding @sue.
July 5, 2018 at 12:04 am
July 5, 2018 at 8:13 am
Deeptiprasad - Wednesday, July 4, 2018 9:39 PMSue_H - Wednesday, July 4, 2018 11:05 AMBeatrix Kiddo - Wednesday, July 4, 2018 6:12 AMAs Sue says, you shouldn't really use a generic connection string.I've seen that question before and it's never made any sense. If you use the same instance/data source, you would be connecting to the same instance every time.
I've always wondered if it was more that they need to have the application layer using logic to decide which connection string to use. Doing that is fairly common.Sue
@sue, i think i was not clear in explaining u the issue in first place..let me rephrase it.
i have one installer which installs multiple applications. And during installation it puts the connection string at various places such as environment variables, registry and few application property files. Once installed I can not expect clients/users to go and change connection string @ several places.
Now the initial installer i can ship to multiple clients. few clients are willing to configure always on feature and few are not. So my intention is my installer should put a generic connection string which will work for all my clients irrespective of whether they r using sql always on with multisubnetfailover parameter or not.
I hope now i am clear in explaining my issue...
thank for responding @sue.
Thanks for that extra information. A software company I worked at uses parameters with the installers for things like this. Works pretty well and they also have a large customer base with several different applications and components. Used by 85 of the Fortune 100 companies so not too bad.
Sue
July 5, 2018 at 8:27 am
MultiSubnet is only needed when there are different subnets for the primary and secondaries.
if you are connecting to the listener, as opposed to the individual server, this would be needed for the application to connect in the event of a failover.
What SQL will do is attempt to connect to both IP addresses at the same time. The active IP will connect.
So, if one server is 10.10.1.1, and the secondary is 10.10.2.1, then this would be required for an automated failover to occur.
Your listener will need to be configured with BOTH IP addresses.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply