Something Went Wrong – A Crash Course in Providing a SQL Trace File

We’re all (probably) familiar with the error message Something Went Wrong. You might also receive a long string of numbers, get told to try again later and report the error to an administrator. These kind of messages aren’t really helpful by themselves.

Regardless of where this error is coming from you need to get a better understanding of what’s actually going wrong in order to be able to work out what to do to address it or even to determine if you need to do anything about it.

In SQL Server there are two features which can be used to provide in-depth information: the SQL Server Profiler and Extended Events. As a SQL Server and/or application administrator learning to use these tools is a critical skill in responding to application errors.

Before we go into any further depth it’s necessary to mention that SQL Server Profiler and also the SQL Trace events are marked as deprecated by Microsoft. This means that the features are no longer being developed and will be removed in future versions of SQL Server. That also means you should learn Extended Events for future reference.

From professional experience however many application vendors we work with will still request an SQL Server Trace file (with a .trc extension) so if you need such a trace to add to a support ticket read on.

A Basic SQL Server Trace

Before any trace is started on the SQL Server instance please be aware that this activity can…will place a significant strain on server resources. This could either be the amount of disk space required for the capture or the impact to processing performance. With this in mind you should try to organise an SQL Trace session when you have either exclusive access to the application database or that you know that it will be a quiet time (i.e. after regular office hours). If you are doing this for the first time definitely practice on a test/training environment before working on production.

The settings I’ll go through here are to be considered a basic trace only. If you are being asked to provide a trace file you should always first check with the software author or your DBA asking what events they need to see in the trace. You can then either open their trace template or use the instructions below to select the trace criteria they require.

Please note that the following was performed on SQL Server 2022 with SQL Server Management Studio (SSMS) 20.2. The SQL Server Profiler has very much been the same with all prior versions so don’t expect any major deviations in the instructions.

Step 1: Launch SQL Server Profiler from either the start menu or from SQL Server Management Studio from Tools > SQL Server Profiler. Login with a user that has permissions for ALTER TRACE by clicking Connect.

Step 2: On the first tab – General – we need to setup the basics of the trace:

Give the trace a suitable name.
For a template use Standard (default) for now.
Check Save to File. This will then prompt a save location.
- Set maximum file size (MB): up this to 100 MB
- I recommend leaving Enable file rollover checked. This will create multiple trace files as necessary.

Step 3: On the second tab – Events Selection – you will now select the events you want to capture. As we selected the Standard (default) template we have a set of pre-selected events to work with already. I suggest adding a few more in order to get a slightly more useful trace:

Click Show all events and Show all columns to see the full list.
Select the additional rows using the checkbox to the left of the event:
- Errors and Warnings
  - User Error Message
- Stored Procedures
  - SP:Completed
  - SP:Starting
Finally uncheck Show all events to only display what events you have selected. Have a little review to make sure the ones suggested have been selected.

Ideally we should filter the trace events to those for the required database(s) only. This can be done by clicking the Column Filters button. On the left scroll to find the DatabaseName filter. Expand the Like operator on the right of the Window then type in the name of the database you want to Trace for. Click OK once you have your databases listed.

Step 4: Click run to start the trace. You will see the trace window appear with events being logged. Don’t worry if the events are flying past too quick. The SQL Server Profiler by default will continuously scroll to the bottom.

Notice that there is also now a trace file saved to the path you specified in Step 2.

Step 5: Either get your colleague to replicate the problem in the application or follow their replication steps yourself. In this example I captured doing a simple select statement from AdventureWorks.

At this point note the two highlighted buttons on the screenshot below. The left “stop” button (Stop Selected Trace) will end the trace and stop SQL Server Profiler from capturing events. The right button (Auto Scroll Window) will stop the window scrolling if you need to quickly study a series of events you have noticed whilst keeping the trace capturing new events.

Once you have finished live analysis make sure to click the Stop Selected Trace button. As mentioned SQL Server Profiler running a trace will have a significant performance impact to the SQL Server instance so don’t leave it!

The aforementioned trace file can now be securely transferred to the software author or you can re-open it later and review it any time you want.

In the SQL Server Profiler window you’ll no doubt see many different events and also captured text in the bottom half of the Profiler window. By studying what the SQL Server engine is doing we can begin the process of troubleshooting problematic or unexpected application behaviour. Whilst the level of information in an SQL Server trace will be comprehensive it’s necessary to take the time to study it properly.

Conclusion

In this blog post we’ve learned to create a basic trace file in SQL Server. Whilst only a basic trace additional events can be captured as well as additional filters specified to help us understand application behaviour when an issue is reported.

If you need further support Digital Incite and Matter can not only help create the requested trace file but we can also work with your software provider to manage the incident case from diagnosis to patch deployment. Please get in touch with us today for further assistance.

Performance Tuning SQL Server Queries

Let’s imagine that you’ve just come back from summer holidays. You’ve been away travelling, enjoying life and having a relaxing time. Crash down back to work in September (sadly) and you’ve been asked to write some T-SQL based queries. Maybe it’s for a new dashboard component, report lines or even a new view. Somehow that query isn’t performing as you or a colleague expect. Panic sets in as you look blankly at the query and think “what do I do?!?!”.

Don’t fret; performance issues with T-SQL queries happen. Most of the time it’s an issue that can be fixed very quickly. If you find you’ve gone a bit rusty over summer here’s five quick tips to help you troubleshoot those queries:

Tip 1: Check For Obvious Offenders

Quite often you’ll find your issue by re-reading your T-SQL code and making some adjustments. Common causes could be:

Avoid Using SELECT *. Whilst this is sometimes OK to get an understanding of the table contents what it does result in absolutely everything in the table being retrieved. Not only is the full set of data unnecessary but you may also be impacting other queries executing simultaneously on the instance.
Check for complex joins. Occasionally a table join will introduce a complex operation that SQL Server must complete to get the matching rows. Check that your joins are appropriate for the data you want to select.
Consider using the WITH(NOLOCK) hint on live OLTP databases. This prevents queries from locking the table and blocking other queries. This may result in dirty reads so watch out!
Not obvious where the query is going slow? Break the query down bit by bit and re-introduce statements one at a time until you find the offending part of the query.

Tip 2: Check the Query Execution Plan

An automatic go-to for understanding how SQL Server is working at the engine level is to include the Execution Plan. In SQL Server Management Studio (SSMS) you can enable this option on the menu bar at the top.

SQL Server Management Studio showing how to enable Execution Plan

Once your query has been executed and completed the actual query execution plan will be displayed as a tab next to the results window. Within the execution plan check for expensive operations, missing indexes and also any other pertinent issues that might be causing your problem. Some execution plans end up quite in depth so take some time to study each part of the plan.

Once you have an understanding of you SQL Server is trying to perform your query you can then go make adjustments to your T-SQL or work with your DBA on the potential bottlenecks the execution plan helped you identify.

Tip 3: Check Indexes

In SQL Server Indexes are used to help the database engine perform the most efficient lookup of data possible. Having indexes in place is imperative to a production database especially where the number of records becomes substantial. Without indexes the database engine must perform more complex lookups of the data held in a table which takes longer to complete versus tables that have appropriate indexes designed.

Check the execution plan for Index Scans and Seeks. Index Scans are more intensive as they mean that the whole table is being looked up whereas an Index Seek means only the matching records. An Index Seek is generally preferable except in certain circumstances where there are a large numbers of matching records in a table. You will also see a warning box at the top of the execution plan if SQL Server has identified a missing index that may have helped the query.

Tip 4: Index Defragmentation and Statistics Update Jobs

As discussed in the previous tip indexes and statistics are important in maintaining adequate database query performance. Over time the indexes on a table will become fragmented and the statistics become out of date. This will gradually harm performance over the course of time. If you find your existing queries are getting slower, slower and slower then this is a probable cause.

Check the system view sys.dm_db_index_physical_stats for your particular database. If the avg_fragmentation_in_percent value for your indexes are running high check on the SQL Server instance that an Agent job or maintenance plan is in place to perform a regular index reorganise or an index rebuild for heavily fragmented indexes.

Depending on how the SQL Server instance is configured statistics may be updated automatically however there should also be an SQL Server Agent job or maintenance plan to update index and/or column statistics on a regular basis as appropriate.

Tip 5: Use the Query Store

The Query Store is a useful feature that was introduced in SQL Server 2016. It is not enabled by default unless you are working with SQL Server 2022 and have created a new database. Not only does the Query Store contain a number of useful reports that help you understand how queries are performing in your SQL Server instance but from SQL Server 2017 it enables the Automatic Tuning functionality.

If the Query Store is not enabled then it may be enabled on a per database via SQL Server Management Studio or T-SQL like so:

ALTER DATABASE <my_database>
SET QUERY_STORE = ON (WITH OPERATION_MODE = READ_WRITE)

Once the Query Store is on you need to let it run for a while to let it capture sufficient data. Whilst this is going on take some time to review the Query Store for information on query regressions and potentially make adjustments to the query accordingly.

Once you have captured sufficient data you can then enable automatic tuning on your database like so:

ALTER DATABASE <my_database>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Still Having Issues?

If you find yourself still having performance issues then perhaps it’s time to bring in a consultant to help you out. Digital Incite and Matter are proficient at query writing and optimisation. If you issue lies beyond the obvious then we can also advise on the infrastructure, instance configuration and expanded troubleshooting.

Get in touch with us today and we’ll be pleased to assist you further.

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.

Compatibility Is Key!

This will be the final semi-regular post of the year so before I get into the subject I’d like to say a brief thanks for your readership over the year!

This week I have been focused more on the platform side of things: organising updates on our VMWare ESXi host with our data centre and preparing for an upcoming attempt at Cyber Essentials certification. In other words not that much on SQL Server this week but still had a dabble when called for.

It has come to my attention that you can’t licence SQL Server 2016 or 2017 through distributors any more. You’ll have to pardon my ignorance on licensing as through my main job we don’t do the SQL Server licensing. Either there’s already an SQL Server there or a new one is deployed.

I recently did a rapid-fire upgrade for a client which called for a move of an existing application from Windows Server 2012 R2 and SQL Server 2012 infrastructure. Because of the age of the application running there which wasn’t to be upgraded the design called for Windows Server 2016 and SQL Server 2016 to be used as these were the highest supported. Unfortunately the client’s IT were not able to source SQL Server 2016 from their distributors any more.

This is a nice segue into SQL Server compatibility levels, what they are and what they are used for and how that could save us.

Every database connected to a SQL Server instance has a compatibility level set. By default for a new database it will be the compatibility level for that particular version of SQL Server that’s been installed (unless the instance has been upgraded but that’s a different matter). The database compatibility level is used by the Database Engine to control what specific SQL Server behaviours and changes are used for that database. If you want to know more then Microsoft have that in their documentation if you fancy a detour.

The official word from Microsoft is that you certify an application against the database compatibility level and not the SQL Server instance version. In other words it’s valid to have a SQL Server 2022 (160) instance and connect databases with a SQL Server 2014 (120) database to it with a corresponding compatibility level to maintain backwards compatibility. Your application vendor might begrudgingly disagree with that policy though so check in with them first.

Examining and Changing the Database Compatibility Level

In SQL Server Management Studio the easiest way to determine the database compatibility level is by right clicking your target database > properties > Options tab. The current compatibility level is displayed in the window.

SELECT [name],[compatibility_level]
FROM sys.databases

You’ll notice that the compatibility level is not 2022, 2019, 2017 etc but a number corresponding to the product version of the SQL Server instead.

If you determine that a compatibility level change is required you can set a new level using the following command:

ALTER DATABASE [MyDatabase]
SET COMPATIBILITY_LEVEL = 160 | 150 | 140 | 130 | 120 | etc

You should note that you may experience query regressions when moving up compatibility levels. The usual reminder to test carefully before deploying to production counts here!

Working With Constraints and Problematic Data

The challenge this week concerns constraints. In an SQL database constraints are used to enforce referential integrity on the data held within it. For example you can use foreign keys to ensure that a sales order in a database always has a reference to a valid customer ID in a customers table or use a table constraint to enforce that a purchase order number must be entered (i.e.: does not have a NULL value).

One thing you can do with a SQL database which is a bit naughty is disable the checking of the data on a constraint. Useful if you need to rework some underlying data in a pinch. Hold that. It’s not. What that does is store the problem up for later and probably for someone else to work out.

Constraints and foreign keys are there for a good reason which is to make sure your data is…you know…referential.

We have a software product that – when moved up to the cloud – now requires that referential constraints are enforced. As part of the upgrade procedure with the “designer” edition of the software (the environment that the data has to be upgraded to before getting shipped off to the cloud in this case) the consultant needs to enable an option in the software which effectively builds a SQL statement to do it across the database.

Sadly when that was enabled all hell broke loose. Well. Many foreign key violations at least. the software itself did not report any issues enabling trusted constraints but another tool which checks the database for correct preparation did.

