January 2011 - Posts

Technology Insights
29 January 11 10:16 PM | GavinPayneUK | with no comments

In my day job I speak to both technical and business people about the subjects which matter to them the most, in depths of detail which please them the most.  This allows me to ensure the technical people work in a way that meets the client’s business goals.


Sadly, I sometimes meet technical people who due to time constraints or lack of opportunity rarely get to do anything but focus on the bits, bytes, parameters and switches which they need to manage in order to make the “machine do its stuff”.  I thought late last year then that by spending 30 minutes with a few slides I could hopefully share some of the business and technical community knowledge I have and give them a broader understanding of why and how people were using the products they spend their time managing.  Alas, my Technology Insight sessions were born.

I’ve done three of these sessions now covering SQL Server, virtualisation and Windows Azure and I aim to have SharePoint and PCI-DSS compliance covered in the next couple of months.  I invited everyone from the technical part of the business to the sessions which I informally hold in a lunchtime.  I’ve uploaded the slide deck I wrote for the SQL Server session to give an idea as to what I discussed (link). 

Why am I sharing this?  Because hopefully there might be people who read this blog who could do a similar thing within their company.  Perhaps you have a Windows sysadmin or junior developer who doesn’t appreciate the breadth of the current SQL Server product, or a firewall engineer who hasn’t had the chance to find out why everyone’s raving about virtualisation.

If there are people who might be able to do that then I encourage you to try it, don’t be put off when only 2 or 3 people turn up for the first few sessions, its a good opportunity for you to build your presenting skills as much as it is a chance to educate others.

The first SQL Server in the Evening event
22 January 11 10:02 PM | GavinPayneUK | with no comments

On Wednesday 19th January I held my first SQL Server in the Evening event at VMware’s UK headquarters in Frimley, Surrey and while the event opened my eyes as to the amount of work needed before and after the event it would not have been possible without four groups of people:

Those who attended – I think I counted around 18 of you, the event was for you so I’m glad you came and later said you’d all come again.

Those who hosted usVMware– we were given the nicest facilities you could have asked for.

Those who presented – I was joined by Clive Wenman from VMware and Matt Mould (twitter) from EMC Consulting.

Those who supported usCoeo (link) – who kindly paid for our mid-evening food and drink, but whose support also gave me that confidence boost that things were going well.


For those who have never been involved in organising such an event from scratch I’d estimate it took about 20 hours over the 6 weeks before the 3 hour event to organise it, and I’ve spent about 6 hours so far after the event following up with thank you mails and the feedback survey.  Every minute was worth it though and will be repeated.

Some of those hours made me nervous and think I’d have to cancel it, but it happened, everyone went home happy, and more importantly since the event I’ve had 5 people offer to present at the next event and 2 people offer to source other locations for future events.


Finally, for those who couldn’t attend, here is an overview of the evening’s presentations:

Using SQL Server with VMware, Clive Wenman from VMware showed us some best practices from a whitepaper about using SQL Server with VMware’s virtualisation products.  After that he told us about some of the new features in vSphere 4.1 such as network and storage I/O throttling, as well as about VMware’s CapacityIQ capacity manger tool.  Clive’s presentation is available for download here.

I then presented an introduction to SQL Server’s Transparent Data Encryption feature, along with a demo and some background on common encryption terminology.  For those previously un-familiar with the SQL Server feature I hopefully introduced the ability to protect our data from hard drive or backup tape theft using this SQL Server capability.  Link and scripts available here and here.

What does the cloud really mean for me? Presented by Matt Mould of EMC Consulting.  Matt demonstrated how as a strategic technology consultant he helps his clients understand how they can adopt cloud services, and how despite all the media attention the term cloud gets there’s still a lot of consideration to be made about when and where cloud services should be used.  If anyone was expecting a simple answer then I think we clearly left knowing that while cloud has a time and place we need to think deep about what those are for us individually.


See you at the next event, probably at the end of March.

What to do with DATETIMEOFFSET?
11 January 11 10:15 PM | GavinPayneUK | with no comments

Someone asked me today if the time zone of a specific instance of SQL Server could be changed to match the country which that instance served.

Some database products allow you to set this at an engine level which made me wonder if your data’s time fields “move” with the time zone setting of the database server instance?  If something was logged as happening at 9am Paris time it happened then, if I change my database server parameters did that event now happen at 9am New York time?  Perhaps relative timestamps are useful in some situations but how do we ensure that business transactions which only ever happen once and at a specific and static point in time get logged permanently?

I knew SQL Server gets its local and UTC times directly from the Windows operating system which means any recognition of time zone within a record’s time stamp needs to be identified and recorded at the application level.  SQL Server will always provide and handle dates independently of time zone deviations, which makes the data it hands globally portable.

Looking deeper into this subject topic I looked at how the new datetimeoffset datatype could help.  This stores the local time along with its offset from UTC and although having a datatype which stores that information in one value is new those datetime components aren’t as the four queries below show: (change your Windows time zone to something extreme to see the benefit during UK winter time!)

select 'My local date/time is ' + convert(varchar(20),GETDATE(),120)

select 'My UTC date/time is ' + convert(varchar(20),GETUTCDATE(),120)

select 'My time zone offset is ' + CONVERT(varchar(20),datediff(hh,GETUTCDATE(),GETDATE())) + ' hours'

select 'My sysdatetime as a datetimeoffset is ' + convert(varchar(20),datepart(tz,sysdatetimeoffset()) / 60)

I then thought about how to use the datetimeoffset datatype to store times which had some level of variance in their time zones, such as the following:

rowDateOffSet   rowLocalDate rowUTCDate rowOffset
--------------  ------------ ---------- ---------
12:20:27 -08:00 12:20:27     20:20:27   -8
12:20:27 -08:00 12:20:27     20:20:27   -8
15:20:45 -05:00 15:20:45     20:20:45   -5
15:20:45 -05:00 15:20:45     20:20:45   -5
20:20:59 +00:00 20:20:59     20:20:59   0
20:20:59 +00:00 20:20:59     20:20:59   0
09:21:20 +13:00 09:21:20     20:21:20   13
09:21:20 +13:00 09:21:20     20:21:20   13

The abbreviated for formatting data in the table could be part of the summary following an import of global sales data for example. 

If I wanted to look at just one country’s business hours trading data I’d probably query on rowLocalDate, if I wanted to look at global activity in a specific period I’d probably use rowUTCDate.  By knowing the local datetime, the UTC datetime and calculating the time zone difference at the point of capture I can use regular T-SQL functions to find or search on any combination of local time and UTC times, regardless of what the server’s local timezone setting is or where in the world the source data came from. 

The datetimeoffset column is a great way to visually see that the time in the left of the column isn’t UTC and what timezone it came from.  Where in my view SQL Server lacks functionality at the moment is to do much with that datetimeoffset column, like easily convert it back to a base UTC time.  I suspect there are a generation of .net apps which will directly consume and manipulate the datetimeoffset datatype, time will tell.

70-432 Done
10 January 11 06:15 PM | GavinPayneUK | with no comments

Today I took and passed my first Microsoft exam, 70-432, MCTS SQL Server 2008 DBA etc.  I’m going to be harsh on my success and say while it’s great to pass an exam someone with my level of experience should be focussing on the next harder exam rather than how great is was to pass an MCTS level exam.

Having written about my preparation for the exam I thought it would be good to share my experiences during the exam.

Before I start in depth I’ll make three key points:

  • No exam is easy, unless you could get 100% every time you took it its not easy.
  • Even if what I learned from my revision wasn’t needed in the exam I’m still glad I learnt the new areas I did.
  • What I find easy, you might find hard, and vice versa.


My first observation of the exam was that I over-prepared.  That’s not say I knew every answer, but the depth to which I was studying now feels far too deep if I was just out to pass the exam.  The exam focussed as much on what would you use to do requirement x as much as it did how would would use feature y.  On the way to the test centre I was reading web pages trying to remember how to enable snapshot isolation at a database level vs. read committed snapshot isolation at a transaction level using T-SQL.  Forgotten details like that were worrying me, they were in my text book and in my practice tests.

My second observation was that the practice tests which came with my MS Press book were far more detailed, difficult and complicated than the exam I took.  I still learnt lots from them but they certainly mislead me about the type of knowledge I needed to take into the exam with me.

Now I’m not going to share any details in public that aren’t on the Microsoft web site about the exam, but at a high level my third observation is about time.  The exam is billed as 3 hours, to say I was in and out in 30 minutes is probably wrong, it was probably more like 35.  When you see the questions you either know the answer, can work out the answer or take an educated guess.  My score wouldn’t have increased had I taken more time.  I was often sat in front of the screen thinking I should be taking more time over this but you reach a point where you’ve done all you can and still have time leftover.

My final point is about the subject content of the exam.  Some subject areas were covered by multiple questions, some by a couple of questions, most by none.  What’s good though is that the amount of subject areas I revised has broadened my knowledge of SQL Server.

Test Preparation Materials
09 January 11 08:30 PM | GavinPayneUK | with no comments

I wrote an article on my personal blog a few weeks ago about my preparation for my first Microsoft exam, 70-432.  (link)  Since then I’ve been reading and demo’ing all the relevant features of SQL Server in the hope that if I get questions on them I’ll be prepared.

I’ve learnt a few things in the last couple of weeks, some good, some bad which I’ll now share.

The first thing I found is that learning about how SQL Server works is fun and interesting, far better than spending an evening watching TV which I never watch anyway.  It’s not very often I take the time to look under the covers and see how things I’m reasonably familiar with work so its been good for that reason, as well as seeing how capable some of the new features in SQL Server are.  As an example, I wouldn’t normally need to know much about how Change Data Capture actually works, only what it does so I can advise people about it, but reading up on that made me impressed with what Microsoft ship in the box these days.

The second thing I learnt is that its very difficult to know exactly what’s in the exam, to what depth you need to know it and how you’re likely to be tested in the exam on that subject.  Microsoft’s learning objectives help and so does the content of some of the textbooks but it can be very difficult to know if you’re revised enough, too much, or somewhere in the middle.


The third thing I learnt is that there are some pretty shocking learning materials out there.  I started off with a training book for the exam which had some things in it which I knew were clearly wrong meaning I had no confidence in the rest of it.  However, that wasn’t the worst thing, what shocked me most was when I thought for $39 I’d buy the TestKing practice test.  Wow.  It can’t have been proof read by anyone meaning the poor English made its educational value almost zero.  There are some questions in it which made sense but I spent more time working out what the question really meant, rather than what the answer should be.  A lot of the time the only value I could find for it was to use the known answer to work out what the question actually meant.  The best line I found was “You need to make sure why the SQL Server Agent service got lost.”  Did it really mean where should you look to see why the SQL Server Agent stopped?


We shall see when I take the exam if SQL Server Agent really can get lost.