6

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?

7

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.

  • 1
    This is just plain wrong. Sure the instance can be played with, under SQLServerManager14.msc => SQL-Native Client => Aliases (be sure to enter the 32 bit version to, for SSMS). The hostname can be played with using the hosts file (C:\Windows\System32\drivers\etc\hosts) or by adding the server name to your internal DNS-server. You need to forward the alias to the proper server and port (instance), 1433 by default. e.g. localhost\SQLEXPRESS =>localhost,1433. You probably need to enable TCP/IP under network configuration for this to work (SQLServerManager14.msc). – Quandary Oct 12 '18 at 4:30
  • Nope my answer is still correct. Adding aliases doesn't change the name of the instance, and as you pointed out you can chance the host name all you want, which is the same thing I said. So thanks for agreeing with me. – mrdenny Oct 13 '18 at 5:07
  • Yes it doesn't change the name of the instance, but it makes the installed instance accessible under another instance name, which is as if it were another instance. For example I can alias localhost/SQLEXPRESS to localhost, and then I can use SSRS projects together with other users that have SQLEXPRESS without having to change the connection string in the SSRS project. And they don't have to change it back as well every time they pull the latest version. One less bothersome merge-conflict. And this also works when connecting from remote computers. – Quandary Oct 15 '18 at 6:44
  • An alias can only be used on the computer that they are created on. Here's the Microsoft documentation on them. docs.microsoft.com/en-us/sql/database-engine/configure-windows/… There's nothing in the SQL Server connection protocol that allows for an alias to be stored on the server. How would the client get this information? Where would the client get this information from? The Op asked if the server can be connected to with multiple DNS names. That answer is yes, SQL doesn't care. This isn't exactly controversial. – mrdenny Oct 16 '18 at 21:44
  • Argh, you're right, I ended up here via google searching for how to change the instance name. I only read the title, not the actual question. Damn my vote is locked. I can't undo. – Quandary Oct 17 '18 at 9:58
6

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.

4

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

  • For management studio, be sure to also enter the alias to the 32-Bit native client, not just the 64 bit one. SQLServerManager14.msc or SQLServerManager<YOUR_VERSION>.msc respectively. – Quandary Oct 12 '18 at 4:25
1

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.

1

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 :)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.