Data-tier Applications, DAC Framework and SQL Azure Integration with a Continuous Integration (CI) Process

:: Introduction

Leveraging the efficiency and cost savings of the "cloud" within an enterprise requires the use of specific tools applicable to the PAAS provider used. When it comes to working with SQL Azure (Microsoft's cloud based relational database engine), I have found that data-tier applications along with some supporting deployment infrastructure (PowerShell Scripts) work particularly well to synchronize both the database changes across a development team and to deploy those changes to other testing environments as they make their way to a production system.

:: Data-tier Applications

Data-tier applications are a project type within Visual Studio 2010 that allows development teams to manage database changes across ALL of their environments whether in the cloud or on-premiss. Developers work with individual SQL scripts within the project which define anything from table schema's to indexes to stored procedures. Once a change has been made to a data-tier application's scripts, the entire project can be compiled and a DAC Package (called DACPAC from here on out) is then created that can be deployed to a SQL Server 2008 R2 or SQL Azure database instance.

For details on how to create and setup a data-tier application for the first time see this link. 

:: DACPAC Files

A DACPAC is simply a file with a ".dacpac" extension that contains all the schema, stored procedures, users and policies required to setup a new database or upgrade an existing one. Note that DACPACs should not be confused with BACPACs. BACPACs are a newer format put forward by Microsoft and the file format can contain everything a DACPAC can but can also include data. 

There is also a version number (with a format of x.x.x.x) embedded within the DACPAC file which can be used to version the DACPAC. Unfortunately this version number is not updated automatically by project when the data-tier application is modified. Therefore a developer will need to increment it manually before deployment. To access and modify this version number right-click on the data-tier application project and find it under the "Project Settings" tab.

:: Deploying Data-tier Applications

Before a data-tier application can be continuously upgraded with a newer version (the typical workflow of any development team), it must first be deployed. The deployment essentially creates the initial database, sets it up as a data-tier application within the database engine (either SQL Server 2008 R2 or SQL Azure) and then goes about deploying all the schema, store procedures, users... etc that are contained within the DACPAC.

The DACPAC can be easily deployed from within Visual Studio by right-clicking the data-tier application project and selecting deploy (note that the connection string within the data-tier application project will need to be setup before doing this). Alternatively you can use SQL Server Management Studio (SSMS) or some PowerShell commands: How to: Deploy a Data-tier Application

Note that the above deployment is really just a one time operation. Once the initial setup has taken place and the data-tier application is deployed, you'll only need to upgrade the data-tier application from then on (unless you delete the database and start again which is handy for certain developers who might break things and need to start afresh).

:: Upgrading Data-tier Applications

There are a variety of mechanisms available to deploy a DACPAC file to a SQL Server 2008 R2 or SQL Azure database instance. Unfortunately the documentation available for these mechanisms is fairly minimal and therefore it makes it difficult to assess what mechanisms to use for each deployment scenario you may have within your enterprise. Here are the ones I have come across, the way to invoke them and the scenario(s) I think they are applicable to:

• Within Visual Studio
• Invoke: Within Visual Studio right-click on the data-tier application project and select "Deploy". Note that Visual Studio figures out
• Details: Depending on the current configuration (e.g. Debug/Release) the appropriately set connection string with be used when the project is deployed. Therefore the connection string can be changed to access a local SQL Server 2008 R2 database instance or a SQL Azure database instance
• Scenario: This deployment scenario is most applicable to developers working locally during  a typical development workflow of repeatedly modifying code, compiling and deploying it and then debugging the result. I highly recommend that developer's use a local instance of SQL Server 2008 R2 for two reasons:
• No data transfer costs are accrued as ALL data transfer occurs on a developer's local machine and not back and forth between SQL Azure and their local machine.
• DACPAC deployments are much faster and more reliable when performed locally since deploying a DACPAC to SQL Azure can take minutes/hours depending on the connection and the database connections can be dropped mid-deployment (this won't cause an error but the deployment will need to be restarted)
• This 20min video, by a program manager on the Microsoft SQL Server Developer Manageability team, explains a range of information about data-tier applications and their use within Visual Studio.
• Within SQL Server Management Studio (SSMS)
• Invoke: Within SSMS open the "Management" folder and then the "Data-tier Applications" node. Right-click the data-tier application you would like to upgrade and select "Upgrade Data-tier Application..."
• Details: Just follow the on-screen instructions making sure to save the SQL Script that the DACPAC uses to apply the changes to the database instance. On some occasions the upgrade wizard has failed but because I saved the SQL Script I could execute it myself within SSMS.
• Scenario: This is the easiest way to get started when deploying a DACPAC to SQL Azure since SSMS provides a easy to follow wizard. Note however that I have seen this entire process take 1-2 hours to upgrade a database (40 tables + 50 stored procedures). Being able to automate this step is very helpful which the next section on PowerShell scripting will explain.
• Using a DAC Framework enabled PowerShell Script
• Invoke: You'll need to create your own PowerShell script to manage the upgrade process of one or more of your data-tier applications residing on SQL Server 2008 R2 or within SQL Azure. Note that if you are unfamiliar with PowerShell and the DAC Framework creating a robust and reliable script will take significant development effort.
• Details: I created my own parameterized PowerShell script that accepts a DACPAC file and some credentials to a SQL Azure database, subsequently connects to it and then upgrades the existing data-tier application. Two sources of information that I used when creating my PowerShell script were the following:
• How to: Upgrade a Data-tier Application
• SQL DAC Examples
• Scenario: This is the most appropriate method for building an automated process for managing data-tier applications that can be used by a continuous integration (CI) system.  
• Note that side-by-side upgrades are the older way to upgrade a data-tier application and were used with the DAC Framework v1.0. There is a newer upgrade process called in-place upgrades and is supported by the DAC Framework v1.1. I decided to use the in-place upgrade since it doesn't create copies of the "pre-upgrade" database. In order to perform an in-place upgrade using PowerShell make sure to use the DacStore.IncrementalUpgrade Method and not the DacStore.Upgrade Method which is now obsolete. Also note that the DacUpgradeOptions passed into the DacStore.IncrementalUpgrade Method control things like whether or not to ignore data loss, ignore drift, skip policy validation and/or perform rollbacks on failure. Make sure to select these options properly for each environment that you're upgrading (i.e. you may not care about drift on your local or CI databases but in your production database database drift should be a cause for concern).

:: Data-tier Application Upgrade Architecture across Environments

The following diagram shows how I used a data-tier application's DACPAC output to upgrade various SQL Server 2008 R2 and SQL Azure database instances across various environments (from local development through to production).

Note that only the changes to the SQL scripts within the data-tier application in Visual Studio are committed (not the DACPAC output that is built locally). The DACPAC is rebuilt on CI using NAnt and MSBuild and is used to immediately upgrade the CI's SQL Azure database instance. The same DACPAC is then archived and used in subsequent deployments for Staging, UAT and Production. The reason behind this build architecture of "build once and deploy to all subsequent environments" is outlined in the book called Continuous Delivery: Reliable Software Releases through Build, Test, and Deployment Automation.

Also note that the "Upgrade" step in both the CI and Staging/UAT/Production environments is performed via the parameterized PowerShell script mentioned in a section above.

Agile Contracts

:: Questions

Lately I've been curious about how one would structure an agile development team within a consulting organization which typically sells fixed-priced projects.

• Can an Agile development team really work effectively when the fixed-priced contract, which governs the deliverables produced, is signed upfront before the project has started and therefore before the requirements have been adequately understood by either the consultant or the client?
• Should a fixed-price contract be avoided entirely or can the incentives somehow be lined up properly such that both the consultant and the client have a large enough stake in producing the best possible outcome together?

:: Resources

I have scoured the web and found the following articles which deal with Agile development and how to structure contracts around it's development methodology:

• InfoQ's Agile Contracts
• Alistar Cockburn's collection of ideas on Agile contracts

:: Conclusions

In an ideal world one can try to structure an agile development methodology around a fixed-priced contract in various ways but most of them only work well when the project is running smoothly. As soon problems start occurring most of the contracts start looking like the wrong approach. Eventually when the rubber hits the road and scope starts screeping, money runs out and/or deadlines loom, something has to give and invariable its the agile methodology because fixed-price contracts are less malleable than the underlying development process. This would be all well and good unless the abandonment of core agile practises during an emergency period of the project did not have adverse effects... but unfortunately time and time again I have seen adverse effects push projects even further into trouble.

-------------------------------

:: Update

I recently came across a 44 page PDF titled Agile Contracts Primer. 

Configuring an Android Application's WebView Component

I recently started on a new project at work to build out a prototype Android application for one of our clients. I'm using the standard Eclipse IDE, Android SDK and ADT Plugin for Eclipse that the Android team recommends.

During development I came across a couple issues related to the WebView component (which is both a layout and a class) that caused a bit of confusion. In case you're not familiar with it, a WebView component can be embedded into an Activity and allows for the viewing of webpages (essentially it mimics the functionality that exists in the device's built-in browser application). I discovered that the WebView component requires some additional tweaking in order for it to behave in a similar manner to the device's built-in browser. The following are the problems and their solutions that needed to be implemented before the Android prototype worked as required:

:: Enabling Flash Content

When viewing a webpage with embedded Flash video content, a pop-up window would display informing the user that they needed to install the latest Flash plug-in. Even after installing the plug-in via the built-in browser and the Android Market App, the pop-up window still appeared and the Flash content was unable to be played. The phone's built-n browser however was able to play the Flash video content (without any pop-up being displayed).

The solution to this problem was that the WebView component needed to have JavaScript and any plugins explicitly enabled before the Flash video content would play. Here is what I included in my Activity's OnCreate method to explicitly enable both these properties:

WebSettings webSettings = webView.getSettings();
webSettings.setJavaScriptEnabled(true);
webSettings.setPluginsEnabled(true)

Note that the webView variable above was previously declared as a private instance variable of type WebView.

:: Initially Rendering in Zoomed Out Mode

When the WebView component loaded and became visible to the user, it arbitrarily rendered a webpage partially zoomed in. This gave a sub-optimal user experience and so the solution was to initially render the webpage in a fully zoomed out mode so that the user could view the whole webpage and decide for themselves if they wanted to perform a pinch gesture to zoom in on a particular area. Again, here is what I included in my Activity's OnCreate method so that the WebView component rendered any webpage fully zoomed out:

WebSettings webSettings = webView.getSettings();
webSettings.setLoadWithOverviewMode(true);
webSettings.setUseWideViewPort(true);

Again note that the webView variable above was previously declared as a private instance variable of type WebView.

:: Forcing WebView to Load all URLs

For a certain subset of URLs that I was trying to load within my WebView component, my application would quit and the phone's internal web browser would launch and load the webpage successfully. I therefore needed to find a solution that would force all URLs to load within my WebView instead of being launched externally on the phone's internal web browser.

The solution entailed creating a CustomWebViewClient class that extended WebViewClient and then wiring up the CustomWebViewClient to handle the shouldOverrideUrlLoading method. This allowed me to force the WebView within my application to render all URLs I was presenting to the user:

private WebView webView = new WebView();
private CustomWebViewClient webViewClient = new CustomWebViewClient();

protected void OnCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    webView.setWebViewClient(webViewClient);
}

