;

how to configure sql express as a default instance

Posted : Thursday, 24 January 2013 09:38:08

Quite often during team development we come across situations whereby connection strings used in .Net configuration files (e.g. web.config and app.config) refer to an server and optionally instance name, for production,staging & test deployments this isn’t a problem as config transformations can be used to produce consistent and repeatable deployment configurations. During development however (particurlarly during web development) the story is a bit different. Almost invariably different developer machines will have different instance configurations, some may have no default instance, some may only have SQLExpress, some may have different versions of SQL Server etc. this makes the management of a common configuration file (eg web.config) a bit of a pain as developers will lneed to make sure that during debugging each connection string in use is pointing at their database. Various strategies for managing this exist, such as all developers using a central development database the two major flaws with this strategy is that it doesn’t support remote development and that developers could easily make breaking changes that require the whole team to update their development environment (for example adding a foreign key constraint to an existing table). Another (and more frequently employed) strategy is for each developer to make changes to the configuration file as required, this works but is mildly annoying and hurts productivity.

A strategy that has served me well for managing this is to update the configuration file to use a named server with no instance name such as that shown below:

    <add name="ProspectDbContext"
         connectionString="data source=APPLICATION_DATABASE;UID=user;Password=password;database=databasename;"
         providerName="System.Data.SqlClient" />

This is then committed to the VCS and will remain unchanged (in terms of teach particular database connection string at least).

Next add an entry to your hosts file to map this name to whatever machine your dev database is on (typically 127.0.0.1):

#local host mapping for APPLICATION_DATABASE
127.0.0.1    APPLICATION_DATABASE

The final step is not required if the database you want to use is on the default instance. If it isn’t (for example you want to use SQLExpress) then you need to use the SQL Server Configuration Manager to add an alias for the instance you want to use. To do this open SQL Server Configuration Manager and under the SQL Native Client xxx Configuration Node, right click Aliases and select “New Alias…”. Then enter the name you used above and the instance that wish to map to:

image

NB if you have 64bit SQL Server installed make sure you set up the alias for both 32 and 64 bit client configurations:

image

You can test if this works by connecting to the non-default instance using SQL Server Management Studio:

image

This strategy enables a common common application configuration to be used while allowing each developer to configure their machine however they choose, it also allows for a disconnected development scenario.

  • (This will not appear on the site)