Learn more about SQL Server tools

   
   















































How to setup and use a SQL Server alias

MSSQLTips author K. Brian Kelley By:   |   Read Comments (21)   |   Related Tips: More > SQL Server Configurations

Problem
I have an application that has a specified database connection that I either can't or don't want to change. However, I need to move the database to a new server. How can I do this easily without breaking other things that maybe using this connection and/or database?

Solution
By using an alias, we can keep the application configuration the same, but tell the operating system to look somewhere else for the database. One proviso is that once an alias is created, it is set at the operating system level. That means if we have two applications which connect to MySQLServer on that computer, once the alias is created, both will be re-pointed based on the alias. You can create an alias using one of two utilities:

  1. SQL Server Configuration Manager
  2. SQL Server Client Network Utility

SQL Server Configuration Manager

Let's start by looking at how to do it using SQL Server Configuration Manager. In the left pane of SQL Server Configuration Manager, if you expand the SQL Native Client Configuration folder, there is a subfolder called Aliases (see Figure 1). If we click on this subfolder, we'll see any aliases that have been defined for the system shown in the right pane. In most cases, there won't be any defined. To create a new alias, right-click on Aliases and choose New Alias... from the pop-up menu.

Figure 1:

A dialog window comes up (Figure 2) which allows us to specify our new alias. In the Alias Name property, you specify the name the application uses. For instance, if it connects to a SQL Server called MyOldServer, that's what you enter in here. You can also enter an alias that appears to point to a named instance, such as MyOldServer\MyInstance. You can then specify the server where the database really is, to include configuring the protocol, the server name, and the port. Clicking the OK button will create the alias. In the case of Figure 2, I'm creating an alias called MyOldServer which points to my local server's default instance.

Figure 2:

You should now see the new alias listed in the right pane (Figure 3).

Figure 3:

The next step is to test the connection. You can do so quickly by either creating and testing a Data Source Name (DSN), or if you have the SQL Server client tools installed, by using SQL Server Management Studio (SSMS). I'll do so using SSMS. Figure 4 shows the dialog window to open a new connection to a database engine. For Server name:, enter in the alias you specified. In the example we called the alias OldServerName, so that's what we'll use here.

Figure 4:

If the alias is pointed correctly to the new location, a successful connection should be established. In SSMS, I was opening a new connection for Object Explorer. Figure 5 shows that the connection was successfully made using the alias. Note that as far as SSMS is concerned, the server name is the alias.

Figure 5:

SQL Server Client Network Utility

If you haven't installed the SQL Server 2005/2008 client tools, you can still create an alias using the SQL Server Client Network Utility. This has come installed automatically on every operating system from Windows 2000 on. To bring up the utility, click on Start, then Run, and run cliconfg.exe. To view or create aliases, click on the Alias tab (Figure 6). As you can see from Figure 6, aliases created by SQL Server Configuration Manager can be seen by the SQL Server Client Network Utility. The reverse is also true.

Figure 6:

To create a new alias, click on the Add... button. This will bring up a new dialog window where you can specify your alias (Figure 7). Just as with SQL Server Configuration Manager, the Server alias is what the application will attempt to connect to. You can specify where that alias points to by specifying the network library, the real server name, and any additional configuration parameters. In Figure 7 I'm creating a new alias called MyOldServer2 that also points to the default instance of my local server.

Figure 7:

Click the OK button to create the new Alias. In order for the alias to actually be created, however, you must click the OK button for the SQL Server Client Network Utility, which will create the alias as it closes.

Next Steps

  • Now that you know how to create an alias to a SQL Server instance look at this option instead of having to reinstall or rename your servers
  • Some application do not give you the ability to connect to a named instance, so this is another way of connecting without having to use the a default instance


Last Update: 11/5/2008


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, June 02, 2014 - 1:51:47 AM - Naresh Read The Tip

I have a database with XYZ123 and port number is 8121. Now I want to create a alias to change my database name. will port number same as now 8121? any error due to 1433 ? Explain me as I am beginner to this.


Thursday, May 29, 2014 - 6:35:10 AM - Naresh Bhansali Read The Tip

Thanks for the information. I really need it.

 can this method also be used for IP address ??

 Actually I have some application which have IP address of Servers.

 


Tuesday, March 25, 2014 - 3:14:16 PM - Jeffrey Ayoub Read The Tip

I am considering using the SQL Server Alias where I work and hoped you can help

 

I am upgrading a two node Windows Server 2003 SQL Server 2005 Cluster from two older servers to two new servers.  The two new servers will be Windows 2008 R2 Enterprise SQL Server 2008 R2 with all the database set to (90) compatibility level.  We have several applications that connect to the database and it would be difficult to modify them.  I would move from Active/Active to Active/Active 

 

Q1: Would this method work for Virtual SQL instances?

 

Q2: Not sure if I understood the article but I would be creating the Alias on the two new servers within the Virtual Clusters

 

Your help is appreciated. 

 

Thanks

Jeff

 


Thursday, June 13, 2013 - 2:47:38 AM - Karthik Read The Tip

