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.

Pulling Teeth* or Pulling Contacts?

I don’t know how this got complicated but it did so here’s a blog post on how I rescued a load of contacts off a Microsoft account without owning a Windows device and therefore Outlook on the desktop.

I’ve very much moved away from Microsoft as my email, calendar and storage provider. My new provider is Proton who are a privacy centric outfit based in Switzerland. The very last bit to move has been the contacts which Proton can do in the Mail client but doesn’t sync with devices so that you can use them in the phone and messenger apps. I posses an Apple iPhone and whilst I don’t use Apple iCloud for mail, calendar, etc I was using it for Tasks. I decided contacts can be stored there for now.

I’ve tried various tactics to get my contacts away from Microsoft but nothing seemed to work. If I had to make an educated guess this isn’t straightforward to do on a technical level as Microsoft’s Exchange and Apple’s iCloud (which is presumably an implementation of CardDAV) will store information in different formats. Microsoft will spit out a CSV; iCloud only accepts contact cards. There’s never much motivation to a provider to make the export process any easier when it’s about migrating away so I decided not to expect a straightforward time.

There’s probably a better way of doing this but as I no longer own a Windows device here’s how I achieved the move in an abstract:

In Outlook.com export all the contacts into a CSV file.
Check your CSV file using your favourite text editor for any errors, duplicated contacts or anyone who’s unfortunately become a bit of an enemy.
In Evolution perform an import into the local contacts folder.
Setup your iCloud Contacts account in Evolution to the CardDAV address https://contacts.icloud.com.
Drag and drop all the contacts into the iCloud account.

You could do the process in fewer steps by importing direct to iCloud instead of the local Evolution folder however I found that Evolution would go unresponsive and not provide a progress indicator. I had 162 contacts and I observed the process to be overall slower by importing direct i.e: it seemed to work faster importing locally then copying to iCloud.

The caveat was that no matter what date format I used in the Microsoft CSV it wouldn’t import to Evolution or produce an error as to why it hadn’t. I had to manually re-enter the dates in my contacts in iCloud.

For my next trick I’m considering setting up a local contacts server such as Radicale.

(* Pulling teeth is an expression that means to do something that ends up quite painful to do like pulling out teeth without anaesthetic!)

I Survived Consulting in 2022

That’s it for 2022. I packed away my work laptop and phone after submitting my final timesheet of the year. Overall it’s been a great year working hard, responding to the challenges of modern working and supporting organisations whatever their mission may be.

