Sql-server – How does a SQL Server instance alias work

connectivitysql server

I'm trying to understand how a SQL Server alias works, but the documentation is lacking to say the least.

Most blogs/videos/documents I can find state that all you need to do is go to SQL Server Configuration Manager and add an "Alias" under SQL Native Client Configuration / SQL Native Client Configuration (32 Bit), which points to the same port as the instance you want to create an alias for. And that's it, done, nothing else required.

Now unless the SQL Server is in a domain, and allowed to register its own SPNs and only Kerberos is being used (and assuming that an alias gets its SPN automatically registered, which I can't find any documentation to either confirm or refute this statement)… I can't really see how this is enough!

Assuming that either the SQL box is not on the domain, or that SPNs are not registered, if I create an alias called SQLTEST for SQL1 on port 1433 and, from a different computer, try to access SQLTEST bearing in mind the lack of SPNs the first thing the client will do is try to resolve SQLTEST to an IP address – yet there's no DNS record for SQLTEST.

So how does this actually work? What are the true requirements to get aliases working?

How does creating an alias differ from just creating 'A' or 'CName' records in DNS without actually creating an alias in SQL itself?

Best Answer

1) You don't need to define the alias on the SQL Server itself, unless you need a loopback for some reason.

2) The client doing the connecting does need the alias defined on it. Make sure you define it in both the 32bit and 64bit version of cliconfg to make sure you don't miss anything there.

  • C:\Windows\system32\cliconfg.exe
  • c:\WINDOWS\SysWOW64\cliconfg.exe

3) The Alias defined in cliconfg on the client is different from DNS in that you can specify a port instead of just a redirect. You could combine the two for maximum flexibility. So you could have cliconfg on the client using a DNS record to point to the correct host, but cliconfg specify a port number. Think of it as a etc\hosts file that only database drivers talk to. Effectively:

  • Application - Connection string to My_SQL_Alias
  • CLICONFG - My_SQL_Alias == MySQLHost:1564
  • DNS - MySQLHost == MyRealSQLHostName
  • FinalConnection == MyRealSQLHostName:1564

4) If part of a domain, you should be able to specify these things through group policy.

WHY USE IT??

I've not found many use cases in my career for setting these up. Really, if you need to hide the fact that you are connecting to an instance rather than the default instance then that's the only thing that really springs to mind. But the challenges with spreading this information out prohibits a lot of these use cases.

Related Question