Stop using SQL Server path’s work with a SQL Alias

A lot of times i run into customers how started out with one SQL server and run in to problems when these servers have to be upgrade or the databases needs to be migrated to different SQL servers of clusters because of expansion or recourses. Let’s say you 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?

By using an alias! We can keep the application configuration the same, but tell the operating system to look somewhere else for the database. Another plus 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:

1620_CreatingAliases_01

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:

1620_CreatingAliases_02

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

Figure 3:

1620_CreatingAliases_03

 

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:

1620_CreatingAliases_04

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:

1620_CreatingAliases_05

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:

1620_CreatingAliases_06

 

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:

1620_CreatingAliases_07

 

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.

Conslusion

Now that you know how to create an alias to a SQL Server instance look at this option instead of having hard SQL connections to server and run into problems when you want to reinstall or rename your servers. Also 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.

Comments are closed.