Darren Liu's Blog

SQL Server DNS Alias

with 2 comments

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’
sp_addserver ‘aliasname\instancename’,’local

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)

Written by darrenliu

04/03/2012 at 2:36 am

2 Responses

Subscribe to comments with RSS.

  1. Thanks for composing “SQL Server DNS Alias Darren Liu’s Blog”. I actuallymay really wind up being back for alot more browsing and writing comments shortly. Thanks, Katie


    02/13/2013 at 10:56 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: