The Successful Consulting Series is a set of articles that are being written to both, help decisions on joining the consulting field and also, help existing consultants in their professional development initiatives. Please visit, “Successful Consulting Series” for a full listing of each part in this series.

Estimating work is both an art and a skill as a consultant.  There isn’t one way or set of standards someone can show you to take and use for every client or project you confront.  What a seasoned consultant can do isprovide a basis you can utilize for each potential client, project or task.

Note: Many consultants have different techniques for estimation.  This will only go over one method that works for some consultants.  Your own skills and time management is a large part in estimation which makes estimation a task that is difficult to set a standard for. Keep that in mind while reading on.

A good way to look at estimation is to look at estimating an actual project.  For this, the following fictitious project and client will be used.

Widget Maker LTD has approached you for help upgrading a SQL Server instance to SQL Server 2008 R2 from SQL Server 2005.  The SQL Server 2005 instance has 14 databases, of which 5 are mirrored.  There are two primary activities occurring for all the databases on the SQL Server 2005 instance; integration services from a SQL Server 2008 R2 instance and SSIS 2008 R2 installation provide ETL processing from other systems, and an application server using WCF services to provide data to a warehouse management system that was developed and maintained in-house.  Widget Maker LTD has made it very clear that there can only be a total of 10 minutes of downtime for the upgrade.  Anything greater would cause a loss of $100,000 a minute.

A meeting has taken place to obtain all the details for the needs of the client.  In the meeting, the client provided the following chart and the details outlined above.  The client is in a rush to meet the end of the year budget for IT.  Since it is already November, the client needs a statement of work (SOW) that they will compare to two other consulting resources.  The client will chose the most appropriate based on the following criteria.

  1. Time to complete – must meet a deadline of December 15th
  2. Under budget of $75,000

Estimating this work

There are stages that need to be considered in any estimation of work.  Each stage relates to the other and in many cases, cannot be performed until one is completed.  This is why the stages are ordered below.

  • Discovery
  • Owners
  • Technical Knowledge
  • System Details

  • System failure points

  • Development and Design Planning

  • Ownership relations

  • Development changes required or development to be done

  • Design new architecture

  • Failure planning / Success after tasks

  • Actual Coding, scripting or documenting of steps to perform

  • Implementation

  • Required owners involvement

  • Personnel Requirements for success

  • Time to implement

Although some stages in the above outline may or may not directly relate to the type of consulting work being performed, with slight alterations to the stages wording, the meaning of each comes to the same conclusions for estimating hours of work.

Breaking down the stages

The first stage, discovery, is typically one that is under-estimated.  This can be said for many fields such as programming, system engineering, implementation or database architecture.  In reality, the actual discovery stage is both the most critical and a major segment of hours in the total estimation of work to be performed.

For Widget Maker LTD, the needs of the upgrade to SQL Server 2008 R2 may initially seem to be a straightforward discovery.   In fact, many consultants may walk away from the meeting and assume the discovery has been completed from the meeting with the client.  After all, the major key aspects to the project have been pointed out already.

  • SQL Server 2005 upgrade to SQL Server 2008 R2
  • Mirroring
  • Application Server WCF services
  • Integration server with SSIS

Mistaken discovery of these only being the major components to the project may lead to a small amount of hours allocated to the project.  Some may even bypass the discovery phase all together as being completed and no hours allocated.

In reality, the discovery stage for this has only begun.  Let’s break it down and see how the thought process should go while adding hours to each discovery item.

  • SQL Server 2005 upgrade to SQL Server 2008 R2

  • 10 – Deprecated feature usage discovery

  • Each database being hosted

  • 5 – Security method used

  • 1 – Editions used and requirements

  • 5 – Owners of each database and buy in to upgrade

  • 10 – Hardware being used to host the SQL Server instances

  • 5 – Licensing needs (changes/cost)

  • 12 – SQL features utilized needing modifications/review/design considerations

  • SQL Server Agent Jobs

  • Any other features installed

  • SSIS, SSRS, SSAS, FILESTREAM, Full Text etc…

  • 5 – Server clustering involvement

  • Mirroring

  • 5 – Custom implementations for failover

  • 5 – In-place failover of features

  • 10 – Hardware being used to host the mirror

  • 5 – Stability of mirror

  • Application

  • 5 – Connectivity provider being used

  • 20 – Changes needed to application

  • 5 – Does application handle the failover scenario?

  • Integration Server

  • 5 – Number of packages

  • 1 – Schedule and execution times of each

  • 5 – Connections and providers used

  • 1 – Security method used

  • 10 – Will SSIS packages require metadata refresh or any other needs?

As we can see, the discovery stage has become much more than simply discovering what version of SQL Server is in place and what other major components are relying on the databases on the SQL Server.  After listing these discovery items out, hours accumulated to 135 hours or ~3 weeks on a 40 hour work week.  This is a method of walking through and estimating discovery.  However, the steps are not completed.  The next step in discovery is movement of hours and making suggestive cutbacks based on where hours can be eliminated to make this a more feasible overall accumulated amount of hours the client will accept.  For example, Application discovery can be reduced by pulling 20 hours from the changes needed to the application and push them to the client.  The next reduction is made by combing hours into similar work performed.  For the integration server, application and SQL Server instances themselves, security can be combined by using methods to discover the security models quicker.  Scripting and so forth can be sent to the client for this discovery to be performed by the client.  This eliminates the hours to be done by you, the consultant.

The primary thing that we have come to in this example and discovery stage is; there are far more aspects to even simple projects in the form of discovery than we may think from the start.  While these discovery items can be seen in higher level steps, the breakdown in estimating them is critical.  Underestimating the discovery with something such as a default, 20 hours for discovery, can truly break a project and the overall relationship with the client.****

Development and Design Planning

The development and design planning stage is another stage that has room for reducing hours in the estimation process by merging it into the coding phase.  For Widget Maker LTD and the project at hand, the hours needed in the development and design planning stage is a great example of this.

For a SQL Server upgrade, there are tools we can utilize to make planning (and post back to discovery) quicker.  Upgrade advisor tools shipped with SQL Server can reduce potentially dozens of hours.  At the same time, they can increase hours depending on what we find.  While running these tools and our own planning, the design and development needs evolve.  Even knowing all planning is an evolution which increases or decreases hours, we need to set an estimation of hours to the stage.  Remember, this is an estimation and while some SOWs may be a fixed bid set of hours, we should be fair to ourselves and the fact that we are being paid for the real hours utilized.

Break down the development and design for Widget Maker LTD SQL Server upgrade.  We would break this down the same for any project and estimation process.  The point here is, break it down and further pinpoint hours we need based on our experience, resources and skills at hand.

  • Design
  • 5 – Method of upgrade to be used
  • 10 – Hardware needed during upgrade

  • 20 – Recovery planning design

  • Development

  • Method to be used requirements

  • 30 – Upgrade scripting needed

  • 25 – Recovery scripting

  • 10 – Application development needs

  • 15 – SSIS changes

We’ve really put this estimation stage down in a high level and, in most cases, it will be.  Estimating the design and development above is made from a seasoned data architect that has performed these steps before.  Now, even seasoned consultants perform the estimation stages.  So don’t assume experience bypasses important steps in estimating work to be performed.  If it is, failure typically creeps into a project.

Given the Widget Maker LTD needs the mirror in place, a seasoned architect would typically take advantage of that mirroring installation for the upgrade.  This reduces hours and reduces downtime for the client.  Recall, the client requires a maximum of 10 minutes of downtime to the databases.  Utilizing the mirror in this case is a perfect method and one that will reduce both our hours estimated and the downtime requirements to be met.

Notice the recovery hours allocated are high.  This is due to the downtime factoring of the client.  Ensure that all steps to accomplish the task are estimated.  In this projects estimation, recovery may have been deemed part of the method of upgrade.  In many cases, underestimating hours creeps in when major tasks are combined.  Breaking them out is a much more stable method and allows us to make better estimates.

We are now up to an estimated 250 hours.  We’ve blown our time essentially at this point.  As mentioned before, to reduce hours and ultimately win the client and work to be done, there can be a merger of stages.  At this point, the hours for design and development can be merged into the actual development of the project.  We would also want to start considering utilizing the clients personnel in the SOW to accomplish the overall amount of work.  This would drastically reduce the hours needed.

For example, the Widget Maker LTD Company has a team of DBAs.  These DBAs could be added as utilized resources to the SOW.  This resource utilization method potentially reduces the overall hours by 100 hours.  Let’s outline this as follows.

Discovery and Design/Development to be performed by client personnel (DBAs)

  • Discovery

  • Document discovery and reporting to be sent to consultant

  • Upgrade Advisor

  • Discovery scripts to execute and send back to consultant

  • Trace capture to capture connections

  • Design/Development

  • Report all hardware and resources available

  • Perform actual upgrade of mirror and re-implement mirroring from 2005 to new 2008 R2 mirror – All performed based on documentation provided by consultant

As you can see, moving these major tasks and further, finding more tasks to offload, greatly reduces the hours needed by the actual consultant or consulting company.  By doing this, let’s say our estimation is now down to 125 hours.  Since the SOW will be written to allow the internal resources of Widget Maker LTD, the hours internally and the hours by the consultant can be performed concurrently.  This reduces our estimated overall time to 4 weeks.

Implementation

As we’ve seen, estimation has many factors that allow us to both manipulate the hours needed as well as critical stages in the estimation and see where hours may be more critically needed.  Implementation is another major stage in an estimation and the successful writing of an SOW.  Implementation can have many factors that the other stages may not have though. In many cases, implementation is not allowed by the actual consulting services.  Internal security and resources are required.  This equates to documentation and efforts put forth by the consultant to ensure the internal resources are aware of exactly what is needed and how each step should be done.

With a restriction of not being able to actual perform the implementation of a project, manipulates the hours estimated greatly.  For one, support is added into the SOW for after the implementation.  Always estimated support hours.  This builds relationships and future work.  What we do have to consider is the hours needed to write an implementation document and if needed, training.

Although documentation and training can be lower than the hours estimated to actually perform the steps provided by consulting services, don’t take them for granted.  This stage is one that truly is dependent on the skills of the consultant.  Having set standards on how to document and how to train is a good way to not blow the hours estimated for these tasks.

For the given Widget Maker LTD Company, estimating 20 hours to documenting and 20 hours to implementation training would be extremely sound.  This allocates the last week to the task.  Also, as we’ve seen, merging tasks is a great reduction in hours estimated to meet needs of the client and cost restrictions.  As with the development merging, documentation over the entire span of work can be done as time moves.  This would end in a document needing polishing but overall, completion while the tasks have been performed.

Given the merging, utilization of resources and writing of the SOW and estimation, we have concluded on the Widget Maker LTD Company that we need 4 weeks to complete the work.  This meets the time needs as well as us, the consultant, working on our hourly rate to meet the budget limits.

Summary

This article has gone over a lot of information, put a lot of hours on a project that was focused on.  As we’ve also gone over, the real art to estimation is the movement of hours and knowing how we can manage estimating work accurately as it meets the skills, resources and client needs.  When estimating work as a consultant, ensure that all stages are considered critical but even more importantly; ensure each stage is related to the next estimation stage.  Pulling hours and merging them into each stage to accomplish the client’s needs will always be more successful as it starts with winning a bid and writing a better SOW.