Querying a SQL Server's CNAME alias instead of the actual instance name

Question

We don't have a DBA here and our infrastructure analyst is trying his best to give us a test instance on a server using a CNAME alias which neither he nor I have done before.

The actual database instance being used is called AN-UATSQL03.domain.com but it has been set up with a host name of [ALT-STG-SQL-ATLNTA1].

Connection on management studio requires a server name of [ALT-STG-SQL-ATLNTA1\DWH] to work. The underlying server being used does have an instance of \DWH but this wasn't needed in the DNS setup, however we can only connect if the instance part of \DWH is added on at the end. That's one weird occurrence.

Right now, if we query on this new instance like so;

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

It doesn't work, even though on Management studio we connected to ALT-STG-SQL-ATLNTA1\DWH as the Server name. It's like the alias isn't recognised in the catalogue and the actual instance that the alias is pointed to in the DNS is what the Query also wants to have referenced.

While [server] in a SQL query from within that same server isn't really necessary, we do that in cases where we are joining across linked servers to resources that have very similar structures, so we know which is which.

We're out of our depth here really in trying to make this work. We need to test the migration of objects from an existing SQL server to this new one via the UAT server that the host name points to in the DNS. We wanted to be able to test it, if it works we can migrate to a live server after altering any SQL code to use the new host name of [ALT-STG-SQL-ATLNTA1] for the server which would stay the same.

Any advice would be so much appreciated!

Answer 1

The reason the CNAME is not working is you are using a named instance. When you specify the name of the instance as in SERVER\INSTANCE the SQL Server client looks up the port number of the instance via the SQL Server Browser service. Because you are using a name that is different from the actual SQL Server instance name, that lookup fails.

Instead of connecting via AN-UATSQL03\DWH, try connecting directly using the port number, as in:

tcp:AN-UATSQL03,xxx

Where xxx is the port number. You can get the port number either from SQL Server Configuration Manager, or via this query:

SELECT SPID = @@SPID
    , InstanceName = @@SERVERNAME
    , InstancePortNumber = CONVERT(varchar(255), local_tcp_port)
    , dec.auth_scheme
    , USER_NAME = USER_NAME()
    , SUSER_SNAME = SUSER_SNAME()
    , dec.connect_time
FROM sys.dm_exec_connections dec
WHERE dec.session_id = @@SPID;

You'll need to ensure the port number is not being assigned dynamically; use SQL Server Configuration Manager to set it to a manually configured TCP/IP port. These Docs should help you out.

This query in your question implies you have a SQL Server linked server setup:

SELECT *
FROM [ALT-STG-SQL-ATLNTA1\DWH].[Database].[dbo].[Column]

If you only want to access the DWH instance via that linked server, you can simply define the linked server using the "real" name, but name the linked server object the aliased name. Something like this should work:

EXEC master.dbo.sp_addlinkedserver @server = N'AN-UATSQL03\DWH'
    , @srvproduct=N''
    , @provider=N'SQLNCLI'
    , @provstr=N'SERVER=ALT-STG-SQL-ATLNTA1\DWH;'
    , @catalog=N'tempdb';

That would allow you to reference the linked server as [AN-UATSQL03\DWH] while actually connecting to [ALT-STG-SQL-ATLNTA1\DWH]. So your sample query would be:

SELECT *
FROM [AN-UATSQL03\DWH].[Database].[dbo].[Column]