Without the help of Redgate, Farm Credit Services of America's automated database deployment process would not be where it is today. I'm not just talking about their tooling. Their employees have been an invaluable resource. In September 2014 Redgate sent a pair of employees, Alex Yates, a pre-sales engineer and Sean Newham, a software developer, to Farm Credit Services of America. Their work paved the way for our automated database deployment process.

I was part of the team that worked with them to help refine our process.

Before Redgate Automation

The time before Redgate automation was truly the wild west of database development.

We have four environments, development, testing, staging, and production. Developers have admin rights to both development and testing SQL servers. That led to this being the development and deployment process for database changes.

1. Dev - Make necessary changes on the development SQL server.
2. Deployment - right click in SSMS editor window and change connection to testing SQL server. Run script.
3. Dev - Make code changes to make use of new SQL server changes.
4. Testers - Test changes, find bugs. Developers fix issues and re-push. Repeat.
5. Dev - Run SQL Compare and SQL Data Compare between testing and staging SQL servers. Generate scripts and supply them to DBAs to run on staging.
6. Deployment - DBAs run script files on staging.
7. Testers - Verify all changes are up in staging.
8. Dev - Run SQL Compare and SQL Data Compare between staging and production SQL servers. Generate scripts and supply them to DBAs to run on production.
9. Deployment - DBAs run script files on production.

In a perfect world, all that work would be done in a short time span, say a week, to eliminate database drift between environments. This process was full of points of failure.

• Manual generation of script files between environments.
• Database environments inevitably have drift between them, so scripts would have to be regenerated multiple times.
• The same script is not used in multiple environments. When a production deployment occurs, there is nothing more than hope that nothing will go wrong. Something always went wrong.
• Forces a human to keep track of all changes, and they must know what should and shouldn't be deployed.
• The window of time between when a database change is made and when code catches up to the database change. Fine for non-breaking change. A massive pain for a breaking change.
• Changes could be deployed to any of those servers at any point. There is no control. It becomes the wild west.

Redgate Proposal

One of the database architects talked with the enterprise architect of databases about how he would like to improve the process. How can this wild west mentality be eliminated? The DBA recommended getting in touch with Redgate.

After some negotiation, a working proposal was agreed upon. Redgate would supply a team of the developers the necessary licenses to prototype an automated database deployment process. In return, Redgate would have the option of writing a case study on Farm Credit Services of America.

My team was the one chosen. This happened because of the following reasons.

1. New Project - New Database - we were creating a new database from scratch which made us more flexible when defining the process. Adjusting on the fly is a lot easier with a small database than a larger established database.
2. TeamCity / GIT - When I became lead dev I immediately moved the project from TFS 2010 onto TeamCity and GIT. The vast majority of the other teams were still using XAML-based builds. Editing those builds was the worst experience possible. TeamCity made it easy, very easy, to add and change build steps
3. Motivation to change - The new project was a rewrite of an existing application. The existing application has roughly 3000 stored procedures and 300 tables. There were way too many times where a deployment occurred and a table update or a stored procedure update was missed. Automation was the solution to that problem.

Prep Work

Alex and Sean were scheduled to arrive several months from when my team was chosen. I did not want to sit on my hands waiting for them. I wanted to get as much setup as possible and then have them come in and tell me what I did wrong and how can we improve it.

I had a feeling the DBAs would not be keen on me forcing a new process on them without proving it out in the development and testing environments. During this time, I knew I had to follow the existing process. Meaning, in order to go to staging and production a delta script had to be generated.
There were no rules stipulating that script generation couldn't be automated.

Without going into the gory details I was able to setup the following process prior to Redgate's employees showing up.

