Tag: SQL Server

Now That We’ve Got a Cumulative Update…Updating SQL Server on Linux

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-sources

You 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 update

This 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-server

This 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-pager

As 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!

But What If I Wanted A Specific Version of SQL Server?

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-server

Where <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.

Weekend Project: SQL Server 2022 on Ubuntu

There’s something I’ll have to admit to: I don’t (didn’t) have an SQL Server test instance to play with. Gasp! I broke apart my Windows desktop earlier this year, sold all the parts and then remembered how am I supposed to play with SQL Server without it? Well it did save some desk space…

This weekend it came to the top of my task list to build another so for this brief tutorial I’m going to show you how to install SQL Server 2022 onto an Ubuntu Server. Yes that’s right; no Windows involved.

Pre-requisites

  • A fully setup Ubuntu Server 20.04 running on either a spare PC/server, Virtual Machine, cloud service, basically somewhere. Hell they even run Doom on pregnancy tests these days so I’m sure you can find something to run it on.
  • SQL Server 2022 requires a minimum of 2 GB of system memory so spec/configure as appropriate.

Once you have your shiny new Ubuntu Server you should then use SSH to connect into the environment and let the fun begin.

Step 1: Import the GPG keys for the Microsoft repository. This means that you can trust the repo that you’ll download SQL Server 2022 from:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

Step 2: Register the Microsoft repository with apt. This means that you add the address of the Microsoft repository into apt’s list it uses to check for and download software & updates. This is where you’ll actually download SQL Server 2022 from:

sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)"

Step 3: Update apt’s list of available updates then instruct apt to install SQL Server 2022. Note the -y switch after the install argument means that you are telling apt to do all this automatically i.e.: yes to all prompts. This will trigger a download around 1.3 GB in total so warm up that internet connection:

sudo apt-get update
sudo apt-get install -y mssql-server

Step 4: Run mssql-conf setup to configure your newly installed SQL Server 2022 instance:

sudo /opt/mssql/bin/mssql-conf setup
  1. You’ll first get asked for the edition. I chose number 2 for Developer as I want to use this build as a test/training server. Whatever edition you choose do make sure you are using an edition you are appropriately licenced for. You’ve been warned.
  2. Following choosing an edition you’ll get asked to accept the licence terms. I know you’ll read them fully and very carefully but make sure you type “Yes” to accept.
  3. Next will be the language. I chose 1 for English but as you require for this.
  4. After that you’ll get prompted to specify an sa password. Make sure you choose something secure and record it securely especially if this is production.

Step 5: Confirm that the service is running. It would be most definitely disappointing if it weren’t:

systemctl status mssql-server --no-pager

You’ll get something resembling this which confirms that SQL Server 2022 is running:

If you’re new to SQL Server on Linux then something you need to be aware of is that you can’t run SQL Server Management Studio (SSMS) on Linux. As an alternative you can go try Azure Data Studio which has the Admin Pack for SQL Server extension pack available that includes tools for Profiler, SQL Server Agent, Import and for working with .dacpac files.

Once you are connected to the SQL Server instance then one thing to note if you run the T-SQL command @@VERSION you’ll get something like this:

Microsoft SQL Server 2022 (RTM-CU7) (KB5028743) - 16.0.4065.3 (X64)  	Jul 25 2023 18:03:43  	Copyright (C) 2022 Microsoft Corporation 	Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

Specifically the point here is that Cumulative Updates (CUs) in SQL Server on Linux are handled differently than Windows. On Ubuntu Linux to take an example CUs are delivered using apt. This is unlike Windows where you have to download and install the cumulative update that you want or accidentally let Windows Update install one if there’s a security fix that needs applying. This means that the release you get by default from the repository is the latest one.

So, congratulations. You’ve made it this far. If you don’t like reading this blog (why?) then you can always follow Microsoft’s direct instructions on the setup above but then you’ll have to admit I made you scroll all the way down here to find this link.

Bonus Round! Restoring AdventureWorks Sample Data

Now you have your newly installed Ubuntu Server with an SQL Server 2022 instance you’re going to need some test data in there (unless you’re straight into production in which case bon voyage!).

