Best Agile / DevOps Open Source Tool Chain

Historically I have been a Microsoft C# guy but the more I work with non-Microsoft shops with Hybrid environments and Java guys running around everywhere the more curious I have become about open source tool chains for Agile and DevOps.
We use Team Foundation Services for Work Item Tracking, Planning, Continuous Integration, and Continuous Deployment to QA and Stage in Azure. That’s all fine and good for projects built almost entirely on the Microsoft Platform but when there are more Java guys on the team than C# guys the holy wars begin.
I love the deep Integration between the tools on the Microsoft stack obviously born from vendor lock in but I am totally open to a more open-source, vendor agnostic solution. I just haven’t been able to find one that provides the required features I’m looking for.
Base level requirements are as follows:
A tool that provides Epic / Story management and visualization (Kanban / Burndown).
A tool for Source / Version control that integrates well with the work item tracking tool and CI server to allow gated check-ins (reject check in if build or tests fail)
A Continuous Integration server that can notify source control of failed builds and tests so check in can be rejected and notifies the work item tracking tool so that a bug work item can be created and assigned to the user who performed the commit of bad code.
A release automation tool / plug in that can trigger a release based on successful CI build and test.
Does this tool chain only exist in the land of flying reindeer and unicorns?

Git and GitHub work fine for source / version control and integrates with almost everything but gated check-ins and automatic bug creation had been elusive thus far.

Anyone have this working already? Any suggestions?


Create a Time Dimension using the SQL Server Data Tools Dimension Wizard

If you database has no Time or Date table you can use the Dimension Wizard in SQL Server Data Tools (SSDT) to generate your Time Dimension.  You can have the tool generate a Time Table either in the data source (if you have permissions) or on the Server.  When creating your Time Table using the Wizard you have the option to specify the Time or Date range the Table will include dates / times between your specified start and end points.

See the article on MS Docs below for more details on creating Time Dimensions automatically using the Dimension Wizard

Currently Reviewing Open Source Agile Tools

Looking for the best open source tools for running agile projects.  The goal of this little experiment is to create a CI / CD pipeline including planning, task management, source control / versioning, triggered build and test and deployment to the cloud.

Today I’m experimenting with Taiga an open source planning and task management tool.  So far the interface is intuitive and it has most of the features and data points that I would expect to capture during planning.

For free you can have 3 team members and 1 private project (unlimited public projects).  There are Epics, Stories and Sub-tasks to track.  There are Sprints, Backlogs and Kanbans to view.  It even has an issue tracker and a wiki.  You can even link your project timeline to a slack channel to share project updates.

So far this tool is looking pretty good for free.  Are there other free tools that I should be looking at?  Looking for integration with Git and Jenkins to automate builds and tests.  The golden feature is Gated Checkins!  If there is a free open source solution that allows association of an assigned sub-task on checkin to version control then triggers a build in Jenkins and creates an issue (bug) in work item tracking if the build or tests fails or deploys to the cloud if successful the contest is over!  If you know of this magical free toolset please leave links in the comments.

I’ll post a video and screenshots shortly with a more detailed review.

CRUD Script and SSMS Toolkit

Using stored procedures in your Data Access code from ASP.Net applications stops most (not all) SQL Injection Attacks and also ensure that the query is executed with the same parameters in the same order and format each time allowing the query optimizer to use the same query plan on subsequent executions.  So it makes good sense to use stored procedures for almost all access to your database.  The only problem with this practice is the time that it takes to create at least 4 stored procedures for each table in your database.  We need a procedure for Insert, one for Select, one for Update and One for Delete.  We may even need additional stored procedures to get customers by email or to search for customers by FirstName or LastName.  In a database that has 1,000 tables that means at a minimum we are creating 4,000 stored procedures.
 So in order to lighten the DBAs workload we can use an SSMS Add-in (SSMS = SQL Server Management Studio) or a CRUD script (CRUD = Create, Read, Update, Delete) to automate the creation of our Insert, Select, Update and Delete statements.
I found a nifty little script that creates stored procedures for Select, Insert, Update and Delete for all of the tables in a Database or for a Single table when a TableName Variable is set. You can find this script in the Demos folder on the ProDataMan Portal with the Name ISUD with Prefix and Schema Support.sql or you can use the following link to download: CRUDScript
*New: I finally updated CRUDScript for Schema support!
Someone told me about a feature of the SSMS Toolkit a SSMS Add-in available here: SSMS Toolkit
This tool allows you to create CRUD stored procedures for tables based on fully customizable templates that you can change to suit your needs. But this tool does so much more!! See the Features page for more details

Story Points Estimation

When planning an agile project creating User Stories and estimating their complexity is an important step to provide your customer and delivery team with a clear understanding of the solution being developed.  Estimating the complexity of a User Story is something typically done by a Product Owner after or during a meeting with a customer then verified and approved by the delivery team during release and sprint planning.  Make no mistake that this is a consensus not a majority rules estimation process.  While the project owner gets first stab at story point estimation it is the delivery team that will be responsible for doing the actual implementation. The delivery team should never commit to adding a story to a sprint without first having a conversation about the delivery team tasks required to bring the story to the teams stated definition of done.

Since we are estimating relative levels of complexity and not actual hours a modified Fibonacci sequence can be used for estimations of User Stories received by the development team.  This will help keep the team from getting bogged down looking for exact estimates and allow them to “round up” to the next level of complexity.

0, ½, 1, 2, 3, 5, 8, 13, 20, 40, 100

Complexity vs Hourly Estimates
Humans are not very good and estimating actual time for complex activities.  But we happen to be very good at estimating relative complexity, this will be about as about as hard to do as that was.  So when estimating at a high level such as story points it is best to keep those estimates at the relative story point level and save the more precise detailed estimates to the delivery team tasks to be captured during release and sprint planning.  Also whenever possible it is best to keep User Stories to a size that will fit within a single sprint, even better to keep them down to 1-3 day sized chunks.

Ultimately Delivery team tasks will be nested beneath the User Stories at a more granular level so we can save time estimates for these smaller work items.  The sweet spot for tasks nested beneath User Stories 2 to 4 hour chunks.  After 6-10 sprints and sprint planning meetings your teams story point estimations should be pretty accurate.

Since the teams capacity describes the amount of story points that the team can finish in a sprint and a sprint is a time boxed event if we accurately estimate the number of story points we can finish in a sprint we can extrapolate the number of hours required to complete the committed story points.

A great way to start the conversation about task estimates is to play Planning Poker.  Here is a great video to get your started:

User Story Slicing
If our User Stories are too large to fit into a single sprint it may be an Epic or Feature masquerading as a User Story, in this case it is best to break this large complex User Story down into smaller chunks to make it easier to understand.  We call this process slicing or sizing of the User Story.  If we think of the User Story as a slice of double chocolate layered cake (the flavor was irrelevant but call it a craving) then we can think of our slicing efforts as a slice of cake from top to bottom and not simply peeling off the top layer (otherwise you miss out on the frosting between the layers).

Slicing the cake vertically means we follow our business process from the User Interface layer all the way through to any data access components that might be involved, in other words if we have a log in user story we can actually log in because the UI, data layer and database required by the story are all in place.

Small Increments
Let’s break things down into smaller components so that you can understand.  The larger the size of the user story the more moving parts it has, the larger the margin for error.  Also if the user story is too large to fit in a sprint it will affect the team’s apparent capacity and velocity as the burndown chart will not move until the story is marked as complete.  A large user will have many delivery team tasks nested beneath it each of these tasks will have its own time estimates and since the sweet spot for these tasks is 2 hours a large user story could potentially have tens of tasks associated with it.

Story as a container for work items
The User Story is a high-level nontechnical customer requirement and is meant to ease communication between the customer, product owner and delivery team.  As such the user story is not the place for technical detail, this is the realm of the delivery team task (formerly known as developer tasks).  The story point complexity rating has a direct impact on the number and size of delivery team tasks to be expected for each user story.  As a general rule it is best to keep tasks to small workable chunks created and assigned in 2 hour increments.  Two-hour delivery team tasks make estimation far more precise by reducing the margin of hour to minutes instead of hours or days.  During sprint planning we should strive to identify about 2/3rd of the required technical delivery team task as the effort and time required to identity 100% of technical delivery tasks.  We should spend on average 2-4 hours in sprint planning for each week of the sprint.
The more complex the User Story the larger the delivery team tasks will be.  The larger the delivery team task the less accurate the task time estimates will be.  Put simply the more we reduce the amount and size of work in progress the more accurate our time and complexity estimates will be.  See our post on Slicing User Stories for more detail on how to size or slice large and complex User Stories.

Scrum Ceremonies Dont just hit the bullet points! (Rant)

Are your Daily Standups and Sprint Retrospectives taking too long?
Do important stakeholders frequently skip your meetings?
Do team members question the value of your Standups and Retros?

It may be time to reevaluate the priorities of your daily standup and retrospectives.

Don’t just hit the bullet points!
A retrospective is not just about what you did yesterday and what you are going to do tomorrow and what impediments you have encountered.  I’m sure that any agile development guide, website or blog post you read will tell you that a daily standup or sprint retrospective is all about discovering:
What we did well?
What we didn’t do well?
What we’re going to do better next time?
Are there any impediments?

These are the standard bullet points to hit during your daily stand up and sprint retrospective.  But if all you do is hit these bullet points you’re missing the point of a daily standup and sprint retrospective.  The goal of these scrum ceremonies is continuous feedback and continuous improvement therefore your feedback is only valuable if:
What you did or discovered yesterday represents a learning experience for the team
You completed a task that was blocking other work
Learned a more efficient way to complete a task
Discovered a previously unknown dependency
Documented / Performed a previously unstated delivery team task

These items will be of interest to the team.  But simply stating that you are working on the same thing your worked on yesterday and the same thing you will be working on tomorrow is not useful in the standup or sprint retrospective as this information should be available on the team portal and project timeline.  It is also important to avoid “solutioning” during? these ceremonies as the act of creating a solution for a problem facing certain members of the delivery team excludes other team members and stakeholders.  This kind of information with narrow relevance will eventually cause team members and stakeholders to avoid the meeting.  Repetitive, minimally useful information provided as part of the software delivery cycle will quickly be categorized as noise and summarily ignored.  Take heed and ensure that all information you provide and request during these scrum ceremonies is timely and relevant.

Slicing User Stories Method 6

Slicing by CRUD or ISUD (AKA Slicing by Operations)

Any User Stories involving a managed entity, such as a Customer, Order, Employee or Product, will almost always require some level of management functionality.  This management functionality will provide the ability to perform a number of operations including at a minimum operation, such as Create, Read, Update or Deleted.  These operations are commonly referred to as CRUD but that is such an unfortunate acronym as it sounds like something you get between your toes…  Not to mention the fact that in most Relational Database systems such as MySQL and Microsoft SQL Server the operations are actually called Insert, Select, Update and Delete making the acronym ISUDISUD sounds better, soapy and clean to wash away the CRUD between your toes.  So forever more on this site CRUD operations will be referred to as ISUD operations!
ISUD operations are very prevalent when functionality involves the management of entities, such as products, users or orders:
As a Specialty Kite Maker
      I want to manage Kites in my ecommerce website
So I can update Kite details and pricing info if it is changed

If we consider the ISUD typically associated with Product management, we can derive the following more specific and granular User Stories:

As a Specialty Kite Maker
I want to add new Kites to my product list
So customers can purchase them;

As a Customer

     I want to view a list of Kites available for purchase
     So that I can buy one;

As a Specialty Kite Maker

     I want to list the Kites in my product list
     So I know what Kites are currently in stock;

As a Specialty Kite Maker

     I want to update existing Kites in my product list
     So I can adjust for changes in Kite details and pricing info;

As a Specialty Kite Maker

     I want to delete Kites from my product list
     So I can remove Kites that I no longer sell;

As a Specialty Kite Maker

     I want to hide Kites in my product list
     So they cannot be purchased for the time being;

When discussing this method, the question often becomes, “do these more granular User Stories actually provide business value?”.  Is our solution really useful if we cannot update or delete products from the system?  If we consider that in the current scenario we are dealing with a “Specialty Kite Maker” odds are there are a limited number of Kites and Kite Accessories that will be in the product list.  If this is the case then adding, editing or deleting the Kites could be done manually through a database management tool like SQL Server Management Studio for the first few Sprints.  So, for the first Sprint we may just add the list (Select) functionality to support customer purchases and delay the other Update, Delete and Insert User Stories for a later Sprint.  This way we get business value sooner by minimizing “Work In Progress” (WIP) we are able to increase delivery date confidence and deploy only features necessary to deliver value to the customer.  In this scenario, the lack of Insert, Update and Delete functionality will not be noticed by the customer because these are admin only features therefore we deliver just the customer facing User Stories.  This allows us to get to market faster and begin collecting customer feedback while we work to complete additional features.  In the case of discontinued or deleted Kites it may be easier to simply add a checkbox that allows the Kite Maker to mark an item as discontinued or deleted.  This approach may keep the record in the database but simply hide it from the customer view making it easier to implement than an actual Delete operation that may require additional operations to enforce referential integrity.
In short if we break the User Story down by operation we can implement only those operations that provide immediate business value in early Sprints and add other more specific stories once the base functionality is deployed to customers and providing them with “Value”.  “Customer Value” = “Business Value” which of course in almost every case translates to “Business Revenue” to pay for all of the Solution Development.
Slicing User Stories – Method 5 ***** Slicing User Stories – Method 7

Cross Site Scripting (SQL Injection) Attack

A SQL Injection Attack is one of the many security issues that must be address when designing and developing applications that access a database.  The injection vulnerability is potentially present on pages or forms where the user must enter a value to be submitted to the server. If the user input is not properly validated and the database doesn’t protect itself then SQLiA can occur. I have posted the source for the sample application on the portal. To download the SQL Injection Attack Sample Web Site and SQL Script click here: VB.Net Version or C# Version To run this demo code you will need Visual Studio 2008 or higher and SQL Server 2000 or higher installed.  See the video below for an overview of the sample app.

The file consists of a T-SQL Script file named CustomerOrdersDB_SQLInjection.sql used to create the database, tables (including sample data) and stored procedures (Stored procedures were created using CRUD Script) and a Visual Studio 2008 project called Demo.sln. The Visual Studio Solution contains 2 pages: SQLInjection.aspx and SQLInjectionFixed.aspx that as the names imply illustrate a page that is vulnerable to SQL Injection and one that is not (not all possible SQL injection attacks are prevented but most).
Test it out here:

To test the search feature that’s vulnerable to SQL Injection:
Open the Solution (Demo.sln)
Select SQLInjection.aspx in the Solution Explorer
Press Ctrl + F5 or Select Start without Debugging from the Debug menu
Type Antoine into the search box
Press the Search button
Note: You will notice that the results displayed on the page are filtered to show only Antoine Victor.
Try a couple more searches then continue
Copy the injection statement from the bottom of the page
Paste into the search box
Press the Search button

Note: You will see a list of all of the tables defined in the current database and all columns defined in those tables.
Think credit card table, employee table with Social Security Numbers.
Armed with this information a hacker could use the same SQL Injection vulnerability in this page to then request columns and rows from the credit card or employees table.
Fortunately there is a relatively easy fix for this. The fix is a 2-part process, first we validate the user input before sending it to the server and removed any special characters or malicious code, and second we make all calls to the database through stored procedures (created automatically using CRUD Script or the SSMS Toolkit)
To see the page with the Injection issue resolved in the current browser window navigate to SQLInjectionFixed.aspx and follow the previous steps.
“This” SQL Injection issue is now resolved.
For a list of other common injection attacks to test with this demo see: SQL Injection Cheat Sheet.
For details on storing the connection string in the config file and stored procedure calls see the videos below.

Refactor the code so that it is easier to update and maintain:

Add stored procedures to prevent SQL Injection: