Hi all! I have a tendency to blog about what I’ve been seeing in the field lately and for some reason SQL Always On Availability Group Installs have been coming our way.
One thing I always recommend when putting a new cluster in place is adding a CNAME in front of your listener string. In case you don’t know SQL will automatically register SPNs of objects in its cluster if the service account has the correct permissions. So if you haven’t added in another layer of abstraction between your connection strings on your clients and your AG’s Listener String you might be in for some pain when migrating off of the cluster in the future.
Configuration With CNAME:
For example if you have a Listener String named “SQLServerListener.contoso.com” and have configured all related applications to point to this string then later you want to do a side by side migration to SQL 2017 you will have to do one of two things. You can either create a new listener string and change ALL your client settings. This can be a pain depending on what tools you have for client management. Or you can shift the DNS Name of the earlier SQL Clusters Listener string to point to the new cluster instance. I’ve always found it to be easier to make the change on the DNS side.
Now in the case that you didn’t add a new CNAME in front of your listener string when you shift the DNS record over the original cluster will attempt to register the SPN for that object automatically. So you would have to shut down the SQL Service and this makes rollback a bit more difficult and I tend to always leave my legacy cluster up for a few days as well for reference.
So if you had created a CNAME for the application you could just change the target of that CNAME to the new clusters Listener String, manually register the SPNs, and your old clients will then be looking at your new cluster.
Migration with CNAME:
The commands I’ve used for this are below and Microsoft also has a nice GUI Tool to assist with the creation of SQL SPNs as well.
Happy Clustering!
setspn -a MSSQLsvc/CNAME.contoso.com contso.com\ClusterNameObject$
setspn -a MSSQLsvc/CNAME.contoso.com:1433 contso.com\ListenerStringObject$
Link to the MS Tool:
https://www.microsoft.com/en-us/download/details.aspx?id=39046
