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

About the Author: Nick Taylor

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.

Three Minutes For A More Secure & Efficient Infrastructure

Short and to the point, Steve’s Email Blasts give you endpoint management tips, tricks, and news in three minutes or less email read-time, guaranteed.

Model says no to spam. Privacy Policy

These Posts Might Be Helpful Too

Model Technology Solutions

Model Technology Solutions is a small but mighty band of infrastructure experts. We’ve helped companies in diverse industries to modernize and automate their infrastructures through effectively managing their Microsoft endpoint suite.

With us on your team, you’ll watch your security and compliance go up and your IT team’s costs (and headaches) go down. You’ll relax in knowing that your endpoints will be secure and online when your users need them most. And you’ll finally get back to your most-important tasks.

Model Technology Solutions
12125 Woodcrest Executive Drive, Ste. 204 Creve Coeur, MO 63141

Phone: (314) 254-4138
General Inquiries: model@model-technology.com
Sales and Quotes: sales@model-technology.com