Software Development Estimates for Small Jobs

As a consultant, I regularly get asked to estimate the amount of work involved in software changes that customers want. In principle this is fine. I know that all work that is done in business these days is based on a quote. However, in Software Development we are often asked to quote without having any kind of real access to the system we are going to be changing.

As a BI consultant, a lot of the estimates I do are for Reporting, ETL, or Cube changes. Most of the time I have never had access to the customer’s database(s) and have little or no understanding about the changes they want done. Imagine for a moment if a Civil Engineer was asked to quote on building changes without first being allowed to inspect the site and current buildings… It wouldn’t happen!

My experience is that the bigger the job is, the more opportunity is provided to do accurate estimates. This means that on the small jobs (e.g. a couple of weeks of work), you’re often just putting a figure down (guessing) and hoping it covers it. But maybe in such cases a full investigation is just not warranted, or even financially viable? I’ve had situations where the estimate on an individual change takes as long as it would to just make the change.

Anyone out there found a solution to this? Is there a better way of estimating work when the work required is expected to be quite small? Or are we always going to be making “educated” guesses for the small jobs?

Posted in Business Intelligence, Development, SQL Server | Leave a comment

Beware: Renaming a database with non-schemabound views

For many years now I have been aware that views may need refreshing. If you create a view without using the “SCHEMABINDING” clause, you will need to refresh the view if any of the underlying objects change (e.g. change the length of a column). This can be achieved by using either sp_refreshview or sp_recompile.

This past week I got reminded about this in a most unexpected situation. At a customer who is using Microsoft Dynamics CRM, we had a need to create some views to abstract others from the sometimes complicated nature of a CRM database. Because the owners of the CRM database did not want us to add these views to the actual CRM database, we created a new database purely to house the views. This database is guaranteed to live on the same SQL instance as the CRM database so we simply created the views with cross database queries (e.g. select columnA from SourceDB.dbo.Table1). This solution has been working fine for months. I should mention that this environment was a test environment (NOT Production).

Where it went pear-shaped was when they decided to rename the CRM database and restore from a recent backup. The restored database was named the same as the original CRM database name. Without giving it any major thought, I assumed that this would be a seamless change as far as the views go. I was wrong.

To cut a long story short, the views kept on working, but to our dismay we eventually realised that the data being returned was from the old renamed database, not the restored one. Since renaming a database is simply a metadata change, the views continued to point to the original database. Although I now understand why this is, it caught me off guard and I feel that this is a situation that Microsoft should address.

Below are some scripts that will allow you to view this behaviour for yourself. These assume that you are a sysadmin on the server you’re using. These scripts were run against SQL Server 2008 R2 SP1.

Firstly, create a source database with a simple table and one row of data (make sure that you adjust the physical location of the data files below):

use master
go

create database SourceDB
ON (NAME = SourceDb, FILENAME = ‘ <Folder>\SourceDB.mdf’)
LOG ON (NAME = SourceDb_Log, FILENAME = ‘<Folder>\SourceDB_Log.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(1, ‘Craig’);

Now create the database containing a view:

use master
go

create database ViewDB;
go

use ViewDB;
go

create view vSourceTable
as
select ID, Name
from SourceDB.dbo.SourceTable;

go

If you select the data from the view now you’ll see the data that we inserted into the database:

select ID, Name from vSourceTable;

image

Now rename the SourceDB:

use master
go
ALTER DATABASE SourceDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO 

exec SP_RENAMEDB ‘SourceDB’, ‘SourceDB_old’
Go
ALTER DATABASE SourceDB_old  SET MULTI_USER
GO

At this point some might think that the view should fail if you query it, but it doesn’t. It returns the same result set as before.

Now create a new database with the same name as the original but with different data:

use master
go

create database SourceDB
ON (NAME = SourceDb_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_New.mdf’)
LOG ON (NAME = SourceDb_Log_New, FILENAME = ‘C:\Databases\Database Files\SourceDB_Log_New.ldf’)
go

use SourceDB;
go

create table SourceTable (ID int, Name nvarchar(10));

insert into SourceTable(ID, Name)
values(2, ‘Bryden’);

If you query the view it is still showing the original values:

select ID, Name from vSourceTable;

image

In order to get the view to start working on the correct database, we need to refresh the view:

exec sp_refreshview ‘vSourceTable’

Now if we query the view, it is using the correct source:

select ID, Name from vSourceTable;

image

Strange but true!

Posted in Uncategorized | Leave a comment

I have joined WARDY IT

I have been a bit neglectful of my blog recently. I suppose I should have mentioned that I am no longer a contractor and am now working for WARDY IT solutions (www.wardyit.com).

I am super excited by this as WARDY IT has got a very good reputation in Australia and the opportunity to expand my knowledge is huge. My colleagues are all highly skilled and well-known people in SQL circles.

So if you are looking for excellent SQL Consultants in Australia, contact WARDY IT Solutions.

Posted in Uncategorized | Leave a comment

Error while installing Visual Studio 2010 SP1

I recently got given a new laptop to work on and soon realised that Visual Studio 2010 was not up to date. I started the installation of SP1, but the installation kept failing. I tried this three times and it failed three times. The thing that confused me was that it failed at random places. Luckily the logs that Microsoft gives for this install are very verbose and so I discoverred that the error was a common error message accross all the failures. Below is an example:

Returning IDOK. INSTALLMESSAGE_ERROR [The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2203. ]

After seraching for that error code, I quickly realised that the issue appeared to be access to a file that it was trying to replace. Given that this was happening at a random point in the install, I figured that the issue must be another piece of software running that was locking files.

After having a good look at the other apps I had running, I realised that there are two potential candidates. One was Microsoft Security Essentials and the other was Carbonite Pro (which automates my crucial file backups). I disabled both of these and the install succeeded.

I figure that Security Essentials was not to blame because i’ve never had a problem installing anything before. I’ve only been using Carbonite Pro for about a month, so I have to assume it was the culprit.

So if you’re getting the error above, check for file backup utilities that are running and disable them while doing your installation

Cheers

Craig

Posted in Uncategorized | Leave a comment

MS SQL Certifications

Today I passed my exam for MCTS: SQL Server 2008 Database Development. I am by nature quite nervous when I take exams, I don’t know why. With every Microsoft certification I have ever written I have finished the test and questioned whether I have passed prior to the results being displayed. In all cases I have passed reasonably comfortably. I have to admit that the Microsoft Certifications do appear to have gotten more difficult lately, but if you are prepared, you should pass quite easily.

The thing I really want to mention is preparing for this last exam…
I have a lot of past experience with SQL Server and therefore the preparation was a case of making sure that I am familiar with all the recent additions to the database engine. for this I purchased the official training kit (http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-433/dp/0735626391). Since I have been mostly focused on the BI stack of late, I thought it would be good to read the book cover to cover at first and then re-read and focus on sections I felt were my weakest. In between all this I was taking regular practice exams using the practice exams that came with the book. I have to say that this book and the supplied materials are almost shocking. For the most part, I spent time on websites or BOL to make sure I really understood all the new features. This book has four different authors and it shows! The style of some of the chapters were completely different to others. In some sections, the amount of detail was excruciating, while in others it was so light on detail that further investigation was definitely required. To make matters worse, I found at least four answers in the practice questions that were obviously wrong. That makes you start to question all of them.

I feel that if Microsoft are going to publish training kits, they need to be of a better quality than this. I would still recommend this training kit as one of the tools for preparation as it does outline everything you need to know. Do a lot of further reading on your weak topics and you should be OK. If you are new to SQL and are relying on only study to pass, you’ll need to do a lot more than just study this kit.

Cheers
Craig

Posted in Certification, Development, SQL Server | Leave a comment

Overregulation in Standard Operating Environments. A Developer’s worst nightmare

Before you get bored of this blog, let me point out that I have to elaborate on my current work situation in order to get to the point. So please try to read the whole thing.

I recently ventured from the world of permanent employment into my second stint as a contractor. I was getting bored with the lack of challenge at my last (unnamed) company. I also felt that getting involved in some big BI project was just what I needed to help me on my journey to calling myself an expert. I knew that a new company meant a different working environment and that some adjustment would probably be needed. Yet very little could have prepared me for my new environment.

Firstly, let me say that I am now on contract at a large government organisation. I expected that this would bring with it many security issues, but I am a software developer, and surely they would provide me with the tools I need to get the job done and to hopefully enjoy my work. Let me interject here and say that job satisfaction is not a luxury IMO. I will spend 40 – 80 hours a week working, so I expect to enjoy my job and enjoy the tools I’ve been given to do said job. Secondly, let me say that I have taken over a proof of concept BI system which has been given the go ahead to evolve into production, so I am in a prime position to engineer some cool solutions, and that excites me. But…

So, what faced me initially was a bit concerning. I sat down to a Windows XP desktop with the following installed:

  1. IE6 (I know… Can you believe it?)
  2. Office 2003
  3. Only the MS SQL client tools and BIDS. No Local SQL Services (for messing with of course). No AdventureWorks. No full blown Visual Studio (for those excursions into CLR dev).

So my first thought was that this was just a recently installed computer from a century old image. Boy was I wrong,  it turned out to be their standard. Then I discovered to my absolute dismay that I was not a local administrator. When I mentioned some of these things, it was made clear that it was very unlikely to change and there was talk of requests going to seventeen thousand desks before I’d hear anything. On my third day there I realised how rudimentary their security was and I managed to install Google Chrome and BIDS Helper simply by installing them outside of the “Program Files” directory. I was not so lucky with other software.

Moving on to the development SQL Server that I had to work with. Firstly, let me say that I accepted that this project had been “proof of concept” up to this stage and probably had very little budget. Initially I was pleased as I realised that I was set up as an SQL, SSAS and SSRS administrator (sysadmin). It didn’t really bother me too much that I wasn’t a local admin of the actual SQL server. What did bug me was the single core and the 2GB RAM (for the database engine, SSAS, SSRS, and SSIS). I think my iPhone has more power. However, I eventually got used to the 10 second response times from a select top 1 *. This server was also serving other projects!!!

About a week into my tenure there, the SSAS database failed to build due to a lack of disk space. It turned out the Cubes were still set to build on the primary OS drive (in program files). I reported this, and three weeks later I am still waiting for the go ahead to perform my 30 seconds of work to rectify it.

Today I wanted to change the default measurement settings in SSRS (which requires a change to a RDL file somewhere in the Program Files directory). I couldn’t since I don’t have write permissions to that file.

Today was the last day for the Project Manager on my project and they don’t have budget for a good replacement.

At this stage, you’re probably thinking this person is ranting. And maybe I am. But in my opinion, Software Developers should be given the basic tools and rights to do their job and enjoy doing it! The changes I would want at my current contract would not cost them a cent in licence fees, so why is it so hard to get it to happen?

So to the point

  1. At the end of the day, there are people who will associate me with this project. My reputation is important to me. Given the current environment and tools, it is hard to see it being successful. So should I stay or should I go (getting 3 calls a week at the moment)? The sad reality is that a really cool project will get totally undermined by red tape (yet again).
  2. Software Developers deserve to be given good tools to do their job to the best of their abilities. If you are an IT manager that disagrees, I can take comfort in the fact that I for one will not be working for you for too long :)

Let me know your thoughts

Craig

Posted in Business Intelligence, Contracting, Development | 2 Comments

First Blog

Hello world!

I have been resisting the blogging culture for quite some time now. This is probably because I normally have nothing interesting to say, or I’ve felt that some people like to say too much (and I don’t want to be perceived to be one of those). Today, for the first time, I have felt like I should blog about something, so look out for my second post following soon.

When it comes to technical discussions, I sometimes feel I can contribute, but in my line of work I need to progress much further before I will feel that I am an expert. I compare myself to some of the leaders in my field (Mosha Pasumansky, Christoper Web and Darren Gosbell being my favourites) and I see how far I have to go. Still, I am yet to walk into a job and not add value, so maybe I’m ok at what I do. Given this, I will focus my blogs on general issues around operating as a BI developer and contracting. Hopefully I don’t end up sucking at it :)

Craig

Posted in Uncategorized | 1 Comment