SQL Server aliases

by Matt 14. May 2010 11:30

Everyone knows the trick of using the hosts file to redirect one hostname to another. It’s a bit of a sledgehammer, but sometimes it’s amazingly useful.

Take, for example, a system that consists of multiple websites, web services and scheduled tasks. I don’t need to run the entire system locally; I don’t need the resource overhead, nor the overhead of keeping it up to date. So I have a remote development environment (maintained by continuous integration) that the website I’m developing locally can consume.

And the hosts file trick allows me to occasionally work disconnected from this environment, hosting everything locally, and without changing the shared config (and accidentally checking it in and spoiling things for everyone).

I like this, but it leaves me without a database.

My connection strings refer to a named server. Perhaps the host file trick could still work here, but I’ve only got SQL Server Express installed under a named instance. Which, as far as I can tell, means I have to change my connection strings from “Server=dbhost;…” to “Server=.\SQLEXPRESS;…”. And whoops, I’ve just checked that in and broken the whole dev environment.

SQL Server aliases to the rescue!

Fire up SQL Server Configuration Manager and expand the “SQL Native Client 10.0 Configuration” node. Right click on Aliases and select “New Alias…”. Set the alias name to the server name value used in the connection string (for “Server=dbhost;…” use “dbhost”). Set the protocol to “Named Pipes” and enter “.\SQLEXPRESS” in the server field (the pipe name gets automatically populated).

sqlServerAlias

And that’s it. You can now connect to your SQLEXPRESS named instance without changing your connection strings.

One thing to be aware of – if you’re on a 64 bit machine, the steps above have just set up an alias for 64 bit programmes only, which (probably) includes your IIS based website. You need to set up another alias for 32 bit applications by doing exactly the same thing under the “SQL Native Client 10.0 Configuration (32-bit)” node. Once you do this, you can connect to the server “dbhost” from SQL Server Management Studio.

In fact, there’s just one last thing to be aware of – I don’t really know what I’m doing. I’m not a SQL Server guy, so I don’t know if this is old news, or the normal, well known way of doing things, and I definitely don’t know what this is doing under the covers. But it’s working, and it’s helpful, and I’m going to use it.

Now to figure out how to mock Windows authentication…

Tags: ,

Month List

RecentComments

Comment RSS