I CNAME What You Did There

Posted by | · | DNS · SQL

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

Consultant – Model Technology Solutions
Nick is an IT professional with more than 19 years of experience and a passion for learning about technology. His areas of expertise include datacenter, hypervisors, storage, network, cloud, and OSD. He spends his free time delving in crypto, video games, automation, IoT, and really anything nerdy.

Disclaimer

The information in this weblog is provided “AS IS” with no warranties, and confers no rights. This weblog does not represent the thoughts, intentions, plans or strategies of my employer. It is solely my own personal opinion. All code samples are provided “AS IS” without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.