Approaching Zero: Making Life Simpler

This is part three in a series on near-zero downtime deployments.

In the last post, I walked through several assumptions, particularly around source control, deployment processes, and testing. In this post, we will look at a few things you can do to make (near) zero-downtime deployments easier.

Use Enterprise Edition

SQL Server 2016 Service Pack 1 expanded the programming surface area significantly for Standard Edition, but there are some things you can only do with Enterprise Edition. For example, one thing you can do is to change indexes, including clustered indexes, online. Online index building doesn’t mean that end users will see absolutely no effect, but it does reduce significantly the likelihood and amount of blocking users will experience. On extremely busy systems users might experience a slowdown, but for the most part, you can get away with these changes. One note on indexes is that up through SQL Server 2017, if you create a non-clustered index on a table with a clustered columnstore index, that operation will be an offline operation, making it much harder to deploy new indexes on those tables after users have started accessing tables with clustered columnstore indexes.

Another thing you can do in Enterprise Edition that you cannot do with Standard is partition tables. Partitioning large tables can be great for maintenance operations: you have the ability to delete old data by shunting that partition off to another table where you can then drop it. Or, starting in SQL Server 2017, you can simply truncate a partition if you could normally truncate that table.

There are other advantages as well to using Enterprise Edition over Standard Edition, but I think those are the two biggest when it comes to making database deployments easier.

Use Read Committed Snapshot Isolation

By default, SQL Server uses pessimistic locking, meaning that readers can block writers, writers can block readers, and writers can block writers. In most circumstances, you can switch from Read Committed to Read Committed Snapshot Isolation and gain several benefits. RCSI has certainly been in the product long enough to vet the code and Oracle has defaulted to an optimistic concurrency level for as long as I can remember.

The downtime-reducing benefit to using RCSI is that if you have big operations which write to tables, your inserts, updates, and deletes won’t affect end users. End users will see the old data until your transactions commit, so your updates will not block readers. You can still block writers, so you will want to batch your operations—that is, open a transaction, perform a relatively small operation, and commit that transaction. I will go into batching in some detail in a later post in the series, so my intent here is just to prime you for it and emphasize that Read Committed Snapshot Isolation is great.

Use Stored Procedures

There is a longstanding debate around whether to use stored procedures or ad hoc SQL in applications. My general preference is to use stored procedures, but I do understand that there are people who prefer ad hoc SQL queries. In this case, however, stored procedures have a huge benefit that you won’t get from ad hoc SQL: the interface effect.

When done right, stored procedures act as an interface layer between the database and the application. You define the input parameters and build expectations about the output, but your application code doesn’t care how you get from inputs to outputs. Particularly with complicated database operations, having a stored procedure gives you the ability to tune database code without changing application code.

Another advantage to having all of your database code in stored procedures is that you can change the underlying tables without changing application code. This makes table modifications much easier to pull off and helps ensure that you make the change everywhere you need to make it.

If you use weakly typed code to retrieve—for example, reading from a DataSet dictionary where you specify column names—you can add new columns to the stored procedure without breaking existing code. This lets you separate the database work from the application work, so that when the application is ready to use that new column, it will be there. Granted, this is something you can do with ad hoc queries, so it’s not a benefit in and of itself. If you use a strongly-typed process which expects an exact set of columns and only that set of columns, you can paint yourself into a corner by limiting your ability to act. I’d recommend avoiding that strategy as it reduces the number of options you have for managing your code.

One last advantage of using stored procedures is that managing backward compatibility is clearer: you can have different versions of procedures which provide different behavior depending upon which version(s) of the code are still running. For example, V1 of a procedure might insert values for 8 columns into a table. Then we added a nullable 9th column that our new code will be able to use. We can create a new version of the procedure which accepts 9 parameters and writes the outputs to all 9 columns. But during our blue-green window, we will have both sets of code inserting rows, so we need both versions of the procedures at the same time.

Use Database Tests

I pointed out the benefits of database tests in the prior post, so I won’t belabor them here. Tests give you the ability to ensure that your database code works at different steps in the deployment process, giving you a safety net. Writing test code is alternately complicated and tedious, but any bug your tests find before release is a bug your users didn’t find after release.

Use Views (Maybe)

Views are a tricky call for me. On the one hand, views can act as an interface layer and give us the ability to abstract away table changes, similar to stored procedures. You can also tune views without changing application code. That’s the positive side when it comes to reducing deployment downtime. On the other side of the ledger is that nested views can lead to massive performance problems. This makes views a risky proposition for an application. If you never join views to views and never nest views, you get some of the advantages of using stored procedures. But at that point, why not use stored procedures? Then you get all of the advantages of using stored procedures.

Conclusion

In this post, we looked at several ways that you can make your life better when working to minimize downtime. The goal of each of these is to increase the number of developer degrees of freedom you have. The more degrees of freedom you have, the less likely you are to get stuck in a situation where the only solution is to accept an extended amount of downtime.

In the next post, I will cover working with stored procedures and how we can perform common stored procedure actions with near-zero downtime.