After some SQL Profiler tracing I found 5 instances where data had escaped the foreign key constraint that was written. This happens usually when someone disables the constraint using the NOCHECK option, changes the data as they see and then re-enable the constraint with the CHECK option.

When you find a constraint violation within a SQL database you can query the problematic data by using the following command.

DBCC CHECKCONSTRAINTS (<constraint_name>)

To check every constraint on the database – either enabled or disabled – use the following but be aware that this may take some time.

DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS

Once I then identified and resolved all the data issues I was then able to take the easy route and have the software enable all the constraints within the database and pass the upgrade check.

Discussing Authentication Modes in SQL Server

I have been on holiday for the past two weeks but just before I left the subject came up with a client regarding authentication methods in SQL Server. Whether you are logging in using SQL Server Management Studio (SSMS) to make queries or configuring an application to work with SQL Server the authentication method is important to consider.

By authentication we mean a process a user must complete to gain access to a system. SQL Server roles and rights fall under authorisation – determining what access a user has in a system – which I’ll discuss another time.

When you install a fresh SQL Server instance on Windows you’ll be prompted regarding the authentication mode to have it in:

Windows – Default and enables Active Directory authentication only.
Mixed – Enables both Active Directory and SQL Server’s built in authentication method

You also get to specify what AD users and groups to add to the sysadmin role. When I do this for clients I make sure to identify the users or DBA groups to add. Adding the domain admins group I would advise avoiding unless as a last resort because given that sysadmin has read permissions on data in all databases there should still be a separation of roles between an IT admin and someone in an organisation who has a both requirement & trust to access that information.

If you change your mind later on after the install you can switch between modes in the instance properties in SSMS or using the registry.

I recommend if possible that an SQL Server instance uses Windows Authentication. This is because it’s simply put the most secure form of authentication of the two.

Windows Authentication makes use of an organisation’s existing Active Directory user directory. This mode is always active and is often referred to as “Trusted”. I’ll not go into the exact workings of Active Directory authentication in this post but the critical point is that SQL Server is not performing the authentication itself. Instead the authentication is done against Active Directory by passing back authentication tokens. A slight downside would be that this creates a dependency on a working and redundant Active Directory Domain Services setup (read: ideally two servers with AD replication working and healthy).

When using an SQL Server authenticated login this will use SQL Server’s own authentication scheme which is much weaker than Active Directory. An example of how much weaker this is would be that in the communication between the client and SQL Server instance you’ll find the password sent in plain text.

Special Note On The sa Account

There’s a default account in SQL Server which by default and on installation is named ‘sa’. This is the most powerful account in any SQL Server instance and has the SID 0x01.

I often get asked the question either in handover or in a support query about the sa account and my answer is always that I don’t use it. Applications, MSPs and consultants that ask for this account lose a few points for doing so. Using the sa account to configure an application or for general use is an absolute no.

I highly recommend leaving the sa account disabled. It’s also recommended to rename this account as an attacker can usually assume there is an account named sa on the instance to try for.

As a safer alternative giving users named logins is highly recommended for accountability purposes instead of a shared account.

Note that if you install SQL Server in Windows Authentication mode the sa account is still created but with a random password and left disabled.

Database Configuration Checks With The sys.databases View in SQL Server

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.

Making Yourself A Quick Backup in SQL Server

A nice a and simple one to share this week. Often when giving SQL Server consultancy I have to make backups at points before I’m doing something hazardous in a client’s database. This might be patching or running a script for example. It’s best to take your own backups just in case something doesn’t go to plan.

I occasionally see people fumbling around the GUI of SQL Server Management Studio to do backups and when there’s multiple databases to do it can really get clunky. There’s an argument to say that often it’s better to learn to script rather than work through GUIs. I certainly agree. Learning to do things in SQL Server via T-SQL commands means you can save them for later and also save a lot of time.

Your most basic command to backup a SQL database is as so:

BACKUP DATABASE [MyDatabase] TO DISK = N'Backup_Mydatabase.bak' WITH COPY_ONLY, COMPRESSION, STATS= 10

That’s really it for you GUI fans.

Let’s break this down further and look at the arguments after the BACKUP DATABASE command:

TO DISK = N’Backup_Mydatabase.bak’ – instructs SQL Server to make the backup to the disk with the name as specified. In SQL Server 2008 R2 and above the backup path can be seen in SSMS by right click the instance name > Properties > Database settings tab and note the path for Backups right at the bottom. Prior to (if I remember correctly) SQL Server 2008 R2 this backup path needed to be specified in full. You can specify a full path if you want to backup elsewhere but you need to ensure that SQL Server has permissions there.

COPY_ONLY – this option instructs SQL Server to only make a copy of the database and not record it in the backup chain. What this means is that if you have a disaster and want to recover then the backup you have just taken isn’t considered the last full backup in the chain. You should consider using this option for copying databases to another server and also for backups outside the normal backup routine.

COMPRESSION – applies compression to the backup. Leaving this off will mean SQL Server will follow the server’s default setting. I have the habit of doing this because unplanned backups take space somewhere and adding unplanned backups can tip the balance if you’re not careful. This being said you should always check space before making backups and restores.

STATS = 10 – this is a useful argument to add on as in the messages tab of SQL Server Management Studio this will give you a progress message at every interval which is a multiple of the number you have specified. So for 10 for example it will say when it’s reached 10%, 20%, 30% and so on up to 100%. This is handy to know as the backup progresses so you can estimate how far off completion you are.

Why Managing Transaction Logs in SQL Server Is Important

This past week I had a situation arise where the SQL Server transaction logs for a particular site grew out of control. This ended up consuming an entire disk volume for the log files which eventually caused the SQL Server to stop processing and by extension caused application downtime. This can be a common cause of unplanned downtime for an application when the transaction log was not managed correctly. Just what is the transaction log in SQL Server for and what do you need to know about it?

What is the transaction log for?

In SQL Server each database consists of two types of files: the first is the data and the second is the log file. Each database may have one of each or multiple data and/or log files.

The database file contains the tables, views, functions, indexes, stored procedures, etc. Basically all the objects in the database. This file normally has the extension .mdf.

The log file on the other hand contains a record of transactions that have occurred in the database. The transaction log file is important to consider for many reasons including performance, data integrity, disaster recovery and high-availability. This file normally has the extension .ldf for primary log files or .ndf for secondary logs.

Note a common myth is that in SIMPLE recovery mode the transaction log is not switched off. All operations are recorded in the transaction log whether that be FULL or SIMPLE mode. The difference is in SIMPLE recovery mode once the transaction is committed into the database then the log is truncated.

Why is it important to manage the transaction log?

The most important answer is that the transaction log forms part of the disaster recovery plan of any SQL Server database. If a disaster occurs then backups of the transaction logs will be required to restore to the last known good point in time.

An immediate unwanted effect of uncontrolled transaction logging is that the log file will continuously grow if autogrowth is enabled. The risk is that this can eventually grow to consume the entire disk volume the transaction log occupies. If SQL Server cannot grow the log file of the database then the transaction it is trying to process will fail.

Why and when should I use SIMPLE or FULL transaction logging?

When deciding on the most appropriate transaction log the decision should be focused around the recovery requirements and acceptable loss window.

For example if you have a live ERP system you should consider FULL transaction logging so that you are able to restore the database using transaction log backups if a failure occurs after a full or differential backup of the database is done.

On the other hand if you had copied the same database into a temporary test system and you are not using the test environment with intention it should be a perfect replica of live then consider using the SIMPLE transaction logging method.

Generally use SIMPLE:

Test/training databases.
Data warehouses.
Settings databases (i.e: lists of usernames, application configuration where the data does not change frequently)

Generally use FULL:

Production databases for live OLTP applications.
UAT and Pre-Production systems where there is a requirement to mimick the live environment configuration exactly for proving purposes.

You should also consider switching a production system with databases in FULL recovery mode to SIMPLE on a temporary basis whilst you are performing an application upgrade or patch which updates the database. This is to avoid the transaction log growing out of control during the operation. After finishing the upgrade the recovery mode should be switched back to FULL.

Footnote: Switching between recovery modes.

Stop all dependent applications for the databases.
Take a full backup of the databases you are switching the transaction log for. Safety first.

--Set the database to single user mode so that further transactions are stopped.
ALTER DATABASE [ExampleDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

--Switch the recovery mode
ALTER DATABASE [ExampleDatabase] SET RECOVERY FULL | SIMPLE 

--Finally set the database back to multiple user mode.
ALTER DATABASE [ExampleDatabase]  SET MULTI_USER

You can then start any application services that depend on these databases.

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.