Process, process, process...

I'm a fan of both documentation and agreed processes. Really. A big fan. There's nothing quite like it. OK, so I'm a DBA and we don't normally document stuff or follow due process, but I really wish we could. It would make things so much easier.

There is a major challenge with this - it's not easy. We all WANT to have everything nice, tidy and available, but we generally rail against processes that don't do what we want, when we want them to. It does seem that a lot of processes and standards exist because then someone can place a tick in a box at audit time (and those timese are becoming more and more frequent, I can tell you).

A business process is usually aligned with a methodology. ITIL, COBIT, PRINCE, TOGAF, etc are all great tools to help you get a handle on what you're doing, how to evidence that you're adhering to to process, and provide auditors with warm feelings inside.

However, when the process just doesn’t make sense (even if it ticks all the boxes), you're less likely to collect good data about your project's performance against key deliverables and KPIs. If the process only exists for it's own sake, then you should really take a long hard look at that process and see if it could be done another way or removed altogether. This, I fear, is a stumbling block for many. Wouldn't it be great if we could just do the job, and the process takes care if itself ? I know I would. I'd also buy-in to a process that was designed with my needs in mind, and if the business needs could also be met from the same data, then all the better.

Got a couple of quotes for you here. One from Albert Einstein, and one from a good friend:

Einstein : "Things should be simple, not simpler."

Friend : "The trick with PRINCE2 is knowing which bits NOT to implement."

Einstein was right on the money that. If the process isn't simple, change the process. All well and good, but try not to get hung up on simplifying EVERY process. There's an iterative approach to evaluating what you're doing, and that's really important. It's not enough to say that you have always done it that way, so that's how it'll be done every time in the future. If you always do what you've always done, you'll always get what you've always got.

My friend was also spot on in his assesment of methodologies - why would you need to run every project to strict principles, when not all of the those principles can be applied to every project ? To quote Monty Python, "Universal affirmatives can only be partially converted : All of Alma Cogan is dead, but only some of the class of dead people are Alma Cogan".

I'll give you a quick example, if I may ?

Today I was passed a document for review. It had a RACI matrix, scope, executive summary, all the things that you need keep your auditors happy. Except content.

That document only contained a link to a spreadsheet that actually had all the data in. The data that was collected and confirmed by those in the RACI matrix.

The problem here is that there's no process for expressing the useful data in the methodology being applied, so the first document only exists to complete the process, and nothing else. It has no other useful meaning. Further, the REAL data has no project metadata associated with it, and as such is considered by the project as 'junk data'. If it's junk, why does it exist ? If it's not junk, how could it be properly classified ?

Perhaps the solution may be to revisit the project processes and standards to make them simple, or perhaps not apply all principles to all projects ?

I'm not sure, but reviewing one appropriate document has to be more efficient that reviewing two that have no actual value.

And it's easier.

Back soon…

Multiple Transaction Log Physical Files - Myth punctured

I always thought that there was no really good reason for having multiple transaction log physical files.

OK, so that's a bit of a broad statement, but humor me a while.

The transaction log is cyclical in nature, as we all know. It's well documented in loads of places, not the least of which is http://msdn.microsoft.com/en-gb/library/ms179355(v=sql.105).aspx

There's a common understanding in the wrap-around nature of the transaction log, in that when one Virtual Log File (VLF) has no free space, the database engine will go a'huntin' for a VLF that’s next in line, and will go back to the first VLF once transactions have been flushed from it. Hence 'wrap-around'.

It follows, therefore, that when you have multiple transaction log files, then the first one will fill up with VLFs until it hits a buffer (be that disk space or auto-growth limits), and the the log will then spill into the next available physical file.

This is actually NOT the case. I know, because I've just seen it.

The scenario was thus:

We're creating a new Sharepoint Content Database, and then doing a stsadm -o mergecontentdbs to shift the content across to the new database. A side issue to this is that the new content DB have multiple files in the PRIMARY filegroup, but that's for another time.

As usual, there's a lack of disk space on the drives that we want to use, and the current content is such that the whole database move operation is contained in a single transaction (again, one for another time, I think).

So, being a diligent DBA, I go hunting for space on another drive to hold the second transaction log file, taking the spill once the primary one hits the 35GB cap I put on it to ensure that there's room on the disk for the new content DB in it's entirety. I find some space on another drive, once I've deleted a developer's PST backups and old Service Pack installers (<sigh>), so that's where I create my second physical log file. I fully expected this to remain at the size I created it until it was required to take up the spill.

What I ACTUALLY saw is that from the moment I created the secondary log file, it started being used even though the primary was yet to hit the buffers. Odd, I thought. OK, so maybe it'll just use that one until it fills up and go back to the primary.

Wrong again.

It seems that what the database engine is doing is using one physical file until it hits the next auto-growth point, then it switches to the secondary. While using the secondary, it then expands the primary by the auto-growth increment. When the secondary hits the next boundary, transactions revert to the primary in the same way as before, so you get this flip / flopping of transaction log file usage, and growth rates are split 50/50 between the two physical files.

Of course, once the operation is complete, I'll remove the secondary log file, as I don't need it, and it's on a drive it shouldn't be on, but the observed behaviour certainly seems at odds with the accepted wisdom that one will fill, and then spill to the second.

See, you really do learn something every day.

Back soon…

Change Data Capture: Keep up...

So…

It would appear that in the great tumult of development, some things take a while to surface. While people are being all 'agile' and 'waterfall', it's easy not to keep up with what's new in the database world.

One thing I'm being asked about a lot these days is Change Data Capture (CDC), which came in in SQL2008 (I think). The questions generally go along the lines of:

"Can we use CDC as our database audit ?"

To which, of course, the answer has to be "No." Not because your DBA doesn't want you to use new stuff, but because it seems that people are missing the point of CDC. It's 'Capture', not 'History' that kind of gives the game away here.

A cursory glance at documentation would tell you that SQL Server can now track changes to your tables, and preserve those changes for later inspection. All good stuff. Honest.

But there are some important points to note about CDC, and you should probably be aware of a few things before you decide which way to jump.

1.      CDC is a log-reader, and uses the same mechanism as Replication. This means that your transactions are committed to the database but cannot be flushed from the transaction log until CDC has processed them. And that's asynchronously. On a high-transaction database, this may lead to a larger transaction log than you would otherwise have enjoyed. Change Tracking is, however, synchronous. Go figure !

2.      CDC can cause index fragementation if the retention value is high. OK, I'll admit to having to go with the consensus on this one, as I've not proved it myself, but it's fairly easy to see how that would happen, especially when you consider that the ChangeTable is a function, not an actual table.

3.      CDC has a default retention of 2 days. This allows you 2 days to extract your audit information from the ChangeTable, and put it in a proper audit table. I can see that perhaps 7 days might not be a bad idea as a safety net in case your extract falls over in a heap, but any more than that could be considered excessive.

4.      CDC is NOT the same as Change Tracking. CDC captures the actual value changed, Change Tracking only tells you that a value was changed. Think of the difference between 'The price has changed from $5.00 to $4.99' and 'The price is different now'.

5.      Implementing CDC is a non-trivial task to get right. And it's probably going to be painful if you get it wrong :-)

6.      Preserving CDC when restoring a database is complex. Someone else wrote a nice blog about this. You can read it here: http://www.sqlsoldier.com/wp/sqlserver/cdcinteroperabilitywithmirroringandrecovery

I think I've got the salient points outlined here, and I'm sure that there's a debate to be had on these topics and more. Please remember that this is only in my own personal experience. You or your DBA may have your own opinion.

But I hope I've made a case for NOT using CDC as your only audit option.

Back soon..

Venting - "Animal Farm"

In George Orwell's classic allegory of the events surrounding and aftermath of the Russian Revolution, the action follows 3 distinct phases;

Phase 1 consists of the animals deciding that a change is necessary and that it indeed must happen, no matter how uncomfortable the consequences. It is the 'greater good' that requires the repressive overlords to be themselves overthrown.

Phase 2 involves the planning and actioning (ugh) of the Revolution, and the state of near Utopia that exists, even if only for a very short time, before...

Phase 3 where those now in power and having dominion over the rest of the population start to behave in a way that makes life after the revolution at least as hard (if not harder) than it was before, and nothing has really changed in the day-to-day lives of those on the farm.

I guess the message is either 'Be Careful What You Wish For' or perhaps in the deeper 'You Cannot Change The Instinct Of Man'. Whatever. I'm no scholar, and I don't want to be drawn into the meaing or otherwise of a literary classic.

What I DO want to say about it is this:

That looks a lot like how Businesses, Corporations, and (to a lesser extent) IT Departments have evolved over the last 10 years or so. And it's worryingly cyclical, and cycling more frequently every time.

I'll explain…

The recent shenannigans in the Financial Markets / Oil Industry / some other crisis means that everyone agrees change should happen. It doesn’t matter what the crisis actually is, you understand - what matters is that those at the top have been percieved to have lost their way by people just below the top. Sheesh, the Brits even recently elected a coalition government - how desperate was that ?!?

And so the Change Agenda / Transformation Journey / Meaningless Portmanteau begins. Everyone gets excited about a new 'Head Of..', new 'Divisions', etc, basically anything that stops them thinking about what THE ACTUAL PROBLEM is. Plenty of time and money is spent in these stages. There's rewards, communications, some parting of the ways, all in the cause of Vision and Change, because change is good, right ?

WRONG ! Change for change's sake is NOT good. I've lost count of the number of restructures / reorganisations / road maps / blueprints I've witnessed in 20 years, and not ONE of them does more than make people think they're doing things differently. Sure, there are more KPIs, more metrics, more RAG statuses to let everyone know how well things are going, but in essence nothing has changed.

(as a bit of a rant, I know of a place where the Restaurant, Shop, and Café have had 3 refits each in 10 years, yet to replace a worn-out desk or chair requires an Occupational Health Assessment)

Change is only effective when things ACTUALLY change. A new OS on the Desktop is great, but shifting to more collaborative working (and by that I mean talking to people and understanding their wants and needs in a mutually respectful environment) would bring about a greater change in your business. A new coffee machine will boost morale (and revenue) temporarily, but it's ridiculous to expect your workforce to be more motivated because they drink FairTrade Decaff Americanos instead of fairly nice free coffee from a vending machine.

It's just that it's hard to change a culture, it's hard to change the nature of business, and it's hard to focus on what the problem really is before diving in and changing everything. Things that are hard tend not to get done.

However, I don't believe that change is impossible. Once we stop thinking of ourselves in terms of 'He does this job, she does that job, and they're different jobs', we might start to get some forward traction. Sure, the jobs may be different, but the people doing them are not so different as they might think.

I wonder what Orwell would have thought about that…

Back Soon...

The Unicorn Scenario

In June 2011, Leicester City Council were forced to admit that they did not have any plans in place for the continuation of Council functions following a Zombie Apocalypse. This was as a result of a Freedom Of Information request sent to the council by a concerned citizen. Presumably, there would have been questions asked before the statement was issued, people consulted, the Press Office would have to prepare and get approval for the copy to go to print or other media. The whole frivolous thing could have cost the Council thousands of pounds, just to satisfy a request which they were legally obliged to respond to, regardless of the probability of such an event occurring.

You can read about it here: http://www.bbc.co.uk/news/uk-england-leicestershire-13713798

Whilst this is, on the face of it, ridiculous, the fact that it happened speaks to a new and worrying trend in the business world - Risk Analysis and Mitigation. Don't misunderstand me, I don't want to suggest that your development / project / live service should NOT be subject to Risk Analysis, it's just that there should be some sanity in that process.

I was working on a project recently that was subject to an over-zealous risk analysis. You know, where someone thinks up the craziest and most unlikely thing that could happen, and then asks you how you're going to mitigate that risk to their project.

Example 1:

"There is a risk that key resources would not be technically skilled enough to carry out the implementation".

What ?? You're being asked if you know what you're doing, and someone thinks that you may not be skilled enough to do your job ? That's not a risk - that's an insult.

Example 2:

"There is a risk that, once implemented, the new service may cease to function in the expected manner, causing loss of reputation and significant cost".

Again, that's not a risk in my view - that's scaremongering.

(I should probably point out that I've not actually been asked to mitigate the above risks. But I've seen something very similar...)

Risk Anaysis IS important. It can help you with your planning and implementation, drive out inconsistancies, and ensure that all stakeholders understand what it is that they are actually about to undertake. Where it gets silly is when those risks and their mitigations become more important to the business than actually doing the work in the first place.

This is what I call the 'Unicorn Scenario', and it goes like this:

Risk:
There is a risk that herds of Unicorns will prevent access to the building at key points during migration or installation.

Mitigation:
Bowls of sugar lumps are to be placed at strategic points in the overflow car-park, thus diverting the Unicorns from preventing access.

Stupid ?? Perhaps. But guess what's next on the lisk of risks ? Here it is:

Risk:
There is a risk that the overflow car-park may not have enough capacity to contain all the Unicorns described in the previous risk.

Mitigation:
After consultation with the landlords, additional space in the allotments adjacent to the overflow car-park is available on demand.

So you see, a seemingly ridiculous risk has turned into something that costs both time and money. And then there's always Risk 3:

Risk:
There is a risk that budget will not be available for the purchase of bowls to hold the sugar lumps. The ownership of the sugar lumps post-Unicorn distraction has also yet to be defined and accepted.

Mitigation:
Head-->Desk, Head-->Desk, Head-->Desk !

Sometimes, we need to take a step back and ask "Is this REALLY a risk". If we don't, we'll end up being terrified of Dragons, but we'll have project approval to play hopscotch on the M25.

Back soon...