Skip to main content

MS SQL Server Named Instances and Aliases For Heterogeneous Developer Environments

On the team that I'm working with, we're supporting MS SQL Server 2000, 2005, and 2008. Depending on when the particular developer joined the team, and thus when they installed the various pieces of software on their development workstation, any of the above listed versions might be the default instance (i.e., "(local)"), while the others might be installed as named instances (i.e., "(local)\SQL2008" or "(local)\SQL2K5").

Every once in a while, a developer has to work on a project with a database installed to a local database server that is on a named instance other than the rest of the development team. With the database server name stored in .config files, altering this for each developer just doesn't make much sense. Thankfully, MS SQL Server has a very simple and straightforward solution for this - aliases.

By using aliases, an application under development can be configured to use an alias in the .config file, and each developer simply needs to create a alias on their workstation pointing to their particular named instance.

Anyway, you can either decipher what MSFT KB Article 265808 has to say, or just follow the step-by-step instructions here (for MS SQL Server 2005):

#1 - Open SQL Server Configuration Manager

#2 - Confirm that TCP/IP is enabled for the named instance for which you are creating the alias.


#3 - Confirm that "Listen All" is set to "Yes" for TCP/IP for that instance.


#4 - Take note of the port number listed next to the "TCP Dynamic Ports" setting under the "IPAll" section.


#5 - Right-click the "Aliases" node and select "New Alias..."


#6 - Fill in the details for the new alias.
  • Alias Name - I like the idea of choosing a name for the alias that a) isn't already a name on the network (obviously) and b) is named for the application that we are developing. It is irrelevant that you have 2, 5, 10, or 500 aliases all pointing to the same database server (web hosting services have done this for years when hosting hundreds of websites on a single server).
  • Port No. - This is the value from "TCP Dynamic Ports" in step #4 above.
  • Protocol - Confirm TCP/IP is selected.
  • Server - This is the database server name you would "normally" use to connect to the particular database server. Note that the SQL Server "(local)" alias works as part of this solution, and in conjunction with or without a named instance name, is what should go in this box.


NOTE #1 - Aliases are "local" to the specific workstation they are created on. In other words, every developer on the team will need to create the alias on their individual computer.

NOTE #2 - Yes, the dev team could standardize the way software is installed on their computers. But on my team, we get paid to do what our client wants us to do, and not to arbitrarily streamline" and tweak every last detail of our development environment. Besides, using aliases just works, and unless someone can comment on why this is technically a bad idea, I'm not going to ever condone wasting my client's money.

Comments

Popular posts from this blog

MS KB928365, ASP.NET Request.Headers.Add() Hack No Longer Works

So a project that I am currently a part of is using an ASP.NET 2.0 HttpModule to add some additional values to the incoming HTTP request's headers in the DEV environment (i.e., our local disconnected laptops) to simulate what an enterprise single-sign-on solution is performing in the production environment. It has worked like a charm. That is until I installed the new security update for the .NET Framework 2.0 release this past Wednesday, July 10, MS KB928365.

Apparently this "hack"has been disabled with the release of this security update.

When attempting to call Headers.Add(), with or without the above hack in place, you will now receive a PlatformNotSupported exception.

All in all, this post is by no means a rant against the security update, but simply an attempt to add a quick answer to the "Google answer machine" for those searching. I am also already aware of a number of other potentially better solutions than the one currently in place for simulating th…

Temporal Database Design, Soft Deletes, and Ayende's Razor

This is a formal follow-up to a post on Ayende's blog on "Avoiding Soft Delete's" in your database where I question the lack of temporal database solutions being applied to these types of problems.

After Oren claimed the following, I felt it necessary to expand on it in a blog post of my own, rather than continuing to clutter his comments, and hopefully finally bring some traffic to my own blog :-)
Ayende’s RazorThis is a response to a comment on another post:Oren, in all seriousness, I thought that problems that were "(a) complex, (b) hard to understand (c) hard to optimize" were the kinds that folks like you and I get paid to solve...Given two solutions the match the requirements of the problem, the simpler one is the better.I could just as well call that statement "Jim's Razor", as I believe in it as much as you do Oren, so no arguments there.

But in the same vane, "wise" (i.e., experienced) software architects/developers strategical…

It was bound to eventually happen...

...that I would start a blog.

Anyway, I've spent better than the past 5 years now reading other people's blogs, and steering clear of starting my own. I still don't think I have the time to offer a lot of content, but hopefully what I do post will be more useful than this obligatory intro. The only real reason I decided to start this was to give myself somewhere to post write-ups on random topics that I couldn't find an answer to via Google. Hopefully posting those write-ups here will save someone else some time some day.

In the mean time, you can keep yourself busy reading my Google Reader Shared Posts.