0

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?

Improve this question
5
  • If your restore scripts don't run successfully, won't you have a bunch of other problems?
    – Doug Deden
    Commented Apr 12, 2021 at 20:14
  • Have a bit of both? A settings table that contains the prod servers name. Then reports compare @@SERVERNAME to that. If it matches then it's PROD. otherwise NOT prod. Then you only need to make sure it stays up to date in one place. Yes, you will need to change it when/if prod's server name changes, but that will probably be less frequent? could even have a set of names if you have failover groups or something.
    – Jonathan Fite
    Commented Apr 12, 2021 at 20:40
  • We don't build meaning into our server names, but we do build it into our instance names. So Server001\AppX_Dev, Server002\AppX_Test, and Server003\AppX_Prod would be our three database servers for Application X. That lets the server names be whatever fits any other standards you might have, but allows you to parse the instance name to determine the environment. Would that work for you?
    – Doug Deden
    Commented Apr 12, 2021 at 20:59
  • @DougDeden while there's certainly going to be a bunch of other problems, I'd prefer the default isn't wrong. I like the idea of building meaning into the instance names, we'll look at that during the next upgrade cycle.
    – StormRider01
    Commented Apr 12, 2021 at 21:24
  • 1
    @JonathanFite that's a great idea, post that as an answer so I can give points.
    – StormRider01
    Commented Apr 12, 2021 at 21:25

2 Answers 2

Reset to default
0

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.

Improve this answer
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.