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 2 – SQL Versions & EOL

Have you ever wondered whether you’re definitely on the latest patch of SQL?

I found a site a few years ago which I use at least once a week, every week.

In SSMS when you view a server’s properties you will see a version number e.g. 11.0.5058.0 which to most of us doesn’t mean a lot. On the face of it you can tell its v11 of the software, and you suspect it’s not the GA release due to the 5058 part, but in reality how does this translate to the year and service pack?

The answer is: http://sqlserverbuilds.blogspot.co.uk/ which provides a nice little table as below.

The best thing is that this site seems to be kept up to date all the time and further down the page you will see all sub-versions/ Cumulative updates and release dates, with hyperlinks to release notes.

I find this info very useful when debugging performance issues, especially with 2008R2/2012 so I can check the customer is on the latest patch of the version they have. Also with Microsoft’s changes to extended life of these products over the last few years, some patches are supported and others aren’t. To check what is/isn’t supported it’s worth browsing (and bookmarking) this link: https://support.microsoft.com/en-us/lifecycle/search/

The Product Lifecycle page at Microsoft is fully searchable for all MS products, and will tell you which patches are supported and which ones may already be out of support. e.g. for SQL Server 2012 you’re fine if on SP4, for SP3 you only have 8 months left, SP2 down is already out of support:

If you ask me we should all be on SQL 2016, but I fully understand the reasons we hold back, whether due to licensing costs, downtime issues, 3rd party support or other issues.

SQL Server Tip of the Week – Winter Break

Unlike the Premier League, I will take a winter break from the SQL Tips,

Most likely I will use the break to build up the quality and quantity of content for an awesome 2018.

On that note I’ll leave you with this link to get your systems ready for the weather… 

https://wateroxconsulting.com/archives/prepare-for-winter/ 

 

Thanks for visiting!

SQL Server Tip of the Week – Avoiding restore errors

Quick tip for this week as I have not been home much to contemplate writing something epic, although I have a couple lined up for future…

This week, my quick tip is for avoiding errors when restoring over an existing database.

It is common practice for many enterprise applications to have the concept of a Live/Production environment and a Test/Development environment. Some products will see you have around five or six different ones, especially during implementations of new systems or upgrades. Typically, I work in a world where you have three environments with your data and one with sample/demo data.

I will be writing a more in depth backup/restore guide, but I wanted to cover some generics to start with.

One common task is to take a copy of live data and restore it into a test or development area so that you can try things out without risking critical data. Some companies may even have policies where they do this weekly or monthly to have a “pristine” copy of their data. When this is, a repetitive task it’s sometimes best to script it out, especially where the process is identical each time, the problem with this though is ensuring the destination database is in a state where the restore can happen. And whilst we can script in the Single-User mode and various other options, we cannot 100% safely validate and kill existing sessions, what if a session to that database is from a business critical system you forgot to switch off before the process began?

Therefore, my recommendation is to use the SQL Server Activity Monitor. This tool is also a starting point for identifying performance issues such as long running queries or table locks.

  1. To access the Activity Monitor load up SQL Server Management Studio, log in, and then right click the Server node (top) in the Object Explorer, under the menu you will see Activity Monitor:

  2. To find whether your destination Database has any connections to it you need to expand the processes section, in there you will see a list of every database connection.

  3. A quick way to see if your database has any connections is whether or not it appears in the database column, you can click on the header to filter on DB name, if yours is not there then it doesn’t have any connections, if it is then filter on it so you can see what connections you have:

  4. If there is only 1 or 2 lingering connections you could right click on them row by row and kill the connection, however if, like above, there are many, I would suggest looking at the Hostname column and seeing what is running on that machine (as the user listed in Login column) to try to identify what you forgot to switch off before attempting the db overwrite.

This tip has saved me many times, it give you a quick view of access to a database, and is the first port of call should you get any “can’t overwrite, file in use” style errors.