1. Database under source control using Redgate's SQL Source Control. This was using a shared database model where the development SQL Server was used as, well, development. All developers on my team got their machines setup to take advantage of this.
2. TeamCity - Checking into SQL Source Control would kick off a CI build to build the database package
3. TeamCity - Promoting to the testing environment would involve the promotion of the database package using the Redgate SQLCI TeamCity plug-in.
4. TeamCity - Promoting to the staging environment would create a deployment script using a custom step that invoked SQL Compare and SQL Data Compare. This script would be dropped into a share which the DBAs could access.
5. TeamCity - Promoting to the production environment would create a deployment script using a custom step that invoked SQL Compare and SQL Data Compare. This script would be dropped into a share which the DBAs could access.

Doing this prep work helped lay the foundation for when the Redgate employees showed up. I had to learn quite a bit about how SQLCI, SQL Compare Command Line, and SQL Data Compare Command line worked. I felt like we had much more intelligent conversations during their time here.

Redgate Visit

The Redgate visit was scheduled for two days. The general overview of each day was:

1. Day One - discuss existing process, work through updated process
2. Day Two - refine updated process, present to all developers at end of day.

Day One

Within a few minutes, it was very apparent both Alex and Sean knew their stuff. Each company they have had to work with is unique in the demand of their process. Alex and Sean knew how to take advantage of the tooling offered by Redgate to improve the process.

We talked through the existing process, what work has been done so far, and ways we could improve it. Before getting too far into how we could improve it we talked about what are some of the obstacles we would encounter with each change. Not only would we face technical obstacles, but also getting buy-in from all the developers, DBAs and database developers. A lot of cheese was being moved.

Alex and Sean knew to ask the right questions to make sure the solution would work. They could easily have come in and tried to force feed a solution to us, but if some regulatory restriction was put into place the process would fail to get adopted.

Our end goal was completely automated database deployments. The same package created and deployed to development is then promoted up through all the environments. Alex and Sean are smart enough to know we first need to build trust in the process before getting to that.

One of the major changes they recommended was local databases. Each developer has their own instance of a database running locally. This provides a lot of benefits. Namely:

1. Schema changes and code changes go hand in hand. This allows developers to get all the schema changes needed (table changes, sprocs, views, functions, etc.) working locally before pushing them out.
2. Supports branching strategies. A shared model only supports one branch, master. With dedicated you better hope all new changes made to your database are non-breaking changes or you will have issues. When using the shared model developer A renamed a single column while developer B changed the code. The rename took five seconds the code changes took a few hours. During that time our development environment was down until we rolled back the change until the code was complete. It should have all be done in a branch.
3. Local data to mess up. If I mess up my database no one but me cares. I can easily recover. I don't hurt anything.

By the end of the day the outline of the process looked like this:

1. Database under source control using Redgate's SQL Source Control. This was using a dedicated database model where each developer had their own instance of SQL Server.
2. TeamCity - Checking into SQL Source Control would kick off a CI build to build the database package. When checking into master. This would also automatically promote to the development SQL Server
3. TeamCity - Promoting to the testing environment would involve the promotion of the database package using the Redgate SQLCI TeamCity plug-in.
4. TeamCity - Promoting to the staging environment would create a delta deployment script between what is in the database package and production as well as a delta script to get staging to match production. Each time we went to staging it would be rolled back to match production. This way the same script is used for promoting to staging and production. Close to build once, deploy anywhere.
5. Manual - the DBAs would run the same script that went to integrated.

Day Two

Day two consisted of refining the process after having a chance to sleep on it and implementing the updated process. Because of all the prep work done beforehand we were given an extra challenge. Implement it using TeamCity AND using Octopus Deploy.

Alex and Sean helped us get Octopus Deploy installed on a server. The DBA got a tentacle installed on the development and testing SQL Server. We were able to prove out the process and work through any kinks.

Side Note: this installation of Octopus Deploy was used by the Web Admins and another development team to help convince the company to move away from Release Management as our deployment tool. Both tools had the same general idea of taking a package generated by a build and deploy it to servers. But the offering from Microsoft was lacking in many key areas. They were able to unambiguously demonstrate Octopus Deploy is much, much superior to the version of Release Management Microsoft put out two years ago.

