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

 

Happy New Year

Wow, 2018 has come around already and its time to yet again share some techy tips and posts that will hopefully not bore everyone too much.

I hope everyone who celebrates the festive season enjoyed it and are feeling as refreshed as I am, ready to hit the ground running for this year?!

 

I will start next week properly, although content is already in the works, I am going share Windows Server and SQL tips going forwards to widen the reach a little, so please share the posts to anyone you feel may benefit.

 

 

SQL Server Tip of the Week – AlwaysOn Introduction

Here’s some notes I recently wrote to help explain the basics of AlwaysOn Clustering in SQl – originally written for fairly technical people, i/e/ they know how to install SQL and use SSMS!

 

 
What is Always On?
SQL’s new (from 2012) failover clustering solution, built in to SQL that allows automatic failover of SQL servers without interruption to the applications using a database.

What is required?
2 fully licensed SQL servers at the same version with replication module installed
Windows Failover Clustering role installed
IP addresses for cluster (1 for cluster, min. 1 per node)
Windows Domain – cluster is a domain object

How to install/configure
Microsoft documentation/Brent Ozar is best source.
e.g. https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/ for 2016
For installing Failover clustering you could add the role via PowerShell e.g.

Install-WindowsFeature FailoverClustering,RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell


What to know in advance

For an installer there are a number of things you need to know in advance:
• What type of synchronization is to be used?
• Synchronous or Asynchronous – https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups
• Cluster Name – this is the name above all servers in a cluster and therefore where Epicor applications point
• Cluster IP address – ideally decided in advance so can be reserved/static , it’s the IP for the cluster not the server
• Node IP address – each server in the cluster will have a secondary IP used purely for clustering, this is not the IP address used for management of that server
• Secondary Node IP address – only if the servers exist in different subnets they need a node IP address in each range
• Witness Server file location – this is another server available to all SQL servers where a witness file is stored to maintain quorum, example \\FILESERVER\SQLWitness

Things to Note

• Asynchronous synchronization can mean a data loss, review the link above for more info, it is also not an automated failover. – designed for offsite replication over a large distance.
• Synchronous is a more accurate (live replica) sync, but “could” be a source of lag SQL side. i.e. increased overhead
• In 2016 Standard SQL you can only have 1 DB in each availability group, therefore from a config side it’s worth considering which DBs need to be available should the system failover.
• When restoring a database in an availability group (overwriting) you must take it out of the availability group first, and example of doing this with existing restore scripts is (with additions highlighted):

--Backup Source DB Code Goes Here
--Safety Backup of Destination DB Code Goes Here
USE [master]
ALTER AVAILABILITY GROUP MyDatabase_AG REMOVE DATABASE MyDatabase; 
--Restore Script GOes here, i.e. grab back of source and overwrite destination...
ALTER AVAILABILITY GROUP MyDatabase_AG ADD DATABASE MyDatabase
GO

Career & Tech Update June 2017

Somehow I managed to let the last 7 months slip by without so much of an update, so no doubt anyone that usually reads this, no longer does!

However, I like to update this blog in order to note my milestones, especially where my career is concerned. I reference all aspects of the last post Found Here

So far in 2017 I have been pretty much doing what I did in the last 6 months of 2016, working with customers to develop technical solutions around their ERP systems. Primarily I am involved in the initial design and install of ERP environments, by that I mean I sit down with a customer (on a technical level) and discuss the various hardware and software requirements around the product the have already signed of on, mostly this is the Epicor ERP10 solution, which is a world leading product aimed at mostly manufacturing and distribution companies across all sectors. Once a customer has made an informed choice on their physical and/or virtual environment where their new system will sit, I am involved in planning, installing and documenting the base deployment of the system. Typically 4 databases (Live, Pilot, Test and Demo) with a variety of additional elements and solutions such as Web Access, Document Management, External System interfacing etc.

Traditionally that is the point where I stop and hand over to the customer a fully documented base install – note if this is an upgrade project they would have their data upgraded as part of this process too. However, I was set a challenge by my superior, and also by myself, tp go beyond the installations and technical planning stages, to spread my wings and my reach a little bit further, deeper into the product.

And so, after 6 months of mixing the day job as described above, with the other day job of keeping my old “managed services” customers up and running, and a few “tech days” on site at multiple customers; training their IT people on managing the ERP infrastructure with an addition of 9 solid days (spread out) of online classroom training, and additional self-paced learning courses… I am now certified as a  Level 1 Tools Consultant up to the latest released version of ERP10 (10.1.600). 

Whilst Level 1 does sound rather basic, and may even be ridiculed by some, what it has given me over the last couple of months, is an ability to understand and appreciate the inner workings of an ERP system, to finally understand how and why customers do not use these systems “out of the box”. Tools consulting is all about helping tailor a system to a company’s individual needs through the use of inbuilt tooling. I am also fortunate that I work alongside a number of people who go one step beyond this into the custom solutions world where they utilise external tools and code to further enhance the systems. I am no developer, but have always dabbled in code, and examples on this blog from years gone by show the random times when I have explored that side of my technical work. Being able to potentially start consulting on tools related work will definitely allow me to mix all aspects of my career to date, and this hopefully will be a great fit for me, allowing me to further enhance and adapt my career.

