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!