Managing ASP.NET Development, Staging and Production Connection Strings (without pulling your hair out)

If you work with a large number of applications then you know what a pain it can be to manage multiple web.config files and multiple connection strings for development, staging/test, and production databases.  Because developers typically have development, staging, and production connection strings to manage (depending upon the environment they are working in), many different strategies have been created to handle switching from development to staging/test to production. One way to manage this task is to have multiple web.config files and simply name them differently. For example, while in development the staging web.config file may be named web.stage.config. When code has been tested in development and needs to be deployed to staging/test, the staging web.config file can be renamed to "web.config" and then moved over to the staging Web Server(s). While this works well it involves an unnecessary step that should be more automated to avoid human error and involves keeping data in-sync between two or more web.config files.

VS.NET build events are another popular way to handle this problem.  Scott Guthrie posted about this topic and links to a post that provides great details if you'd like to go this route. 

When large numbers of developers are involved in creating different applications that have separate web.config files for each application that may hit the same databases, a management issue comes into play. If the database connection strings change at all (for instance moving from passing username/password credentials to integrated security or database name changes due to upgrades) then all web.config files with the appropriate connection strings in them must be updated. If you manage a large number of applications (an Intranet environment for example) then you now have to open many web.config files to modify the connection strings and hope that your search and replace routine catches everything.  A more centralized way to store connection strings needs to be available in this case so that you can go to one place to make changes and be done with it!

The example code that follows demonstrates how to avoid renaming web.config files when applications are moved between environments while also providing a centralized storage location for database connection strings as well as other server information such as proxy servers and mail servers. The code relies upon XML serialization to load data from an XML file named ServerConfig.config into an object-model, detect the environment automatically based upon data in the ServerConfig.config file, and return the appropriate connection string for the environment (development, staging, production, etc.). It also allows primary and secondary connection strings to be accessed in cases where a secondary production database server is available to use when the primary is too busy or down. An example of the Server.Config file used in the downloadable sample code is shown below.  Each server defines the environment it supports as well as the domains that can run on it.  The domains are used to dynamically lookup the proper environment (dev, staging, prod, etc.) at runtime.

<?xml version="1.0" encoding="utf-8" ?>
<ServerConfig>
  <Servers>
    <Server Name="DevelopmentServerName" Type="Web" Environment="Development" UserName="" Password="" Domain="" IP="">
      <Domains>
        <Domain>localhost</Domain>
      </Domains>
    </Server>
    <Server Name="StagingServerName" Type="Web" Environment="Staging" UserName="" Password="" Domain="" IP="">
      <Domains>
        <Domain>staging.xmlforasp.net</Domain>
      </Domains>
    </Server>
    <Server Name="ProductionServerName" Type="Web" Environment="Production" UserName="" Password="" Domain="" IP="">
      <Domains>
        <Domain>www.xmlforasp.net</Domain>
        <Domain>xmlforasp.net</Domain>
      </Domains>
    </Server>
    <Server Name="ProxyServerName" Type="Proxy" Environment="Production" UserName="" Password="" Domain="" IP="">
      <Domains>
        <Domain>your.proxy.server</Domain>
      </Domains>
    </Server>
    <Server Name="MailServerName" Type="Mail" Environment="Production" UserName="" Password="" Domain="" IP="">
      <Domains>
        <Domain>your.smtp.server</Domain>
      </Domains>
    </Server>
  </Servers>
  <ConnectionStrings>
    <ConnectionString Database="Northwind">
      <Primary>server=prodServerName;Integrated Security=SSPI;database=Northwind</Primary>
      <Secondary>server=secondaryProdServerName;Integrated Security=SSPI;database=Northwind</Secondary>
      <Staging>server=stagingServerName;Integrated Security=SSPI;database=Northwind</Staging>
      <Development>server=devServerName;Integrated Security=SSPI;database=Northwind</Development>
    </ConnectionString>
    <ConnectionString Database="Pubs">
      <Primary>server=prodServerName;Integrated Security=SSPI;database=Pubs</Primary>
      <Secondary>server=secondaryProdServerName;Integrated Security=SSPI;database=Pubs</Secondary>
      <Staging>server=stagingServerName;Integrated Security=SSPI;database=Pubs</Staging>
      <Development>server=devServerName;Integrated Security=SSPI;database=Pubs</Development>
    </ConnectionString>
  </ConnectionStrings>
</ServerConfig>


Once the ServerConfig.config file is created you can reference it in web.config (or better yet, in a higher level config file that is global to the server) by adding the following:

<configuration>
    <appSettings>
    <!-- Suggest placing this in a more global location rather than in multiple web.config files -->
        <add key="ServerConfigPath" value="~/ServerConfig.config"/>
    </appSettings>
</configuration>


The "ServerConfigPath" key is a fixed name in this case since the object model looks specifically for that key.  Where you put the ServerConfig.config file is up to you of course, but putting it in a place that multiple Web applications can access is recommended. 

A sample of accessing the Northwind database connection string from a data layer class is shown below.  A class named ServerConfigManager does all the work of looking up the proper connection string to use based upon the domain that the application is currently running within.  If no matching domain is found, it defaults to the development environment (that behavior can certainly be changed) and returns the development connection string.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using Wahlin.Configuration;

namespace ServerConfig.Data {
    public class Northwind {

        public static SqlDataReader GetCustomers() {

            ConnectionString cs = ServerConfigManager.GetConnectionString("Northwind");

            //Use sproc if possible and avoid SELECT * of course.....just a demo!
            string sql = "SELECT * FROM Customers";
            try {  //Hit primary database
                SqlConnection conn = new SqlConnection(cs.Primary);
                SqlCommand cmd = new SqlCommand(sql,conn);
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception exp) {
                //Log error 
                try {    //Hit secondary database if needed
                    SqlConnection conn2 = new SqlConnection(cs.Secondary);
                    SqlCommand cmd = new SqlCommand(sql,conn2);
                    conn2.Open();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch {
                    //Log error
                }
            }
            return null;
        }
    }
}

This code works in development, staging and production since the appropriate connection string to load is dynamically discovered. No code changes or file changes need to be made when the code is moved to production. All of the ServerConfig.config functionality is accessible via an object named ServerConfigManager (as shown above) which has several static methods such as GetConnectionString(string database), GetServerEnvironment(), GetServer() and GetServerConfig(). While the code does not currently encrypt/decrypt the connection strings, that functionality could certainly be added. 

I've used this type of connection string management strategy in a large scale environment very successfully over the past few years but there are always ways to make things better so if you use the code and enhance it please let me know.  All of the code can be downloaded here along with a simple demo Website.

comments powered by Disqus

6 Comments

  • XmlPreProcess works wonders. Been using it for the past year or so. The nice thing is the resultant web.config is easy for our operations people to understand and tweak if necessary. I generate the appropriate web.config at installtime in my MSI package.

  • Relying on exceptions for control flow is pretty expensive. They calculate the stack every time they are thrown and really aren't a great way to go. If I had to pick I would rather have something that detected something at the machine level and made a choice between various options (e.g. a registry value, environment value, IP address mask, etc...). We manage it by renaming files now. It's not great, but it'll do for now.

  • Colin:
    Exceptions don't actually play any role with getting the connection strings, detecting the environment, etc. &nbsp;The code that shows accessing the connection strings in the post is only demonstrating how you could fail over to a separate database (redundancy) which would of course be valid if an error occurred when the primary database was hit. &nbsp;99.9% of the time that secondary database shouldn't be hit so there's no expense unless the exception is actually thrown and in that case you'd want to do something rather than simply causing the application to fail due to having no data. &nbsp;It's pretty standard practice for those that have secondary databases available in case the primary database is down.&nbsp; There's nothing in the ServerConfigManager class code that manages control flow by exceptions though.
    The environment is detected by simply checking the host name that is calling the application and then looking it up in a cached object which gets its data from the ServerConfig.config file that is shown above. &nbsp;Nothing expensive there and it greatly simplifies management in enterprise scenarios where a lot of web.config files exist and doesn't require server modifications such as registry tweaks, environment variables, etc. which cause deployment headaches.
    It's certainly not a solution that everyone will need, but it works well for people that have to worry about a lot of web.config files with embedded connection strings and is useful for accessing server information programmatically as well (such as the SMTP server to use in a production environment or the proxy server to route HTTP requests through). &nbsp;People supporting only a handful of ASP.NET applications can just as easily manage things by renaming web.config files as you mention (I've used that technique as well and it certainly works). &nbsp;In more enterprise scenarios that can be quite painful though when connection strings need to change due to security changes, database name changes, etc. &nbsp;With this particular solution you can go to one place to make those changes rather than to multiple files.

  • Dan,

    This pwns. Thanks for sharing. It's a simple and elegant solution to a problem that many devs, organizations, (and especially) enterprises wrestle with.

    My fav part is how you key off of the current request url. That takes all of the pain out of toggling different configs back and forth (which introduces more possibility of human error). This makes it possible for the computer to do the work (imagine that *grin*).

  • Do the Environment attributes always need to be Production, Staging or Development? For my application, I have 4 different databases that are used. I'll use 2 as an example:





    As long as I use and under the setting, will that work?

    Thanks,
    William

  • William,

    You'd need to update the schema that is used to generate the ServerConfig object (I run the xsd.exe tool to convert an xsd schema into C# as you'll see in the sample code). The schema currently only defines Development, Staging, and Production as valid Environment values. So, if you tweaked the schema and regenerated the C# code (xsd.exe /c /namespace:YourNamespace ServerConfig.xsd) then it would work for you. You can definitely customize it anyway you'd like.

Comments have been disabled for this content.