SQL-aliases to create connectionstrings that work everywhere

When using different environments (staging) for development, testing and production (OTAP (dutch)), and application usually requires a different configuration for each environment.

This can be done manually during the installation (a custom screen asks allows modifying specific configurationsettings) or after the installation (by modifying configuration files (e.g. web.config)). It also possible to create different configuration files for each environment and use Visual Studio to package the correct file during compilation (check Scott Guthrie's article).

Another solution would be to centralize configuration and let the application retrieve its configuration during startup by contacting the central configuration service. This central service could then automatically determine to which environment the requestor belongs (e.g. by IP-address) and supply the correct configuration.

However… if the connectionstring is the only setting that differs in each environment, SQL-aliases may be all you need!
A SQL-alias can be used as a reference to a database-server. This way the connectionstring can refer to the SQL-alias, instead of the actual machine/instance of the database-server. By configuring the SQL-alias in all environments (and make it point to the database-server for that specific environment), the connectionstring can be the same in all environments.

Figure 1: A list of all available SQL-aliases

Figure 2: Adding a new SQL-alias

<?xml version="1.0"?>
<configuration>
  <connectionStrings>
    <add name="AdventureWorksDB"
            connectionString="Data Source=AdventureWorks;Initial Catalog=AW_DB;Integrated Security=SSPI;"
            providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

The connectionstring in the above config-file will work anywhere, since it only references the SQL-alias, not the actual database-server.

Although SQL-aliases have been around for some time (SQL 2000 supports them as well), I hardly ever see anyone use them. Instead they struggle with DNS-entries (which supply similar functionality, but require network-configuration and can only function as an alias for a machine, while a SQL-alias also targets a specific SQL-instance, port and protocol), different config-files for each environment, host-files, etc. Therefore I figured it might be useful to shed some light on this very useful, but hardly used feature!