Can a single SQL Server instance have multiple names?

Question

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?

Answer 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.

Answer 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.

Answer 3

I find it easier to create Aliases in the SQL Server Configuration manger that point to the same sql server.

Answer 4

I ran a simple test by adding an entry to my HOSTS file, and it would seem that SQL Server doesn't care about the name used in the connection string. I always thought that part mattered, but it would seem it doesn't.

In this case, I'm not connecting to a named instance, just the default instance. I would assume that as long as the server part of SERVER\INSTANCE matches, you'd be able to do this with a named instance as well. I don't have any named instances to test with, though.

Answer 5

Well Tim in my experience, you can give the server many DNS entries and you should not run into an issue with reaching the default SQL instance (or any instance) on the server.

The instances themselves are differentiated on the server with the SQL Browser Service which has to run so that the named instances can be seen. If not, usually (99 times out of 100) the only instance that can be seen when this service is not running is the default instance.

I hope I helped you in some way :)