Friday, 17 November 2017

Interview advice for DBAs?

Earlier today I saw an interesting book being advertised on a social media channel I use regularly. The books subject matter was around interview questions for the DBA and had been gathered by a bunch of people who had interviewed at various organisations and presumably the book (because I haven't read it) is being aimed as the how-to-get-that-DBA-job manual.

I'm not going to single the book out because I hope that the author had genuine good intentions when writing it however having on been on both sides of the interview table I can honestly say that an interview guide for this type of role isn't actually going to help as much as having two particular things; relevant skills and experience.

For a technical I'm sorry to say that is all you need. If you've simply studied on the "popular" interview questions and their corresponding answers and you don't have any technical understanding then I'm afraid to say you'll be worked out in a matter of seconds by whoever is conducting the interview and it's probably going to end very quickly.

The other thing to bear in mind is that no interviews are ever the same. Some favour intensive technical tests whilst others can be very informal but in both cases they're designed to and will assess your actual abilities and no matter how hard you try, you cannot take any shortcuts, no matter how well they're advertised!

Sadly this particular book isn't on its own; there are quite literally hundreds of books, ebooks and web articles out there that quite frankly are setting you up to fail and it's wrong. So if you are looking for a new role in the SQL platform please avoid the temptation for taking the fast route, because it's not going to happen.

Now it's fair to say actually preparing for an interview is very different and there are certain things you can do to help your cause, it's pretty high-level advice though; make sure you can talk about (and back-up) your skills listed on your CV, give examples that relate to your previous and/or current job(s) and research the subject matter that you might not be as clued up on if the job description asks for it.

The last point is crucial. I don't mean look into the top 5 interview questions about Always On but if the job asks for it and you haven't had too much exposure then look into the technical guides that are out there and spin up some test scenarios. You can't know everything, the interviewer should be aware of that and a candidate who makes that sort of effort does stand out in an interview, that I can assure you.

So if you are looking for a role right now in the data platform don't forget there are also a wealth of people in the community that there to help just avoid anyone who is offering a clear short-cut that will only ending leading to a dead end.

Sunday, 12 November 2017

Automated Index Tuning

It was announced last week that automatic tuning will be enabled by default for Azure SQL database, coming into effect in January 2018. Clearly and the as the article states the technology has been through enough testing in the field to see that it is advantageous and as such this new way of thinking of our databases as being self tuning is a lot closer.

Now this of course adds further speculation to the whole future of the DBA debate but as I have said previously, these technical advancements are not to be viewed as a threat to the DBAs existence. 

After all, automation is a fundamental process to life as an administrator and has become even more important in recent times, but automation isn't without it's potential problems. Firstly we can very easily automate thing wrong processes where we simply gain nothing from doing so. We can over-complicate and run the risk of creating a new automated process that is actually even more hands on than the original.   

But another problem and one that I immediately thought of when I read the automatic tuning article was that being provided with an automated solution means that we can effectively substitute learning about a technology or functionality in favour of being provided with an out of box process that just does it.

This is a point that I often make with developers using ORM technology and in fact whilst delivering a training session recently we spoke a lot about pros and cons of the very same topic. Don't get me wrong, I think the technology is fantastic and without doubt very very useful but the technology does allow the creation of lots of database functionality without the developer actually having to understand database functionality (which is actually one its plus points). 

Having automated index tuning switched on strikes me as being very similar and I certainly can see the appeal, particularly for those who perhaps do not understand the sometimes very intricate processes involved in tuning indexes. Like any automated solution though there has to be some form of monitoring, something (or more to the point, someone) to ensure that the tuning options are correct.

The trick is that we then need to implement an automated way of testing and we can only do that if we truly understand what we're trying to achieve, how we're doing it and how to determine if we've been successful or not. With something as deep and finely balanced as indexing I can't envisage a time where we can enable an option that will just look after it and then forget about, it just can't happen like that. 

It will be good, hey it's probably going to be great but it needs to be treated as something to understand and build on and not just put a tick in a box. There will still be the need for tuning expertise it's just now we have a process to do some of the leg work for us.

As always; I'm really interested to hear your thoughts, view and opinions on this part of a very quickly evolving data platform.

Saturday, 23 September 2017

Microsoft Visual Studio Dev Essentials

The last article that I posted was about my thoughts on the future of the DBA role and the direction that it and many others are going. If you haven't read it then please give it a read as it's been really interesting to read other peoples views and opinions on this topic and of course, huge thank you to anyone that has taken the time to do so already.

The TL/DR version of the post is that whilst job roles will be changing to keep with all of the technical advancements going on around us this isn't necessarily something to be worried about and it's actually quite an exciting time for us with lots of new these avenues to explore.

That's all fine but how do we go about gaining these new skills and will it be cost-effective to do so? Keeping our skills up to date has been of paramount importance to IT professionals and traditionally it's been down to the individual to shell out for courses and training material just to stay constant. Now there has been a bit of shift in regards to training and thankfully it has swung very much in the favour of those seeking to learn the technologies that are now becomign more common place.. 

Behind this shift are the very same organisations advancing and pushing their platforms into the commercial spaces. The bottom line is that as well as offering these technical solutions they also need people to be able to both use and support them. The more people that can do that the more adoption rates increase and with pay-as-you-use services such as the cloud this is vital.

In a nutshell, this means that they're giving us lots of training, mainly for free!

I don't want to sound like a TV/radio advert and say things like "THIS OFFER WON'T BE HERE FOREVER" but there is a little bit of truth to this. Whilst there are skills shortages in areas such as the cloud platforms these really won't last forever, particularity with adoption rates on a such a steep upward curve. Whilst I'm sure any free training options won't disappear, it does make a lot of sense to get on board now.

One option that I would certainly recommend you go look at is Microsoft Visual Studio Dev Essentials. Although the same suggests is very development focused it's definitely been designed and put together for anyone working in Microsoft's Data Platform. 

There's a bunch of goodies to download such as Visual Studio (surprise, surprise!), Developer Editions of Microsoft R and SQL Server, plans for Office Online and Power BI and crucially a trial subscription for Microsoft Azure.

Then there's the training options:

Now the image is a little blurry (maybe there's some copy and paste courses for me?!) but this is what you get:

3 months of online training with Opsgility (Microsoft Azure training),
3 months of full access to Pluralsight (um, everything!) ,
2 month subscription to Linux Academy (makes sense with SQL 2017 etc),
3 month subscription to WintellectNOW (for developers) and the various courses offered by Microsoft's Virtual Academy.

That is a lot of free training material and when you factor in all the resource available already out there like tutorials, labs and of course the community contributed materials, all in all it makes for one superb learning platform.

Choice is great but I would also recommend pausing for just a second before you hit the activate button on the training modules! Before you do make sure you have a good look at what courses are on offer, what interests you and start to formulate a plan for your learning. It doesn't have to be a strict timetable but being smart upfront will avoid any waste, after all, if you activate each training option at once and you are already pushed for time then some bits will be missed, it's bound to happen (and that would be a shame).

It is worth mentioning for those wondering if it's similar to an MSDN subscription then yes, it's very similar to a cut down version, last time I looked MSDN offers some of the same but for double the subscription period so if you want a paid option, or your organisation will pay for one then it might be worth going down that route.

It's a good time for many reasons; SQL Server 2017 now has a generally availability date of the 2nd of October and with it's native support for Linux, languages like R and Python then as always training is going to be really important and right now there is a lot of material out there for us to start exploring new areas and that is exactly what organisations like Microsoft want (and need), and as such they're heavily supporting it.

It's a really important time to be involved in the data platform right now and with things changing very quickly it makes a lot of sense to be both keeping up with changes and learning more about them. I'll post again shortly and explain some of the areas that I am focusing on but for now, I highly recommend if you haven't already take the time to learn a bit more about Azure or Linux or whatever appeals to you to advance your career as a data professionals.

As always, really interested to hear others views.

Wednesday, 30 August 2017

The future of the DBA role.

For quite some time now there has been a lot of talk on the various social media platforms regarding the future of the DBA role and whether or not it still has a place in the not so distant future.

I've actually wrote this post a few times but it's always ended up being a very lengthy read of epic proportions so I've decided to hack it to bits and get straight to the point(s) and hopefully open it up for some more discussion because I think it's a very hot topic still and I'm really interested to hear peoples opinions on where the role is heading.

Lets get straight to the point; is there a place for the DBA role? The answer to that is most definitely yes, whilst databases exist there will always be the need for administration, but as core administrative tasks are being automated there will be less for DBA to do along these lines.

One example I hear of why the DBA will still be very important is performance tuning, after all in cloud platforms you are literally going to pay for poor performance but then with the likes of automated index management and the arrival of the adaptive query processing family in SQL 2017 we can see that the time we spend on tuning activities could well be shrinking as well.

This is really where the concern is coming from but perhaps this is the wrong way of looking at things. Instead of worrying about what we're going to be doing, or rather not doing, we should be looking at how the technical landscape is changing and looking at the opportunities that lay within it.

Now I'm not saying for that we should all become data scientists (and nobody else is by the way), data science is hard but it is a great example of an emerging area within the data platform that we may seek to explore for own careers, in fact there is no real reason why anyone shouldn't spend at least a bit of time familiarising themselves with the technology and its capabilities. This goes for a lot of functionality now present within SQL Server; it's native support for R and Python, the likes of Always On and In-Memory OLTP becoming more prominent and the rise and rise of PowerShell automation, we can even run on Linux now and of course there is that cloud thing that everyone is talking about.

All of these technologies are integral parts of the Microsoft's vision for an ever widening data platform and as organisations look to implement them and leverage their advantages it is the DBA that can be at the forefront of this technical transformation, if they want to be.

This for me is the real point. The changing technical landscape is only a threat to those unwilling to explore new areas and learn new skills and this certainly doesn't apply exclusively to DBA's, whatever your involvement in IT this technical shift effects you and to put it bluntly, you can either go with it, or be left well behind. 

For DBA's there could be some areas that are out of the comfort zone, perhaps the Dev/BI stacks or architecture but thankfully there is an abundance of training material out there which doesn't cost a small fortune or in some cases anything at all, not to mention all the support coming from within the technical communities. The decision really is yours how you'd like to advance.

Now it is fair to say that organisations won't be simply moving to a new platform overnight, after all how many companies are still on SQL 2005 for example (if you need upgrading, give me a call) but rather than sit back and worry about what might happen and even worse do nothing about it, it's time to start looking at how the emerging technologies can benefit not just the organisations that you work with but how they can benefit you as a data professional.

Sunday, 16 July 2017

A question on index and statistic columns.

This post is a question around how SQL Server creates statistics for a new index or in other words - how can the columns for an index and it's statistics be the opposite away round from one another?!

Here's what I originally found when having a poke around a database; it's a pretty basic clustered index (with names blanked out to protect the innocent).

We can see that the leading column is a varchar(20) type and the next one is varchar(50). Now let's have a look at the statistics for this particular index, just for info this is the only index on the table.

This time the the leading column is the varchar(50) which is then followed by the varchar(20) column, hmm. Now column order is pretty important and interestingly enough the varchar(50) column is actually the more selective of the two so I wondered if this why perhaps the statistics are in a different order. 

In order to test this I've used an old SQL 2014 test database that's been hanging around on a dev instance of mine. It has a very simple table composing of an ID field, first name and last name. Incidentally the first name and last name columns are varchar fields with lengths of 20 and 50.

Here's a new clustered index based on the first and last name columns:

After I have created it (yeah I know the name sucks btw) I'll check the statistics:

That seems fine, or at least the order is the same as the index, which we'd expect. 

Now lets recreate the index but modify the column order so it looks like this, with the last name now the leading column:

Now if I check the statistics...

They are in a different order to how I have just defined my index. 

Now originally I did wonder if the statistics had been manually altered, however just to rule that out if you try to change the columns of statistics in SSMS, you get the following:

Now of course this is with a clustered index, what happens if I try the same with a non clustered index?

Here is my new index where once again I have altered the column order. The statistics this time look like this:

Okaaay, this time the statistics reflect the column order of the non clustered index that I've just created. This makes (at least in this example), the statistic creation process different between a clustered and non-clustered index.

So the question, why the difference? Has SQL Server decided on the best column order for statistics for the clustered and non clustered indexes or has the creation process for the clustered index just not picked up on the column modification or does it even use another method when creating stats?

Monday, 19 June 2017


Quick reference post on the VIEW SERVER STATE permission within SQL Server. This is a server level permission that once granted enables a login to view the results of Dynamic Management Objects.

I find that it's typically used for troubleshooting or performance tuning related activities and is a good alternative to the good old sysadmin role membership route, especially for external people.

To demonstrate what the permission allows I'll first create a new login on a test instance with the following command:


Now I've logged into Management Studio with the credentials I've created above. So let's try to select records from a DMV, in this case my instances cumulative wait statistics:

SELECT * FROM sys.dm_os_wait_stats

I get the following error:

Msg 300, Level 14, State 1, Line 1
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

SQL Server has been quite specific on how to resolve the issue by stating that the VIEW SERVER STATE permission was denied. 

There are a couple of ways we can grant this permission, from the server properties > permissions window as in the image below. Remember that although the error message indicates the issue is on the master database it is a server level permission not a database one (such as view database state). 

Or we can use T-SQL syntax such as the following:


Now the results from the DMV are visible without error. 

This is a really useful way of restricting access for what could typically be viewed as an administrative task, however, one final word of caution though. This permission is applied at the server level and gives access to all of the Dynamic Management Objects and whilst in this particular case something like wait statistics might not be that sensitive the DMVs and DMFs do expose a lot of information so you have to bear this in mind when applying this level of permission.

Tuesday, 13 June 2017

Databases and DevOps

This is my post for T-SQL Tuesday #91 hosted this month by Grant Fritchey, the subject this time around is Databases and DevOps. For those who aren't aware what T-SQL Tuesday is it's essentially a monthly blog party where the host (Grant this time) will decide on a topic and fellow bloggers will write a related post on the subject; you can read more about it here.

My post is going to be rather high level (what's new I hear you say!) and that's because this is where I often see DevOps fail, people don't quite grasp the fundamental concepts and requirements to make it work, but to begin with, sing a long with me for a second: 

Now this is a story all about how
My life for flipped-turned upside down
And I'l like to take a minute
Just sit right there
and I'll tell you about how implementing DevOps sometimes fails. 

Okay, it doesn't rhyme, I stole the lyrics and I certainly can't rap (not without whisky anyway) but for some people the concept of DevOps does bring with it the idea of having their (working) life being flipped upside down. People get confused about what it all means and this can cause resistance, an unwillingness to look at what DevOps is trying to achieve and essentially hold on to their current way of working.

The main cause of this tends to be how people go about implementing DevOps. I've seen organisations sort of grasp at the concept, try to introduce it too quickly or even try to impose it and then, quite understandably it fails miserably each time. 

People (or indeed companies) tend to focus on the lower levels of DevOps or even try to get the benefits straight away, the "continuous this" and the "continuous that" when in actual fact they're not even starting at the right place, a case of crawl before you can walk if you like.

The phrase DevOps is the bringing together of two different terms; Development and Operations so to make a success of it we need to think along those exact same lines. That means we need to focus on two things; communication and collaboration.

Communication is easy right? After all everyone kind of talks to one another so what's the problem? Well look at the traditional relationship between Developer and the DBA (operations). Both have been working very different styles for many years now; developers are making constant changes, pushing out releases as often as possible whereas the DBA is trying to put the brakes on and keep the systems in a stable state. 

This often results in push backs and whilst they will certainly communicate, it might not necessarily be the right kind of communication and now we've got to try to get them to meet in the middle somehow and work in a very coupled fashion! 

Fundamentally what is needed is an understanding of each others role. For me this is the real starting point of DevOps and although in some cases this will mean the breaking down of walls in no way is it an impossible task. Introducing each others way of thinking without trying to abolish the existing mindset but rather have a purpose of helping one another is how this common approach should be formed, and taken advantage of. 

Side Note: I have noticed that this sounds a lot like couples therapy!? Is this the real meaning of DevOps - are we been healed somehow!?

Ultimately this mutual understanding results in much more solid foundation that can be used to then implement the lower levels of DevOps such as the different technical methodologies and tool sets.  

Some of the most successful DevOps cultures that I have seen are where teams contain developers that are ex database admins and vice versa - yeah it's true, people actually do this! In these cases people haven't just brought their technical skills over to a new team, they've brought their understanding of the other functions too and will often use that in a co-operative manner to find the best solution - essentially, isn't this what DevOps is all about?

Now I am not saying that anyone should start shuffling around their IT department because that's the last thing you want to be doing. You can't force or impose this concept, it needs to grow and to some degree let people find their own ways of understanding and working with one another. Whilst challenging yes, the process doesn't need to be threatening or overwhelming in any way and done correctly it won't feel like that. 

So to go back to the lyrics right at the beginning of this post, no it won't flip your life upside down we just all need to take a minute, sit right there, and learn from one another.