April 2012 - Posts

What to do when the solution will never be in front of you
25 April 12 10:46 PM | GavinPayneUK | with no comments

Sometimes you have a requirement which you don’t have a tool in your toolbox for.  When that happens the solution may not be to just get another tool and add it to your existing toolbox, it might be you have to use an entirely new type of toolbox with tools you’ve never used before in it.  Doing that maybe a bold architectural decision but is often where the biggest performance or functionality gains can come from.

Recently, in the SQL Server space there have been some technologies become available that showed how existing performance bottlenecks were removed by solving the problem in a completely new way to the previous successful but relatively temporary fixes.  Two examples we’ll consider are solid-state storage and Columnstore indexes.

10x faster can’t always come from 10x the power

Tonight I read the keynote presentation that Conor Cunningham gave at SQLBits recently about ColumnStore indexes (available here) but rather than read about how ColumnStore indexes work I read it from an architectural decision making perspective. 

Midway through the presentation is the line “Making something 10x faster is HARD”, followed by a slide questioning how could you make a query run 10x faster.  The answer wasn’t optimising the existing way of doing things, it was to implement a completely new way of working, in this case a new query optimiser mode and index format.  The existing options just couldn’t be tuned anymore to meet the requirement.

Game changing storage

The other example I quoted was the introduction of solid state storage in database servers.  Making storage fast was always a goal of infrastructure teams, they used different RAID levels, different caches and different numbers of disks until the industry realised that 5 and 10% gains weren’t always worth the effort or cost of implementing.  Instead, what we wanted was something that was 10000% faster and the answer wasn’t more disks, it was a totally different approach to persistently storing data, in this case using memory chips instead of spinning platters.


You’re probably never going to be asked to re-write a query optimiser or create a new type of storage hardware, however you maybe asked to keep making something go faster or scale further after having just done all you can do to make it faster.  It could be making reports run quicker or supporting more concurrent users.  My conclusion is that you shouldn’t just think about how to solve today’s problem using today’s options, think about a medium term option that may mean doing something radical but it won’t be a shock to you when you realise you need to do it.

Microsoft Certifications – a newcomer’s guide
23 April 12 03:01 PM | GavinPayneUK | with no comments

At the recent SQLBits X conference I was invited onto the panel during a session about Microsoft certifications and I said I’d write a follow-up article about some of what we discussed.  While one of the session’s aim was to start communicating the changes the SQL Server 2012 certifications are bringing, most of the questions were about Microsoft certifications in general, although there was a disproportionately high interest in the Microsoft Certified Master.  I’ll cover MCM topics in a separate post.

These are just my views and the advice I give people who ask me similar questions, they may differ from yours, they may not.

Which level of certification should you aim for?

For the SQL Server database engine, there are four levels of certification, the BI track will soon have a similar number, and at the same time all of the certification names will change but for now I’m going to use the SQL Server 2008 era titles and list them in increasing order of “difficulty”.  They are: Technical Specialist (MCTS), IT Professional (MCITPro), Certified Master (MCM) and Certified Architect (MCA). 

Which level is right for you depends on your personal goals.  If you have only just started using SQL Server or want to get confidence through external validation that what you know is accurate then the Technical Specialist is a good first goal.  In order to prepare for this you’ll probably need to broaden your knowledge to include some SQL Server features you haven’t used before, preparing you for when you do use them.  This is an important step as it shows how certification will drive your professional development as well as deliver your certification goals. 

If you take a Technical Specialist exam and get a good pass mark then you’re probably already on the way to achieving the IT Professional certification.  I’d recommend this level for anyone who uses SQL Server all day everyday and has done so for 1-2 years.  Passing this level will not only validate your knowledge, but also give you confidence to talk about SQL Server .  If you’re in a role that deals primarily with SQL Server then you’re going to have people asking you questions about it, you’re on the road to becoming your department’s subject matter expert. 

When it comes to the Microsoft Certified Master there’s only one person who can tell you if that level of certification is a suitable goal for you, and that’s you.  Actually, your manager might have a view as well especially if you work for a company that’s passionate about SQL Server.  If you look at the requirements that microsoft.com gives for Microsoft's highest level of technical certification and think “I can do that” or “I want to pass that” then you’ve just set your next goal.  It’s true that there’s probably a lot more thought that goes into aiming for this difficult and expensive certification but ultimately you have to have that “I want to pass” motivation in you otherwise the 6+ months of preparation will just be something you start tomorrow.  If you’re serious about wanting to pass the MCM then it has to become part of your everyday life.  I’ve met a few people who are “18 months into a 6 month revision plan”, they’ve just got side-tracked apparently.

The Microsoft Certified Architect is Microsoft’s highest level of certification for SQL Server and one which often gets overlooked.  Why?  I put it down to it not being a technical certification.  To me however its equally as important as any technical credentials you might have if you’re positioning yourself as one of the best in the world at deploying or architecting SQL Server solutions.  Being a Microsoft Certified Master is a pre-requisite for taking the MCA program which doesn’t have an exam.  Instead you have to submit a 40 page portfolio and attend a 6 hour Board in Seattle where you’re quizzed on design and deployment methodologies, communication and influencing styles, and risk and resource management amongst other things.  For me personally, its an exciting challenge.  After 2 years of tech-heavy MCTS, MCITPro and MCM preparation I need to balance my skills with the ability to walk into a blue-chip company and say “here’s the right SQL Server architecture for your business and technical requirements, and I’ll lead the implementation of it.”  That to me is powerful.  

Which version of certification should you choose?

Whenever a new major release of SQL Server appears people always starting asking whether they should stop revising for the previous version and start studying for the new version.  Again, that’s a question only you can answer as versions are only old if you start using something newer.  If you work in an environment where your software vendor’s support policy means you’ll never be able to upgrade from version x of SQL Server and you want to learn more about that version then there’s probably no benefit in you taking exams for a newer version that you’ll never use.  If however you want your knowledge to always be relevant and current then you’ll quickly get used to taking exams as soon as they get announced.  However, if you’ve taken that approach in the past make sure you maintain the real-world experience to back up text-book reading otherwise your success with the SQL Server 2012 exams and especially the MCM will be limited.

Strengths and Weaknesses

One of the perceptions newcomers to certification often worry about is that you need to be an expert and get every question in an exam right in order to pass.  If you look at the learning topics and begin to see un-familiar subjects, perhaps impersonation, XML or CLR, you might think you need to be as good at those areas as you are in your stronger areas.  The good news is that to my knowledge, no Microsoft exam requires you to get 100% to pass, in fact the published pass mark for most if not all is 70%.  I’m not suggesting you can ignore entire areas of the syllabus, but at the same time you don’t need to get every question right to pass.  If you go into an exam knowing more about indexing than required but less about XML than needed you might be lucky and pick up extra marks to balance out your overall score.  Of course, there are quite a few topics you' need to be familiar with the pass but knowing that its ok to get some questions wrong should help remove the fear that you need to be an expert in every part of the exam.  No one is an expert in everything, someone will always have a strength that makes up for a weakness elsewhere.

Just what comes in the box?
11 April 12 08:52 PM | GavinPayneUK | with no comments

As a SQL Server architect/consultant/advisor/advocate/you name it, one of my roles is to advise clients on the best solution for their requirements and their budget.  That last piece may seem like common sense but people often forget with SQL Server where the dividing line between the Standard and Enterprise editions sits. 

Knowing what comes in each edition, or more commonly what doesn’t come in Standard Edition especially in the area of high availability, is critical for me when I’m designing solutions for clients.  So, with the recent public release of SQL Server 2012 my hunt for the “official SKU breakdown” began.  There have been licensing datasheets released since late last year but they were too high level for me and the marketing terminology didn’t always translate into technical features. 

Fortunately, TechNet has a feature by edition breakdown well documented on a web page here.  Some of what the page shows you could guess, such as AlwaysOn Availability Groups being Enterprise Edition but 2-node FCIs remaining in Standard, however there were lots of surprises, caveats and footnotes which caught my eye.

I’ve yet to test some of the exact scenarios the web page described, but here is a list of the licensing breakdown which caught my eye:

  • Database mail, SSDT, Intellisense, and version control support are not available with the 64-bit version of Standard edition
  • There are a lot of advanced SSIS features and adapters only in the Enterprise edition and not in the BI edition
  • Database mirroring is still listed as being supported in Standard and BI editions, just deprecated
  • Database Tuning Advisor has limited functionality in Standard Edition
  • The BI and Standard editions are limited to lesser of 4 Sockets or 16 cores
  • Standard edition also has a “Maximum Compute Capacity” limit, explained in a complex way here
  • The database engine in BI edition is limited to 64GB of memory but SSAS and SSRS have no memory limitation

I don’t intend to review each observation, instead make a general observation that if you’re looking in Microsoft’s words to deploy more than “non-mission critical” workloads on Standard or BI editions you should check the SKU breakdown first.

Community Event Leader Tools
03 April 12 05:49 PM | GavinPayneUK | with no comments

As many of you know, I run a small SQL Server community event here in the UK, SQL Server in the Evening, with the help of Coeo colleague and MVP Justin Langford.  There’s been half a dozen evening events in the last 16 months and recently it got to the point where I needed to start putting proper tools in place to communicate with my event’s followers.  As well as telling them when the next event is it’d also be nice to share some of the Chapter Leader mails I get from Pass etc.


Chaining Together Disparate Services

What amazed me was not only how many of the essential services I needed were available for free but also how they integrated with each other.  I’ll now talk you through what I used and how it connected.


Web Site

I have a really really simple web site for the event (link) which just lists information about the next event.  I host this using a free blog provided by Wordpress (link) but rather than creating blog posts, I just have a single “page” which I edit with each event’s details.  Wordpress have a content management system so I can create and edit the page in a web browser, no need to upload files like a traditional web site.  I should one day make something more of the web site but I’ll save that for the future. 


Domain Name

One of the nice things Wordpress also do is sell and host domain names, before integrating them seamlessly with a blog site.  For about $20 a year I have my domain name (sqlserverintheevening.com) linked to my free blog site and hosted by Wordpress for me.  That may not sound that impressive today but setting all of this up took about 5 minutes and was done through a couple of simple web pages. Use of the domain name isn’t just limited to the web site though, its also used by email.



Wordpress have a setup web page in place that will do all the domain name configuration needed to accept email for your domain.  What’s even better is that they a step by step guide for configuring a Google mailbox to send and receive using your domain name.  This is a pretty complicated process but Wordpress’s and Google’s configuration pages together make it a 5 minute process.  5 minutes later I had a Google Mail mailbox setup to send and receive email for sqlserverintheevening.com.


Event Registration

I’ve used EventBrite (link) to meet my venue’s requirements of providing a list of who’s coming to each event.  This is a very slick web site which allows you to customise your event listing, the information you require, and more importantly store and provide the lists of who’s attending each of your events.   


Email Mailing Lists

Finally, after the event I want to email those who attended and tell them about the next event.  For this I use Mail Chimp (link) and their free service is good enough for almost everyone I’d imagine.  The web site does everything you need to create, manage and report on an email “campaign” as they call it and most importantly, people can un-subscribe without my involvement from the list. 

The best part about Mail Chimp for me though was it can integrate into my EventBrite events and extract the names and email addresses of everyone who’s attended one of my previous events.  That was really straight forward and took about 30 seconds, priceless.  I also love being able to see how many emails bounced, were opened and who clicked the web link to the registration site.


The Best Part

There were two best parts for me, firstly all of the services apart from the domain name are free.  The vendors sell premium services but the free versions are more than good enough for my SQL Server community event.  Secondly, setting everything up and sending my first email to everyone took about an hour, and I never saw an error message or have to resort to reading the help pages.  It all just worked.


Finally, I should clarify that for everyone who’s been emailed by me I’ve got multiple ways for them to opt out and to never get an email from me again, you should always do that too.  Mail Chimp is almost too helpful in making sure you don’t spam people.

How many per-core licenses do I need?
01 April 12 09:44 PM | GavinPayneUK | with no comments


With SQL Server 2012, your licensing requirements can choose to use or be required to use a per-core model depending on the edition you’re deploying. This is a change to previous editions which used a per-CPU socket model that made counting how many per-CPU licences you needed easier – cores and HyperThreading didn’t influence the CPU socket count.  Any complications which people did have typically came from running SQL Server in a virtualised environment, was a vCPU a socket or did licensing related to the host’s CPU configuration etc.

SQL Server now shares with us for the first time in 2012 its understanding of the server’s CPU configuration on start-up.  In the first few entries in the errorlog you’ll see an entry like these:

SQL Server detected 1 sockets with 4 cores per socket and 4 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

This doesn’t replace the need for accurate licensing purchases based on the hardware architecture you’re deploying but it should at least help you understand what SQL Server’s view of the hardware looks like.