September 6, 2016 at 7:40 am
I am currently setting up an AG, 2 node in the primary data center with synchronous commit and a third node in the secondary data center with asynchronous commit.
All three servers will have the same set of databases on.
Question1: Should I name the instances on each node the same?
Question2: Should users \ processes always connect to the instance using the listener name and port?
If i use different instance names they will have to use the listener. But not sure what best practise is?
fast response appreciated
September 6, 2016 at 7:49 am
SQLAssAS (9/6/2016)
I am currently setting up an AG, 2 node in the primary data center with synchronous commit and a third node in the secondary data center with asynchronous commit.All three servers will have the same set of databases on.
Question1: Should I name the instances on each node the same?
Question2: Should users \ processes always connect to the instance using the listener name and port?
If i use different instance names they will have to use the listener. But not sure what best practise is?
fast response appreciated
They should always use the listener. The connection string, however it comes, should have multisubnetfailover=true se, otherwise you are going to have serious problems with your connections. this setting allows the client to poll all ip's behind the listener for the active one off the bat, rather than wait for a timeout before going to the next one (which is generally going to time out). Requires .NET 4.5 + on the client box.
Yes, I'd personally use consistent instance names, but AFAIK that's just personal preference. I like consistency and wouldn't think of doing it any other way
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 6, 2016 at 10:29 am
Thanks for the reply.
Whats the benefit of using the listener and port to connect rather than cluster name and port or instance name if the instance names are the same on each?
I understand that if the instance names are different on each node the listener MUST be used.. but if the instance name is the same, wouldn't it work using the cluster name and port or instance name?
Whats the drawback of using the cluster name?
September 7, 2016 at 2:51 am
If you use a listener device you don't have to worry about routing your write requests to the primary, as the listener will do that for you. So if there's a failover and the primary node changes, you won't have to change your connection strings.
September 7, 2016 at 4:21 am
^^^ Exactly This!
If you have scores of servers and hundreds of systems, manually repointing them all in the event of a datacentre, or even host - loss, for a number of guests is going to be a fraught business and may well cause you to miss your Recovery Time Objectives (RTO).
With third party software, you may get a resistance where the companies don't understand SQL Server let alone HA "we have point to a server, we don't support clusters, HA etc, etc". Basically pointing to a listener is transparent to them, other than the change to the connection string - IF their software supports running on a .NET 4.5+ box.
However, for systems that don't, or ones connection via JDBC <barf> and therefore don't support the multisubnet failover option, you'll need to consider
1) in the event of a failure they're going to be impacted more severely than other systems as you're going to have to intervene manually due to the way the software is put together and that there will be a delay in getting them live again
2) get written agreement from senior management (preferably C level) as to what your priorities are in this situation. When you have X business systems owners demanding THEIR SYSTEM IS THE PRIORITY, you need to have your list agreed, as well as the escalation points. It may be that system Y, which is not normally a priority is actually the critical priority at this point
3) feed back the results to your system owners so they have the opportunity to buld their contingency plans. Obviously, they may not do so, but that's their problem, not yours, you've let them know
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
September 7, 2016 at 9:05 am
Appreciate the feedback guys - cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply