Feedback on the Schema Engine OSS release

March 27, 2011 20:46 by Matt Whitfield

A few weeks back, I decided to open source Schema Engine, which is the core of the Atlantis product set. I was really hoping that I would get some good feedback on where to take it, see it used in some new and interesting ways and get some good ideas for features.

One person in particular, Justin Dearing (blog | twitter) has come up with some excellent ideas. He sent me an email saying he had some ideas for features, and I thought he was talking about the 'change the formatting of this DDL' level of feature. I was surprised (pleasantly, I might add) to see that he had thought about it at a much higher level - and some of his thoughts mirror where I had it going anyway (for example, Postgres support was something I had always wanted to do). But some of his other ideas are really cool - so check out his blog post on them, and leave feedback.

I have to say, I thought more people would have been interested in this release, and I am sure there are a fair few SQL people out there who could make use of this sort of functionality. Hopefully, Justin's input will start to give the project the impetus that it would need to achieve the future goals that he has outlined. So do, please, hop on over to his blog, check out his ideas, leave your feedback. Or, if you're looking at using the Schema Engine yourself - leave some feedback here, let me know what you think it's good at, let me know what you think it's bad at.

Schema Engine will be appearing on a more standard on-line forge - most likely CodePlex, in the not too distant future. I need to re-organise the code a bit first, so that it doesn't totally bork all the Atlantis products, while not including huge amounts of non-related code - the structure of the libraries in the Atlantis source tree is a little different, including all sorts of non-Schema Engine related stuff. But, right now, I'm busy preparing my session for SQL Bits 8, and hopefully I will meet some of you there...

Tags: , ,


T-SQL Tuesday #13 - What The Business Wants Is Not What The Business Wants

December 14, 2010 00:00 by Matt Whitfield

This is a really interesting topic chosen by Steve Jones of SQLServerCentral fame, and, for me, is a story of communication skills.

What the business wants can be expressed in a few different ways, by varying people. For example, a high availability system might be described as having no downtime, or it might be described as having geographic redundancy. These requirements might be voiced in a few different ways:

  T-SQL Tuesday
'We need a system that is always available for our customers'


'This system is business critical and cannot be unavailable at all'


'We need to use replication to make sure our system is disaster proof'


'The system has to be hosted in the cloud so that if the office network goes down it is still available'

Based on the premice that a little knowledge is dangerous, those statements are listed in order of increasing danger.

What do I mean by that? Well, the sentiment behind each of those statements is:

'We recognise that our systems and the data within them are valuable, and we want to protect them as much as is reasonably practicable given our budget'.

But the statements above start to not only specify the what - but the how. I have seen many, many systems where dubious architectural decisions have been taken at an early stage because 'that was the way Mr. Manager wanted it'.

Is it reasonable for us, not only as DBAs, but as computer scientists, to allow Mr. Manager to specify the how without a reasonable understanding of what was being asked for?

We've all been there - sitting in a meeting room and Mr. Manager comes up with another gem:

'We could use an EAV table to store that kind of information'

Everyone in the room knows that's not the way to go for the scenario at hand, but, strangely, nobody speaks up. Everyone has that slightly sinking feeling at the pit of their stomach, but nobody says a word.

And there, right there, is the failing. Both in Mr. Manager's 'I've just read about technology X and will suggest it every time I feel slightly out of my depth' and, in each of the people who failed to speak.

That sinking feeling doesn't mean 'Oh dear this system is going to be a mess'. That feeling means 'I need to speak up now in order to avoid a steaming pile of rubbish'.

You might not feel that you have the right environment in which to speak. Or maybe you feel like you shouldn't 'embarrass' Mr. Manager. Well, you're wrong. Ok, you can take times and places to deliver your message, but the message that needs to be delivered at the initial stage of the project is:

'We need, right now, to focus on the what, and leave the how for later - getting the what right is critically important to our success'.

Sometimes, a picture can paint a thousand words. If you're having trouble, get out a flip chart and write, in big letters:

'We need to avoid this:'

Below that, attach a print-out of this classic project picture:

The project

That should make the point, while not embarrassing anyone, and getting things off to a good-humoured start.

Tags: , ,


SQL Southampton User Group

December 13, 2010 00:53 by Matt Whitfield

Last week I attended the first SQL Southampton user group meeting, and I have to say it was a hugely enjoyable occasion.

First of all, thanks to Mark Pryce-Mayer (Twitter | Blog) who put in a lot of care and effort into setting up the user group - even going as far as extensive 'biscuit research'.

We had a small room at St. Andrew's Church on the Avenue in Southampton, and it worked out really well. I gave my presentation 'CLR Demystified' that I gave at PASS Scania and SQL Bits 7, but it was nice to give it to a much smaller audience who could ask questions and engage with me during the presentation. For those of you who haven't seen it - it's a pretty packed presentation - covering everything the CLR has to offer SQL Server in just one session.

What was really interesting was giving that presentation with no projector. I spent the first half bent over my laptop with everyone crowded round - but in the second half I sat down, put the laptop on my knee and presented that way. I felt like Santa at story time - well, it is nearly Christmas!

However, what was really good was to meet some people who I hadn't met before (or, at least, hadn't had the chance to talk to properly) and have a good chat. I had already had the pleasure of meeting Adrian Hills (Twitter | Blog), but it was just a really good opportunity to meet some more people, and I enjoyed having a good chat with Christian Bolton and Andrew Fryer too.

I am really looking forward to the next user group meeting, and I am sure everyone else is too.

Tags: , , , ,


T-SQL Tuesday #12 - Why are DBA skills necessary?

November 2, 2010 00:00 by Matt Whitfield

So, I thought I would try and participate in T-SQL Tuesday, and the topic for this month is 'Why are DBA skills necessary?'. This one is a topic that's close to my heart, as it's an area where I have been bitten in the past while at the same time I continue to see businesses struggle on regardless today.

Let's rewind to way back when (ok, so I'm only talking about 9 years ago) when my career with SQL Server really started to take shape. I'd been tinkering around with SQL Server for 4 years prior to that, but never really got into it.

The task I had was to design a database for an on-line transaction processing system, which would process loyalty transactions from an unspecified number of client devices. When I say system, I do mean system. I mean hardware, software & database. In fact, I even designed the logo. This was a small shop.

At the time, I didn't have much experience as a DBA at all, and designed the system based on what I perceived to be best practices. Actually, my idea of best practice back then was 'interesting' to say the least - focusing on just the database design - there was a total lack of referential integrity, a unique constraint to me was not being able to use the coffee machine because it was being serviced and a transaction was something I paid money in a shop for. I really had no idea.

There were several design mistakes that I made along the way, which all had to be rectified at one time or another. Let's get an example of what I'm talking about:

  • Not thinking up-front about where the clustered index would go was probably the mistake that caused the biggest headache.
  • Not understanding the difference between @@IDENTITY and scope_identity() was fun when data was linked incorrectly - and this wouldn't have happened had referential integrity been enforced.
  • Not having a clear naming scheme - sometimes xxxID meant a reference to ID in table xxx, and sometimes it was a client-supplied match value (think TerminalID - was this a link to the tblCfgTerminals table, or was it the number that the client terminal used to identify itself?).

So, I was making some pretty newbie mistakes - and these were mistakes that I had to live with over a considerable period of time.

Hang on, what on earth does this have to do with DBA skills being important?

Here's the core of what I'm trying to get at - which is that DBA skills are absolutely essential in a mentoring capacity. Not important, essential. And not only DBA skills, but DBA experience. Would a freshly certified MCDBA (or whatever this week's exam is called) have been able to see the mistakes I was making? Would they have been able to tell me a story that really engaged me with their advice, or would it have been the 'because this web page says so'?

Had I had a seasoned and battle-hardened DBA from which to learn, then I would have still made mistakes, no doubt - but I would have made far less mistakes. The time I spent fixing those mistakes could have been spent on learning some new facet that I hadn't yet understood.

Coming from a development background, as I have, I would have found the presence of a DBA hugely beneficial.

So, fast forward back to the present day, and I still see projects and solutions being delivered where it's clear that there has been a total lack of a DBA's touch in the development of systems.

Some of the things I see today make me understand that actually, the mistakes I was making back at the start of my career were not uncommon. In fact, they were very common. Looking at a few soundbite examples of things I've seen recently:

  • A company who have client devices that have well-structured configuration. Instead of this being stored in tables, it's stored in XML with a non-defined schema - and stored in the database as ntext. I thought the developer was joking when I asked 'can we see which devices have x set to y' and he said 'not easily'.
  • Monitoring systems that dump events into tables with no indexes at all, and then create new tables with the same schema arbitrarily - meaning you have to look in a different table based on the date you're looking for, and there is no way of telling which table you have to look in before you look.
  • Reports being generated with nested cursors when relatively simple DML performs the same operation 100 or 1000 times quicker.
  • People using in-line SQL in applications because they don't realise that stored procedures exist.
  • People realising that stored procedures do exist, but running everything in dynamic SQL because they haven't quite understood.

The list goes on. There is a whole world of epic-database-fail out there, and that world needs us. It needs us to use our skills and experience to educate and influence, in a positive way, to try and make sure that the world of the database is understood much more widely, and that fundamental mistakes can be designed out and not rectified later.

The onus is on us to translate the failures of design into terms which management can easily understand - monetary terms. How costly would a system fail be because not enough thought was put in at the beginning? Is it even possible to put a cost on damage to reputation when dealing with failures of a service that is growing in popularity? It's in these terms that we must explain the importance of designing something right from the get go.

Nobody wants to build a house with no foundations, and nobody wants to buy a car where brakes are an optional extra. I think it's time to realise that the following statement is just as true:

'Nobody wants to build a data system without getting a DBA involved, right at the start'.

Tags: , , ,

community | t-sql

Why the SQL Server Community is awesome

October 19, 2010 21:47 by Matt Whitfield

Having had a bit of time to reflect on the time I spent at the Community Connections event at Malmo in Sweden and SQLBits 7 in York, I have come to the conclusion that the SQL Server Community is awesome.


In a previous life I was a Drum and Bass producer and DJ. I worked with a close friend on a lot of music, and had some success with it - we released a few records and had a lot of fun doing it. However, the community around the music left a lot to be desired - sure, within your 'pocket' of friends, everyone would treat you with respect and be pleased to see you. But outside of that you could rarely expect someone to be genuinely interested in talking to you. In all walks of life, you have people who are stratospherically successful in their niche - and those people usually don't have the time to talk to 'normal people'.

So this is why the SQL Server Community rocks - at SQLBits 7 I made the acquaintance of several people who are really 'up there' in terms of their respect from the community at large. And they were happy to talk to 'normal people'. Engaged, in fact. Here were people who had travelled literally thousands of miles to present at an event, and there they were - mucking in, helping stuff bags and joking around with everyone else - not a superiority complex in sight.

This encouraged me hugely - because I feel more engaged with the SQL Server community now than I did before, and I feel like I want to continue to contribute and get to their level. All I can hope is that I can do so with half of the humility displayed by these people.

Also, a big thanks to everyone @ SQLBits 7 - especially those who attended my CLR session!

Hopefully I'll be giving another presentation again at another SQLBits...

Tags: ,


Schema Inspector

Tag cloud


<<  April 2017  >>

View posts in large calendar