Reasons why DevOps implementations fail.

Over the last few years we have seen a monumental rise in the number of organisations adopting a DevOps working culture. This trend shows no signs of slowing down whatsoever and whilst many are now realising the benefits of these working practices many are also struggling with the adoption and in some cases it's either stopped completely or not even started.

There's a number of reasons of why this happens and I've seen some common causes, which is actually a good thing because we can recognise where these are occurring or even prevent them before they start to become a problem.

No clear direction.

It's very difficult to drive to a destination if you don't know where you're actually going (trust me on this, I've tried it). This is obviously very reasonable advice however many DevOps projects fail because of a lack of direction. It's actually a common issue with many buzzing trends, particularly in IT where organisations rush into a technology stack or movement just for the sake of doing it. Inevitably this often always leads to failure.

Organisations need to fully understand what a DevOps culture is, its objectives and its close relationships with their existing business processes and frameworks. A common misconception is people often viewing DevOps as a direct replacement for ITIL when in actual fact it's more of a cultural change built on top of ITIL principles. By fully understanding DevOps the benefits of adoption become much more viable and ultimately the path to it's success becomes much simpler.     

Adopting a silo approach to DevOps.

I often see individual teams being very successful in implementing components of DevOps practices only for other teams being behind in terms of adoption and/or understanding. The classic case is the Developer and DBA; the developer is pushing for much more frequent releases (a realised benefit of DevOps) but then the DBA, who perhaps isn't on board, is then trying their best to slow down all of these changes to their production databases. In the words of Bon Jovi, "we're half way there".

This lack of cohesion or a shared direction can result in a significant bottleneck and the DevOps practices start to creak a little. Then other unintended side effects start to creep in, such as blame and finger pointing (some of the things that a healthy DevOps culture seeks to eliminate) and then it can all start to fall apart. 

DevOps for databases is one particular area that is so heavily reliant on lots of people in different roles working together in a collaborative manner. An organisation must identify this and start to encourage teams to engage and build with each other in the early phases of a DevOps implementation, but organisations also have to be very careful in how they seek to develop this collaborative culture...

Forced collaboration.

I believe collaboration underpins the entire DevOps methodology so it makes perfect sense for organisations to work towards developing much closer working relationships between teams however organisations can also over-formalise things, even making the activity seem very process-driven which often leads to much less buy in from individuals, even entire teams.

This causes obvious problems, not least the silo approach mentioned in the previous point, so organisations have to find the balance on being almost relaxed in how they let relationships build and at the same time provide a certain degree of steer. This isn't as easy as it sounds and it is certainly reliant on strong leadership. In my experience successful implementations have been led by those that enable positive change rather than those who try to dictate it.

Rushing into new tools.

New tools are awesome, fact and in a DevOps ecosystem there are so many to pick and choose from that each bring new ways of doing things and [hopefully] improving productivity. The advantages are great, without a doubt but often tools can be implemented way too early without a focus on the underlying processes. This can significantly reduce the effectiveness of what a particular toolset/platform is trying to achieve; a release management tool for example won't improve a change/release process if the process is fundamentally flawed.

The most successful DevOps implementations focus on people and process first, leveraging the strengths and principles of existing frameworks and building strong collaborative working practices. New tools are going to be an important factor of a system designed with DevOps principles in mind but they need to be leveraged correctly and for the right reasons.


These are some of the common pitfalls that I've seen occur during DevOps implementations, many of which are deeply rooted in the working culture, not the technologies. There is undoubtedly so much to gain by adopting the principles and often it requires organisations to step back and observe their working practices first and spend time exploring the benefits of working with DevOps.

In my next post I'll cover how to address some of these issues and offer some insights into the benefits of building collaborative relationships between data professionals.

The DBA and the Data Platform

I've been consulting now for well over a year, it's been a fantastic experience so far; I've had the pleasure of meeting and working with some truly incredible people and it's also been amazing to be helping organisations get the most from their SQL Server platforms.

The last 12 months have been extremely interesting considering the current technical shift towards a seemingly endless list of new platforms and capabilities. For many organisations this shift has fundamentally changed how they do things (or going to do things) and many are going through their own journey of digital transformation understanding these new technologies and how to leverage them to improve their existing processes.

Right at the centre is data, nowadays considered to be a companies greatest asset, so how we use data or to be more exact how we extract more value from it is a frequent aim of these transformation projects. As a result the data professional roles within an organisation have changed too and many teams and individuals are having to broaden their technical skill set to design, deliver and support these new capabilities.

There is no truer example of this than the role of DBA but yet the question still remains on which direction(s) they should be focusing their future on? By that I mean development; which skills should they be learning, what certification, what products, platforms etc? It's easy to see why it's confusing; there are lots and lots of new technologies available to us but that also means there is no consistent path for administrators to set out on. 

It was easier in the not so distant past; the DBA role tended to come in two distinct flavours; development or operational, but now the line between the two has became far less apparent and in many cases non-existent. The DBA role has without question become much more rounded containing core elements from both "sides" but it goes beyond that. Modern approaches to database administration have reduced the footprint of the operational side of the role so a more DevOps orientated DBA is an essential part of the roadmap, for organisations and individuals alike.

This shared roadmap is actually an integral component of an evolving data platform. I've worked with many different organisations at different stages of development but a key component in each has always been a collaborative working practice between data professionals and I'd say the success of a platform depends on it. Over the past year I've worked alongside many different teams; architects, DBA's, developers, release analysts, change and testing specialists, when working together they all have a great influence of the effectiveness of a data solution. 

Whilst it's fair to say organisations should be encouraging this type of working practice it is very much dependent on individuals to pursue and implement it. For DBA's this means engaging with other teams much more frequently, understanding their skills and processes but also adding their own expertise and skill set to produce a much more co-operative function. 

It might not fully answer this common question of what next for DBA's, there's a lot of factors involved, not least your personal career aspirations but it does show how when looking for your next phase of self development the paths that you need to take could be very close to your current role. 

Your skills as a DBA will always be of paramount importance towards availability, security and performance, but by widening your technical scope by working much more closely with those around you not only improves you as an data professional but also the overall effectiveness your data platform. The starting point is to seek out these opportunities; start building relationships, start knowledge sharing and developing new ideas and different ways of working. 

In many ways the data platform is a reflection of the people tasked with shaping it. If we choose not to broaden ourselves, learn new ideas then at some point we come to a standstill, especially in this rapidly evolving technical landscape. If that happens a data platform stops growing, not in terms of data quantity but most certainly in terms of both capability and perhaps most critical of all, value. 

This puts a big emphasis on self-development but we've always had that, it's nothing new. The key is to seek out those opportunities, start close and work with teams that you will probably already have a working relationship with. As these relationships grow so does the collaborative skill set and as a result business processes improve and technical solutions get faster and more productive.

This also cuts down reactive measures, less time fire fighting means more time delivering enhancements and of course, more learning. Any learning process shouldn't have a cut off point, the more we broaden our horizons the more the effective we become and in turn, our products, our services and our platforms keep on evolving.

This is the true nature of a technical platform and we as data professionals play such an important part not just in the support or development of them but towards shaping their growth, effectiveness and their value. Perhaps the key to all of this is realising the value of our own development alongside it.   

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.

Installing SQL Server on Ubuntu 16.04.2

This article is going to show you how it to set up a Linux/SQL Server test environment which will consist of a virtual machine using the Ubuntu Operating System with the latest CTP release of SQL Server 2017 running on it. 

The plan to make SQL Server available on Linux was announced way back in March 2016 and with the recent announcement of the SQL Server 2017 (and subsequent CTP releases) things certainly appear to be right on track for SQL and Linux. 

It's worth adding that in recent weeks I have started to see organisations really take up the idea and have spoken to a few people who are creating their own test boxes and started to think about how to use this combination. Not only that, they've also started to ask for people with the right technical knowledge too so perhaps if you are a DBA who hasn't had a bit of exposure to Linux then now is probably the right time to start! All in all though, it's an encouraging sign for Microsoft.

Anyway, before we get going I'm going to be using VMWare Workstation 12 Player to create the Ubuntu virtual machine, you can download the software from this link and use VM's for non-commercial use.

To start off I need to download the Ubuntu Operating System ISO, which is available from here where you will find the following two download options:

I went for the 16.04.2 LTS version and once downloading and the following message was displayed, which I had great delight sharing with my open-source buddies (this one is for you Adrian).

Once the download has finished I can open up VMWare Player and select the Create a New Virtual Machine option as shown in the image below:

From here I can choose how the Operating System will be installed; from a DVD in my machine, from an ISO (what we will select) or I can install an OS later. Here we can see that I have browsed to the downloaded ISO file and the install process has recognised that it's the Ubuntu 16.04.2 OS.

After clicking next some user details are required:

Which actually tells me off as user names can only be lowercase apparently so I fixed that and carried on to the next part where I need to specify a name for my new Virtual Machine:

Clicking next takes me to the disk capacity screen, I left the options as default so used a 20Gb max disk size and left the split virtual disk into multiple files option selected:

After clicking next we move on to create the virtual machine however before we click Finish and proceed with the create/install process I need to make a slight modification to the configuration of my VM.

The system requirements for running SQL Server on Ubuntu 16.04.2 contains the following 

Note

You need at least 3.25GB of memory to run SQL Server on Linux. For other system requirements, see System requirements for SQL Server on Linux.

On the create VM window the Memory is currently set to 1024 MB so by clicking the Customize Hardware button I can change the allocated memory to 4GB (4096 MB) as in the screenshot below:

I can then click Close as there are no more hardware configurations that I need to make and now I can click Finish and the install process will start; if prompted to install VMware Tools for Linux then go ahead and Download and Install.

Pretty neat install screen, you just don't see enough purple these days!

Once installed the virtual machine will reboot and Ubuntu will start. I get presented with a login screen where I need to enter the username and password that I specified during the install process and now I am ready to go!

Ubuntu!

Now I was following the initial guide that is available here but ran into an error at the very beginning when trying to import the CPG keys, wasn't a biggie as it meant I just didnt have the curl tool so I just had to run the following command first:

sudo apt install curl

Before I could successfully run the curl command:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -

This brought up a install progress type screen and because the Time Spent value was increasing I figured things were progressing...however after a few minutes I was given a connection refused error!!

A little bit of digging around soon led me to a solution, superuser mode which kind of reminded me of the run as administrator option in Windows a little bit.

To start superuser mode type the following:

sudo su

Then I was able to run the curl command once more for info here's a screenshot containing the connection error and the subsequent sudo su and completed curl command:

Now for the next step I need to register the mssql-server repository:

curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list |

sudo tee /etc/apt/sources.list.d/mssql-server.list

No errors, this is good and now I can also quit using superuser mode at this point:

exit

Now for the actual installation and to do this I need to run the following commands which will go ahead and install SQL Server:

sudo apt-get update sudo apt-get install -y mssql-server

Next step is to run the conf-setup to specify and confirm the administrator password for SQL Server:

sudo /opt/mssql/bin/mssql-conf setup

Success!

SQL Server is now installed but I can run a quick test to see if the service is running correctly by using the following command:

systemctl status mssql-server

Which brings up the following screen (with a reassuring green selection of text):

That's it, I now have a brand new test instance of Ubuntu with SQL Server running quite happily, for now at least! 

Why I became a SQL Consultant.

This post is kind of a follow up to my recent "How I became a..." article that I wrote about my early days of a DBA. You can read that post and plenty of others from database professions on this page provided by Kevin Hill. The posts are seriously good reads and although needless to say, I cannot recommend them enough!

This post though is centred on the biggest and most difficult decision that I have ever had to make about my career and that is to quit working as an employee and become a consultant. I've been wanting to write this post for some time and because lately I seem to be getting asked at least half a dozen times a day why I made this decision now seems an ideal time to get it out there.

Firstly I need to paint a bit of a picture of my situation prior to making this change. I was in a great place; brilliant role, fantastic manager (you're welcome), using lots and lots of technology, pretty much everything you could want. Which of course does beg the question, why did I decide to move away from this?

Don't get me wrong, I'd had this sort of vague aspiration for a long time, largely because of the amazing people out there doing seriously great things already and whilst they were (and still are) a big influence on me it wasn't just a case of "hey I want to do that" and off I went. 

I did a lot of thinking about what I enjoyed the most from working with SQL Server and how I could take that forward. For me the most appealing thing has always been helping people get the most from their SQL Servers. As a DBA this can take many forms; it could be installing and configuring SQL Server as part of an entire enterprise solution, or it could be an upgrade to a newer version, performance tuning, bug fixing, implementing HA/DR, training, mentoring etc etc. The point being that whatever I was doing or whoever I was working with the motive was always the same, to make a particular SQL platform better in some shape or form.

Which is exactly how I think as a consultant.

In fact as a consultant I operate in a very similar way only now it's on a larger scale. I still have exactly the same motives as before but whereas in a permanent role I was working within a single instance, now my driver is to help as many organisations as I can with their data platforms. 

In many ways it's like scaling out a database (bear with me) but rather than distributing data I'm distributing my services and (hopefully) doing more work. Of course that comes with its own set of challenges which I will go into on another post.

Since I made the move to consultancy the #1 question I have been asked is why, hence this blog post and the answer is always the same. I've always loved working with SQL Server, with different people and different systems etc so becoming a consultant was a way to expand on that and reach out to even more people and help them achieve what they want with their data platforms.

If you'd like to know more about the consultancy and the services we provide then you can follow this link and by all means if you have any questions, feedback then by all means get in touch, be great to hear from you.

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.

Consistency checks on large databases

This is a bit of a follow up from my last post about torn pages in SQL Server. Within that post I mentioned the DBCC CHECKTABLE command which performs a consistency check against a specified table name.

It reminded me of a situation that I've certainly come across a few times where a databases size has meant it's outgrown the acceptable maintenance window and that resource intensive operations like consistency checks are now overlapping into production hours. This can seriously impact items like ETL processes so a clever approach that balances both consistency and resource contention is now required. 

One way to achieve this is to  split up the consistency checks covering smaller objects and native functionality allows us to do just that, we can perform the checks at the table level or indeed if they are implemented at the filegroup level too using the DBCC CHECKFILEGROUP command.

How to go about this is pretty straightforward; take the list of tables, split them into equal(ish) groups. The groups now form a pool of objects and within a nightly (or daily) window perform the check on each object in the pool. This effectively spreads a database consistency check over multiple days, you avoid the impact on production activities but also ensure all objects are checked over time.


DBCC CHECKTABLE ('dbo.ReallyImportantTable')
GO


DBCC CHECKTABLE ('dbo.AnotherReallyImportantTable')
GO

So the next question is how often should consistency checks be performed? For me the answer is simple; as often as possible. I've always suggested nightly checks where possible and if the database size means we can't do this then we start to break down the objects. Perhaps we have two pools of tables and on our first maintenance night we do pool one, night two we perform the checks on pool two and we repeat the process throughout the week. It could be 3 pools, even more - but at least the checks are being performed and are avoiding other processes.

The golden rule is to ensure database consistency and there are occasions that due to its intensive nature we have to find ways of avoiding impact on to production systems. So it's worth knowing that there are options available that make it more manageable, avoid the impact on these other processes and maintain confidence that your databases (or rather their objects) are consistent; and that is the most important thing.

Torn Pages and using DBCC PAGE

Today's post is about torn pages and using the undocumented DBCC PAGE command within SQL. Occasionally you might find you need to run this command and in this example it's because I have received an error message that looks a bit like this:

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0xa9aaaaaa). It occurred during a read of page (1:25756919) in database ID 9 at offset 0x000031209ee000 in file 'F:\DATA\ScratchDatabase.mdf'

This is an error that has been picked up on one of my test systems and indicates that SQL Server has detected a torn page, that is a page that has been incorrectly written by SQL Server and possibly indicates a problem in the IO subsystem.

The problem here is that whilst we know the database and the page where the error has occurred we don't know the specific table the page belongs and importantly what type of page is in error. The reason why the page type is important is because this will drastically impact our recovery process but the first thing we will do is check a system table to see if any other page errors have been reported:

SELECT * FROM msdb..suspect_pages

database_id	file_id	page_id	event_type	error_count	last_update_date
9	1	25756919	3	1	2017-01-29 08:24:05.760

Here we can see that only one row has been returned, the event_type refers to a torn page, here's the full type descriptions and a link for more information:

1 = An 823 error that causes a suspect page (such as a disk error) or an 824 error other than a bad checksum or a torn page (such as a bad page ID).

2 = Bad checksum.

3 = Torn page.

4 = Restored (page was restored after it was marked bad).

5 = Repaired (DBCC repaired the page).

7 = Deallocated by DBCC.

To identify the table in error we will use DBCC PAGE. This command takes a database id (or name), file id and page id and will return the actual page information back to the results window (we need to enable trace flag 3604 to do this though); for my example I would use the following command:

DBCC PAGE (9, 1, 25756919, 0)

This returns a lot of information to the results window (I can use the option WITH TABLERESULTS for a grid view) so I have removed a fair chunk of it and highlighted the two parts that I really need.

PAGE: (1:25756919)

 

PAGE HEADER:

Page @0x0000000728010000

m_pageId = (1:25756919)             m_headerVersion = 1                 m_type = 2

m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x104

m_objId (AllocUnitId.idObj) = 266296m_indexId (AllocUnitId.idInd) = 256

Metadata: AllocUnitId = 72057611489902592                               

Metadata: PartitionId = 72057607060652032                                Metadata: IndexId = 20

Metadata: ObjectId = 567673070      m_prevPage = (1:25756918)           m_nextPage = (1:38369560)

The first is Metadata: ObjectId = 567673070 which refers to the table that this page belongs to. The second is Metadata: IndexId = 20 which tells me as it isn't 0 (heap) or 1 (clustered index) that the page error is within a non-clustered index and I can use the following code to identify the actual index where the problem lies.

SELECT * FROM sys.indexes WHERE index_id = 20 AND object_id = 567673070

This returns the suspect index and to resolve we can very simply drop and recreate the index effectively reallocating the pages, suspect and all. This is potentially much quicker than performing a full restore and I have lost count of the times I have seen people resort to a full restore without checking the actual type of page corruption that they're attempting to resolve.


Another option here is that because we know the effected table we can run a DBCC CHECKTABLE command (rather than a DBCC CHECKDB on the entire database), passing in the full table name as a parameter; to get the name of the table we use our ObjectId again that was returned from DBCC PAGE with the following:

SELECT OBJECT_NAME (567673070)

This would return the table name that we can use to check the structure to see if our page issue has been resolved:

DBCC CHECKTABLE ('dbo.ETL_TestLoad) WITH NO_INFOMSGS

DBCC results for 'ETL_TestLoad'.
There are 341465 rows in 93513 pages for object "ETL_TestLoad".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Using DBCC CHECKTABLE in this scenario may have proven that our torn page has been resolved but you should always, without question, perform a full database consistency check to look for further errors. In the event of IO issues this could easily return more and more page problems, maybe irrecoverable data page ones at that. Any page read/write errors must result in both a full consistency check and a check with the storage people to see if they can see any underlying issues.


For some further reading check out my post: How to consistency check large databases