Using CName to point to the Availability Group listener

All we need is an easy explanation of the problem, so here it is.

I have SQL Server 2008 named "OldSQLServer". Its data is accessed by hundred of SQL Servers via linked server.

I am moving the "OldSQLServer" to AG on SQL Server 2019, windows 2019. And I want to keep connecting to it by “OldSQLServer” name.

So I created windows cluster “MyCluster” and AG group “MyAGGroup” with AG Listener “MyAGListener”. I pointed virtual computer name “oldSQLServer” to the same IP address as “MyAGListener”

Everything looks to be working. Clients connect to “OldSQLServer, they connect to “MyAGListener” IP, which takes them to the primary node, be it “MyNode1” or “MyNode2”.

I wonder if there could be any issues with such setup?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

I wonder if there could be any issues with such setup?

There are some additional configuration steps required to make Windows Integrated Auth and TLS work when connecting via a DNS alias.

For the Windows Auth steps see: How to Add a Hostname Alias for a SQL Server Instance

For the Certificate configuration, it’s the same as enabling connectivity via the AG Listener, documented here: Listeners & TLS/SSL certificates

And note that you can alternatively just use the name for your AG Listener.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply