All of us have Production database servers and hopefully, you also have additional database servers for Development, QA, and UAT. Some IT shops will also have a Continuous Integration server and maybe other servers. If you only have Production servers this needs to be addressed and is outside the scope of this post. In the locations where I have worked, we also have a Scrub server. The question is when a script executes, do you know what environment the query is executing in? Most scripts will not care what environment the script executes in but other scripts could cause damage in a Production environment. For example, if the script is removing email addresses so you don’t spam your clients with automated email messages, you would not want the script to execute in a Production environment.
So how do you make your database server environmentally aware? Here is how I do it:
- Create a DBA database to use for DBA purposes, not master!! I call mine, Admin. Only restore this database to the same server or a new server that is replacing the old server. It will contain the server environment information.
- Create a table to store specific information about the server.
- Add fields to store information key and information value. Example: Environment = Production or Environment = QA
- Populate the fields with Environment and the value for the server you are on. You can do this by maintaining a population script.
The DBA database will be on every database server including developers computers, Environment = Dev. No database server will be excluded. If SQL Server is installed on the computer, the DBA database will be on the computer.
Once this has been accomplished, you are now able to make your scripts environmentally aware. How would you do this? When your script needs to be environmentally aware, get the environment location from you DBA database. Here are two examples of how I use the environment information:
- Data change scripts – If I don’t want the change script to execute in Production, at the beginning of the script I would check the environment location. When I am in the Production environment I would just return.
If @Environment = ‘Prod’
- Security scripts – I change the value of the security based upon environment
Case when @Environment = ‘Prod’ then ‘ProdAccount’
When @Environment = ‘UAT’ then ‘UATAccount’
When @Environment like ‘QA%’ then ‘QAAccount’
When @Environment = ‘Dev’ then ‘DevAccount’
Notice, QA may have multiple servers and if I am using the same security account, I could use a like to search multiple QA servers.
Protect yourself and make your database servers environmentally aware!