To do this you can use wget to bring in the SQL Server 2022 edition of the venerable AdventureWorks databases.

--OLTP
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorks2022.bak

--Data Warehouse
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2022.bak

--Lightweight
wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksLT2022.bak

This should download the databases into your Home directory if you haven’t done a cd out of there.

You can then use Azure Data Studio to restore the databases if you have preview features turned on. If you don’t then you can restore using the following T-SQL:

--OLTP
USE [master]
RESTORE DATABASE [AdventureWorks2022] FROM DISK = N'/home/<user>/AdventureWorks2022.bak' WITH  FILE = 1, MOVE N'AdventureWorks2022' TO N'/var/opt/mssql/data/AdventureWorks2022.mdf', MOVE N'AdventureWorks2022_log' TO N'/var/opt/mssql/data/AdventureWorks2022_log.ldf', NOUNLOAD,  STATS = 10

--Data Warehouse
USE [master]
RESTORE DATABASE [AdventureWorksDW2022] FROM  DISK = N'/home/<user>/AdventureWorksDW2022.bak' WITH  FILE = 1, MOVE N'AdventureWorksDW2022' TO N'/var/opt/mssql/data/AdventureWorksDW2022.mdf',  MOVE N'AdventureWorksDW2022_log' TO N'/var/opt/mssql/data/AdventureWorksDW2022_log.ldf', NOUNLOAD, STATS = 10

--Lightweight
USE [master]
RESTORE DATABASE [AdventureWorksLT2022] FROM DISK = N'/home/<user>/AdventureWorksLT2022.bak' WITH  FILE = 1, MOVE N'AdventureWorksLT2022_Data' TO N'/var/opt/mssql/data/AdventureWorksLT2022.mdf', MOVE N'AdventureWorksLT2022_Log' TO N'/var/opt/mssql/data/AdventureWorksLT2022_log.ldf', NOUNLOAD, STATS = 10

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.

  1. 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.
  2. 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.
  3. 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.

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).

Some Things I Never Knew In SQL Server

Over the course of the last couple of months I’ve come across some things in SQL Server that I’ve never really thought about.

“..” implies the default schema for A user.

If you have a user in a database with say a default schema of “Production” writing this:

SELECT ProductCode, ProductName, ProductColour
FROM CompanyDB..Products

Actually implies the Production schema.

The TempDB can really cause problems if it’s not configured correctly.

More of a reminder this one. I’ve recently worked on a client who was having some performance issues with a reporting tool. When they ran extracts from a SQL database it wasn’t very fast. I tried it myself and agreed it wasn’t performing as well as it should.

The reporting tool is heavily driven by cursors but let’s not get started on those. I looked in Activity Monitor and noticed quite a lot of contention on the TempDB. A further investigation revealed that there was a total of 1 TempDB for a Octa-core server.

After adding a further 7 TempDB row and log files we found that the reporting tool ran much much quicker and the contention was gone.

You can alias without using “As”

This one probably isn’t “best practice” but you can write:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers AS C

As:

SELECT C.CustomerID, C.Name, C.Address1, C.City
FROM Customers C

I don’t like doing this because to me that’s breaking a general principle of keeping script readable plus the reason I’ve never known about this is because all the T-SQL books and resources I’ve read have NEVER mentioned this is correct syntax so they must also consider it a no-no.

You stand a good chance of recovery if your server doesn’t boot but you’ve still got the SQL data.

I recently helped a client that lost a SQL data warehouse server to a Windows update that somehow caused it to not boot. They managed to pull all the files off the volumes so we had all the user databases as well as master and msdb databases.

The client was on a tight budget so we raced against time to get the server going as quick as possible. I was surprised to see that simply dropping in the master and msdb databases over the ones installed on the new server (shut the SQL Server instance down first) and putting the user databases at the original location restored pretty much all settings and data.

Of course you’ll run into problems if there was some encryption in use and you’ll need to use the same SQL Server release but other than that it proved a seemingly OKish way to restore the client’s data warehouse quickly.

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.