The tooling necessary to incorporate DevOps practices is already out there. There are many different flavors of build tools, Jenkins, TeamCity, VSTS, AppVeyor, and Bamboo. Release management is covered by Octopus Deploy, Release Management, and Bamboo. And finally, database automation tooling provided by Redgate SQL Toolbelt and Microsoft SSDT. The tooling is out there. It has been out there for years.

What is slowing down DevOps adoption? People. Compounding that, the more people you have to sell, the slower the adoption will be.

In my limited experience in adopting DevOps practices, I've found it is typically the front-line individuals (system admins and developers) in the large organizations who push for DevOps. I have yet to see the people who make the final decisions or the ones who approve the budgets push for DevOps. Which makes a degree of sense, system admins and developers live with these problems every day.

What this means is system admins and developers have to become salespeople. As an introvert who was a salesperson for a couple of summers in college that is the last thing, I want to do. But it is necessary, especially when asking for $50,000 for licenses to a suite of tools and dozens of hours to adopt those tools.

Which brings me to the point of this article. A good salesperson has a set of reasons why someone should buy a product. This article will dive into one of those reasons for DevOps, increased database security.

Databases Are Critical

Don't bury the lead! Databases are critical. If you bring down a database server you might stop a company in its tracks. Like I did. Every company I have worked for lives and dies by the data in the database. It is much easier to recover if an application server crashes than if a database server crashes. This is something which people can easily agree on. And with databases being so critical, securing them is vitale to the health of the company.

Permissions

For those readers who have yet to adopt automated database deployments let me ask this series of (leading) questions. Who all has access to your production databases? What specific permissions do they have? Are you really sure about your answer?

I've worked in large development organizations and on small development teams. Almost always there was someone who had elevated permissions on a production database they shouldn't have. Perhaps they had the ability to run a stored procedure that could insert a record. Or, they were added to the db_datareader and db_datawriter role. To add to the chaos, even more the permissions are rarely consistent across the environments.

Adopting automated database deployments forces you to come up with a permission policy. Through a tool such as Redgate's DLM Automation, that policy is then enforced across all the environments. This is because Redgate's tooling creates a set of scripts representing the schema to store in source control. Those scripts are used in the deployments. Included in those scripts are the permissions to access the database objects. Redgate's DLM Automation will remove any permissions which do not appear those scripts.

Important Note: it is still possible to have different service accounts for each environment. What is typically done is a filter is created to exclude service accounts from source control. Then a role is created for the service accounts. The role is the only thing checked into source control. One of the switches for Redgate's DLM Automation is to ignore users and role membership. The service account is manually added to the database once and added to the role once.

Reducing Elevated Access

The need for a large group of people to have elevated permissions decreases dramatically, for all environments, not just production, as more databases are placed into the Automated Database Deployment pipeline. A good chunk of the time the people who had the elevated access needed it to deploy schema changes.

Automated database deployments require any change first be committed to source control. If a person makes a change to a test SQL Server without checking in that change then that change will be deleted the next time the database is deployed through the automated process. Soon the thought process will be if schema changes are just going to get deleted when they don't follow the process, do they really need db_owner rights?

Audit Trail

Security is about much more than permissions. Some of the most common questions I hear is "when was that done?" and "who made that change?" and let's not forget my favorite "who approved that change?"

Redgate's SQL Source Control provides part of the answer by saving database changes into source control. That provides the audit trail of when a change was made and who made the change. Requiring pull requests for source control provides one approval audit record. Octopus Deploy provides the remaining audit records because it records who requested a change be deployed to a server and when it was requested. Octopus Deploy also has the concept of manual intervention which requires an approver to proceed. That approval is also logged. All of this information can easily be retrieved.

Compare that to manual database deployments. When a developer is making a change to a stored procedure they were "required" to put a comment at the top as to what they changed.
Two major problems with that. If the change was large then the comment would often be lacking in detail. I can't tell you the time I saw "refactored the sproc to make it better" as the comment. The second problem is there is no good way to enforce that.

Consistency

Automated database deployments help enforce consistency. Once a database change is added to source control it will be consistently applied to all databases throughout the environments. This means if a schema change plugs a security hole then it will be applied to all the environments. Not just the one environment with the problem. And automated database deployments can apply it quickly, in some cases quicker than a human can.

Conclusion

Asking for money from a company can be tough. It can be even tougher when the amount of money approaches the salary of a seasoned employee. And let's be honest, Redgate's SQLToolbelt (required for automated database deployments) ain't cheap. Compared to say Octopus Deploy (different tool purpose, but it is a lot cheaper for the enterprise license). But Redgate's SQLToolbelt is a best in breed tool which will increase the security of the databases. If I had to do it all over again at the first company I tried to get this tool adopted...Wow, that was a mouthful. Fine, let's try this again. I wish I thought of this back when I started my journey down the automated database deployment path at Farm Credit of America. Looking through the prism of security helps both technical and non-technical people get on the same page.