A Hostname alias SQL Server instance is a simple Hostname that allows clients to connect to an instance of SQL Server without specifying an instance name or a port number. They are very useful for upgrades, server migration, consolidation and disaster recovery. A network alias can redirect clients from one server to another. This is most useful for named instances because a Hostname Alias can make a named instance look just like a default instance. But it’s also useful for moving around default instances or consolidating multiple default instances onto a single server.
There are three steps to creating a Hostname alias:
- Create a DNS A record to the IP address of target Server
- Configure SQL Server to listen on port 1433 on that that IP address
- Add BackConnectionHostNames entries to enable NTLM Authentication.
- This step is to overcome access error you try to access server using its FDQN. More details here at Microsoft KB article 926642.
- Execute PowerShell script to add BackConnectionHostNames on SQL machine. Please note that highlighted value below is environment specific. It’s the DNS that was created in step#1.
New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa\MSV1_0 -Name “BackConnectionHostNames” -Value “NewHost.dbrowne.lab” -PropertyType multistring
For details on host name alias for SQL Server instance, see How to Add a Hostname Alias for a SQL Server Instance.