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

 

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!

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 4 – DISM Cleanups

In the last post I covered DISM for the purpose of changing/upgrading the version of Windows, and we found that the DISM tool has existed since 2008, however in 2012R2 they added much improved, usable functionality. Did you know DISM is also responsible for things such as Windows updates?

Under the hood DISM is the tooling being used to install and maintain those wonderful Windows Updates you get every month on Patch Tuesday (is that still a thing?) Which also means we can use the toolset to also tidy up afterwards. Remember Disk Clean up (cleanmgr)? Well it’s a nicer CLI version of the Updates clean-up in there.

There are 3 levels of update clean up via DISM

  • Component Clean up – this cleans up updated components i.e. removing the packages used to install the updates
  • Reset Base – same as above, but means all deletes all superseded versions of every component in the component store, meaning you won’t be able to uninstall service packs/updates applied before this point in time
  • SPSuperseded – removes all backup components needed to uninstall the service pack, also you cannot remove the service pack anymore after using this command.

In short doing 2 and 3 will free up more space but makes rolling back very difficult, option 1 is a nice way to keep the system tidy:

Example on Windows Server 2012R2

  • Disk is at 37.1GB free after being fully updated and having key applications installed

  • Component cleanup is run and takes quite a while… (I had to go out and check 3 hours later, suspect it took ~1 hour)

  • Following the cleanup run we can see around 7GB has been freed up on the system

From this quick demonstration above it’s clear to see that significant disk space is used up by irrelevant, outdated files. The only reason to keep these is if you want to remove an update and reapply later, but surely not all 7GB worth…

I’ve started using the dism cleanup commands during my build process, so that before I start using a server in anger, I get it to as clean a state as possible, fully updated but tidy!

The full commands for DISM cleanup are:

  • dism /online /Cleanup-Image /StartComponentCleanup
  • dism /online /Cleanup-Image /StartComponentCleanup /ResetBase
  • dism /online /Cleanup-Image /SPSuperseded

Tip of the Week 3 – Converting Server Eval to full version

NOTE: You will require a valid Windows Server product key for this to work, this is in no way intended as a workaround to legitimate licensing.

Sometimes when just messing around with an idea or trying to prove a concept out you may find yourself quickly downloading an ISO from Microsoft for whatever OS version you’re testing on. Perhaps you haven’t got access to your usual ISO library or forgot credentials for your Volume Licensing portal etc.

The only issue with the TechNet downloads is that they are Evaluation editions, this means that within a certain number of days (usually 60 or 180 depending on version) you will not be licensed, activated or legit… slight problem here is that within 180 days you’ve probably built your best dev/test platform in years and don’t want to lose it, in fact you want to move it to your production servers, keeping it and maintaining it for eternity. Do you build a new VM with the proper ISO and Product key?

The answer is no, just upgrade your edition.

A while ago now, Microsoft introduced DISM (Deployment Image Servicing and Management tool) – a handy suite of command line tools for managing such situations. So how do I convert from evaluation to Server Standard or Datacenter editions, and what versions of Windows Server does this work with?

  1. Open Command Prompt as Administrator
  2. Open PowerShell as Administrator – it’s the 21st Century
  3. Run the following command:

    dism /Online /Get-CurrentEdition

  4. The image above shows I’m running Version 10 (Server 2016) and I’m on a Server Standard license, therefore not Eval. However if you were on an Evaluation license it would say ServerStandardEval
  5. In order to upgrade the license to a full version, or to go from Server Standard to Datacenter edition you can run the following:

    DISM /online /Set-Edition:<edition ID> /ProductKey:XXXXX-XXXXX-XXXXX-XXXXX-XXXXX /AcceptEula

    The <edition ID> will be either ServerStandard or ServerDatacenter

  6. The ProductKey is the bit unique to you/ your company, however, if you use KMS keys you can obtain these from the following page: https://docs.microsoft.com/en-us/windows-server/get-started/kmsclientkeys – I won’t go into the KMS vs. MAK conversation here but feel free to Google it to work out which you have/need.
  7. Here’s an example I ran using a KMS key:

    Note it removes the “package” of the one installed and replaces it with the one you’re putting in, A reboot will also be necessary.

For more info regarding DISM go here: https://docs.microsoft.com/en-us/windows-hardware/manufacture/desktop/what-is-dism

DISM has been around since Windows 7/ Server 2008 but it’s been most useful since 2012R2 onwards, I will be posting some additional DISM tips in the next post!

 

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.