Best way to determine Dev/Test/Prod environment in MS SQL Server 2012+? 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 &quot;Environment&quot; 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 &quot;settings&quot; 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?
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.