viernes, 9 de enero de 2015

Alias for SQL Server named instance

In this entry I'll describe a simple solution for a common problem that I saw while working on development teams with multiple coders.

Every time you install SQL Server, you might give the instance that you are installing a new name, or not and SQL Server will use ".". The problem is that when you start working with other coders on the same project, their instances names probably wont match yours and this generate problems in the project's connection strings.  This happened to me multiple times and every time I did a pull from our git repository I had fix the connection string.

Named instances aliases are a great solutions for this problem! Basically the only thing that you need to do is to define an alias with your coworkers and set it up. If you work with different groups of people in different projects, you can adds new aliases pointing to your SQL Server instance, and you can forget about fixing connection strings.

Lets see how to do this:

1) Open "Sql Server Configuration  Manager"

2) Open the network configuration and enable the TCP/IP protocol if you have it disabled

Note: I have 2 sql server instances, Im doing this for my "SQLSERVER2008R2" instance

3) Open the TCP/IP protocol properties and enable the "Listen All" properties so accept connetion on any ip that your computer has.

4) Grab the port where your instance will be accepting the TCP/IP connections. For this, right to the bottom you need to set "0" as the value for "TCP Dynamic Ports" and restart your sql instance. Once you do this, you will get a port number assigned and it will be reserved for this instance in the future. Copy the port number as you'll need it for the next step

Here I already have the port assigned
5) Now do a right click on the "Aliases" section and select "New Alias". You will need to specify, the name for the alias (this is what you will use in your connection strings, we used "SQLDev" with my team), the port number that you grabbed from step 4, the protocol (TCP/IP) and the server (which is the machine name, you can use ".", and the named instance if your instance is named if not it works with ".").

6) Use your Alias! 

This is how to use it with SQL Server Management Studio
You can use this alias in any connection string. Enjoy!

No hay comentarios:

Publicar un comentario