February 22, 2022 at 6:46 pm
Hello, I'm working on migrating from an existing multiple FCIs to Instances with availability groups. I'm attempting to keep the impact to my customers as small as possible so I'm wondering about connection strings with availability groups.
Current Environment, multiple instances some as named instances in existing FCI
SQL1 and SQL2\NamedInstance
Proposed new environment.
Physical machines\default instances: SQL3 and SQL4
Ag Listener's Names such that client connection strings do not need to be updated:
SQL1 and SQL2.
Is there a way to create an AG listener so that it will listen for SQL2\NamedInstance, or do all of the applications using SQL2\NamedInstance need to be updated to use SQL2?
I haven't seen much documentation on AG Listeners that use named instances, so I'm assuming the answer is no. If you have any documentation you can point me to that would be great.
Thanks,
-Luke.
February 22, 2022 at 9:57 pm
Before you set up an AG from a cluster, one of the main differences is that there is no shared storage. While you need a cluster to run an AG, there is no clustered storage. Are you planning on "breaking" your current cluster, and adding a second set of disks for the other server?
You would create the AG, and add the servers to the AG as replicas. It does not matter that you have a named instance. Those are added in the same manner. I have multiple AG's that have replicas with named instances.
You would then create the listener, which will connect to whatever server may be the primary.
You connection strings / applications would then connect to the listener. If one was a read-only secondary, as an example, that was only to be used for reporting, you could connect directly to that server. In the event of a failure all traffic would be hitting the same server. Your applications would experience a minor loss of connectivity in the event of a failover
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/
February 22, 2022 at 10:09 pm
Typically you would just connect to the listener not listener\instance.
As you can span different named instances as replicas when you failover, you would break the connection string.
There is also no warranty that a patch also won’t break the functionality.
2019 RTM has some issues using instancename with a listener, it was fixed in a patch later on, but if it was broke once, then it can break again.
For safety just use the listener nothing more, nothing less, don’t over complicate things.
February 23, 2022 at 3:41 pm
Thanks for the information. Yes we'd already taken into account additional disks etc. It was more a question of how impactful we'd need to be to the connection strings for existing applications. We've been trying to limit the changes needed to make this happen. If that's not an option, we'll have to engage many more resources in order to get the migration to happen in a timely manner.
Thanks again for the responses.
-Luke.
February 23, 2022 at 8:00 pm
SQL1 and SQL2 are presently identities in your domain. They have machine accounts and DNS entries. Creating a Listener with the same network name as another server on the network is the same thing as trying to build two separate servers with the same name.
There are some stunts you can pull with DNS and fiddling with the SANs on SSL certs to make one server pretend to be another one on the network, but it's messy and error-prone. Ultimately "SQL1" will resolve to a single network address. If you want to use a Listener with the name of "SQL1", you would need to tear down and decommission any existing server/service using the "SQL1" network name.
Eddie Wuerch
MCM: SQL
February 23, 2022 at 8:45 pm
Perhaps I wasn't clear.
@eddie-2 Wuerch, yes what we typically have done in the past with existing FCI's is get everything setup and ready, replicate the data to new instance, then during cutover we stop replication, and rename the Role's Name to swap new for old. With appropriate AD permissions this is straightforward and just works, as long as you perform the rename in master afterward etc. We're familiar with using DNS to enable existing applications to work with no changes. That is not what I'm questioning.
In short what I'm looking to do is know if having instance names in the connection strings will work with an AG Listener.
So if today I have connections made to SQL2\MyInstance and I use whatever DNS or other AD magic it takes to get an AG listener named SQL2 pointing to databases on my new servers.
Is there a way to setup the listener so that it will listen for SQL2\MyInstance? And will that listener accept connections as SQL2\MyInstance no matter which is the primary replica the database even what the local instance name (Default or otherwise) might be?
Or will I have to have all of the customers change their connection strings to connect to SQL2? I ask because I'm talking about a hundred or more different applications connecting to these databases... So changing the connection strings will be less than fun.
If we have to change the connection strings anyway, we'll just setup the listeners with new names in advance and have them change the connection strings in advance of the cutover to limit downtime, but ideally I'd prefer to not have to coordinate hundreds of connection string changes.
Thanks,
-Luke.
February 23, 2022 at 9:07 pm
Perhaps I wasn't clear.
@eddie-2 Wuerch, yes what we typically have done in the past with existing FCI's is get everything setup and ready, replicate the data to new instance, then during cutover we stop replication, and rename the Role's Name to swap new for old. With appropriate AD permissions this is straightforward and just works, as long as you perform the rename in master afterward etc. We're familiar with using DNS to enable existing applications to work with no changes. That is not what I'm questioning.
In short what I'm looking to do is know if having instance names in the connection strings will work with an AG Listener.
So if today I have connections made to SQL2\MyInstance and I use whatever DNS or other AD magic it takes to get an AG listener named SQL2 pointing to databases on my new servers.
Is there a way to setup the listener so that it will listen for SQL2\MyInstance? And will that listener accept connections as SQL2\MyInstance no matter which is the primary replica the database even what the local instance name (Default or otherwise) might be?
Or will I have to have all of the customers change their connection strings to connect to SQL2? I ask because I'm talking about a hundred or more different applications connecting to these databases... So changing the connection strings will be less than fun.
If we have to change the connection strings anyway, we'll just setup the listeners with new names in advance and have them change the connection strings in advance of the cutover to limit downtime, but ideally I'd prefer to not have to coordinate hundreds of connection string changes.
Thanks,
-Luke.
No, you do not understand.
You apps need to connect to the listener. It cannot be the same name as an existing server or instance. What Eddie is saying is that you may be able to "trick" things with DNS or certificates so that your apps can still use "SQL1" in the connect string which will actually translate to the listener name.
So, as we understand it, you have a an AG made up of SQL1 and SQL2\NamedInstance . The servers are named SQL1 and SQL2. You want to create a listener name SQL2.
That will not work. Your AD has 3 unique objects, plus a 4th for the cluster. Adding a new object to AD that shares one of those 4 names will not work.
Your AG and AD will need these objects and IP addresses
SQL1, SQL2\instance, Listener, and Cluster
You can attempt to re-direct connections to the listener via DNS by creating a DNS entry of SQL2 that maps to the Listener. That can be problematic.
You can also attempt to create aliases for the listener, although I'm not sure that will work. What needs to be done is that the connect strings need to be changed. I am curious why this is a difficult task.
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/
February 23, 2022 at 9:40 pm
Nope, It's no that I don't understand. I do truly, I've done this a number of times before where I've renamed a traditional FCI. I understand the computer objects that the AG listener uses. I understand what that would mean to change that. I tried to simplify the environment to make it easier to ask the question and probably did a poor job of that. I'm not planning on adding a new object with the same name as one of the old ones, or at least not until the old name has been renamed.
Let's not get hung up on the names of the objects.
Can I make a connection to SQL2\MyInstance when the ag listener is named SQL2 and have it connect to a database (DB1) that lives on MyNewServer(DefaultInstance) - Primary replica and MyOtherNewServer(Myinstance) - Secondary Replica
Will the AG Listener and/or SQLBrowser be smart enough to ignore the instance name and just make the connection? I'm assuming not, but I've been unable to find any information about AG Listeners with instances names, which is why I'm asking.
The reason I'd like to avoid changing the connection strings is over 100 applications on various app servers, including third party vendors that I'd need to involve, connecting to a few hundred databases across 4 instances.
While yes, I get that changing a connection string is minimal, changing connection strings on many servers, which would need to be done by other teams (server admins, programmers etc), including outside vendors is not a small undertaking and the time/cost likely wouldn't be approved by management.
If that would be required I'd probably just stick with the existing traditional FCI infrastructure. This is currently a test environment and we're evaluating what it would mean if we wanted to start using AG's as a means of migrating some of these workloads to Azure MI etc.
Thanks,
-Luke.
February 23, 2022 at 11:11 pm
Is there a way to setup the listener so that it will listen for SQL2\MyInstance?
It's not supported; the command to create a listener accepts a DNS name. That doesn't mean it won't work, but I would be surprised if it did.
When you specify a connection using the servername\instancename convention, the driver connects to the SQL Browser service running on the target IP address. The Listener Service is a separate service on its own IP address that points to servers in a single AG.
One possible solution is to align the port numbers - set a static port number for the current SQL2\MyInstance instance, and then start changing your connection strings before you make the switch such that they point to the port number instead of an instance name. So if you set MyInstance to port 12345, you could then connect to either "SQL2,12345" or "SQL2\MyInstance", and either will work. Later, when you create the listener, specify that port. After the switch to the new servers, clients that had been reconfigured to hit "SQL2,12345" will just work (pending DNS cache flushes if the IP address changes, etc. - if you're going to change the IP address of 'SQL2', I would also advise lowering the TTL of the DNS entries until after the switch)
ALTER AVAILABILITY GROUP [MyAG]
ADD LISTENER 'SQL2' (WITH IP ( '<SQL2_ip_address>' ), PORT = 12345);
You will still need to change everything, but you can stage the changes, spread out the work over time, and ensure everything works before you start tearing things down.
Eddie Wuerch
MCM: SQL
February 24, 2022 at 7:56 am
There is NO official support for using instance names with the listener.
Will it work with an instance name as part of the connection string, most probably.
Will a patch / new release break it? Most probably.
Listener\InstanceName in SQL2019 RTM DIDN’T work. So it was broke. It was fixed in some later patch, but it COULD break again, so do not take the risk.
Follow the official supported documentation and ONLY use the Listener in your connection string.
February 24, 2022 at 5:54 pm
February 24, 2022 at 6:05 pm
There is NO official support for using instance names with the listener.
Will it work with an instance name as part of the connection string, most probably.
Will a patch / new release break it? Most probably.
Listener\InstanceName in SQL2019 RTM DIDN’T work. So it was broke. It was fixed in some later patch, but it COULD break again, so do not take the risk.
Follow the official supported documentation and ONLY use the Listener in your connection string.
Where is this documented from MS? I certainly cannot find it, and I have been running multiple AG's with named instances for at least 5 years on SQL 2016 and 2019.
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/
February 24, 2022 at 6:18 pm
Using named instances is totally supported as replicas.
In relation to the listener only there is no documentation or support as far as I know for using Listener\Instance. All the documentation for the listener is to use the NetBIOS name only not the instance name also.
That I can understand as you have Server1\Instance1 and Server2\Instance2 as the replicas, when the group fails over the connection string is invalid as it’s no longer Listener\Instance1.
I saw a mix of 2019RTM and CU12 I believe it was using instance as part of the listener.
When primary was RTM Listener\Instance failed
When primary was CU10 Listener\Instance worked
Patching resolved the issue in the RTM node, but as we all know it could well revert again and as the documentation is all in relation to using just the listener name, it’s just safer to follow the docs and use just the listener name.
So using names instances as a replica - Yes works a treat
Using instance name in the connection string as part of the listener, it may work, it may not work, best be safe and follow the docs and just use the listener on its own.
February 24, 2022 at 6:31 pm
Using named instances is totally supported as replicas.
In relation to the listener only there is no documentation or support as far as I know for using Listener\Instance. All the documentation for the listener is to use the NetBIOS name only not the instance name also.
That I can understand as you have Server1\Instance1 and Server2\Instance2 as the replicas, when the group fails over the connection string is invalid as it’s no longer Listener\Instance1.
I saw a mix of 2019RTM and CU12 I believe it was using instance as part of the listener.
When primary was RTM Listener\Instance failed
When primary was CU10 Listener\Instance worked
Patching resolved the issue in the RTM node, but as we all know it could well revert again and as the documentation is all in relation to using just the listener name, it’s just safer to follow the docs and use just the listener name.
So using names instances as a replica - Yes works a treat
Using instance name in the connection string as part of the listener, it may work, it may not work, best be safe and follow the docs and just use the listener on its own.
Ok, I was baffled for a minute.
Considering the listener is not an instance of SQL Server, I would not expect that a named listener could be created, or even work.
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 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply