Collaborating on a DB and having different named SQL instances

Wednesday, March 21, 2018

Most geeks have come across this problem.. You're collaborating on a DB and each of you have different named local SQL instances. what do you do?

Most change the connection string temporarily, then accidentally ruin someone else's setup when you accidentally commit it. 

I've known devs add the web.config to the to the ignore list, loosing all other changes that may have been made to the file.

I'll just keep a set of known good configs here in this folder I'll copy in...

Well, it turns out you can set an alias to use more than one instance name...

e.g. "localhost" can resolve to ".\SQLEXPRESS" 

Follow these steps to add an alias:

  1. Open SQL Server Configuration Manager
  2. Expand SQL Native Client Configuration
  3. Right-click Aliases and select New Alias...
  4. On the New Alias window:
    • Enter .\SQLEXPRESS for the Alias Name.
    • Select the appropriate protocol (e.g., Named Pipes)
    • Enter  .  for the server name (that's equivalent to localhost).
  5. Click on OK to make the change.

Refs: 

  • https://serverfault.com/questions/236666/alias-to-sqlexpress/308708#308708
  • https://twitter.com/KevinGiszewski/status/976190245778665472