SQL Server Health Checking

These past few weeks I’ve been working on health checking a client’s SQL Server 2017 instance that hosts databases for their finance and spend-control applications. In my suite of tests there were over 70 different points checked across SQL Server and the applications to ensure that they were operating at peak potential.

Three useful things you can do to quick check a SQL Server instance even if you don’t have a DBA or SQL management skills yourself:

1. Check the installed Cumulative Update (CU)

SQL Server itself is a highly tested product and usually you’d not expect to have any serious issues in production. From time to time though there are security, performance and functional updates released for the product. It’s recommended to frequently apply the latest Cumulative Update or “CU”. These are highly tested updates that are certified to the same level as what a service pack used to be.

You can check the currently installed CU via installed updates in add/remove programs, by comparing the version string in the properties of the SQL Server instance in SQL Server Management Studio (SSMS) or referring to documentation your SQL Server installation consultant gave to you.

If your instance of SQL Server is no longer supported then it’s time to plan out an upgrade.

2. Check the free disk space

SQL data files, log files and backups can grow to consume a lot of space. These files are necessary for a full functioning SQL Server instance. It’s therefore necessary to manage the disks they are stored on.

If SQL Server runs out of space it will follow the database settings for Autogrowth if switched on. If there’s no data to allow the growth to occur then SQL Server will return an error instead which the end user will then somehow experience (error message, timeout, nothing happening).

As for backups that’s a lot more straightforward; no disk free then no backup.

Simply check these by looking at free disk space in File Explorer and then consider expanding the disks, moving files about or planning a migration to a server with more storage. Another important note that you should avoid shrinking production databases. Yes, it’s best to allow disk space to be pre-allocated to SQL Server databases! This avoids fragmentation which can reduce performance and also delays in response due to the database engine awaiting the disk to allocate more space.

3. Checking over the host Operating System.

Your SQL Server will only ever be as good as the operating system it’s hosted upon. You should periodically check that the latest updates are applied to the OS, ensure that there are no major events that need attention logged in Windows Event viewer and also ensure that CPU and memory aren’t under pressure from other applications.

This by all means isn’t a comprehensive list of things you should be checking on a SQL Server but it’s relatively easy to do for someone who has sysadmin skills for Windows Server (or even Linux).

If you have performance, security or operational issues with SQL Server don’t hesitate to get in touch with us at Digital Incite and Matter Ltd. We are experts in maintaining mission critical SQL Server instances for organisations of all shapes and sizes.

Seriously, Stop Using Windows Server 2012 & 2012 R2!

(Also SQL Server 2012 please)

Extended support for Windows Server 2012 and 2012 R2 expired on October 10th 2023. We’re coming up to November 2023’s Patch Tuesday which means that there’s really, really, really no life in Server 2012 or 2012 R2 any more in case that first deadline wasn’t important enough. Hacking crews out there will highly likely be able to spot a vulnerability in Server 2012 / R2 by checking out the vulnerabilities for Server 2016 and newer. So in other words if you’ve not planned to be off Windows Server 2012 / R2 by now you’re a bit stuffed. That is unless your organisation’s forking out for Extended Security Updates in which case you can breathe easy a bit longer.

If you are in the UK have Cyber Essentials renewals coming up you either need to be shut of the servers or segregate them somewhere off the main network to their own retirement VLAN before the audit starts otherwise you’ll fail it. Don’t say I didn’t warn you.

Don’t Just Move It To Azure!

Yes it’s true that you can move your server to Azure and get an extra three years of security updates included in the price of the VM service. Three years sounds a lot of time but that will run down before you know it. So don’t kick the proverbial can down the proverbial road.

Moving a series of servers from a private cloud or IT infrastructure to a hyperscaler can also be costly in direct costs for the VM (CPU, memory, Operating System, disks, etc) but may also result in hidden fees in terms of having to build remote access solutions bring in consultants and even patch the application. It’s generally cheaper to run VMs in a private cloud if they are needed 24/7 so check costs carefully.

Mark Your Calendars for Windows Server 2016 End of Extended Support

January 12th 2027. It’ll be here before you know it.

Looking Back Where You Came From

