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: ,

Comments (12) -

New york yankees Baseball cap
New york yankees Baseball cap
6/19/2011 3:05:57 PM #


I've learned a lot from your blog here,Keep on going,I will keep an eye on it,One more thing

Reply

suv review
suv review
7/20/2011 10:25:04 PM #

it ti permette di orientarti con semplicità nel pulviscolare niverso di Moncler.Troverai le indicazioni per lo spaccio o negozio Moncler più vicino a casa tua felpe moncler, pantaloni e abbigliamento sportivo.

Reply

iphone g4
iphone g4
7/23/2011 7:29:43 PM #

Per il tuo bambino scegli Moncler. Una scelta di capi, estivi ed invernali, eccezionali. Tuo figlio sarà sempre alla moda e potrà muoversi in totale comodità.

Reply

LAURENCE  Debora
LAURENCE Debora
7/27/2011 10:48:01 AM #

Bienvenue sur annuaires-gratuit.com.Des fonctionnalités inédites, nombreuses annuaires.

Reply

DEBORA Laurence
DEBORA Laurence
8/1/2011 8:35:02 AM #

Il ne vous reste plus qu' à créer votre propre annuaire, l'inscription est  gratuite .

Reply

DEBORA Laurence
DEBORA Laurence
8/8/2011 9:43:20 AM #

Vous pouvez configuré vos systèmes de paiement allopas sur annuaires-gratuit.com

Reply

DEBORA Laurence
DEBORA Laurence
8/9/2011 7:46:04 PM #

Recevoir la newsletter et les informations des partenaires de annuaires-gratuit.com

Reply

DEBORA Laurence
DEBORA Laurence
8/12/2011 8:13:51 PM #

vendez aux enchères vos objets neuf ou occasions, livres, téléphone portable, jeux.

Reply

DEBORA Laurence
DEBORA Laurence
8/13/2011 9:23:03 AM #

Le système d'Enchère automatique sibeys.com, vendez aux enchères vos objets neuf.

Reply

Billiga fönster
Billiga fönster
9/2/2011 9:05:08 AM #

Very Interesting. Is there any more information on the topic?

Reply

UGG Kensington
UGG Kensington
10/31/2011 6:10:58 PM #

<br>The autumn collection of <a href="www.kensingtonoutlet.co.uk/"><strong>UGG Kensington</strong></a> are now in stock . The range features Kensington boots.
<br><a href="www.kensingtonoutlet.co.uk/"><strong>UGG Kensington Boots</strong></a> catalog has boots, sneakers and slippers are for everyone that wants to keep warn and dry with the ultimate style. <a href="www.kensingtonoutlet.co.uk/"><strong>UGGs Kensington</strong></a> are the hot boots right now yet there are lots more Ugg cozy feet products to choose from. The winter season selection has an exiting choice in a number of colors.
<p><a href="www.kensingtonoutlet.co.uk/"><strong>Kensington UGG Boots</strong></a>
<a href="www.kensingtonoutlet.co.uk/"><strong>Kensington UGGs</strong></a> are fashionable biker type boots that are amazingly comfy. Totally <a href="www.kensingtonoutlet.co.uk/"><strong>Kensington UGG</strong></a>, 100 % cool. They really are warm too because the insole is authentic sheepskin and so are the lined uppers. The external soles are a particular EVA with synthetic rubber pod inserts, so no chance of slipping and they last forever too.

Reply

Podrywanie
Podrywanie
11/25/2011 4:39:04 AM #

Great article! I added it to favorites. I’d like to know more. I’m waiting for it.

Reply

Pingbacks and trackbacks (1)+

Add comment

biuquote
  • Comment
  • Preview
Loading

Month List

RecentComments

Comment RSS