Coding By the Sea: The story of SQL Search

SQL Search is a new tool from Red Gate that allows you to search SQL Server database schemas, instantly locating objects. This is the story of its development.

Back at the start of October, Neil Davidson, one of Red Gate’s CEOs, posted a request along these lines on our internal forum:

We’re going to rent a house (probably on the Suffolk coast, far away enough to get some distance from the office but near enough to be convenient) for a week, fill it with a project team, give them a project and see what they can achieve in a week. I’m looking for:

– 2 developers
– 1 tester
– 1 UX
– 0 project managers

If you fit the bill, have a sense of adventure, are willing to try something new and are free from Sunday November 1st to Saturday 7th November then send me an e-mail. We’ll expect you to work fairly solidly for the extended week. This isn’t a 9-5 thing.

You will be living in the house, and you can’t bring partners. In return, we’ll pay all your expenses (including a small beer allowance), you’ll get to work with people you might not have worked with before, in a way you haven’t worked before, and produce something cool. It’ll be fun.

Chapter 0 – The Prequel

Well – there’s an offer you don’t get every day! After the team was announced (Alex, Dom, Nagashree and myself), we had a quick chat to decide on what project we were going to attack. Clearly this was an important decision: we wanted something sufficiently interesting that it’d produce a worthwhile result, but at the same time, have some chance of being achievable in the week We settled on a search tool for SQL Server, with the aim of being able to release it as a free add-in for SQL Server Management Studio, trying to eliminate some of the pain points with locating and navigating around objects in a database’s schema.

Sunday 1st November quickly came round, and we set about packing the cars. I would have love to have seen the look on the Security Guards’ faces watching five computers, about ten TFTs, countless post-it notes, a whiteboard, and two office desks leaving the building mid-afternoon on a Sunday. I guess they must be used to us by now…

A couple of hours’ drive later, we made it to our accommodation for the week, and what a sight it was. A beautifully converted barn, big enough to sleep 12, with everything you could want, including table football, wood-burning fire, amazing kitchen, and two of the best chairs I’ve seen, all presented wonderfully. The solid wood dining table was soon turned into our office, though, filled with IT gear. The webcam installation meant Cat5 wrapped around the oak beams, and cable ties on the iron railings, and the enormous chairs turned into office seating for the week.


Chapter 1 – The Planning (Monday, 0900)

Monday morning arrived, and with only five days to go, we needed to rapidly specify exactly what we were going to aim for in the week. The post-its came out, and got distilled into three groups of problems we were trying to solve. These were turned into our sheet of requirements that we had to fulfil if the project was going to be a success. Some were technical, but most were user-driven: “I want to be able to quickly locate a code snippet within my database, and see it in context.”

Knowing roughly what we had to do, Alex and I got down to some prototyping of the more technically challenging bits of the system, integrating into Management Studio and being able to rapidly and efficiently search potentially large database schemas. Dom started putting the sketches we’d all come up with during the planning discussions into something firmer (a great relief, given the quality of my drawings at least), and Nagashree began planning the areas of testing we’d be focussing on.


Chapter 2 – Early Development (Tuesday)

With development underway, tasks were being added to our development backlog (one of the tables we’d bought, standing against a wall), being developed, and moving over to the testing area (nicknamed “Testlog”), and then finally to a “Done” area. Inspired loosely by Scrum, but with an absolute minimum of overhead, we knew exactly when the project was going to complete – Friday night – and rather just needed to make sure we got everything that needed to be done completed in the time.

The first milestones arrived soon after on Tuesday, with an initial algorithm for indexing schemas being completed at the same time as a the first draft of the UI, allowing results to be displayed. Crowded around Alex’s (three…) monitors, we were truly amazed by just how responsive it was – type-down search over AdventureWorks with no noticeable delay at all.


Chapter 3 – Later Development, Testing and Bug Fixing (Wednesday – Friday)

There was still plenty to do for the rest of the week – lots of UI polish, and making incremental updates to the indexes without having to read the whole schema every time you want to search, whilst allowing new objects to show up as soon as they’re added to the database. The latter took somewhat longer than we’d hoped, but nothing that a few late nights and more coffee couldn’t handle.

As we headed into the latter half of the week, we needed to make sure we were going to have something shippable by Friday. We opted for a simple solution: the backlog got a big line drawn across it, and everything that had to be in the product went above the line and things that were merely nice to have went below. We started at the top, and worked down. Sure, we thought of new features we’d like to put in throughout the whole week, but as long as they got added in the appropriate place on the backlog, that was fine.


Chapter 4 – The Final Push

By Friday afternoon, the area above the must ship line was looking reassuringly empty, but there were still a couple of critical bugs and testing remaining. Still, the end was in sight, and we were pretty confident we could make it and still get some sleep before Saturday morning.

At this point we decided to give those back at the office, who’d been watching the webcam and Twitter feed all week, a chance to sink their teeth into the product. Normally this results in a whole load of bugs we weren’t expecting, and no small number of outright crashes. The latter case certainly held true – David Atkinson managed an unhandled exception before he even got as far as searching, but generally feedback was very positive: “Really liking the tool guys – well done. It’s not coming off my machine now”; “I love the way that you index everything so the results are seemingly instant, almost working like a filter. Nice one.”

A quick trip to Tesco to pick up some Champagne for later, the last bugs closed, off, and we were ready for a toast. Sadly the joys of Windows Vista meant that by this time it had frozen slightly, so it was more an alcoholic celebratory slush-puppie (apparently better known as a Slurpee or Icee for you guys in the US!), but it was well-received nonetheless.

All that was left was to pack everything up, ready for an early start on Saturday morning, and head back to normality.


Chapter 5 – The Sequel

So, back in the office, lots of people asked how the week went. From the team’s point of view, it was a huge success – we achieved what we set out to, in the time available, and we still don’t want to kill each other despite spending a week together.

Make no mistake – it wasn’t a 40-hour week, and it wasn’t something I could do again this week without a break, but it really was quite outstanding. Weeks and projects like this are what, for me, software development is about: taking a real problem, analysing it, solving it, turning it into a product, and getting it out there. No extraneous meetings. No messing around. No politics. (Not much sleep.)

As for the product, we’re hoping you can be the judge of that yourselves – we’ve got a bug hunt this afternoon, and then we’ll be doing a private beta release, and hopefully a public release shortly afterwards assuming all goes well.

Could you develop all products like this? Probably not. Some are just too large to get anything meaningful done in a week. Could a lot of new products benefit from this kind of approach? Absolutely – as a way of kick-starting a new team, getting everyone really involved in the project, getting some early technical investigation done, or a first EAP out the door, I think this is an amazing kind of opportunity. It probably can’t be done too often, or it won’t have the special feeling that means you don’t mind coding late into the night in pursuit of a challenging goal, but once in a while, it can remind you just why you work in the software industry in the first place.

And so back to our toasts that we had on Friday night: to SQL Search, to Red Gate, and to Neil – thank you.