If you’re stuck in life trying to move on the internet might throw back at you various quotes in the vain of: if you’re trying to move forward then don’t look back. This blog isn’t about philosophy but sometimes it’s damn well close.

Recently I caught myself reminiscing the earlier days of my career when I’d joined the family firm and ended up taking charge of the IT infrastructure. When I started this was one Fujitsu box running Small Business Server 2003 along with a rabble of Windows XP desktops dotted about. There were a few Vista laptops appearing as well at the time.

When I first joined the company I became aware of an issue that would strike at some point over the weekend. On almost all Monday mornings the internet would be down. Not every Monday but quite a lot.

The simple fix was to exclude the Internet Security and Acceleration Server (ISA and now called Forefront Server) proxy cache file from the weekly full backup that Backup Exec was doing. That was the first major IT issue solved in my career.

Shortly after fixing that the company decided it had outgrown the Small Business Server 2003 setup and we decided to replace it with Small Business Server 2008 on advice of our IT partner. Windows 7 had also appeared which was of keen interest to the company and long suffering employees with that all too blue XP interface.

But if you don’t know much about the legendary Microsoft product that was Small Business Server I’ll explain dear reader.

The big idea with Small Business Server was to bundle together many core products vital to a growing business into one licence, at a reasonable price and all carefully designed to work together more or less out the box. It would then be up to an IT provider to design, implement and support the server. In addition if you needed it you could buy the shiny Premium add-on which granted a second Windows Server plus a licence for my favourite video game SQL Server (at the time the rather advanced SQL Server 2008).

For Small Business 2008 it would provide: Active Directory, Microsoft Exchange, File & Print, SharePoint, Windows Server Update Services plus a backup solution built in. To manage all this the server offered a console which also reported on the status of the server as well as the clients connected to it. Somehow it was a product that was sold like an appliance, worked like an appliance but wasn’t actually an appliance.

(There’s probably something important I missed out in that list).

Did I mention this thing also provide Remote Desktop Web Gateway and PPTP based VPN? Yes indeed! This was a more innocent time in the age of the internet where broadband lines weren’t quite as ubiquitous as they are now. You did have to run it behind a router as having a second network interface was prohibited.

SBS 2008 Console — Behold! The SBS 2008 console.

But in my tenure as an SBS admin this simply wasn’t enough. Nope. We decided to add on Symantec’s Backup Exec and Sophos Endpoint AND Sophos PureMessage. Somehow it all continued to work together.

This product back in the day was on one hand valuable for small/medium businesses to access server technologies but on the other questionable as to whether it was such a great idea to actually run it. By the standards of today it’s an absolutely crackers product for a small to medium business to run because the sheer number of moving parts on the installation were asking for trouble.

I would be very surprised if there weren’t stories of horror out there of SBS completely falling over, backups not working and entire businesses grinding to a halt. This product was arguably dangerous to run a small to medium business upon.

The world moved on from Small Business Server and the last version would be Small Business Server 2011 based on Windows Server 2008 R2. For the Windows Server 2012 era Microsoft would replace it with Windows Server Essentials and also nudge you towards Office 365.

By the standards of today it’s an absolutely crackers product for a small to medium business to run…

I owe a great deal to Small Business Server. What I learned running the product was the basis for the first 16 years of my career. After 9 years I moved onto a consultant role and took the skills with managing Windows Server, Active Directory and most importantly SQL Server. Arguably the last remaining “on-premise” skills now that the world is more cloud centric.

The most valuable lessons I learned from supporting Small Business Server?

First was to never run a server on RAID5 because whilst the storage is efficient (only 33% is used for parity) the performance was absolutely dire. Taking 10-20 minutes plus to reboot whilst emails were probably getting lost was unacceptable then and would be grounds for dismissal now.

Second was that given the rise of email, instant messaging and to a lesser extent services like SharePoint it’s absolutely vital to keep these afloat and therefore a single box running everything is too great a point of failure in the business. It’s time to consider hosted or cloud for such things unless you have the resources to reliably host and build adequate redundancy on site.

Third well now the product has gone it’s always worth remembering that there was a time where we needed to run everything ourselves. In a cloud first era someone still has to do the work in the datacentre to run all of this. Tip a thought to those individual every so often an appreciate the work that gets put in.

(And no I don’t want Small Business Server back)