Looking back at 2014

As today is New Years eve I thought it would be a good chance to look back over the last year.

tangleAt the start of the year I finally got agreement for the installation of a 30MB leased line. This was an amazing achievement as I had been really struggling to keep everything working on our ADSL connections and the complexity of the connections was starting to cause problems itself.

However it was not a smooth process, the site survey revealed a substantial installation cost would be required, however the Council was about to launch a voucher scheme that would give us £3000 off. So most of this was just waiting around for the Council to get things going. By April we had it installed and I have been very happy with the ISP we chose, York Data Service (YDS).

Also during April this year was the end of support for Windows XP and Office 2003. This didn’t impact us too much, mostly due to my constant nagging of the directors to replace our oldest machines. We now only have a couple of machines still running XP and no machines running Office 2003. Most machines are now running Windows 7, I have one Director running Windows 8 for presentations and a few surveyor tablets running windows 8 as well. Like many businesses I am waiting for Windows 10 next year before doing any more substantial replacement of OS.

In May it was decided that I would move more into development work, so I got a new desktop PC and Visual Studio. I am very happy with my new machine (subject to dell fixing a problem later today) So far I have only dipped my toe into development work, this is largely due to needing more time to build up others to do what I do.

In June I made another huge achievement. I got agreement for a new server. This was our first Server 2012 server and allowed us to make huge strides towards virtualizing our services. Once we had this server up and running I was able to decommission two of our Windows Server 2003 machines well before it goes out of support next year.

I am a huge fan of this new server, I have only used a fraction of its memory. Hyper V is really great and I hope to make progress next year into HyperV replication.

Also during 2014 we made huge progress with our clouds software. Within the last few weeks it is running on the Azure platform which will not only make it far easier to expand and create new features it will provide us with cost savings. I am looking forward to learning more about Azure as I get deeper into development.

Wow I achieved a lot didn’t I, on top of this I hired a new IT person and trained him up (still more to do), I have also fixed lots of printers, websites, servers (had a server motherboard die!), and made hundreds of changes to our internal databases.

I don’t know what is in store for 2015. I expect a lots of the same with hopefully more focus on developing in Visual Studio, training myself and my department and getting the last few tweaks into our internal servers.

I would also like to thank everyone that I have worked with over the last 12 months (you know who you are), as you made a lot of what I have just described possible.

Happy Christmas

I saw this tweet on twitter.

He’s making a database,
He’s filtering twice
SELECT * FROM customers WHERE behaviour = Nice
Santa Clause is Coming to town.

This started me thinking surely in a normalized database structure behaviour wouldn’t be stored in the customer table, so I propose the following change.

He’s making a database,
He’s filtering twice
SELECT * FROM customers WHERE EXISTS (select * from behaviour where behaviour.CustomerId = customers.Id and behaviour.Type = Nice)
Santa Clause is Coming to town.

Happy Christmas everyone, hope you all have restful holidays.


Software Developer Quiz

Moving house, lack of internet and lack of inspiration has caused a lack of posts recently but hopefully more to come. Been doing some filing and found an old software developer quiz. Thought I would have a go.

Many thanks to Keith for originally writing the quiz.


  1. Write a function that determines if a string starts with an upper-case letter A-Z
  2. Write a function that determines the area of a circle given the radius
  3. Add up all the values in an array of integers
  4. Given a table called “Nodes” with the following structure and sample data (see below after questions):
    …where ID is the primary key,andParentID references ID, complete the following:

    1. write a stored procedure to return all nodes beneath a given node ID
    2. describe how you might write a query to return all nodes at any depth below a given node ID (i.e. recursively)
  5. Write a function to get the prime numbers up to 1,000,000
  6. You’ve been given the following code to review (below table)– what comments would you give back to the developer?
ID ParentID Name Type Depth
1 NULL My Documents Folder 0
2 1 My Pictures Folder 1
3 1 My CV Document 1
4 2 Photo of me Document 2

@NodeId INT


SELECT @ParentID = ParentID FROM Nodes where ID = @ID
SELECT @Name = ‘’
SELECT @Name = Name FROM Nodes WHERE ID = @ID

SELECT @Type = Type FROM Nodes WHERE ID = @ID
SELECT @Depth = Depth FROM Nodes WHERE ID = @ID

SELECT @ID, @ParentID, @Name, @Type, @Depth

My Answers:

  1. static bool GetUpper(string var)
    if (char.IsUpper(var[0]))
    return true;
    return false;
  2. static double AreaOfCircle(int radius)
    double area =0;
    area = Math.PI * radius * radius;
    return area;
  3. static int SumArray()
    int[] MyArray = new int[10] { 1, 2, 5, 12, 4, 9, 8, 18, 9, 6 };
    int Sum = MyArray.Sum();
    return Sum;
    1. create procedure getnodes
      @node int
      select * from dbo.nodes where parentid = @node
    2. For a recursive query I would write something along the lines of:
      select * from dbo.nodes n1
      join dbo.nodes n2 on n1.id = n2.ParentId
      join dbo.nodes n3 on n2.id = n3.ParentId
      where n1.parentid = 4544054

      But it would need to be customised depending on the depth, eg more joins for higher depths
  4. static void prime()
    Console.WriteLine("Prime: 1");
    for (long i = 3; i <= 1000000;i++ )
    bool isprime = true;
    for (long j = 2; j <i; j++)
    isprime = false;
    if (isprime)
    Console.WriteLine("Prime: "+i);
  5. No Brackets around parameters, @NodeId parameter never used, select @id = id from dbo.nodes where id = @id is pointless as same id that is passed it being set, Name = NULL should be Name is NULL, no from specified in last query. There are probably more issues as well.

In the Zone

On Friday night I had the urge to fire up Visual Studio and tomorrow (Monday) I move house.

What’s with that? Why am I in a programming mood when I have boxes to pack and shelves to take apart?

For the last few weeks I have been too busy with other things to do much programming or when I had the time I felt more like relaxing than learning anything new.

How do I get into the programming zone, what was it about this particular weekend that made me want to? Was it just the fact I had no time to concentrate on it, or is there some more useful factor that controls my desire to code?

I can think of a few factors that I should make note of.

  1. Peace and quiet – Friday night I had the place to myself so I could concentrate and do whatever I wanted.
  2. Ideas – Before I started I had some ideas, why don’t I try doing x. Once I have a coding idea I need to investigate it and see where it goes.
  3. Break from routine – Friday night was the start of a whole week away from work, this gives me more time to forget about the daily stress of the office and think about other things.
  4. Goals – Work annoyed me on Friday and if I want to achieve my goal of working in development more then I need to work at it.

I am not sure how I can put these into practise so that I spend more time on development but we will see. One thing I want to achieve in my new home is a quiet place to concentrate and think without distractions, I have a few options which I will investigate over the next few weeks.

Anyway need to get back to packing boxes.

If you had £5000 to spend on technology what would you get?

I listen to the dot net rocks podcast and they ask this question every show. (If you don’t listen then you should!)

I don’t know what my answer would be as there are so many gadgets and cool things that I like the sound of.

I might get a surface pro 3, it’s a tablet with attachable keyboard that’s more powerful than my current laptop. But at £600 it’s expensive so maybe not. Maybe instead I would install a SSD into my existing laptop to increase disk performance.

I’d like a new car with lots of gadgets built-in. A friend gave me a ride in his new BMW and it’s nice. Bluetooth so I don’t have to have an audio cable to listen to podcasts. Parking sensors so no chance the wife will prang it. Built in satnav etc

google-carWhile we are on the subject of cars eventually I’d like a google self driving car as I think Google is a better driver than me. Think about it I drive to a friends I have a drink I sleep in the back seat as my car drives me home. Once all cars are automated I believe the roads will be much safer.

The last thing I would spend on is an intensive training course on development, I would really like to get further in dev and more training would really help. Specifically something that would help solve my current dev problems. I am sure it is possible for me to learn dev with self-study but that requires a discipline I don’t think I have, I easily eat my time up with my current job, wife and other interests.

I recently had £6500 to spend on improving my employers IT infrastructure. That was really hard. It sounds like a lot of money, but it really isn’t.

Once I started looking at the problems that I wanted to solve and what hardware I wanted I quickly used it all up and that was before I factored in license and software costs. In the end I just had to order what I wanted and hope that on a future date I could spend on other areas. I got a super powerful server that I could virtualize almost all our existing physical servers with it I needed licences, which left me just enough to get a NAS for file storage.

Interview questions

I have done a few interviews from both sides of the table, I’m not very good at either but thought I would have a go at answering my favourite questions.


Describe an IT disaster and what you did to turn it around?

The question is all about the turn it around bit. I’ve had lots of answers that emphasise the problem rather than what the candidate did to turn things around.

My answer would be: on a Saturday I was rearranging the server room and when I came to turn on our main file server and pdc it wouldn’t boot. My plans for the weekend went out the window it was all about getting this server back up. I tried the usual unplug everything and reconnect still nothing. I rang a friend to get a second opinion and between the two of us we formulated a plan of action. I then rang my director to tell her that I was having problems and what I was going to try. (keeping people informed is an essential skill)

So the server in question was getting power but nothing was happening during boot. The motherboard had died. What I needed to do was connect the raid card to another server so I could copy the data from it to our Nas drive (luckily I had an upgrade plan and this failure had just accelerated it)

A few hours later the data was copying and I could breathe again. I told my director that I had fixed things but there would be minor issues on Monday.

Describe your strengths and weaknesses? 

It wouldn’t be an interview without a strengths and weaknesses question. As an interviewer I want at least one weakness and I want it to actually be a weakness.

My strengths are my problem solving skills, I can look at a problem and investigate what is going on and find a solution. If its a technology I haven’t used before I can read up about it and find out how it works and then use it to solve the issue.

My weakness is my interpersonal skills, I am much better at analysing a computer issue than figuring out why one member of my staff is not performing. This is an area that is improving over the last few years I have taken on more responsibility over people and I am learning more about getting the best out of them.

Technical Questions

I have never included technical questions in one of my interviews until recently and I think it is a good way to gauge ability. I often come out of an interview unsure how well that person would perform under the stresses of my job. If I have something down on paper it is a good start.

But the personality that comes through during the interview must also be considered as most knowledge can be taught.

A good technical question I have used in the past is write a paragraph explaining DHCP. This question illustrates what their knowledge is like for an essential technology, but it also indicates what their writing style is like. Could the passage they have written be given to a Director or Client?

Good IT people need both technical knowledge and the ability to communicate at all levels, I still struggle to know if someone is good or not and even sometimes if I am any good.

To boldly go where no SQL has gone before Part 2

Let’s continue looking at a database schema for storing details of every Star Trek Episode. If you are new to databases, a schema is just the design of the structure of the database.

We have three tables, Episode, EpisodeWriter and Writer. See my last post for more details of these. It has been suggested that a slight change to this structure would enable storage of more of the creative staff.

Lets rename Writer and call it Credit, and rename EpisodeWriter and call it EpisodeCredit. Now any creative staff member involved in an episode can be stored in the Credit table. Lets alter EpisodeCredit and add an extra column called CreditType. CreditType is just a text field that stores the role that creative person had on that episode, it can be anything from Director, Writer, Actor, Science Consultant etc.

In case anyone wants to recreate the databases I have described here, I have saved the SQL on a separate page which can be found here.

We now have the ability to store information relating to the episode in the episode table and any creative people in the credit table. What else can we add to the database? How about a table that can be used to record when an episode was last watched. I am probably weird but sometimes I want to watch a Star Trek episode that I like but I haven’t watched in ages.

The last watched table is really simple and just have a datetime field and episodeId. This can be further expanded to have a userId field if you wanted to keep track of what episodes your friends had been watching.

Another idea could be to tag episodes with certain themes or topics like Klingon episodes or meaning of life episodes or Kirk talks a computer to death episodes. Again this is fairly simple table containing TagName and EpisodeId.


To boldly go where no SQL has gone before

My last post proved quite popular so I am wondering if I can combine a post about IT and Star Trek.

Years ago I used to have lists of Star Trek episodes, which included such information like original air date, production number, episode title and brief description.

One thing that was hard to keep track of was how many episodes were written by a specific person. This is because episodes are written by multiple people. A column called writer would then need to contain multiple people, another option would be to have columns called writer1, writer2 etc. This wouldn’t help either as you wouldn’t know which column a specific writer had been saved in.

manytomanyThe relationship between writer and episode is known as a many to many relationship. An episode can have many writers and a writer can have written many episodes. To achieve this structure in a SQL database you will need three database tables as it is not possible to create a many to many join between two tables. The first table will contain all the episodes, the second table will contain all the writers, the third table known as a junction table, will contain the relationship between the two.

Let’s do an example so we can see how this would work. Gene Roddenberry creator of Star Trek wrote the pilot episode ‘The Cage’. So Gene would be added to the writers table with an id of 1 and The Cage would be added to the episode table with an id of 1. In the junction table, it has two columns episode and writer, so we would enter 1 and 1 into these columns.

Select * from Episode e
Join EpisodeWriter ew on e.id = ew.EpisodeId
Join Writer w on w.id = ew.WriterId

But if Gene Coon and Gene Roddenberry had writing credits on The Cage we would need to add Gene Coon to the writing table and add an extra entry to the episodewriter table.

I am going to do more posts based on this as I expand the database structure to include other information, I may go on to create stored procedures for bringing back certain information or I may use this as an example to talk about coding a user interface.