Category: T-SQL Tuesday

Steve Jones talks about how, at a prior company, he & coworkers simplified their deployment processes and their lives:

The lead developer and I both had little children at the time. Spending 12+ hours at work on Wednesday wasn’t an ideal situation for us, and we decided to get better.

The first thing we did was ensure that all code was tracked in VSS. We had most web code here, but there were always a few files that weren’t captured, so we cleaned that up. I also added database code to VSS with the well known, time tested and proven File | Save, File | Open method of capturing SQL code. This took a few months, and some deployment issues, to get everyone in the habit of modifying code in this manner. I refused to deploy code that wasn’t in VSS, and since our CTO was a former developer, I had support.

The other change was the lead developer and I started building a release branch of code each week. We’d move over the changes that were going to be released to this branch, which simplified our process. We could now see exactly which code was being deployed. This was before git and more modern branching strategies, but we were able to easily copy code from the mainline of development to the release branch as we made changes for this week.

It’s a good read.

Alex Yates gets on his soapbox about continuous integration:

Everyone does CI wrong!

(OK, perhaps not everyone, but a lot of people.)

Whenever I deliver a conference session about database continuous integration (CI), I like to start by asking a question to the audience. “Who can tell me what continuous integration means?”

I almost always get responses like:

“Automated deployments!”

“Automated builds upon commit!”

Very occasionally someone will impress me with something like:

“Unit tests!” or “Automatically running my unit tests!”

Not bad answers. Have a biscuit. But you are still missing the fundamental point.

Alex makes a number of great points, so check it out.

Robert Davis discusses shipping database changes and the role of the DBA:

I don’t know what percentage of people out there are doing DevOps, but I’m going to go out on a limb and say that it is most likely some number LESS than MOST of them. I don’t think more than half the companies or people out there that do Ops are doing DevOps. I also believe that DBAs that make really good money aren’t making it because DBAs are rare. They are making it because DBA is a tough job to be really good at and the ones who are really good at it are rare. All DBAs are molded by the environments they work in, but really good DBAs are ones that eventually learn to mold their environments to them.

A normal DBA may say something like, “I do it this way because that is the way it is done here.”

An exceptional DBA says, “We do it that way here because that is the way I have it done.”

Robert makes great points.  I have on my agenda to write a post entitled something like “The Cloud’s Not Going To Steal Our Jobs,” somewhat in the same spirit as this post.  Definitely a must-read.

Riley Major looks at small things you can do to help smooth out deployment processes:

It’s not just about exercising restraint, though. It’s also about taking small additional steps to minimize the impact of new development. If you are making a new stored procedure parameter, can you provide a default so that its omission will make the procedure act as before? If so, you can now deploy that change without affecting any consuming code. If you are changing the name of a column, can you create a computed column with the old name which simply mirrors the original column’s data? All insert- and update-related code would still need to change, but you could save the SELECT statements for the next round.

Ideally, these steps are transitory. Optional parameters can be made mandatory once all of the consuming procedures have been adapted. The legacy-named computed columns should be deleted once all old queries are updated. But recognize that this might not happen. Other priorities come up, and these supposedly temporary constructs can linger.

Click through for additional hints.

Aaron Bertrand tells how he would work with a federated system back in the SQL Server 2005 days, and ties it back to modern database deployment practices:

The biggest concern I have about database deployment is not about how you deploy, or even whether or not you use source control. It’s that your database changes are backward compatible – meaning they won’t break the current application, in the event the application can’t be changed at the same time (and with distributed software, that’s impossible). The largest number of bugs I had a hand in creating were caused because I assumed the application or middle tiers would be deployed at the same time.

I quickly became very motivated to make sure my changes would work before and after the application tier changes were deployed. Add a parameter to a stored procedure? Make sure it’s at the end, and has a default. Add a column to a table? Make sure views and stored procedures don’t expose it (yet) or require it. And a hundred other examples.

It’s a good read.

Derik Hammer makes the vital point that continuous integration isn’t a tool; it’s a process:

SQL Server Data Tools (SSDT) is a tool that I am particularly familiar with and will become the subject of my examples. SSDT database projects shift the source of truth from your database to your source control. The intent is that the project and its build artifact, the dacpac, is the desired state of your database. SSDT will then generate the code necessary for you to migrate from your current state to the desired state.

The problem with my description is that it is similar to saying, “hammers drive nails into wood,” and then expecting that you won’t have to learn how to swing the hammer, aim at the head of the nail, or regulate how hard you hit it. Tools like SSDT are not magic and they can have problems. A solid understanding of how they work can mitigate or completely avoid these issues, however.

Click through for Derik’s rant.

Vladimir Oselsky walks through his database deployment workflow:

When it comes to actual deployment to Test and production servers, it is handled by application update program that runs scripts on the target server one by one in alphabetical order. Since we have clients running different versions, scripts always have to be applied in order, for example, if the customer is on version 1.5 before the could get 2.5 they need 2.0. This ensures that database changes are applied in correct order, and I don’t have to worry about something breaking.

One last problem that I have to deal with on a regular basis is Version-drift. This is caused when I manually patch a client for a fix without going through the proper build process. In those cases, I just have to manually merge changes into development to guarantee that it will make it out to other clients. Once in a while, it becomes quite complicated to keep track of different clients running different versions and how to ensure that if they need a fix, it is not something that could be resolved through update versus manual code changes.

Version drift can be a big pain, but check out Vlad’s workflow.

Ryan Booz uses schooling as an extended metaphor for database deployment:

In general, the biggest issues we hit continue to be client customizations to the database (even ones we sanction) and an ever growing set of core-pop data that we manage and have to proactively defend against client changes.  This is an area we just recently admitted we need to take a long, hard look at and figure out a new paradigm.

I should mention that it was also about this time that we were finally able to proactively get our incremental changes into source  control.  All of our final scripts were in source somewhere, but the ability to use SQL Compare and SQL Source Control allowed our developers to finally be a second set of eyes on the upgrade process.  No longer were we weeding through 50K lines of SQL upgrade just to try and find what changed.  Diffing whole scripts doesn’t really provide any good context… especially when we couldn’t guarantee that the actions in the script were in the same order from release to release.  This has been another huge win for us.

This is a view from someone in the middle of the process.  Ryan’s group isn’t pushing everything automatically, but they’re building out to that.