July 21, 2018 at 11:10 pm
We are planning to migrate our SQL Failover configuration to Alwayson AG.
The plan is that there should be no connection string changes in the application.
For example if the SQL Virtual Network name in Failover Configuration is 'Test1'
We need to create the same Listener in Alwayson AG as 'Test1'
I know that listener names should be unique.
Can you please guide me through how can I use the same name?
Note: Always on setup is being done on brand new servers and we want to completely get rid of failover cluster servers.
Thanks
July 23, 2018 at 2:48 am
We did something similar about a year ago. One of our main objectives was to minimise downtime.
The main potential period of downtime is where we migrate databases from a SQL cluster to SQL non-clustered always-on. The only way we could see to minimise downtime at thise stage was to have the databases replicated between the cluster environment and the always-on environment. There are a number of ways to achieve this (sql replication, disk volume block-level replication, always-on).
We thought the lowest risk option was to use always-on. We therefore decided to configure always-on on the SQL cluster, making the SQL cluster the primary node of our always-on environment. We also decided to use a single AG with a listener as we had no reason for multiple AGs. Your environment may differ on this.
This took us to the next problem - connection string stability.
We had already implemented a series of DNS aliases for our connection strings, but were aware that many connection strings had been set up just pointing to the SQL cluster name. We therefore went through a process of ensuring all connections strings we could find were using a DNS Alias. Initially these aliases were pointing to the SQL cluster name, but after we configured always-on for the SQL cluster we changed the aliases to point to the always-on listener name.
We then built the always-on non-clustered SQL instances, each on a new node of the Windows cluster. We needed to add /SkipRules=StandaloneInstall_HasClusteredOrPreparedInstanceCheck to our SQL install to allow a non-clustered instance to be built on the new nodes, but this all worked fine.
To prepare for the always-on failover we finally added all our databases to the AG (using async replication) and ensured everything was up to date on the secondary nodes. This was monitored for a few days to ensure everything was stable.
For cutover we did schedule an outage, and we had people available to hunt down and fix any connection strings we had not found earlier. We then did an always-on failover, making the SQL cluster a non-readable secondary so that any bad connection strings would stand out. Cutover was mostly smooth. No connections strings that used an alias needed changing, they all worked ok at cutover. For the rest, the most important things worked at time of cutover, while some troubleshooting of undocumented dependencies did take a few hours. But at the end of the day it all worked.
When we were happy about the new environment we destroyed the SQL cluster and decommissioned the nodes they used. This left us with a Windows Cluster running non-clustered SQL using always-on to provide resilience.
As part of doing this, I upgraded the SQL FineBuild tool to support all that we did. This is now available for download at https://github.com/SQL-FineBuild/v3.4/releases
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
July 24, 2018 at 8:02 pm
Thanks for your reply.
From what I understand you used DNS alias to point to the alwayson listener name.
We too have series of DNS alias name pointing towards SQL Cluster.
So you think this will be the cleanest/simplest approach?
July 25, 2018 at 12:45 am
I think you made the right choice. Using DNS aliases in the connection string gives stabilityof the connection string wherever the alias is pointing.
In your situation you obviously have two main choices:
a) Get your databases replicated from your SQL cluster to your new always-on environment via your favourite process. Script the DNS alias change to give a very short outage when you cut over.
b) Ad an AG and listener to your cluster and use Always On to replicate your databases to your new environment, and change your aliases to point to the AG listener before AG failover. Moving to your new environment is then just a AG failover with effectively zero outage.
If you can use option b) I think it is the best way to go. You can minimise implementation risk by making your always on servers use the same SQL version as your cluster. This means when you failover the AG if you find an unexpected problem that blocks acceptance of your new environment you can fail back to your cluster.
We did this, SQL 2014 cluster to SQL 2014 AG, then built another set of servers to do a SQL 2014 to SQL 2017 upgrade. The cluster to AG move showed some holes in our documentation resulting in some unexpected troubleshooting. The SQL 2014 to SQL 2017 move was less of a problem as our documentation and preparations had improved. The main issue was if you have CLR routines they needed to be registered in a different way on SQL2017.
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