When implementing any kind of automation for database deployments, it’s important to implement safeguards for your production environment. This is needed even in the best conditions when team members collaborate well and there is a high level of trust, for a very simple reason: accidents happen easily!
As a person who has accidentally destroyed quite a lot of things VERY RAPIDLY with automation
, I am a fan of adding protection around production environments to lower risk for everyone, including myself.
On the other hand, it’s also desirable to empower team members to be able to quickly build and experiment with automation in other environments. It’s best to not lock down more than you need to.
In this post I’ll give a quick overview of three control points which help protect your production databases when working with Azure DevOps Server/Services (or legacy Team Foundation Services). The controls I’m discussing here apply to SQL Server instances where you are deploying changes with self-hosted agents.
There’s more than one way to do these things, so if you’ve got thoughts about your favorite alternatives, I’d love to hear about them in the comments.
1. Control who can create new pipelines against production instances
While it’s useful to let folks create new pipelines and edit them against all sorts of development, test, or QA resources, not everyone should be able to create a pipeline, point it to an existing production database, and deploy changes to it.
One simple way to manage this is to create one or more agent pools for production SQL Server instances and use the security properties on the agent pool to limit who can create pipelines using that agent pool. For those who need to see the progress of pipelines/monitor deployments, there is a ‘Reader’ role available.
This could also be done with deployment groups for parallel execution.
2. Control what the agent can do
When you install a self-hosted agent, you are prompted to configure not only which agent pool it uses, but also which account the agent will run under. If you wish for that agent to only be able to deploy to one SQL Server, you may choose to use an account which you have granted only permissions for that SQL Server instance.
In other words, if you are concerned about the agent being used to communicate with other SQL Server instances across the network in your production environment, you have the ability to control this by using an account which only has permissions granted against the SQL Server instance of your choice.
3. Control what can be done within an existing release pipeline
Pipelines are quite flexible and there are many things you can do with them in terms of gating and approvals. Two very powerful controls which you may choose to configure right away for pipelines with production targets are…
Configure who can run vs edit vs view a pipeline: Pipeline security can be configured to allow people to interact with an existing pipeline in many different ways – for instance some users/groups perhaps should be able to execute the pipeline but not edit the pipeline definition.
Configure approvals within the pipeline: Stages may be edited to set approvals. An example with Redgate tooling is that you might create one stage to create a release artifact for a production change, then another stage which performs the deployment of that release artifact. You might choose to add an approval step at the entry to the deployment stage.
It’s OK to start slowly
If this is a bit overwhelming, remember that you don’t have to automate everything at once, and in fact it’s preferable to have a strong foundation for automation. The first step is to identify the current pain points and constraints for your team and start from there. If your database code isn’t yet in version control, beginning there is very likely the first step. If provisioning dev, test, or pre-production environments is slow and cumbersome, working on automating provision and deployments there will help increase your velocity. But when it does come time to begin automating deployments to production environments, do take the time to examine the available safeguards and implement the right controls for your organization.