Thursday, 31 December 2009


I never wrote down my goals for 2009. Not that I didn’t have any. I’m always learning and often worry about what I don’t know rather than what I do. I'm not sure why, I just do!  My 'to do' list spans a google docs spreadsheet and several notes of personal projects, the majority of which are sql based.

A project to rewrite processing and reporting in an Enterprise Infrastructure is my baby. After a departure from the team, the project became my own. Having planned and documented the stages of the project, I began the frustrating learning curve that is SSIS. I learnt a lot of SSIS (Integration Services) on the fly out of necessity. This was through books, websites, blogs and trial and error.
I utilised VB.NET 2008 for CLR functionality too (something I have only dabbled in before). This was very successful in terms of improving the performance for 2 scalar functions.

DBA wise, I migrated all environments to SQL 2008 Enterprise
This left me free to investigate and implement Enterprise features,

Half way through the year I decided to attempt some certifications. The result was obtaining MCITP : Database Administrator 2008 in September and MCITP : Database Developer 2008 in October. 
Both tracks involved passing 2 exams, first the MCTS (Technical Specialist) then the MCITP.

Sunday, 27 December 2009

I/O Query - Average Stall times per database

Adapted from Glenn Berry's Average Stalls query
DB_NAME(database_id) AS [Database Name]
,AVG(CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1))) AS [avg_read_stall_ms]
,AVG(CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1))) AS [avg_write_stall_ms]
,AVG(CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))) AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
GROUP BY DB_NAME(database_id)
ORDER BY DB_NAME(database_id)

Link : Glenn Berry's Performance Queries

Tuesday, 22 December 2009

Agile Development - Anything but !

Firstly, Agile must work. I'm sure some companies run it very well and reap the benefits.
The business gets flexibility and visibility of technical projects. The adhoc nature of being able to get on without a massive upfront cost of planning, meetings and documentation is surely a developer's dream?

In the interests of impartiality, here is an excellent video by Axosoft on how it is supposed to work.

Why I am currently unconvinced is based on one company's painful implementation of scrum and the epic volume of time wasting that ensued. The introduction of 'Agile' has been a real eye-opener. It has clearly shown the true colours of people wallowing in implementing processes, arguing over whether we are doing things 'the Agile way'. Rather than enabling the team to self manage it has become micro-management, an irony as it is anything but 'Agile'.

I'm pretty sure everyone is to blame for the scenario -
  • Managers for wanting stuff 'now' and circumventing the processes.
  • Developers for not pushing back and communicating lost time.
  • Everyone for wanting work done quickly (and being prepared to circumvent testing and qa processes).
We're mostly very concious of the creation of technical debt, but this in itself is leading to a negative vibe towards quality. This negativity has propogated from attitudes to discussions and has been self  fulfilling in terms of code quality.  Every day has become a fight to be allowed to engineer elegent technical solutions

As a Database Professional the standard answer is 'It depends'. That's not to be awkward, it's the truth.
You can have your data now as a one-off run or automated daily in a process.
How effeciently that process runs can have many levels of optimizations
  • Have we chosen data types wisely?
  • How well is the code written?
  • Is it secure?
  • Does it scale? Today's data volume and execution plan will not be valid a year from now.
  • Is the hardware adequate?
  • Is it configured optimally?
Scrum doesn't discourage planning it merely timeboxes it. Our implementation heavily encourages quick wins and the prioritization of asthetically pleasing output over well thought out solutions. In many ways getting everyone together to discuss the next project helps with the realism of how much will be omitted by releasing at the end of a 2 week cycle. To be fair, increasing technical debt due to quick wins can happen anywhere and can not totally be blamed on 'Agile'.

The agility of the team is majorly hampered by other factors too, namely -
  • Domain masters. Members of the team who exclusively deal with certain projects/code.
  • Zero documentation. 
  • Minimal code commenting
  • Superficial testing (usually only of visual aspects)
  • A build/release process that only a couple of senior developers understand.
    (Old issues reappear at every release due to extremely poor release management).
The main benefit of Agile is that it has provided is a high level of business visibility (everyone likes to be in the know).

The cost for this has been extremely high however.
For the first 6 months the number of people involved at all stages has been ridiculous.
  • The entire team (originally numbering 16) partiticating in 'poker planning', ironically a time estimating task conducted first in story points and eventually (after much debate) in hours.
  • The entire team doing Sprint Planning (breaking down tasks into a painfully stupid level of detail)
  • The entire team doing Sprint 'reviews'  (self congratulatory bleugh...)
The above meetings happen EVERY 2 WEEKS. Rarely do we make a fortnightly release, surely the point of the cycle!

Training is a major point where I feel we have fallen down. Rather than train the team, some senior members attended training and eventually 'certified' as ScrumMasters (theres a parallel with a Rugby 'scrum' here in terms of the style in which early meetings were conducted). 1 informal overview session of knowledge transfer occured and subsequent to that, each planning meeting was used to ram home 'scrum'. The lack of control (and initially timeboxing) of these sessions encouraged a 'free for all' debate that regularly took twice as long as it should. The relevence of material did not match the skill sets and the number of attendees far in excess of a quorum.

The upshot of this was that one team member became ScrumMaster and 'supported' the team for 2 weeks (support encompassed a range of skills around office comedy, Twitter and Youtube, with the useful addition of enquiring as to our progress daily). The Agile tool itself became a hot issue with the team switching repeatedly between an online planner, shared spreadsheet and finally a flip chart to show progression.

Fortunately for me it (eventually) became obvious that I was shoehorning my work into the scrum process for the sake of it. Poker Planing is irrelevent when you're the only one working with a technology and therefore estimating it. Estimating has been largely guesswork due to the SSIS learning curve (or mountain). The same reasons apply for omitting detailed discussions around implementation and the planning process.

The DBA role (including a significant amount of  ETL development) is now considered a SysAdmin one and I now sit outside of Scrum, acting in consultancy to when necessary.

By not practicing 'Agile' doesn't mean I'm not agile, it doesn't mean I cannot be flexible and respond quickly to well thought out requests. As a DBA I'm very reactive when I need to be. Admittedly the attention I pay to planning, implementing and monitoring my environment means that those surprises occur rarely.

Time is very important to me and this is my principle reason for opposing the scrum alliance. The man hours wasted in these meetings is frustrating for the technically driven, let alone anyone striving to achieve a systems nivarna. Anyone with the slightest sense of pride in their productivity ends up making up this shortfall from personal time.

I do genuinely hope that I do see Agile work well one day, but until then...

Agile : A selection of reading -

Chickens, Pigs, and Really Inappropriate Terminology

Google : Anything but waterfall

Top Reasons To Not Go Scrum/Agile

The decline and fall of Agile

The top 10 things I hate about Agile projects

The secret skill revealed in the rusty booklet

Bookmark : Custom Alert Scripts for Transactional Replication

There is such a wealth of great material coming out of the SQL community now.

This from Kun Lee on replication is excellent...

Customised Alerts for Transactional Replication

Monday, 21 December 2009

New Site - BIDN

I seem to be endlessly posting links nowadays. There WILL be some real content soon! (have about a dozen posts in draft at the moment).

Anyway, BIDN is Brian Knight's new site over at

It's short for Business Intelligence Developer Network and looks to be a promising community site, a bit like SQL Server Central but for BI developers.

Wednesday, 16 December 2009

SSIS : Accessing Variables

During some recent SSIS package development I had cause to access a variable I had defined from scripts in both the Control and Data flows.

So that I remember (and for anyone else that cares) here is the subtle difference in syntax.
(I'm using SSIS 2008)

For the Script Task (within the Control flow) >

1) Add your defined variable to the ReadWriteVariables property of the task.
2) Within the script, refer to the variable like this :

Dts.Variables("User::MyVariable").Value = “New Value”

Within a Script Component (inside a Data flow) >

1) Add the variable to the ReadWriteVariables property of the component.
2) Within the script, refer to the variable like this :

Me.ReadWriteVariables("User::MyVariable").Value = “New Value”

Bookmark : How to build and maintain a tiered WSUS infrastructure

Could have done with this a couple of months back...

How to build and maintain a tiered WSUS infrastructure

Monday, 7 December 2009

SSIS : Execute SQL Task for Dynamic Update Statements

Using the SSIS Execute SQL Task for updates

1) Create a Variable to hold the SQL command
2) Set EvaluateAsExpression to TRUE
3) Set the Expression (example below)

"UPDATE  [Audit].[ImportProgress] SET LastProcessedID = " +(DT_STR, 12,1252) @[User::LastProcessedID]  + " ,  LatestDate ='"  +(DT_WSTR, 20)(DT_DBTIMESTAMP) @[User::LastDateTime]  +  "'  WHERE ETLProcess = 1"

4) Add an 'Execute SQL Task' task to the control flow.
5) Set the SQLSourceType property to Variable
6) Set the SourceVariable property to the variable name specified in step 1

Sunday, 6 December 2009

Wednesday, 2 December 2009

SQL 2005 Express Edition - No Sql agent!

I've never had a cause to look at the Express edition of SQL 2005 until today.

My suprise finding (and reason for this post) is that there is no SQL Agent!
Google brings back no end of third party solutions however...