Monday, 16 May 2016

Creating a SQL Server alias

The Problem

I have a laptop with a SQL Server Express 2012 installed and configured as a named instance (localhost\SQLEXPRESS2012). I’ve cloned a code repository and want to be able to run the applications it contains but they are all configured with connection strings that look for a different instance name (localhost\dev_2012).

I could start modifying connection strings but there are multiple applications and therefore connection strings to modify. I’d prefer to be able to create an alias to the database that matches the one in the configuration files so I don’t need to modify them at all.

The Solution

The solution is to create an alias to the named instance using the SQL Server Configuration Manager.

Open the SQL Server Configuration Manager (Start Menu > All Programs > Microsoft SQL Sever 2012 > Configuration Tools).

Check that TCP/IP is enabled for the instance you are creating an alias for. Enable it if it is not.

sql-alias-001

Once TCP/IP is enabled we can create an alias to the instance for the SQL Native Client. In my case this was for the 32-bit version. Expand the SQL Native Client 11.0 Configuration element and right-click on Aliases. Select New Alias… from the context menu.

Use the new instance name as the Alias Name and set the Server value to the original named instance.

2016-05-16 11_03_01-localhost_dev_2012 Properties

Note the Port No field. By default SQL Server uses 1433 but you can check your setup using the SQL Server Configuration Manager. Open the SQL Server Network Configuration element again and select the protocols for the named instance. Right-click on TCP/IP and view the Properties.

2016-05-16 11_13_10-TCP_IP Properties

If Listen All is enabled on the Protocol tab move to the IP Addresses tab and scroll down to the IPAll section. If Listen All is not enabled you will need to look for the appropriate IP section.

If SQL Server is configured to use a static IP address it will appear in the TCP Port value. If is is configured to use a dynamic port the port in use will appear as the TCP Dynamic Port value. In either case this is the port number to use when creating the alias.

2016-05-16 11_15_22-TCP_IP Properties

Click OK to close any open dialog. Restart SQL Server.

The new instance name will now get routed to the actual instance (calls to localhost\dev_2012 will get routed to localhost\SQLEXPRESS2012 in this case).

You can check everything works by connecting the SQL Server Management Studio to the new instance name.