Database Continuous Integration - Chapter 4 - Looking through development lenses

By: Alex Podlesny

In this section: adopting Continuous Integration as core of the development process  

If you landed on this page from search engine, we would recommend starting with the first chapter

From an application standpoint, database is essentially a resource. As long as the application has access to a given database it'll work. Looking back into second chapter of our discussion, essentially this resource is a some “Creature” created base "Creature DNA".  

So, if we can create brand-new-fresh-and-clean database, backup this database and keep files as artifacts, then this files can be used by other processes to restore and reuse that brand-new-fresh-and-clean database in the target environment. 

If this is achievable, then data-centric application would have the luxury to use clean non-polluted database with the latest tested code. 

Here is how to implement CI practice as a backbone of the development process:  

  1. Maintain source code repository:
    • As the first step, installing in-house (or using a hosted solution) SVN, Git, Mercurial, TFS or any other system would do.  
    • RepositoryAt the next step, all database code should be scripted. Make scripts that create tables, views, triggers, stored procedures, functions, synonyms, schemas, roles, permissions,  data types, constraints, and every other thing that an application might need. Together with scripts that create database objects, you need scripts to populate application-specific metadata and a few seed records. 
      Each of those scripts should be checked-in into code repository.  
      The goal here is to make the clean database populated with default set of metadata; 
  2. Commit changes often, never longer than a day:
    • CommitAll developers should commit code changes to source code repository as soon as possible, providing comments and explanations why changes made, why new objects added or why do they removed. 
      Changes to default metadata should be checked-in to a documented as well.
    • There is no big task that could not be split into committable small sub-parts, developers should not hold features and code hostage; 
  3. Every commit to the master branch should be built:
    • Out of many branches that developers use in the source code repository a master branch should always be build when new changes are committed;  
    • But what does it mean to build? how can it be built?
      These are good questions, and we cover all terms in more detail in our future post. For now let assume that we prepare a package that have all the scripts necessary to create brand-new-fresh-and-clean database;
      To get that done create set of build scripts that automatically download all committed changes from source code repository, run those database scripts to produce brand-new-fresh-and-clean database, and finally backup this database;
  4. Build Automation:
    • AutomateMany build automation tools exist on the market today, like TeamCity, Jenkins, Hudson, and others, some of them are free. Those tools make the life of automating a build processes very easy and enjoyable. 
  5. Make sure that built artifacts are available as installation packages
    • Save time for your fellow team members, instead of  writing miles of deployment documentation, create installation packages ready for automated deployments and available to team members.   
    • What sort of installation package to create? Are there many different kinds?
    • These are superb questions, and we cover them in more details in our future posts, for now let assume that we prepare an archive with following files:
            1) a package that have all the scripts necessary to create a brand-new-fresh-and-clean database
            2) script that call this package to create brand-new-fresh-and-clean database on the target server   
    • Installation package should be easily consumable by existing commercial, open source or simple home grown tools
  6. Build process should be fast:
    • Build server should continuously monitor source code repository for new changes and start the build process as soon as possible. 
      A few minutes to build a product is excellent, half an hour is questionable, an hour is unacceptable.  
    • System might be configured to check for changes, compile the solution, run all unit tests, prepare and publish artifacts - all in One Giant Process. 
    • Alternatively, it may be configured with many small independent sub-processes that create their artifacts.   
  7. Automated Unit Testing:
    • Automated Unit TestingAfter build is done; product should be checked for conformity to expectations and requirements by running a set of automated unit tests.
      Unit tests should be created by developers, since they are those who build a feature, and they are first who verifying that new functionality. However, that might not always be the case; integrators and testers should automate their testing as well. 
    • Adopt Test Driven Development practices.    
    • Unit Test should be stored in the source code repository and be available to the build servers alongside database scripts used to create database artifacts. 
  8. Available Build Results:
    • Everyone in a team should know that build failed. The earlier team knows, the faster they can fix it. 
      Preventing downstream environment from getting corrupted code, and uncovering integration issues is at the core of a continuous integration practice 
    • Any existing on the marked CI and deployment servers would make build results available to users. They can be configured to share the status in many different ways: from email to text message, from the web page to flashing red light in the hallway. 
    • Everyone should know when build failed, what code brought it down, when was it fixed and how; 

Some might argue that following two steps are not relevant when product is just a brand-new-fresh-and-clean database intended to be used by developers. 

For now here they are, the extra steps: 

  1. Keep the set of pre-production environments. Configure automated deployments to them 
  2. Tune the process to the point of continuous deployment to production  

See Also:


Sunday, January 4, 2015 4:25:00 PM
Comments are closed on this post.
  • RSS
  • Add To My MSN
  • Add To Windows Live
  • Add To My Yahoo
  • Add To Google


  • Entries (13)
  • Comments (0)