This article explains creating alias name for a server. If you want to create for sql server, create in linked server using the below query.

 EXEC master.dbo.sp_addlinkedserver @server = N'ALIASNAME', @srvproduct=N'Microsoft', @provider=N'SQLNCLI', @datasrc=N'xxx.xxx.xxx.xx', @catalog=N'MASTER'

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ALIASNAME',@useself=N'False',@locallogin=NULL,@rmtuser=N'LOGINNAME',@rmtpassword='strongpassword'


Thursday, May 02, 2013 - 2:31:55 PM - Jim Chandler Read The Tip

I would also be interested in how to do this from the command line.


Thursday, May 02, 2013 - 5:24:25 AM - kumar Read The Tip

Am using SQLSERVER2008 R2       .I tried your steps to change the alise name for the server,but its not working...

 

any idea.?


Thursday, April 18, 2013 - 1:37:56 PM - exhale Read The Tip

How do you refer to it in your ODBC connection?


Thursday, April 18, 2013 - 3:45:01 AM - Arora Read The Tip

Is there a way to do this from the command line?


Friday, August 24, 2012 - 1:23:56 PM - zahoor ali Read The Tip

I am getting error:

 

Microsoft SQL server Error: 10061

 

Please suggest!

 

Thanks!


Tuesday, July 17, 2012 - 3:45:35 PM - Sam S. Read The Tip

Thank you for a great article.

I have created an alias according to your steps on the development server (remote). 

When I am trying to create a connection through a connection manager in my SQL BIDS, or trying to connect through SQL Management Studios, my alias is not being recognized.

Any ideas?

Thanks!


Thursday, June 14, 2012 - 12:25:23 PM - Edward joell Read The Tip

Never mind.  The community comment on this page http://msdn.microsoft.com/en-us/library/ms190445(v=sql.100).aspx  pointed out that it is necessary to create that alias in both 32 bit and 64 bit for it to work.


Thursday, June 14, 2012 - 12:14:03 PM - Edward joell Read The Tip

Followed instructions.  Port is determined dynamically so made sure SQL Browser was running, and left port blank using configuration manager.  That did not work.  So then used SQL Server network client and set check mark to dynamically determine port. And that did not work.

I am at a loss.


Tuesday, May 22, 2012 - 4:16:26 AM - Wilfred van Dijk Read The Tip

What about having a SQL2008R2 (on windows2008R2) in 64bit mode? It seems you have to define the alias on the 32bit and 64bit part (in the server config mgr).

I can't get an alias (defined on the server) to work. Client alias definition works, but defining a client alias is not an option (laptops floating around). The definition of an alias on the server should be enough?

 


Thursday, May 10, 2012 - 3:07:42 PM - Ganesh Read The Tip

Hellow, I have Exported list of all Aliases before formating my PC. Now I wanted to import alises for 100 servers but I do not see Import option on SQL Server Configuration manager. Please let me know the batch process to insert many aliases.

Thanks, Ganesh


Wednesday, May 09, 2012 - 9:42:31 AM - BobC Read The Tip

@KBrianKelley  Can you compare this to the DNS CName approach?


Monday, February 27, 2012 - 2:13:16 PM - K. Brian Kelley Read The Tip

You don't have to create a new alias. You just have to modify the existing one.

Using the alias is something you'd only consider doing when it's either (a) not doable to change the connection or (b) it requires you to change it in a lot of places or is very hard to change. If the app config is relatively straightforward to change, then you'd make the change. And you may be thinking, does (a) exist? Yes. I've seen 3rd party apps that hard-coded stuff like this during the install. That would be a case where you'd use the alias.


Monday, February 27, 2012 - 11:49:14 AM - LC Read The Tip

What if you have to move a database the second time to another server. Effectively now you will have to create another alias? Thereby the application would make two hops?

APP -> OLD

APP -> OLD -> NEW

APP -> OLD -> NEW -> NEWEST?

Doesn't it make more sense to go into the application's config to change it there, for whatever application it is?


Friday, March 12, 2010 - 10:26:22 AM - Dewy Read The Tip

 Is there a way to do this from the command line?

I have to side by side installations of 2005 and 2008 on my local machine that we use for dev.

In most of our code, our test code assumes (localhost).

What I want to do is create some kind of exe/bat file/script such that I can quickly change which DB is localhost as I switch between projects?

Cheers

Dewy


Thursday, January 15, 2009 - 11:47:51 AM - K. Brian Kelley Read The Tip

This is a technique we often used on Windows 2000 servers. The original MDAC that shipped with Windows 2000 didn't support looking up the port for Named Instances. However, if you knew the port, you could create an alias and everything would work just fine.

 


Thursday, January 15, 2009 - 11:40:18 AM - kunsikl@gmail.com Read The Tip

One case would be, if you have named instance, most likely the port number is not "1433" but something else. You can use Static Port Assignments in SQL Server 2005 http://www.mssqltips.com/tip.asp?tip=1412 article to check what port number and make it static and create alias based on that port number and try it.


Tuesday, November 18, 2008 - 2:00:01 PM - yoey2008 Read The Tip

I have to support several older apps developed in VB4 (16 bit) and VB5 and we are migrating from SQL Server 2000 to SQL Server 2005. The alias works great with apps developed in VB5 using ODBCDirect and ADO but does not seem to work for apps developed using VB4 (16 bit) using DAO for data access. The new database server is setup as a named instance. Is it the alias or the named instance that is causing the problem? Any ideas on how to get around this?




 
Sponsor Information