SQL Server DNS Alias

  • Article

The customer wants to setup DNS for SQL Server because it’s part of their Disaster Recover (DR) plan.  I learned something from the folks in our team and Grant G from the PFE team on how to do it. Posting the step here just in case that I need to do it next time.

In order to setup a DNS alias for SQL server/SQL cluster, the first step is make sure DNS alias is in place, SPN is setup correctly.  Once the pre-requisites is setup, following the steps below:

1. Execute the following query.

 select @@servername

2. One you’ve verified the server name, execute the following SQL query.

 sp_dropserver ‘servername’
go
sp_addserver ‘aliasname\instancename’,’local’
go

3. After you execute the above query, stop and start the SQL server.

4. Execute the following query again to make sure SQL server is setup correctly.

 select @@servername

The steps above assumes that SQL is listening to the default port of 1433.  To configure SQL listen on different port. Please see the following article on MSDN.

How to: Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)