After lunch we worked on the presentation Alex and Sean would give to all the developers, database developers, and leaders. The presentation was scheduled to last for an hour. It was impressive how they were able to fill that much time. I spent almost 30 hours on a 45-minute presentation and they got one together in a single afternoon!

After the presentation, we bid our farewell. Now it was time for the real work, implementing the proposed process.

Post Visit Roadblocks

We ran into three major roadblocks towards our adoption of this process.

1. Company-wide project - we had a major project which involved every single development team and a tight deadline. Getting teams onboard with automated database deployments had to take a back seat.
2. Redgate Changing DLM Automation Suite pricing - the day Alex and Sean left the price for a single license of Redgate's DLM Automation Suite was $300 - $400 depending on how many was purchased. A month or so later that jumped to $2000 - $5000 per license depending on how many was purchased. This caused the purchase of additional licenses to be moved to the next fiscal year. Redgate has listened to feedback regarding this and has adjusted their licensing to be a lot more friendly and affordable. Still, it was a bit of a sticker shock.
3. Microsoft SSDT Tooling Considered - the increased cost of licenses forced us to take a hard look at Microsoft's SSDT tooling. Could it do what Redgate did? Could we build additional functionality around it? In the end, the answer was no, but that delayed us as well.

Process Refined

We soon learned rolling back staging to match production was a lot more trouble than it was worth. We often dropped tables or columns by doing the rollback which messed up a lot of data added by testers in the staging environment. They were very confused why their data kept disappearing. We eventually abandoned that step completely and went back to generating the delta script prior to production deployment. That only lasted a few months because shortly after that we dropped TeamCity and started adopting Visual Studio Team System and Octopus Deploy for automated deployments.

This allowed us to have this process.

1. Dev - make necessary changes on a local SQL server. Update code to reflect database changes.
2. Dev - check in code changes and database changes. Push changes to source control server.
3. CI Server (VSTS) - build code and database packages. Run any automated tests to ensure the validity of changes.
4. Deployment (Octopus Deploy) - automatically deploy database changes to development SQL Server. Verify changes.
5. Deployment (Octopus Deploy) - automatically deploy database changes to testing SQL Server. Verify changes.
6. Testers - Test changes, find bugs. Developers fix issues and re-push. Repeat.
7. Deployment (Octopus Deploy) - push a button via the Octopus Deploy website to deploy database changes to staging SQL server. Verify changes.
8. Deployment (Octopus Deploy) - schedule the deployment of database changes to production SQL server. Verify changes.

Conclusion

Without Alex and Sean's visit and Redgate's support, I doubt we would be where we are today. It is a testament to Redgate for hiring such quality individuals and making great tools. They have helped us save a lot of time, we used to have to spend up to an hour just to generate all the scripts promote to staging, we would then have to turn around and spend the same amount generating new scripts before going to production. That has all been reduced to less than a minute. And we don't have any emergency deployments because we forgot a database change. The business has found a lot of value in this as well, they are a lot more comfortable with us pushing to production a lot more often than before. Previously it was once every couple of months, now we can go once a week (more if needed). This has gotten us closer to our dream of continuous deployments and go multiple times a day.

They were the ones who recommended and/or really pushed:

1. Local database development
2. Octopus Deploy
3. Build Once, Deploy multiple times
4. Faster Feedback, get changes to testers quickly for faster feedback

Side Note: they also showed us how to get a CI build setup in TeamCity (which I later adopted for VSTS) to run on every branch checked in. This allowed developers to get faster feedback on their code and see if their changes would compile with all unit tests passing prior to checking into master.

Since their visit, Alex and Sean have moved on from Redgate. Alex has started his own company, DLM Consultants and Sean now works for Thoughtworks. I'm not 100% sure if Sean gets to do this type of work at his new company, but I know Alex does. That is the whole reason he founded his company. He even offers a package similar to his visit with FCSA through his company. Highly recommended.

I don't know if Redgate currently offers this to everyone. Due to the cost, your IT shop might have to be a certain size. I have some questions out to Redgate regarding this. Once I know the answer I will update this post.