Why use a database alias?

If you have a live sql server in a hosting environment, the chances are it has an odd name that means something to the hosting provider but is totally impossible to remember. This is probably the most common reason to use an alias. Name the server something that you can remember (and keep a theme if you have many servers). This will make connecting to your servers easier but beware that some operations do not always work with an alias (e.g. replication).

Even in a local environment where the server names are easier to remember it is worthwhile using an alias particularly if you host many sql servers on the same physical machine. You could help yourself by adding _Live or _dev to the end.

Another good use for an alias is to make the server name consistent to code and utilities such as maintenance plans, SSIS etc. e.g. When you connect to your server in a maintenance plan you could use (local) and build your plan on the server but if you then try to edit the plan using a remote management studio such as your local machine then the (local) reference will attempt to resolve to a sql server on the local machine (your pc). To get around this create an Alias on your machine and also on the server with the same name, then you can edit the plan either on the server or on your machine (or any other machine with the alias configured). Be consistent with naming!

To create an alias you use the SQL Server Configuration Manager tool found in the SQL Server -> Configuration Tools folder of the Windows Start Menu. Add the alias name, port 1433 and the IP or server name of the server you are trying to connect to.

Note that using the server name is only possible if the network you are on can resolve the name to an IP address (try pinging the name to see if this is the case, if not use the IP address or put the name in your hosts file).

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in SQL Stuff, Uncategorized and tagged , , . Bookmark the permalink.

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