Thursday, 18 May 2017

Installing Python and using the new command line T-SQL script generator

Earlier today two new command line tools were announced for SQL Server, one an experimental Linux tools DBFS which enables access to live DMVs without using a UI like SSMS and secondly a tool that enables script generation of objects within SQL rather like the Generate SQL Scripts option in SSMS.

In this post I'm going to run through the installation of the script generator tool and provide a very quick demo. The reason I'm going through this is because in order to install the tool we need to use something called PIP. PIP is a package management system that enables us install and use packages written in Python. Yeah, Python again! 

So if you haven't already installed Python, because it isn't available by default in Windows you'll need to get it from here:

This will take you to the download screen and here you get two version options; 2.7.13 or 3.6.1, I went for the latter and there is a link that explains the differences between version 2 and version 3 of Python:

And this is the installation screen, before hitting the install option make sure the Add Python 3.6 to PATH is selected otherwise things won't be working as expected after install.

Nothing untoward in the actual installation and we get a successful message in a couple of minutes.

So now that Python is installed we need to install the mssql-scripter package and that is very straightforward and rather familiar to those using PowerShell I guess. We use the command: pip install mssql-scripter from a Windows command prompt ran as administrator.

The installation package goes and does its thing!

Again, after a couple of minutes we get a couple of done messages and returned to the command prompt, installation all good.

Once complete we can pass in a -h parameter to the mssql-scripter command and see a wide array of options that are available to us but in this demo I'll keep it simple and you can refer to the original link to see the different ways of using the tools.

To use the command I've typed out the following which will create a script (adventureworks.sql) in my working directory containing the DDL and DML commands for the objects in the AdventureWorks2014 database:

mssql-scripter -S localhost -d AdventureWorks2014  > ./adventureworks2014.sql

Worth noting that although mssql-scripter is brand new I am using this on a SQL Server 2014 instance. 

The script ran in just a few seconds which is very impressive and it produced the file as expected and whilst I'll not put the entire script content in this post here is a screenshot of the script opened inside SSMS.

Now I did work through some of the examples on the release article however I did run into unrecognized argument errors when using -schema-and-data which is shame as I did want to test that functionality. I'll await a comment back on that one and then perhaps run through some more scenarios of using the command.

Edit: I did parse the created script: 

Command(s) completed successfully.

New posts on SQL Shack

I've just posted a couple of articles on SQL Shack, the Apex SQL community website. The first post introduces Dynamic Management Objects and how we can use them to monitor SQL Server, it's an entry level read and introduces some T-SQL fundamentals along the way.

Then I've followed up that post with an article focusing on sys.dm_exec_requests. This is a very common DMV to use as it shows all active requests on a SQL Server and this article explains some of the key columns to use in monitoring/troubleshooting.

Here's a link to the first post; sessions and connections and the post contains a link to the next part of the series at the bottom of the page.

Hope you'll find them good reads and as always feedback is more than encouraged, love to know what you think.

Tuesday, 9 May 2017

A little bit more on how I became a...

My last article was a post for Kevin Hills brilliant collection "How I Became A" where data professionals share their experiences working in the world of SQL Server. I highly recommend having a browse, there are some great reads in there and advice too. Without a doubt you should get your own story on there, we all have a very different journey in one way or another and different experiences mean different viewpoints that can certainly help others - so get writing!

Writing my own post did mean that I spent some time spent reflecting on my early days as an accidental DBA and as a result of the post I received a couple of questions which I'll answer here.

The first was on certification and if I went on any courses and if I found getting certified useful. I didn't go on any courses and my training was done via a couple of books that I purchased and most definitely Books Online. I felt passing the exam was a great achievement sure and because I was just starting out it did definitely add some weight to my CV so it did help but it was the both the first and last SQL exam I took. 

Where it all began, kind of.

I mention this because since the early days my learning has been without examination. I've certainly spent a lot more time studying since using a huge wide range of different methods but never felt I had to have the certificate. However, I do feel we are in the midst of a huge technical shift in both SQL Server and the Data Platform as a whole and I think certification will prove very useful for all levels - interested to hear views on that.

I also got asked to describe the low points of being a DBA. It's safe to say every job has its ups and downs and database administration is no different. The incidents that stick in mind are those that tend to involve long nights, such as lengthy deployments or when disaster has struck! Disaster Recovery is a fundamental activity for any DBA and even though the DR plans are well practiced they're never much fun in reality.

The first major disaster I was involved in struck mid afternoon and took until 4AM the following morning for everything to be operational again. Then I was on the obligatory progress report at 7AM which meant zero sleep that night. It was a low at the time and it certainly wasn't the last late one but actually when everything came back online and the formalities were sorted it was quite a satisfying experience in retrospect.

I've enjoyed looking back at some of my favourite moments and thinking about how much I've enjoyed, no enjoying the journey. Whilst things are changing, undoubtedly, It really is a great time to be working in the Data Platform and I encourage everyone to use posts like Kevin's to share their journey too!

Sunday, 7 May 2017

How I became a...

I started out as an accidental DBA.

Accidental DBA’s are not uncommon. Most of us took on the role because nobody else was doing it, or wanted to for that matter. The thing about the accidental role is that a lot of us stick it out for the long ride and for me it ended up being the most fulfilling career “choice” I ever made.

That isn’t to say the journey was straightforward, the reality is that it was anything but! You see an accidental DBA doesn’t have a lot of backup (geddit?) and because in most cases a DBA role is formed when the organisation REALLY needs a DBA and then all of a sudden everyone seems to have a database problem! 

We do get there though and eventually we drop the "accidental" part. We move on from being a reluctant volunteer and find out that we start to enjoy the challenges and no matter what anyone says, we do seriously enjoy them! There is a transitional period and it’s during that time that we come to realise some very important lessons that we take on with us to the next phases of our career and these are what I wanted to go through in this post. I know…it’s taken a while to get there!

Lesson #1 – You don’t have to fix everything right now.

This really applies to those data professionals in a new role who are trying to take on board every single issue that is out there. 

Lesson 1 should really be titled prioritise because that’s exactly what you need to do. For a DBA matters like the organisations Disaster Recovery and High Availability plans come way before that report for Mr Bloggs in Marketing that used to take 1 minute to run but now takes 4. Once you’ve got the really serious stuff under wraps then you can look at other “issues” but during that time learn how to say no…politely of course.

Lesson #2 – You don’t have to know absolutely everything.

Eagerness can introduce pressures that you don’t need and even though it’s one of those darn cliché interview favourites how we handle questions that we don’t know the answer to is important. You see we all like to fix things and make things better, that’s we signed up for this (well some of us didn’t exactly sign up but you know what I mean).

Sometimes we find it difficult admitting that we don’t know the answer because of the impression that it might reflect badly on us, we might be frowned upon by our peers etc etc. The reality is, particularly with SQL Server, that it’s impossible to know everything and if someone is claiming to, they’re lying. So don’t be afraid of saying you’re not sure, it’s not your particular area of expertise, whatever, just don’t try to wing it as that never ends well!

Actually, there are some exceptions – when was the last full backup, can it be recovered etc etc. These are the sort of things that a DBA must know, so spend time working on the priorities from #1.

Lesson #3 – Develop a learning plan.

Learning on the job can be very difficult. As a DBA one minute you could be looking at a problem with replication (because replication) and the next could be a particularly nasty execution plan. This experience is a vital part of the learning process but having a learning plan is very important.

The method that I undertook was certification. Whilst passing the exam was great the structured approach to learning was a huge benefit. Not least because the course materials mapped perfectly to the priorities that I was working to in the day job. Yes there was a lot of discovery, the architecture of SQL Server, physical files those sort of things etc but the initial subject areas that were covered were dedicated to fundamental activities like installation, backup, recovery, high availability options. When creating a learning plan I always start with these same points dedicating time in each area to develop an understanding.

Lesson #4 – Go beyond the learning plan.

First I’m telling you to develop a plan and now I’m telling you to go beyond it?! Let me explain.

When I passed the first administration exam I had a good understanding of how to look after a SQL Server properly. That was fine and in all honesty it was enough to get by but SQL Server is an incredibly deep product. As an example, when I was studying the exam guide had a chapter dedicated to indexes, it went through the different types of indexes, why and how they are used, the structures etc it was a good introduction. Actually, you can get entire books dedicated to indexing and they still won't cover everything!

There's an awful lot under the surface and this is where you will use a vast array of resources to help you with learning. I remember buying the accidental DBA book from Redgate which certainly opened my eyes to things like wait statistics that I had barely covered before. For each of the waits that I read about I used websites, forums and blog posts to dig even deeper. I soon learnt that ASYNC_NETWORK_IO waits were nothing to do with the network (sorry guys!) and actually fixing those CXPACKET waits by setting MAXDOP to 1 wasn't a good idea.

This does mean investing a lot of your own time into learning all about SQL Server. You'll naturally find some areas that you like and some you don't, like replication. Eventually you'll find specialist areas as well and it's no coincidence that they tend to be the one's you enjoy working with the most.

Lesson #5 – Get a sandpit and play in it.

Practice makes almost perfect. I say almost because experience has taught me that what works on one instance doesn’t necessarily work on another. You’ll always bump into mishaps and the best place to bump into them is on an isolated test box and not the mission critical OLTP system where you’ve decided to see what really happens when you put a database into single user mode.

One quote always springs to mind; “if the first time you test the effectiveness of your disaster recovery plan is in a real life situation then the only thing you’re testing is the effectiveness of your CV”. The message is simple; test, test and test and one of the first things you should do at work is get a sandbox environment which you can scrap and rebuild at your leisure. 

The other great thing about testing is that you’ll often end up in a very different spot to where you first intended. I remember installing three additional named instances on my laptop to have a play at database mirroring solution that I had in mind and very quickly I started learning all about the perils resource contention!

Lesson #6 – Ask.

I would have laughed at this in my early days! Ask?! Ask who!? The problem with being an accidental DBA is that there aren’t any others and without someone else’s experience to rely on then who exactly can you turn to.

Well here’s the thing.

You’ve probably gathered by my novel sized article that we SQL folk like to talk SQL, a lot. It’s true, we love it and given any chance to talk about the ins and outs of parallelism for example we’ll gladly do so!

My main point here is that I’ve never met or spoke to any SQL Server person who doesn’t enjoy giving back and helping others (just take a look at #sqlhelp on Twitter as a great example!) So if you are stuck, need some advice or just want to know a little more about something then go ahead, reach out and ask! Remember there’s no such thing as a daft question and everyone will be more than happy to share their experience and expertise. I lost count a long time ago the amount of people who used their time to help me out with technical problems or advice on how to get from a to b. 

The final thing that I have to say is to enjoy it and get the most from the journey. There's lots to learn but there's also a wealth of resource and people there to help you along the way. Personally, with all the recent developments I really don’t think there has ever been a more exciting time to join the SQL Server platform so if you are intending on working as a data professional now's the time!