private class CustomWebViewClient extends WebViewClient {
    @Override
    public boolean shouldOverrideUrlLoading(WebView view, String url) {
        view.loadUrl(url);
        return true;
    }
}

-------------------------------

:: Update

I recently found that the Android developer resources have a tutorial on setting up a WebView correctly. It goes into detail for some (but not all) of the items described above.

.NET Code Contracts: Invariants Explained

I have recently started using .NET Code Contracts. I love the streamlined approach to implementing pre-conditions and post-conditions that previously required multi-line if statements along with the ability to check coding assumptions statically and even at runtime (release or debug builds). .NET Code Contracts are another valuable tool in a software engineers arsenal to combat the growing complexity of software systems.

The latest MSDN magazine (June, 2011) has an thorough article on invariants which are an element of .NET Code contracts. The article will go into much more detail but essentially invariants allow for the verification of an object's internal state before and after a set of operations. To create an object invariant, a class simply needs to contain a new private method with the ContractInvariantMethod attribute and then specify any conditions within the private method that should hold true before and after any operation on the class or instances of the class. Here is an example of what the object invariant method would look like for a class called News which has internal state for the Title and Body of a news article:

[ContractInvariantMethod]
private void ObjectInvariant()
{
     Contract.Invariant(!String.IsNullOrEmpty(Title));
     Contract.Invariant(!String.IsNullOrEmpty(Body));
}

