Can a single SQL Server instance have multiple names?
It's pretty much all in the title. I expect the answer to be no, but wanted to ask to make sure. Basically, can a single SQL Server instance be referred to by multiple names. I know you can set the DNS to resolve multiple names to the same IP/server, but how would this affect connecting to SQL Server?
Solution 1:
SQL Server doesn't give a damn about the DNS name you are connecting to, unless you are using SQL Server over SSL. The name of the instance can't be played with unless you are connecting to a specific TCP port number.
Solution 2:
The SERVER part of the SERVER/INSTANCE can be any name that resolves to the correct address, either an extra A record or a CNAME or a name from a HOSTS file.
The only caution here is that the fake name may break Kerberos authentication. SQL with Kerberos is a funny beast, the SQL Client does a reverse DNS lookup on the address and then uses the name it gets to build the SPN so it should work so long as the reverse lookup gets the original host name of the server. Also SQL will fall back to NTLM so this will only cause a problem if you require Kerberos for some reason, like you are using delegation.
You can't make SQL respond to more than one INSTANCE name though. You can however define an alias in the CLIENT configuration.
When you connect to SQL Server, the instance name you specify is sent to the SQL Browser service on the server, the browser service responds with the TCP Port that the requested instance is listening on. It is possible to specify this port number either in the connection string, or in an alias. I have just created an alias called NEW that points to mysqlserver port 1966 which has a named instance listening on it. I was then able to connect to the named instance by simply specifying a server name of NEW.
There are two disadvantages of this method. First is that the configuration is needed on each client, I don't know if there is any way to push that configuration. The second is that a SQL Instance normally uses a dynamic port. If you want to define aliases then you need to change the server so that it listens on a fixed port number that the clients can then be configured to connect to.