SQL Server Development Tips – Automated, Incremental Builds

The black-box mechanics of SQL Server can make database versioning a challenge.  Traditional approaches include keeping a log of all structural SQL changes, or taking backup snapshots at development milestones.   The primary weaknesses of these approaches are the lack of flexibility and prototyping with the first, and deployment challenges with the latter.  Herein we present a solution to the challenge using version control and automated, incremental build scripts.

Many database developers, especially those from an in-house software background, view the database as an ecosystem – a living menagerie of the company’s information.  Although this approach is effective for large database instances with one deployment target and several staging/test environments, software developed for production release to multiple customers can take a different, more effective approach by treating the database similar to the software codebase.  This consists of three primary changes to database development methods – versioning the SQL structure, creating standardized sample data, and developing a set of scripts for one-line testing and deployment.

Versioning the database SQL structure is relatively straightforward.  Although testing and prototyping SQL statements against a live database through SQL Manager is encouraged, any final SQL that will be shared with the software should be coded in a set of create and alter statements.  These files should also populate standard system-level parameters and data, and contain any necessary grants and permissions.  Once any database update is finalized, the entire database should be dropped and recreated using the build scripts, before deployment to the test environment.

Although SQL statements could be organized by table, it is often more effective to group the SQL by function.  For instance, auditing code could be handled in one SQL file, lists of values in another, the menu system in a third, and so on.  This method of organization helps facilitate code reuse, and reduces development time of future projects.

Next, sample data, or several series of sample data, should be stored in separate folders.  Developers should be aware of the fact that their updates will be temporary.  More stability in the testing environment is often required during integration and regression testing.  During that time, either the testing scripts can be updated with additional test cases, or simple held-off until completion of the testing phase.

Finally, in order to quickly execute the mass of scripts, a deployment or “make” script should be developed.  This could be either a PowerShell or command file, and often a Windows port of Sed/Awk can be very handy for database target substitutions.

Using an automated, iterative database development methodology can help reduce development time, bring Agile concepts to the database space, help integrate with version control, and improve code quality by reducing errors during synchronization with deployment targets.  Although it adds a little structural overhead during the initial development, this technique often pays back dividends during the testing, deployment, and maintenance phases of the software project.

SQL Server Software Development Chicago

Written by Andrew Palczewski

About the Author
Andrew Palczewski is CEO of apHarmony, a Chicago software development company. He holds a Master's degree in Computer Engineering from the University of Illinois at Urbana-Champaign and has over ten years' experience in managing development of software projects.
Google+