What this accomplishes for programmers is that if they inadvertently try to alter the internal state on an instance of the News class to something it should not be (such as setting Title = string.empty), the .NET Code Contract static checker will warn the programmer that they are violating the object's invariant.

Thus, programmers can now design and build classes with the help of .NET Code Contracts that enforce correct API usage. This is a very powerful feature that will avoid subtle bugs from ever occurring.

Technical Debt

:: Definitions

Technical debt is a high-level metaphor used to describe the quality of software source code. It was first introduced by Ward Cunningham back in 1992 and has since take shape in many different forms. Today it can be used informally to describe the state of a software application to a non-technical audience or it can be used more formally for measuring technical aspects related to software source code or specific tasks required to "clean" it up. Given that the term "technical debt" can have a wide array of definitions, the following sources provide some deeper insight about the term: 

• Wikipedia Article on Technical Debt
• Technical Debt Blog Post by Steve McConnell (Nov 1, 2007)
• On Technical Debt by Philippe Krutchten, Prof. of Software Engineering at UBC (March 21, 2011)

A software application is only as good as the team that designs, builds and tests it. Therefore, technical debt has many sources and can be introduced by architects, developers or testers. It usually starts out being just a small issue but if left unmanaged, can grow to become very debilitating for a development team to produce software in a timely manner. Technical debt can be associated at any level of the software application stack whether embedded in the underlying architecture, classes, methods, algorithms, documentation and/or build systems. It is therefore important in software projects to not only know what requirements have yet to be implemented fully but also to know what technical debt lurks within the software so that it can be managed effectively.

