How do we alias a Sql Server instance name used in a Connection String .config?

Question

I have two development teams, that come from different groups.

• Group A develops against a local default instance of Sql Server 2008 R2;
• Group B develops against a local named instance of Sql Server 2008 R2.

Is there a way to setup an alias such that both groups are coding against the same name? As it stands right now we have a war of Connection Strings as group B changes (local) to ./DEV and group A changes it back again?

Answer 1

In SQL Server Configuration Manager, under the SQL server native client configuration section there is a subsection called "aliases" you can add an alias into here that points to your named instance. You just use the alias as if it is the default instance on a server with the name of the alias. We use this exact model and have only one connection string that points to a standard alias. Each developer has the same alias name pointing to their own instance.

Answer 2

Aliasing to a Local SqlExpress Instance

To expand on Ben's answer, I had the specific requirement to alias a connection string pointed at a specific Server Instance, and instead re-route this to our local developer Sql Express instances, i.e. to alias from:

SomeServer\SomeInstance

to

.\SQLExpress

This proved somewhat tricky until I found this link here. My Sql Express instance was running on the standard port 1433 (i.e. adapt to suite)

Enable TCP/IP for SqlExpress

Since aliasing is done via TCP/IP ports, TCP/IP protocol must be enabled. (Opening SqlExpress for remote access isn't necessary if you are working locally).

Using Under Sql Server -> Configuration Tools -> Sql Server Configuration Manager:

At the same time, set the Listen All property to Yes.

Enable LocalHost IP's

Ensure that both IPv4 (127.0.0.1) and IPv6 (::1) localhosts are active and enabled.

On each IP's, Leave the Dynamic Port at zero (as the name suggests, the port will be allocated dynamically). The IPAll Dynamic and TCP Ports are then used globally.

You'll need to restart the MSSQLServer / SqlExpress service to effect the change.

Creating Aliases (32 and 64 bit)

Under the Sql Native Client xx Configurations, this requires simply adding the alias "From" Server\Instance as the Alias Name and the actual server + instance as the Server (i.e. my local SqlExpress instance). I was able to connect via both port 1433, or the dynamic port on IpAll (9876), although saw no reason to use the latter. The aliases must be done for both 32 and 64 bit client configurations.

You should now be able to connect using the aliased SomeServer\SomeInstance via SSMS.

Other Notes

• Since I was aliasing to a local instance, I did NOT have to add an alias for the from host, SomeServer to a DNS or LocalHosts. This will likely be required however if you are aliasing to a remote server (plus I guess some other security headaches)
• I did not need to have the Sql Browser service running.

So it would seem that the Sql Client configuration takes care of the substitution prior to any network or security steps.

Answer 3

Personally, I'd make them all use the same box.

That said, you can set local aliases per developer's box using SQL Server Client tools.

Anything IP address or server name based (such as hosts file or DNS) will fail because instance names (and ports used) are different