Today, somebody asked about how to reconfigure a default setup of SQL Server 2005 Express so you don't have to include the Instance name.

I've encountered this kind of problem myself in the past.  I mean... come on... what the heck with that long *#*^#!@ name SERVERNAME\SQLEXPRESS.  Why can't I just connect to it using SERVERNAME instead?

Well, guess what, you can.  Using a feature in SQL Server Client configuration called Alias. Mind you, this is a hack on the client machine and not on the server.  Meaning if you are going to use the Alias, you will have to configure it on every single client that will use the new alias.  If you would rather reinstall the SQL Server Express as a default instance, you can follow this link.

Me, I am fine with the alias technique since it does not require me to reinstall the server (pain in the ass, well, not really, but being a lazy ***...) and most of the time I need this to simulate a live environment on my development machine (instead of having to change connection string settings every single freaking time I want to deploy, I don't have to anymore since the name of the live server is being used as an alias on my local SQL Server Express installation).

Now, How do I do this, you might ask...

Well, lucky you, follow these simple steps (I will be using Named Pipes protocol for this example):

1. Say I have a SQL Server 2005 Express installed on my machine (JIMMYPC), with instance name: JIMMYPC\SQLEXPRESS
2. I then launch SQL Server Configuration Manager tool
3. Navigate to the SQL Server 2005 Network Configuration in the tree view, open that so I can see Protocols for SQLEXPRESS node.
4. I click on the Protocols for SQLExpress node, it will show me a list of network protocols available to SQL Server.
5. I enable the Named Pipes protocol by right-clicking on it and choose Enable from the context menu.  The tool will notify me that the changes will take effect when I restart the SQL Server service
6. I then click on the SQL Server 2005 Services node on the tree view.
7. Right click on the SQL Server (SQLEXPRESS) instance item and choose Restart to restart the service.
8. I navigate to the SQL Native Client Configuration node in the tree view, open that so I can see Client Protocols and Aliases sub nodes.
9. Right click on the Aliases node and click on New Alias… from the context menu, this will bring up the New Alias dialog box.
10. I enter a new alias in the Alias Name field.  For example: JIMMYPC (to simulate removing the SQLEXPRESS instance name)
11. I enter in the Server name in the server field. For example: JIMMYPC\SQLEXPRESS
12. I change the protocol to Named Pipes in the Protocol field, the Pipe Name field will be automatically populated by the tool.
13. Click OK to save the changes.
14. I then launch SQL Server Management Studio Express (or any other SQL Server client tool to connect to the SQL Server)
15. In the Connect to Server dialog, under Server Name field, I enter in JIMMYPC and click Connect button to make the connection.
16. The client tool should now be connected to the same JIMMYPC\SQLEXPRESS instance but using the new alias (JIMMYPC)
17. At this point, I can change my custom application setting connection string if needed to say: SERVER=JIMMYPC;DATABASE=mydb;Integrated Security=true instead of SERVER=JIMMYPC\SQLEXPRESS;DATABASE=mydb;Integrated Security=true and it should still work.