SQL/Server Health Checks

Recently I’ve been asked to develop a set of scripts, plans and reports packaged up as a general server health check, but also with a specific focus on ERP servers, by which we’re interpreting as the Application and Database servers (Microsoft SQL in this case).

The thing is, if you Google “Server Healthcheck” or “SQL Performance Analysis” and everything in between, you will find a large array of sites out there specifically designed for these purposes. Therefore, I am now interperting my task as “analyse the tools already available and package up a selection of the best to add value to our customers”.

As thie self proclaimed king of scripting I have already started my work on a selection of scripts to analyse and build reports on various areas of systems, from the OS level all the way through to the DB contents where required. As always I welcome feedback on things I could be using on this project.

To begin with I’m focussing on the SQL analysis, T-SQL is still fairly new to me, so I’d rather reuse what’s already out there for example Brent Ozar’s SQL Health Check (https://www.brentozar.com/archive/2017/10/free-sql-server-health-check/). I will use the intial out of the box analysis, running it across a variety of systems to see where the standard baselines need adjusting (paramaterization and parallelism anyone?) and then build my own rules and descriptions to better benefit our specific needs. Sounds fun right?

Watch this space as I develop the scripts and reports, the eventual end game would be to run 1 executable/script with a set of predefined constants (server names, user credentials etc) and have a close to complete report out the end of it. If you do know of any sites or tools out there that can help me complete this then please do get in touch @jaward916

 

This and That

Over the last few month I’ve tried to expand my horizons a little bit. Since 2009 I have worked in a few different technical roles, from helping to run data centres, and setup environments for ISV engagements at IBM, to running all systems for a rapidly growing Oracle partner, whilst on the side managing 100 websites including e-commerce sites. That led into my quick stint doing tech support in the Automotive sector before moving into customer facing roles in Jan 2016. Since then I’ve always been running on a few different threads, these have been, loosely:

  • Installs/Config for ERP systems including initial system design
  • Technical training of customers in those ERP systems
  • Technical management of escalated issues (across the world)
  • Cross-team liason for high profile or highly escalated customers
  • Coordination of international team of installations consultants
  • Development of internal tooling for installs/ technical consulting
  • Management of environments for wider team

From my recent posts it’s obvious which areas on that list have received the most focus over the last few months, notably the last two, which is where all the DevOps/Code posts are centred around. The reason so much focus has been on this, and I’ll add at this point a lot of it out of work hours, is because it’s something I enjoy, something I’ve been on the edge of before, and an area of technology that I personally believe we should all be at least aware of, and able to understand the basic principles of.

DevOps was a term coined many years before it became mainstream. Mike Loukides wrote a 20 page book called “What is DevOps” back in June 2012, which is published by the world renowned O’Reilly Media. (http://shop.oreilly.com/product/0636920026822.do) That’s some time before I came across the term, although it seems I was already aware of some of the practices that now come under that umbrella. Back then I was managing E-Commerce sites, writing PHP websites against MySQL databases and moving a very static, cumbersome “tin-factory” infrastructure over to more dynamic, sustainable growth-capable platform. With a little more time and knowledge at that time I would’ve potentially moved in different directions. I am now starting to close that circle a little from the other side.

For me, career development is crucial, I am more than happy to stay with one company, or in one role, but I will always push to make more of myself, learn new things, get involved with everything possible and break down any and all barriers. I don’t do this to benefit myself, I see it as an opprtunity for me to be a benefit to those around me, both customers and colleagues.

Outside of DevOps activities over the recent months I’ve also been working on my presentation skills, with opportunities to present to colleagues and customers about various technical topics, including System Adminstration, upcoming product changes, best practices etc. This is in part due to being given more free reign with my current role, while we work out what my future roles may or may not include, and that’s if any change at all! In the background, the day to role keeps me busy, planning installs, speaking to new customers about how to deploy, speaking to existing customers about upgrades or enhancements to their systems, all the fun stuff that keeps money in the bank and roofs over heads!

The next few months may get a little busy, well hopefully they will, and all the good stuff will be posted when the chances arise.

#Code

First things first, #notadeveloper. I cannot stress this enough, I am not trained to write code, neither am I employed to do so. However, I do enjoy writing code, I find a lot of satisfaction in hitting the run button and watching something I wrote come alive. Previously I have posted many tweets and blog entries of my coding adventures over the years. My crowing achievement to date is probably the PHP/MySQL based “Asset Management” system, a glorified inventory list ability to Assign to a person, and add a list of repairs or reinstalls against the items. It automated a part of my job I disliked, and quite frankly that is exactly what I love about code. Almost all of the scripts I have written over the years have had the primary purpose of automating repetitive tasks any sysadmin can do with their eyes closed, mostly this has been silent install scripts and updaters.

Fast forward on a little from my sysadmin days, and to the brave new world (for me) of ERP. My primary day job is planning, coordinating and performing installations of ERP software into all sorts of manufacturing and distribution companies. Some are small, many are large, so the nature of, the deployments can vary slightly. That’s generally the bit I’m good at; sizing and planning the system to meet size and expectations of the end users. What we found over the last 2 years is that whilst deployments vary slightly, there is a bulk of work that is virtually the same every time round, certainly in process if not inputs, however we found that amongst the team; time, accuracy and experience could vary, significantly in some cases. Therefore a colleague of mine, with vastly more years experience in product and process went to the efforts to write an automation tool, a set of PowerShell scripts and XML files used to automate the bulk of the installation process. Roll on a few months and instantly accuracy and time were improving, which in turn was improving everyone’s experience. Gone were the days of random (user) errors and here are the days of productivity and valid errors which have much, much more context!

 

So let’s get techy on this and roll on a little further in time; following a few changes, ownership of the tool is now with me. And with a potentially different future ahead, it may only be a short term thing (it may also be long term!), so with this in mind, I sought help of people who know what they are doing, exceptionally smart developers in this case. After a couple of remote session the following has occurred:

Task 1 – Get the code secured. We can’t have something this crucial to our process hiding on a random VM with no backups.

Solution –  Git based code repository, in this case Visual Studio Team Services (VSTS)

 

Task 2 – Get the additional features into the code, but fully tested before deploying.

Solution – Branch off. Currently running with 2 branches, one for immediate fixes/quick additions, and one for next revision which will do far more than just  installing (Shhhh it’s Top Secret)

 

Task 3 – Get the code tidied up, to some form of best practices etc.

Solution – VSTS Build running PowerShell scripts with Pester and PowerShell Script Analyzer to validate all PowerShell scripts against a set of generally accepted best practice rules.

 

Task 4 – Packaging. No one wants to manually build a zip file, upload it to a SharePoint site and email out a notification for every small fix that goes in

Solution  – NuGet and Chocolatey via a VSTS Package Feed.

 

Since this became my problem, three versions of the tooling have been released, packaging only got tested this week so isn’t the primary deployment method yet, but now we have it as a capability there will be many more versions, but that just wont matter as they will always have whatever is the latest in the master branch!

 

Ok so that all explains my random tweets from evenings and weekends over the last month or so, fortunately I’ve had some incredible guidance from some very skilled and friendly development colleagues. Without those guys, I wouldn’t be anywhere with all this other than a whole load of files and folders on one machine with no backups!

 

I’d also like to give back to the community a little, so I plan to have some scripts that I write for more generic tasks uploaded to a public facing Git at https://github.com/jaward916 further to that I have below added a list of all the bookmarks I’ve been building up, especially the ones around Tasks 3 and 4, which has been the key functionality I’ve explored and implemented in the last week.

 

I stress once again, I am not a developer, please do not laugh at my code, or my very basic explanations of the tools and processes, I am learning for fun, but developing to make everyone’s lives a little easier in my world!

 

Bookmarks for VSTS

GDPARGGHH

At last, the 25th May 2018 has passed and a little known acronym of a European Directive has come into force.

After weeks of panic from companies, being forced into asking us if we would like to hear from them, and having to write paragraphs of privacy statements to outline exactly what it is they will be doing with our information, and of course reminding us of our rights to request immediate deletion of that data. We are now in the post-apocalyptic era, a new Europe, a new World.

So now we are in Europe 2.0, the U.S aren’t quite sure what to make of it all, and I’m sitting here with weather somewhat comparable to a nuclear winter, despite 3 record breaking weeks of sunshine, in a country where every other word is Brexit (which shouldn’t be a word, but some OED hippies added it anyway) and I’m reflecting upon that last few months of insane marketing ploys and inbox overload.

I get GDPR, I like what it stands for, philosophically, not the literal letters, those can sod off. But I am utterly fed up, like most people of how it has been handled. Whilst some of the more hip companies out there have bothered to embrace modern culture and throw in a pun or two along the way, the underhandedness of almost every business, big and small to use out inboxes as a way of confusing us over whether we are opting in to opting out, or opting out of contact, or opting in to our information being shared to 3rd parties and who knows what other combinations, the lack of consistent messaging has really messed us around.

Quite honestly, I am pretty sure the vast majority of us, who have never really read all the terms and conditions of software or understand the direct debit agreement in full legal context, have just inadvertently signed ourselves up to years of inbox terror and torment.

My advice going forwards, forget the inbox, speak to people instead.

Virtual Home Server Part 2

Back in November last year, I posted to this blog with the initial setup of my “home” server (https://www.wardnet.co.uk/virtual-home-server/) which in its former life was a Veeam backup server for an SME. This previous life means that it is a very high spec for its age and it may have a few TB of storage to boot! – Storage is an interesting point here as whilst there is around 8TB total (after RAID) it is not SSD or even high end SAS, therefore this space is at the cost of performance. The good thing here, is that performance is not critical to what I am using the servers for, there’s more than enough capacity and performance to deliver media via Plex for local and remote users and deliver reasonable DB performance for testing of installation processes – which currently relates to my day job. Below I have included an image of the VMWare ESXi Dashboard from today, not too much difference to the one I posted back in November other than the number of VMs has increased from 8 to 13 and therefore available storage has gone down by just around half a terabyte:

But that’s boring… what about the VM setup, anything exciting there?

Well let’s take a look:

Therefore, I have a lab domain setup now (wardnet.local) which I am using for testing out various ERP install/config scenarios, including down to the client layer with the last one in the list being a Windows 10 VM.

Four ERP servers I hear you scream, well, yes, rather… ERP2 is now solely an SQL server delivering the DBs for ERP3 and ERP4, with ERP1 being a self-contained SQL and App for the latest and greatest versions. APPS is actually a SharePoint 2013 Foundation server (DB is on ERP2) which is acting as document storage currently for the ERP servers.

I am also utilising my DC as a mail server with hMail and a .Net based webmail service (with mySQL backend) so I do not need a mail client anywhere! – My hMail implementation will be an extra post on here in the not too distant future I hope.

Extrasphere will be a blog post of its own TBC but it is a free cloning utility for ESXi implementations and works quite nicely.

In Summary, this server has allowed me to get my geek on with virtualisation, networking, server hardware and operating systems deployment from an infrastructure point of view, but also it has allowed me to delve back into the Sysadmin side of things, Domain creation, GPO deployments (Windows Updates, Shared Folders, BGInfo and more), mail server management, SharePoint admin etc. In addition, it has strengthened my expertise in the more recent transition into the application side of things, with many ERP deployment scenarios now tested (and scripted), as well as some real world simulations into config and usage of the ERP systems themselves. So now, I have a platform for testing anything, from Hardware tweaks through to Accounts Receivable invoicing!

Tip of the Week 5 – When sfc fails, DISM prevails

In the last month or so, I have come across a number of servers where the only possible cause remaining for the “issues” is file system/OS corruption. This has been across various scenarios; Cloud hosted, on premise physical, on site virtualised etc. and each time the obvious Googleable thing to try is a sfc /scannow.

For those who do not know this command, it is an old, old, old Windows built in tool designed to scan and repair corruption within Windows itself, notable directories such as System32. File system corruption can occur for many reasons, a dodgy build of the OS to start with, Windows Update issues, potentially caused even by viruses, or remnants of. So the cool thing about running sfc /scannow (from an elevated Command Prompt), is that it is has been around since Windows 98 – incidentally my first OS on a PC that was solely mine!

So sure, I have run this many times, probably more than a hundred, but recently on more advanced operating systems such as 2012R2 and 2016, I have seen it failing a little. Usually the result of the scan states

“Windows Resource Protection found corrupt files but was unable to fix some of them

Details are included in the CBS.Log windir\Logs\CBS\CBS.Log.”

So if you see this does it mean your system is totally broken…

 
 

All is not lost

 
 

If you have seen my previous posts on DISM you will know it is great for keeping a system tidy, especially when it comes to Windows Updates and the bits they leave behind. However, did you know that DISM could also be used to repair the file system? – Well no, neither did I until this year!

There are a couple of commands very useful to try, when a sfc fails:

  • DISM /Online /Cleanup-Image /CheckHealth

    This checks for corruption without attempting repair

  • DISM /Online /Cleanup-Image /ScanHealth

    This checks for Windows Image corruption and takes significantly longer than a CheckHealth

  • DISM /Online /Cleanup-Image /RestoreHealth

    BINGO! – this one will actually attempt the repair of a corrupted filesystem, and from experience means an sfc /scannnow will also complete without errors.

     
     

 
 

Please note I am not saying this is the solution to all file system/OS corruption, what I’m saying is in the last few weeks it has saved 3 servers from being binned!

 
 

So yet again, system maintenance via the DISM tool is a winner, whether keeping it tidy or just in one piece.