Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Aliasing a SQL Server: When it works, when it doesn’t and when it may be your problem.

Creating an alias for a SQL Server is fairly easy and there are several ways to do it. Configuration Manager is my personal favorite. Open up configuration manager and select the SQL Native Client xx Configuration. Under that you will find Aliases.

Alias1

From here you can add, update or delete aliases.

So at this point some of the more polite members of the audience are probably thinking “Unfortunately I have no idea what you are talking about. Would you please explain what an alias is?” And I appreciate that from both of you. The rest are probably thinking something along the lines of “You idiot, if you’re going to talk about aliases it would be nice if you explained what they are first!”

Per BOL:

An alias is an alternate name that can be used to make a connection. The alias encapsulates the required elements of a connection string, and exposes them with a name chosen by the user.

In other words once you have created an alias you can connect to the aliased machine using the new name in the connection string. For example I create an alias GEORGE and point it to my (local)\sql2012 instance. I can now connect to my instance using either its correct name (local)\sql2012 or GEORGE.

When it’s working

Let’s say you have a series of servers: LARRY, MOE and CURLY. You decide to do a side by side upgrade of CURLY. You are going to move all of the databases on CURLY (a 2008 server) to SHEMP (a 2012 server) and then shut CURLY down. There is a bit of a problem however. The developers have told you in no uncertain terms they do not have time to find all the dozens of places that CURLY was hard coded into the various applications. We can solve this easily enough by creating an alias on the application server pointing the alias CURLY to the new server SHEMP. Now when the connection strings try to go to CURLY the alias says to go to SHEMP and everything continues to work.

When it doesn’t

Over the weekend you’ve moved the databases and the applications were all tested. Everything went smoothly so CURLY was permanently shut down. Monday morning rolls around and the developers start calling. They can’t connect to the database. Want to guess why not? An alias only works locally, each individual client (the machine you want to connect from) must have it’s own alias created.

When it might be your problem

Over time alias’ get created (sometimes by accident believe it or not) and get forgotten. Over the years I have seen a number of situations where the answer to “Why can’t I connect to server XYZ?” is an alias that the user either didn’t know about or had forgotten. That’s why this has become one of those Start with Stupid steps that I take when someone can’t connect but everything else looks ok.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: alias, microsoft sql server

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...