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:
SQL Server Configuration Manager
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.
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.
You should now see the new alias listed in the right pane (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.
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.
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.
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.
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.
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
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.
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?
Thursday, January 15, 2009 - 11:40:18 AM - firstname.lastname@example.org
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.
Thursday, January 15, 2009 - 11:47:51 AM - K. Brian Kelley
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.
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.
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.
Tuesday, May 22, 2012 - 4:16:26 AM - Wilfred van Dijk
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, June 14, 2012 - 12:14:03 PM - Edward joell
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.
Thursday, June 14, 2012 - 12:25:23 PM - Edward joell
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.
Thursday, May 29, 2014 - 6:35:10 AM - Naresh Bhansali
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.