If you’re reading this I’ve temporarily closed off SQL Server Management Studio for the year and I’m spending time with my family & friends.
I hope you are having a wonderful Christmas too and that Santa brought you all the things in the world you wished for or at least everything Santa could do with the SQL login privileges he had at the time.
Thank you for reading the blog and I look forward to writing more content into 2024.
Recently building a system and preparing to handover the system to a colleague who will be completing the build they asked how they SQL Server databases were configured for the transaction logs, compatibility levels and other things before before other data was loaded in.
To answer questions on that regard you could go through each database one-by-one in SQL Server Management Studio to check the configuration but that’s going to get fairly tedious really fast.
As I’ve said before it’s often best to try and script things out to save them for later rather than poking through the GUIs all the time so this is where building something you can repeatedly use comes in. In this instance the view sys.databases is a really useful one to know.
I’ve built myself a T-SQL script that I use to bring out useful information I can then copy out for documentation or just a quick check of the databases. This script is available in my GitLab repo for you to try. My recommendation would be to modify to your requirements or be an inspiration to build your own.
Your SQL login needs to have ALTER ANY DATABASE, VIEW ANY DATABASE or CREATE DATABASE permission at server level or be in the SYASADMIN server role. If you call the view any database you have the db_owner role over will appear too.
This script I use to give a quick check following building a new system. Just an an example it’s usually a really good idea to have AUTO CLOSE and AUTO SHRINK set to OFF on production databases. Sometimes clients might set these on as they sound like a good idea just by reading them. With sys.databases this is an example of option you can check to see what’s configured for either general checks or troubleshooting purpose.
My invitation to you is to check out my script, leave me a few comments and have fun building your own.
A few weeks’ ago I blogged about installing SQL Server 2022 on Linux. It just so happens to be that Cumulative Update 8 for SQL Server 2022 came out this week. This gives us the perfect opportunity to talk about applying those all important CUs to the SQL Server we built a couple of weeks back.
As discussed applying SQL Server Cumulative Updates on Linux works differently to Windows. On Windows you generally either get CUs by manually downloading the update via Microsoft’s KB on that particular update or accidentally let one install via Windows update if there’s a security fix. On Linux you generally get updates via the repositories you have configured for your server.
Applying Cumulative Updates for SQL Server (of any supported version) ensures that you have the latest fixes for the SQL Server platform. Each update is certified to the same degree as a service pack used to be and Microsoft generally recommends you keep up to date with their installation. That being said you still should test carefully in UAT before applying to a live system.
The first thing you need to do of course is backup your SQL Server. Something could go wrong so you must make sure you have a rollback plan in case that does happen. This could be: taking a snapshot of the server’s VM in your hypervisor, performing a full backup of all the system and user databases, initiating a full server backup using your favourite backup agent or something completely different.
Once you have a test plan in place which has been approved plus your backups you are now ready to install the latest Cumulative Update.
Step 1: Check to see if you’ve got the correct apt source in your repos:
sudo apt edit-sourcesYou will then get a 1-4 choice for which editor you want to use. I chose nano which is option #1. At which point you’ll see nano appear. Scroll right down and find your sources.
If you don’t see Microsoft’s sources for SQL Server in there it’s not all bad news as Microsoft have made a guide on how to resolve that problem.
Once you are done reviewing the sources list press CTRL + X to close nano.
Step 2: Run apt update to fetch the current package list from the repositories:
sudo apt-get updateThis takes a few minutes and for my server resulted in a 14 MB download. This command is vital to ensure that the next command runs properly otherwise you’ll be using an outdated package list.
Step 3: Perform an upgrade of msssql-server using apt:
--To update everything on the system at once
sudo apt-get upgrade
--To do MS SQL Server only
sudo apt-get upgrade mssql-serverThis will then list out all the packages that need upgrading based on what’s installed to your Server vs what’s available in the repository. If you decided to upgrade everything one of those packages should be…yes you guessed it…mssql-server. Cumulative Update 8 is approximately 268 MB to download:
You are of course going to answer ‘Y’ to this question. Or press enter (note that the “Y” is capitalised. This means that it’s the default answer if you smack return).
This will then run through all the updates to go through. Highlighted here is apt setting up the mssql-server package version 16.0.4075.1-1 which is indeed Cumulative Update 8.
Step 4: Verify the mssql-server service is alive:
systemctl status mssql-server --no-pagerAs you can see we’re onto a winner:
At this point it’s also probably a good idea to open SQL Server Management Studio or Azure Data Studio to check you have a working connection. To double check you have Cumulative Update 8 you could also execute the following command in whatever SQL query tool you are using:
SELECT @@VERSION AS VersionString
Microsoft SQL Server 2022 (RTM-CU8) (KB5029666) - 16.0.4075.1 (X64) Aug 23 2023 14:04:50 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>At this point you should start performing your acceptance tests on your databases and applications. Aim to verify that the usual business and system processes are working before declaring a success!
Whilst it’s usually recommended that a new SQL Server instance should go through a UAT phase with whatever application(s) will be running against it using the latest CU available at the time. However there could be situations where a specific version of SQL Server is required. Whatever that reason in order to do this run the following commands:
sudo apt-get install mssql-server=<version_number>
sudo systemctl start mssql-serverWhere <version_number> is the version string you need. For example Cumulative Update 7 is 16.0.4065.3-4. To find the version number you need consult the release notes for SQL Server 2022 on Linux.
This week video conferencing specialists Zoom announced that they expect employees back in the office at least for some of the week. At least if you are working within “commutable distance” of their offices.
It’s not just a Zoom – which strangely was the weapon of choice when we were all sent home for the COVID-19 pandemic – but also the likes of Amazon have decided that they will track and penalise employees working from home too much. And it’s not just them either. Over the past year the likes of BT Group, Apple and Twitter/X have also decided on some form of return to work.
So what’s the big fuss?
Work from home was a necessity during the pandemic for obvious reasons but now that’s in some kind of endemic phase it’s not really a barrier to bringing people back to the office any more. Top level management are seemingly intent on using that office space they’ve spent the cash on to reap the benefits of bringing teams back together. Or so the theory goes right?
I read different articles and different studies on the effect from working from home. Some in favour, some against. Personally I think it’s very much down to the individual and their circumstances. I do work from home but I don’t really like doing it as I feel there should be a separation of work and home space but also interacting with people face-to-face is critical important. I do live alone so that’s a big reason for the aforementioned mind
There are MANY different benefits to working from home polices in terms of environmental benefits (less cars on the road and/or stuck on the M62 might be nice for some), parental responsibilities, focus in busy environments (who on earth likes working in open offices?!) and countless others.
Despite the fact I personally wouldn’t work from home if I chose to I would still discourage an employer from withdrawing the facility. Fact is that it’s outdated to think that computerised work can only take place in one location and that location must be the company offices. Not everybody wants to work in an office and be closely supervised on work they can do easily at home. Just why am I working this hard just so that employers can decide working from a beach bar is a bad idea?
Last week I’ve been back at work after a week scoffing pasties down in Cornwall with the family. I had a Parkrun and a few sea swims in between too which has been great. I also gave myself time to finish reading Diana Nyad’s memoir Find a Way.
Diana was the first person to swim from Cuba to the United States. This incredible achievement over a distance of around 180km (110 miles) took 5 attempts, a lot of heartache and dedication to the dream.
I wanted to read this book after watching an interesting Ted Talk by Diana titled “Never, ever give up”. In the 15 minute appearance she talks about the challenges and motivation she found for taking on extreme distance swimming. Right at the end she calls to the crowd “find a way!”. Not only did I want to read her full story I wanted to find out who exactly Badass Bonnie was too!
I found Find a Way well worth the read and interesting as an open water swimmer. I would definitely recommend it to anyone whether a swimmer, sports fan or in need of some inspiration.
That is also to say that other than doing “catch up” not in the water that’s as much as I’ve done professionally following my return.
I remembered this week that I’ve not posted here for a while so in the interest of (1) confirming I am alive, (2) making some fluff up to prevent the blog from looking “abandoned pending domain expiry” and (3) to get back in some kind of rhythm.
Honestly it’s been busy. Nobody wants to stand still and as we all work out what’s going to be the “new normal” there’s a lot of push for cloud, automation and remote connectivity. That means a lot of work to be done so less time to have a natter on the blog.
Hopefully I will have a new laptop to review pretty soon so watch out for that.
Speaking to a lot of people right now everyone’s at the stage where they’ve not much to talk about seen as they can’t leave their homes and…you know…do interesting things. I am looking forward to being back swimming and for a pint at the pub (not together though).
In the meantime I put a fresh lick of paint on the blog. Enjoy.
(Well there is but you know what I mean.)
In the UK we have gone into Lockdown 3: The Winter Crisis so there’s not much Round Tabling, swimming or pubbing to be done until the COVID-19 virus is brought under control. Fortunately consultancy is back in full swing so the bills are getting paid.
Priority number one and two for 2021 will be to stay safe and get the vaccine respectively. As a child my parents would always tell me to play outside and avoid any drugs especially needles. In 2021 they’re telling me to stop inside and get a jab as soon as my turn comes up. How times change.
Over Twixmas I did try my hand at building a PiHole on a Raspberry Pi 3 Model B that has been lurking in my drawer but I scrapped it after a week. I felt the omission of DNS over HTTP/TLS by default is too important to overlook. I plan to build a WiFi 6 core network at my house sometime in 2021.
Meanwhile I’ve been discussing PC builds with some good friends of mine as we’re enjoying Cyberpunk 2077. I’m currently drawing up a parts list for my next custom build I’ve provisionally called Build-V. Yes that is indeed because it’s the 5th custom build I’m intending to put together and yes that’s where all that petrol money I’ve saved is going.
Not dead. Honest.
This week marks the end of the third week of isolation at home for me. As our technical work is primarily remote anyway we began this practice 1 week before the UK was put into lockdown. Now that situation is government mandated that means both myself and my colleagues are at home until UK.gov say so.
Not all people have the luxury of staying at home, getting paid and keeping out of harm’s way. I’d like to thank people in my local community for keeping me running during the lockdown: Haigh’s Farm Shop, the three local Co-Op stores in Mirfield, the local Hermes delivery driver and I’m sure many more to come. It’s far too easy to take people for granted and I hope that at least one positive outcome of this lockdown is that we learn to appreciate what these people do for the community.
In terms of work nothing’s really slowed down for me as of yet. I still have upcoming projects to plan, projects going live, live-running systems that need some technical assistance. Should that dry up the plan will be to do some level of self-training to top up our skills. I haven’t had the time to even think this through yet but I suppose brushing up on SQL Server, T-SQL and maybe even learn some Python for fun in my spare time.
It has certainly been interesting watching the sudden adoption of “WFH” (Working From Home) practices. There will of course be long-term ramifications of the COVID-19 pandemic and not every change inflicted will be positive. I’ve certainly not missed the mass-migration that is the 9-5 rush hour and hopefully new working practices can begin the long and arduous process of finally killing that off. Surely there will be many environmental, social, health and economic reasons for that.
Stay safe and keep observing lockdown.