SQL Server development and deployment can be a difficult and slow process at times. This is especially difficult with all of the automation and quick deployment with modern development IDEs, such as Visual Studios. Having spent many years developing, reviewing and deploying code, I figured I would go over some ways I’ve made my development experience easier. These included using a live schema tool (like SQL Server Development Tools), writing re-runnable scripts and using unit testing. All of these things have increased my development/deployment time and reduced QA time.
Live Schema Tool
I’ve run into many scenarios where development teams would store table creation scripts, and alter statements separately, and over time, this code gets stale. They tend to regress to only having alters or just doing SQL compares to maintain and deploy code. This is fragile and makes it difficult to control the database like other applications. Enter “SQL Projects”. For Microsoft, this is in the form of “SQL Server Data Tools” AKA SSDT. This free tool allows you to interact with a project that represents your database in VS and only have to worry about making a change once, without worrying about the right “ALTER” syntax and other nuances. With built-in deployment that you can add to your favorite build tools and a testing framework, this is my go-to tool.
Re-runnable Scripts
This discipline pays dividends in reducing deployment errors. Essentially, you change your deployment artifacts to be re-runnable, so that a partial deployment can be rerun without breaking the final state of the database. Here is an example. Say you have a lookup table that you want to seed some data to. Below is an application state table:
I might seed a few state rows like so, if I wasn’t thinking about re-runnability:
Notice that I ran it once and it works fine. Run it again, however, and boom. At least we have our constraints in place.
Now let’s try it with a re-runnable script and see what happens:
First run is fine, so let’s run it again:
No rows updated and no errors. Perfect! If you have situations similar to this, consider how you can make re-runnable scripts to reduce problems with your deployments.
Unit Testing
Last but not least, SQL Server unit testing is a great way to reduce errors and have a good set of tests to define the requirements of a database application. Essentially, the goal is to wrap as much code into testable units as possible and define a series of tests around it. Within the SQL Server tool stack, SSDT has a framework for doing unit tests and extensive documentation. I try to use inline table valued functions (TVPs) to create testable units. If you’d like to see an example of doing this, check out my previous blog post here. When used extensively, you can deliver a lot quicker with much less QA test/fix iterations.
All of these techniques can vastly reduce errors and problems with your SQL Server development and deployment. It’s just a matter of learning and refining. If you’d like to hear how I can help you improve your development and deployment process, contact me for more information. I’ve helped organizations such as RELS, UCare and Ally Bank to improve their processes.