:: Measurement

The holy grail for measuring technical debt would be to measure the impact of a single software change in terms of both the value it produces (most likely profitability) and the risk it either increases or mitigates. The ideal software change would maximize overall value while reducing overall risk. As an industry, software development is a very long way from this fine grained approach to measuring technical debt but there are many techniques that can be used (either individually or combined together) to measure the potential burden that a piece of software may have and thus provides some better insight. Like tracking finances you never really know where or how much money is being spent until you keep track of it and the same is true for technical debt... you never really know what software components might need attention or how bad they actually are until you measure them directly.

There are a number of metrics that can be used to measure specific aspects of technical debt across software source code. Unfortunately none of these metrics are able to provide a complete picture of the technical debt for a piece of software but taken together they can provide some key insights into its overall quality. Note that care should be taken to fully understand what a metric is in fact measuring and then to not over-state its importance. I have seen many development teams become religious over certain metrics and subsequently implement policies to stay within some arbitrary bounds. This can often lead to development practises that are detrimental to the actual software being developed. Measuring technical debt is more of an art than a science but quantitative values can still be helpful in the arts.

Here are some of the metrics I have used to measure source code:

• SLOC (Source Lines of Code): The size of a software application in SLOC isn't a great metric in an of itself, but when used as a comparison mechanism (e.g. the ratio of production code to testing code) it becomes a good indication of relative code quality.
• Cyclomatic Complexity: A great indication of how complicated the code paths are in a portion of code (usually a method). For methods, it is also a very good indication of how many unit tests are required to effectively traverse and thus test each code path. This is also one of the few metrics statistically correlated with buggy software.
• Coupling: A number of metrics are defined in this category but of particular interest are fan-in and fan-out metrics which measure the number of calling or called routines for a portion of code.
• Branch Coverage (Also known as code coverage): Measures the amount of production code that is covered by unit tests. If you think you have tested a method or class sufficiently this is a good metric to observe to verify that thought.
• Duplicate code: Duplicate code is a particularly interesting metric because the code that is duplicated can in fact be of a high quality. The problems arises when the duplicated code needs to be modified due to a bug. As a result the developer must make sure that each and every occurrence of the bug is fixed otherwise one occurrence of the bug will be fixed but one or possibly many others will remain. A false sense of security that a bug is fixed when it is not can be a disaster in certain contexts.
• Nested depth level: Some algorithms require nested 'if', 'for' or 'while' statements and an large number of nested statements (also called the depth level) increases the complexity of a portion of code and makes it harder for any one developer to understand it without considerable effort (leading to possibly buggier code).
• Violations of coding standards: If a coding standard is defined there a usually a number of tools available that either enforce compliance with the standards or simply count the number of infractions.

Apart from simply using metrics to quantify the technical debt of a software application, I have also found the need to capture various software "cleanup" tasks. Anything from dead code to code refactoring to build system improvements can be added to a ticket in your bug tracking system, labelled as "technical debt" and estimated accordingly. By doing this the technical debt within an application can be is tracked over time and observed as it either increases or decreases. It can therefore be managed in a pragmatic way instead of avoided or simply kept unknown.

:: Tooling

There are a whole range of open-source and commercial static analysis software tools available to capture metrics and enforce their compliance:

• Java Specific:
• Google CodePro Analytix
• Checkstyle
• PMD
• FindBugs
• Crap4j (C.R.A.P stands for Change Risk Anti-Patterns)
• .NET Specific:
• NCover
• FxCop (Comes integrated with Visual Studio 2010 Premium and above or can be run as an external application for lesser versions)
• StyleCop
• Code Contracts (Includes a static analyzer for pre and post conditions within methods)
• MSDN also has an article on ways to calculate code metrics for .NET projects in Visual Studio.
• Multi-language:
• SLOCCount (Linux/Mac)
• CLOC (Windows)
• McCabe IQ

And here is an even bigger list of static analysis tools.