Back on my last post in November 2016 I listed a number of areas where I was developing within the IT/Tech world. I’d like to review some of those with an update:

  • SQL Skills – With the release of SQL 2016 and a number of challenges regarding compatibility with various platforms and products, I have yet again become rather adept at installation and configuration of SQL environments. I’m also starting to develop a nice library of useful SQL query snippets, from resetting DB ownership, to replacing strings in fields and backup/restore/overwrite of entire ERP environments!
  • ERP – As discussed above I now have a much deeper understanding of the implementation and usage of ERP systems, recent training has opened my eyes to the worlds of finance and manufacturing processes. I have also branched out further into some specific areas, such as CRM and Field Service setups. More of this to come I am sure.
  • Hyper-V – Not really had the chance to go really hands on with this level of setup, in most cases I advise the customer how to set things up but don’t actually get involved in those stages. That said I’ve fixed a handful of Hyper-V snapshot and backup related issues for multiple customers recently so I have to keep my knowledge up where possible.
  • VMWare ESX – same as for Hyper-V really, although I have some interesting migration/decommission work coming up soon so maybe a watch this space.
  • RDS  – I finally have my RDS customers stable and happy, with good performance and no recent complaints! I’ve also worked on implementations related to handheld warehouse devices and ERP client deployment both on RemoteApp and Desktop Sessions.

Do you remember the Coming up list…

Back in November I listed 3 key areas of focus for 2017, with a little Watch This Space…

It seems that out of the three items, two of them are very real, in fact the whole beginning of this post/story covers the Tools area, the other is Solutions, which as a side note to the day job appear to be my area of expertise. By this I mean i have become the main installation resource for a variety of extended solutions (CRM clients, Document Management Email plugins, Project Management integration etc.), also I have become a suport contact point for these and have had some significant contact around the globe  identifying and troubleshooting issues on these solutions. Basically becoming a human intermediary between Developers and Customers – as we all know Developer’s are beyond human  😉 

My last point was Cloud, and in a way without realising it Cloud is part of every day life at home and work. I need to specifically do more work with it, and have targets set against that, so I hope that by the end of this year I will be able to update in more details on that!.

 

Thanks as always for reading what is by far my longest post!

 

Tech Update

It has been a while since I posted an update of all the technologies I’ve been working with, projects I’ve worked on and general IT related things that have affected me in recent times, so here’s a brief update of my world of tech!

 

Latest Technologies used

  • VMWare vSphere, vMotion, ESX 5.5 etc. – underlying technology of the infrastructure in my current role, love the live server migrations between hosts, and the performance monitoring is very good.
  • Windows Server 2012 R2 – finally have the opportunity to work with Microsoft’s latest stable enterprise OS, and I have gradually started to like/ understand the new interface. Some of the updates to certain roles are also very impressive including WSUS (Updates), WFCS (clustering), ADDS (Activce Directory), some very nice improvements over Windows 2008 R2 which is the last main release I am familiar with.
  • WhatsUp Gold – systems monitoring software from Ipswitch, what a fantastic product this is. I can monitor all servers, virtual machines, network components, even WLANs (which is one of the most used features). I have heavily customised the environment to have dashboards for all sorts, most notably the SQL performance (most important system) I can now see real-time memory usage/ system statuses, even in-depth transactional performance!
  • SQL (2008 R2) – Something I’ve had to get to grips with very quickly as it runs the 2 most important systems for my current role. I am fairly confident with performance monitoring, understanding heavy queries, bottlenecks, even a grasp of lazy writes and paging! SQL is usually administered by a professional, qualified DBA, but we cope just fine!
  • SQL (2014) – only within a test scenario but I am very proud of my virtualised SQL 2014 Always-On testing cluster, it has given me an opportunity to better understand SQL installs, Windows Failover Clusters, SQL High Availability and basic database maintenance.
  • Cisco Wireless Networking – heavily used within large organisations across the globe, I now have a fantastic understanding and hands on experience with a network capable of almost 100% wireless coverage within an huge site! I also have great experience with security on these networks and have implemented whitelists (MAC filtering) etc. I’ve also been able to ensure the wireless network is 100% monitored across all nodes and devices, with historical data available to help resolve any issues.
  • Windows 10 – I’m a pure techie, so when the latest version of something is out, I have to try it! I was sceptical of the whole Windows 10 thing, even after trialling the pre-release versions around 12 months ago. So when the opportunity came to try at work I though yes that’s safer than at home! – so 2 months in; every device I have is now Windows 10 where possible. Being an IT admin naturally I’ve butchered it as much as possible to better understand its workings, and to be fair so far I am impressed!

Project Work

Whilst the current role hasn’t really thrown any specific projects my way yet, I am actively involved with a domain migration project initiated by the buying out of our division by another company. That is something that will be happening very soon, and will probably consume a lot of my working hours up to Christmas.

I’ve also been spearheading work to implement a standard way of displaying data across site, this has been done using Raspberry Pi with Screenly OSE which allows scheduling of web content, images and videos for display on HD screens. This again is a recent thing that so far is working very well (no reboots/crashed in 3 weeks)

At home I have also been playing with a Raspberry Pi, and have plans to implement a media server to stream content such as from the home CCTV system and family photos etc. Hopefully this will be done before the year is out once I have a screen in the kitchen!