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?
Best Answer
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.