How to determine at runtime if I am connected to production database?

Go To StackoverFlow.com

4

OK, so I did the dumb thing and released production code (C#, VS2010) that targeted our development database (SQL Server 2008 R2). Luckily we are not using the production database yet so I didn't have the pain of trying to recover and synchronize everything...

But, I want to prevent this from happening again when it could be much more painful. My idea is to add a table I can query at startup and determine what database I am connected to by the value returned. Production would return "PROD" and dev and test would return other values, for example.

If it makes any difference, the application talks to a WCF service to access the database so I have endpoints in the config file, not actual connection strings.

Does this make sense? How have others addressed this problem?

Thanks, Dave

2012-04-03 21:36
by DaveN59
One problem with using a table is remembering to update it when you restore a backup from prod into your test or dev environments and freaking out when you see PROD when you think you shouldn' - Conrad Frix 2012-04-03 21:49
@Michael - that would work beautifully, but I can't accept it as an answer as it's only a comment.. - DaveN59 2012-04-03 22:00
@DaveN59 In that case, I've converted my comment to an answer.. - Michael Fredrickson 2012-04-03 22:06


5

The easiest way to solve this is to not have access to production accounts. Those are stored in the Machine.config file for our .net applications. In non-.net applications this is easily duplicated, by having a config file in a common location, or (dare I say) a registry entry which holds the account information.

Most of our servers are accessed through aliases too, so no one really needs to change the connection string from environment to environment. Just grab the user from the config and the server alias in the hosts file points you to the correct server. This also removes the headache from us having to update all our config files when we switch db instances (change hardware etc.)

So even with the click once deployment and the end points. You can publish the a new endpoint URI in a machine config on the end users desktop (I'm assuming this is an internal application), and then reference that in the code.

If you absolutely can't do this, as this might be a lot of work (last place I worked had 2000 call center people, so this push was a lot more difficult, but still possible). You can always have an automated build server setup which modifies the app.config file for you as a last step of building the application for you. You then ALWAYS publish the compiled code from the automated build server. Never have the change in the app.config for something like this be a manual step in the developer's process. This will always lead to problems at some point.

Now if none of this works, your final option (done this one too), which I hated, but it worked is to look up the value off of a mapped drive. Essentially, everyone in the company has a mapped drive to say R:. This is where you have your production configuration files etc. The prod account people map to one drive location with the production values, and the devs etc. map to another with the development values. I hate this option compared to the others, but it works, and it can save you in a pinch with others become tedious and difficult (due to say office politics, setting up a build server etc.).

2012-04-03 21:39
by kemiller2002
I'm not sure that applies here. All our users use the same account (production line) and we publish using Click Once. Also, see my later edit about WCF service... We really can publish code that will hit our production service and not know it until too late - DaveN59 2012-04-03 21:47
+1 For aliases.. - Michael Fredrickson 2012-04-03 21:51
Thanks for your thoughtful answer. Part of my original reluctance was due to the fact I am unfamiliar with machine.config. Digging into what I can find on that, all the functions seem to be confined to those settings contained in the configSections portion - which does not include System.ServiceModel. So off I go on a learning expedition... At any rate, yours is by far the most complete answer so you get the check - DaveN59 2012-04-04 20:31


2

I'm assuming your production server has a different name than your development server, so you could simply SELECT @@SERVERNAME AS ServerName.

2012-04-03 22:06
by Michael Fredrickson
I do intend to implement this, but after thinking about it for a bit I realized this is not going to work as the final solution. It does solve the problem along the lines of my original idea, but it's not good to just let the app fail when we mess up, and that's what my original idea would do. Ugh. Anyway, I intend to implement your suggestion as a fail-safe to catch us when we really screw up, but not as the solution to the problem. Thanks for the suggestion - DaveN59 2012-04-04 19:41


1

Not sure if this answer helps you in a assumed .net environment, but within a *nix/PHP environment, this is how I handle the same situation.

OK, so I did the dumb thing and released production code

There are a times where some app behavior is environment dependent, as you eluded to. In order to provide this ability to check between development and production environments I added the following line to global /etc/profile/profile.d/custom.sh config (CentOS):

SERVICE_ENV=dev

And in code I have a wrapper method which will grab an environment variable based on name and localize it's value making it accessible to my application code. Below is a snippet demonstrating how to check the current environment and react accordingly (in PHP):

public function __call($method, $params)
{
    // Reduce chatter on production envs
    //  Only display debug messages if override told us to
    if (($method === 'debug') &&
        (CoreLib_Api_Environment_Package::getValue(CoreLib_Api_Environment::VAR_LABEL_SERVICE) === CoreLib_Api_Environment::PROD) &&
        (!in_array(CoreLib_Api_Log::DEBUG_ON_PROD_OVERRIDE, $params))) {
        return;
    }
}

Remember, you don't want to pepper your application logic with environment checks, save for a few extreme use cases as demonstrated with snippet. Rather you should be controlling access to your production databases using DNS. For example, within your development environment the following db hostname mydatabase-db would resolve to a local server instead of your actual production server. And when you push your code to the production environment, your DNS will correctly resolve the hostname, so your code should "just work" without any environment checks.

2012-04-03 22:11
by Mike Purcell


1

After hours of wading through textbooks and tutorials on MSBuild and app.config manipulation, I stumbled across something called SlowCheetah - XML Transforms http://visualstudiogallery.msdn.microsoft.com/69023d00-a4f9-4a34-a6cd-7e854ba318b5 that did what I needed it to do in less than hour after first stumbling across it. Definitely recommended! From the article:

This package enables you to transform your app.config or any other XML file based on the build configuration. It also adds additional tooling to help you create XML transforms.

This package is created by Sayed Ibrahim Hashimi, Chuck England and Bill Heibert, the same Hashimi who authored THE book on MSBuild. If you're looking for a simple ubiquitous way to transform your app.config, web.config or any other XML fie based on the build configuration, look no further -- this VS package will do the job.

Yeah I know I answered my own question but I already gave points to the answer that eventually pointed me to the real answer. Now I need to go back and edit the question based on my new understanding of the problem...

Dave

2012-04-18 21:31
by DaveN59
I'm glad that SlowCheetah ended up working out for you - Sayed Ibrahim Hashimi 2012-04-29 23:47