Covering Data Consulting, LLC, Minneapolis, MN

From the blog

Improving SQL Development and Deployment Speed

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:

appstatetable

I might seed a few state rows like so, if I wasn’t thinking about re-runnability:

appstateinsert_bad

Notice that I ran it once and it works fine.  Run it again, however, and boom.  At least we have our constraints in place.

appstateinsert_bad_2

Now let’s try it with a re-runnable script and see what happens:

appstateinsert_good

First run is fine, so let’s run it again:

appstateinsert_good_2

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.

Contact now »

Leave a Reply

Your email address will not be published. Required fields are marked *

Services

Performance Tuning

When your employees can’t work because of a poor performing application, your business can lose thousands of dollars in productivity. Worse yet, you can lose customers on a client application. Since a large portion of application performance issues trace back to the database, you need a professional who can quickly[…]

Learn More

Database Design

Your data is some of your most important assets, yet some businesses are hand-cuffed by poorly performing databases. Application development is difficult and many developers are tempted to have tools auto-create the databases. Don’t compromise your future data needs by skimping on your database design. We can work with your[…]

Learn More

SQL Server Health Checks

You know your database server has problems, but you don’t know where to start.  The sys-admins blame the code and the developers blame the hardware.  We can come in and identify your issues and work with your staff to get the database working again.  Plus, we will provide detailed information[…]

Learn More

Database Solution Architecture

You’ve done the indexing and performance tuning.  You’ve run out of options, but you still need more out of your database.  We can help you maximize the use of your existing database instance to save money and get you back on track. Contact now »

Learn More