Lots happened for me in 2022. Professionally I ascended to membership of the British Computing Society, passed a few Microsoft exams and also formally adopted permanent working from home. In my private life I helped pull off a successful beer festival and bonfire as part of Mirfield Round Table, I got close to my goal of swimming 10k by swimming…9k…but I also had my heart broken a couple of times :’-(.

Key Anticipations for 2023

It’s getting a lot cloudier out there. For my part in this I’m going to be focusing a lot lot more on cloud hosted applications whether that be lifts n’ shifts to public cloud VMs or migrating clients to cloud native solutions. Fact is they don’t want anything “on-prem” anymore. Fine by me.

I also anticipate we’ll be talking more about general ethics in IT. Whether that be privacy concerns, making the profession more inclusive or ensuring that we are safeguarding the planet for future generations we do have our work cut out for us and it’s critically important we rise to that challenge.

We’re also inevitably going to see a lot more challenges regarding security, stability and connectivity. As we move to (arguably) post “Wintel” desktop and server world to one that’s more cloud native and ARM powered we will see opportunities and problems arise. A constant challenge of mine is getting applications into the hands of users in a variety of settings, devices and conditions. My personal challenge for 2023 and beyond will be to make sure I can do that for people who aren’t “Wintel native”.

However your 2023 looks I wish you a Merry Christmas and a Happy New Year.

AZ-900: Just Have Fun With It

Third exam of the year. This time it’s AZ-900: Azure Fundamentals and yes here’s another shiny, shiny badge I can show to Mum to prove it:

AZ-900 is the easiest Microsoft exam I’ve ever done by a long way. It’s testing knowledge of Azure at a very basic level. That being said it’s definitely not one to underestimate. You need at least a superficial understanding of how Azure works and what the key concepts are with cloud computing.

On May 5th 2022 Microsoft adjusted the exam and made it even more fundamental going as far as removing the bits about databases. A lot of the resources you’ll find on the internet therefore probably go far too into depth.

The Study Plan

The key is AZ-900 is not to overthink it. This one is free with the cornflakes. Take an Azure Virtual Training Day: Fundamentals course to get your free exam voucher then either have a go at the exam or go study with the Microsoft Learn Learning path with a copy of the study guide in hand (or on screen; save paper please).

If you fail it you can always redo the training day or pay £69 to save yourself the hassle.

Practice Exam? Don’t bother. Just enjoy learning and relax about it.

DP-300: Cloudy With a Chance of Database Failover

Not content with just one exam to pass I’ve gone and done another: DP-300: Administering Relational Databases on Microsoft Azure. This one I nearly passed out whilst doing it but thankfully I passed the exam instead.

This one along with DP-900 completes the two exams I needed to do this year so I am very pleased with both passes and both done first time.

This also means I get another shiny badge to put up on this blog so here it is and yes you can click to verify I’m not fibbing about it:

The Study Plan

I could’ve very much done a copy and paste job on the DP-900 effort I did earlier this month but that would’ve meant that I couldn’t write another witty title and that would be boring.

I have now 6 years experience deploying, patching, configuring, troubleshooting and tweaking SQL Server. In these exams well founded experience and knowledge of what you’re being tested upon helps.
Used the learning path for DP-300 on Microsoft Learn. I’ll admit at this point I did not finish the last two modules on Automation and High Availability but thankfully I did very well on those questions.
Again, used the Measureup practice test and yes some similar or even the same questions came up on the exam. I did start to notice with this test that I began learning the answer by recognising the question rather than understanding what was being asked. The note of caution here is to not rely on this too much as there’s only 122 questions in the bank.

It’s quite a hard exam to do and I found it challenging. Whilst SQL Server is familiar to me Azure SQL Database is completely new. I had a lot to learn in a short space of time but I got through comfortably. If you’re taking the same exam soon then all the best you.

DP-900: Putting the Fun Back Into Data Fundamentals

My last Microsoft exam was back in 2017. Really.

Today I managed to pass DP-900: Azure Data Fundamentals with a passing score of 982/1000.

Should be noted that the contents of the exam changed on 5th May 2022. From the updated skills measured sheet it would occur to me that they made the new format more “fundamental”. That’s not to say it’s an outrageously easy exam. I had to learn a few new concepts but as someone with an interest in SQL Server I enjoyed the learning process.

Here’s the badge to say “I did it”.

The Study Plan

I attended the free Azure Virtual Training Day: Data Fundamentals from Microsoft. Each session was just under 4 hours long and was a pre-recorded video. By attending across the two full days you receive a credit to take the exam for free so not only do you get an intro to the subject you also save £69 for the exam.
Used the Azure Data Fundamentals Learning Path on Microsoft Learn. This was a good source of basic knowledge and a few free labs on Azure were available too. Made lts of notes here to revise with later on.
Subscribed to the official practice test available on Measureup.com. Some questions in this practice test came up on the exam although it must be said that the practice test probably does not reflect the May 2022 changes just yet but keep an eye on the website for more info. I put the test in practice mode and set it to explain wrong answers to strengthen my knowledge and further improve my notes.

Overall not the hardest exam to pass. As long as you understand the subjects in the exam you’ll have no problem passing it. All the best!

Patch Time

If you’re in the SQL Server world you should be aware of this by now however for those who’ve been busy getting stuff done this week Microsoft have released a security update for SQL Server described in full in KB4583459.

Data can be sent over a network to an affected Microsoft SQL Server instance that might cause code to run against the SQL Server process if a certain extended event is enabled. To learn more about the vulnerability, see CVE-2021-1636.

Patches are available for SQL Server 2012 and above with currently supported service packs. As it’s most people’s best interest to maintain a SQL Server that doesn’t allow this to happen it’s a great idea to get this patch installed now. If you are intending on ruining someone’s day with this exploit I wholeheartedly apologise for spoiling your fun.

I anticipate quite the majority of my clients will “accidentally” end up with this patch through Windows Update which is probably for the better (exceptions given if it somehow kills the SQL Server).

70-462: Administering SQL Server 2012/14

This week I passed Microsoft Exam 70-462: Administering SQL Server 2012/14. This was my second attempt after a near pass of 674/1000 (pass mark is 700) on the first attempt.

My preparation was similar to that I did with 70-462 so check out my earlier blog post for more details. The main difference was that I built a lab environment and messed around with everything in SQL Server with the aid of a Microsoft study guide. Lots of stuff has been learnt from this and confidence with the product comes with that progression.

Honestly the first attempt at 70-462 felt easier than 70-461. Naturally I’m more of an SQL administrator (alright: configurator) than a query writer. I was disappointed to have failed it first time but I decided to come back for seconds.

My 3rd and final exam I have planned for this year will be 70-411: Administering Windows Server 2012. Upon hopeful completion of said exam I will become an MCSA in SQL Server 2012/2014 with a view to upgrading to SQL Server 2016/7 next year.