Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+?

All we need is an easy explanation of the problem, so here it is.

We have 3 MS SQL Environments: Dev, Test, and Prod. There are many Dev servers, and 1 each in Test and Prod.
What is the best way to determine the "Environment" from a query? For instance, we want to watermark Dev and Test SSRS reports if it’s dev or test.

I don’t want to hard code a check for Prod’s server name, in case the server name changes.

I also don’t want to check a "settings" table in the database, because then I’m depending on the environment’s restore scripts running successfully.

Are there any other solutions that will work from a query?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Given your requirements and desire to keep things simple, I would suggest a bit of both options.

Create a setting table on your production server. It should have every server that is considered production (so if you have HA, all node names).

Then in your report / business logic that needs to know if it’s NOT on prod, have it compare @@SERVERNAME with what’s in the list. If it finds a match, it knows it’s in prod. Otherwise, it must be somewhere else.

The advantage of this is that if you restore prod to somewhere else, that it immediately knows that it’s not on a prod server and you can do your special display logic.

Changing Production servers probably happens rarely enough that you can safely include this in your production migration process. You can even have a job that runs only on the prod servers that will insert the @@SERVERNAME into the settings table (not sure if that works for you or not though). But in any case, if you forget to update the setting table when you move prod, it will blow up noisily and can be quickly fixed.

Method 2

You could create a utility database on each server with a table(s) to hold metadata about that server (environment, purpose, build date etc). As each utility database is tied to a server/instance, it won’t get refreshed along with any databases that get refreshed so you don’t have to worry about it being inaccurate if a refresh fails.

Include the deployment of this DB in your standard build process and then you can simply reference this DB in your query to determine environment. If you keep the same name throughout your SQL estate, then your queries don’t need to change between servers either.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply