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

Few things to cover…

Firstly, thank you for stopping by, taking time to view my blog, read my posts and hopefully take something away from them.

If you do happen to like the posts on here, then please do say so; retweet, Facebook share, LinkedIn, whatever, it would be great to get more of my content out there, and more of you on here!

Also If you have any post requests or tech questions, please send those over too, Twitter is possibly the best for that @jaward916

Secondly, apologies for the lack of posts during February. Over the Christmas break I had some good ideas which I made lots of notes for then came up with the 4 posts during Januray, however the ideas have dried up (already) and family related things have meant less free weekends. The weekday’s are taken up with the job, typically Sundays are when I get “me” time to do some techy stuff for my benfit rather than for customers!

Finally…

Whilst I don’t have a nice full topic to write up for this week (I promise there are some ideas bouncing around in my head) what I do have is a snippet of 2018 so far in my world of tech/code/software etc.

During January I spent many hours getting to grips with a new major release of the software I work with, as I’m a little bit of a nerd, a lot of those hours were spent in my own time, drilling down into things, working scenarios out, deplyoment strategies etc. What I ended up with by last week was a full test scenario, remeniscient of a real world deployment. Effectivley emulating what a customer would have. The really cool thing is this allows me to very quickly test out scenarios, when a customer reports something “not working” I can run it through my servers and give them an answer same day along the lines of (usually) “try this, I think you’ve done X in the wrong place”. This is in no way a bypass to my wonderful colleagues in Support, but more of a way to assist the customer with getting their deployment up and running. I don’t generally delve into the applications, I’m not that kind of consultant. What I do is design deployments and implement them, I get the back end of a system up and running. The latest version included quite a few new technical enhancements, so getting experienced with them is an essential part of me being able to do my job!

February hasseen a few more interesting engagements for me, site visits all over the place (on top of delivering 2 training courses during January), with some more lined up, possibly even abroad.

What I am being asked to do now is anlyse, review, and in some cases redesign or reimplement deployments. Not because what they have was done wrong to start with, far from it, but more to help them become future-proof, employ best practices and become more agile as the world around us is changing, and the software adjusts to match. There’s no wheel reinvention, just a set of new tyres here and a bit of air there.

I write on here a lot about SQL Server as it is the underlying DB server platform for all systems I support. Another area of SQL that has always interested me is SSRS (SQL Server Reporting Services), basically a very smart, sometimes fiddly report generation toolset. What I have been able to do over the last few weeks is take some reports, rip them apart, analyse a few minor but irritating issues and develop solutoins to those problems. The strange thing is that I’ve not been trained in SSRS, or had the change do do anything with it prior ot this. I just saw an issue, delved straight into the SSRS builder and worked it out, for myself. I forgot I had those abillites and it’s been refreshing to remember how good I used to be solving new problems.

I’m thinking some SSRS tips in a post may be some decent content in the future, think I’ll build the scrapbook up on those!

 

That’s it for an update, I’ve also updated the About page on this site to reflect the last 2 years!

 

Tip of the Week 1 – IIS Application Pool Recycling

This guide was designed for use with Epicor ERP10 products, however applies to any and all IIS Application pools which you may wish to set to auto recycle.

To Set Automatic Application Pool Recycling:

  • Open IIS on the App Server
  • Under Application Pools, select the one you wish to configure:
  • On the right pane click Recycling (not Recycle!)
  • The Edit Application Pool Recycling Settings window appears, one recommendation would be to set a Specific Time out of business hours, and outside of any other ERP processes e.g. MRP.
  • Note that this allows recycling to be set at individual application pool level, you should check which applications this affects by reviewing the applications set to use this pool. To do this when the pool is selected, click the View Applications button the right pane (or by right clicking)
  • An alternative to individual app pool recycles is to do a full IISRESET which will restart all IIS related services and applications. To do this it’s best to setup a Windows Scheduled Tasks to run the command IISRESET will full administrator privileges.

Epicor Users Group EMEA 2017 Conference

Epicor as a company would be nothing without its customers, and customers wouldn’t get very far without Epicor.
Throw into that mix an array of partners, after all I joined the Epicor world through a partner, and you have a fantastically balanced world of skills, expertise, knowledge, and ideas. More importantly it’s the people, and in the last week I finally got to really understand that community of people, and become a part of it.

On Wednesday, 15th November, 2017 at 08:30 I arrived at the Priest House Hotel, Castle Donnington, UK with an unusual, previously unknown kind of nervous excitement. I had arrived, suited and booted, laptop in tow and ready to showcase myself, represent my company and embrace this wonderful community.
Only 2 weeks prior had I received the invitation to present on Server and SQL Optimisation, a topic I know a fair amount about but the concept of presenting at this level was new to me. That said I think I may have mentioned previously that it was something that interested me, so it was potentially my own doing.
2 weeks to prepare a presentation isn’t a huge amount of time when you have a day job, and a home life to work around, but I relished the challenge and got stuck in, only a few days after the initial request and approval did I discover that I in fact had 2x 1 hour presentation slots…ah… a slightly bigger challenge, but again one to relish.
With 4 days to spare I had 2 presentations written, and a full mirror test complete, with only a slight worry on timings.
Fast forward back to 09:30 on the day and the first presentation begins, 30+ people in the room staring at me wondering how this will go down. 1 hour goes by, maybe a bit more, and it seems to be going ok. The questions are flooding in, I’m somehow managing to respond to all of them, answering everything thrown at me. The feedback continues into the break, a number of people saying “great presentation”, “thanks for the information”, “I’m going to look at X when I get back”. Wow, I actually resonated with some of these people. Bearing in mind that many have been in the Epicor and technology worlds far longer than I have, they seemed to all pick something up from the first one. Great, confidence restored, let’s nail the second presentation.
Before I knew it lunch was served, I was still conversing with various people; customers, partners, ex-colleagues and current ones. It wasn’t until I realised only a sausage roll was left in the room that I had gone through 2 hours of presenting and almost an full hour extra of chatting on only 1 coffee, a mini cinnamon swirl and this sausage roll!
Never before had I talked so much tech on so little caffeine, it was at that point I realised that knowledge, preparation and adrenaline we key to this day.

The EUG EMEA 2017 conference seemed to be a huge success across the 3 days from everything I have seen, the middle day on which I presented was focussed on non-product specific IT related topics. I think the fact I came up against the hot topic of GDPR in my session slots and still had a fairly full room was testament to the fact the nobody knows everything, and everyone wants to know a little more on the subjects of Server and SQL optimisation. The feedback at the time certainly reflects that.

I want to once again thank everyone in the EUG EMEA team for having me, the customers who attended the sessions for their perseverance, great questions and feedback and Epicor for letting me attend and present at fairly short notice. Hopefully this will be the start of a new chapter for me as I look to push on into 2018.

For more info on the Epicor Users Group (EUG) please check out the following:
Web: http://www.epicorusers.org
Twitter: https://twitter.com/eug_emea
Conference Twitter: #EUGEMEA2017