Hi everyone,
I’ve been tasked with migrating a Standalone SQL Server instance to a highly available environment with minimal disruption (Not wanting to update the connection strings) I’d love to get some advice from the community on the best approach.
Current Setup (For the sake of this post)
Existing SQL Server: SQLSERVER\INSTANCE
New SQL Server Environment : NEWSQLSERVER1\INSTANCE and the second node is NEWSQLSERVER2\INSTANCE
There is an availability group between the 2
Availability Group: AG1
Listener Name: AG1-li
Objective:
The customer wants to move from SQLSERVER\INSTANCE to the new environment with the least amount of disruption. Ideally, I want any connections to SQLSERVER\INSTANCE to be seamlessly redirected to AG1-li, so applications and users don’t need to update their connection strings.
Updating Connection Strings – Ideally the customer doesn not want to do this.
Has anyone done something similar? are there any potential pitfalls I should be aware of? Is there a better way to handle this?
Appreciate any insights!
Thanks!
March 19, 2025 at 10:20 am
Of course, best is to modify the connection strings ( over time )
Worth the try
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 20, 2025 at 9:55 am
You could also look at asking your network team to create a load balancer and then use the load balancer in the connection strings.
Once all the connection strings are pointing at the load balancer you should then be able to quickly failover to the new listener. By keeping the load balancer you will also be able to quickly failover when you do the next upgrade.
I don't have an environment to try this out but Johan's response may be part of the answer. But there are some fundamental issues that need to be overcome. I am assuming your servers are part of a AD domain and DNS is active.
You say your current connection string is 'SQLSERVER/INSTANCE' and the new listener name is 'AG1-II' and is hosted on server NEWSQLSERVER. If your client did not connect using an instance name, the problem would be easy to solve. Using an instance name makes things much harder.
You say your current connection string is 'SQLSERVER/INSTANCE' and the new listener name is 'AG1-LI' and is hosted on servers NEWSQLSERVR1 and NEWSQLSERVER2. When a client tries to connect to SQL Server, the first thing it needs to do is identify the server that hosts the SQL instance.
I think the answer is to treat your client connection request as a token, and move it forward step by step until it connects to AG1-LI.
The first step is to get the client connection request to your new server. Is the SQLSERVER a DNS alias, or is it a physical server name. Also look at the end point names on SQLSERVER/INSTANCE and see how they are defined, this might help in some way.
Make sure that your AG is active on NEWSQLSERVER1
1) If SQLSERVER is an alias, then at cutover time you simply point the alias to NEWSQLSERVER1.
2) If the SQLSERVER part points to a physical server name, then at cutover time you need to delete the server name from DNS and replace it with an alias with the same name (SQLSERVER) and point the alias to NEWSQLSERVER1.
(Note that if your client connection string did not include an instance name, thenAG1-LI should be used instead of NEWSQLSERVER1 in both of the above.)
You now have the client knocking at the door of NEWSQLSERVER1 and asking for INSTANCE to connect to. Because you have built your AG on top of NEWSQLSERVER1, the SQL instance on that server will not be listening on the /INSTANCE name, but only be listening on the AG1-LI name.
Set up a SQL alias as suggested by Johan. It will need to redirect connections from NEWSQLSERVER1/INSTANCE to AG1-LI. Note this is an alias on the target server, not a DNS alias. You can test if this is will work by trying to connect to NEWSQLSERVER1/INSTANCE from SSMS.
If this all works when the AG is active on NEWSQLSERVER1 you now need to deal with failover.
Create a SQL alias on NEWSQLSERVER2 to redirect connections from NEWSQLSERVER2/INSTANCE to AG1-LI. Test this works from SSMS, while the AG remains active on NEWSQLSERVER1. If you ever add NEWSQLSERVER3 to the AG then also set up a local alias on that server.
At failover time, as well as failing over the AG, you will have to update the SQLSERVER DNS alias to point to the active node name. Hopefully it should all work without changing any connection strings. In order to get quick connection after failover, set the keep-alive property of the alias to a low value.
You may be able to get a load balancer set up that covers NEWSQLSERVER1 and NEWSQLSERVER2. If you do this then change the DNS alias to point to the load balencer name, to avoid the need to update the alias at failover time.
Long term, change the connections strings to remove the use of /INSTANCE. You may also want to change the SQLSERVER part to be a 'proper' alias name, whatever that is in your organisation.
Also look seriously at using DNS aliases as the target of all SQL server connection strings. This gives you a level of indirection between the client connect request and the topology of your SQL server environment. We did that, and many times changed server and AG names as we upgraded over the years, all with zero impact on connection strings.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply