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.
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.
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.
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:
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.
Microsoft have announced this week that future versions of Windows will disable TLS (Transport Layer Security) 1.0 and 1.1 by default. These ageing cryptographic protocols are designed to secure traffic over a network. The move is a bid to improve the security posture in Windows by ensuring that only newer versions of TLS are used between client and server applications.
TLS 1.0 and 1.1 were standardised in 1999(!) and 2006 respectively. They were both deprecated in 2021 via RFC 8996. Although Microsoft claims that no known unpatched exploits exist in the Schannel implementation newer versions of TLS offer much better security. With older versions of TLS a number of bodies have mandated that these older versions should be avoided. For example as the Payment Card Industry (PCI) have deprecated their use since 2018. There are a number of security flaws with both TLS 1.0 and 1.1 which means that we can no longer rely on them for securing traffic.
In addition all major browsers have dropped support for anything prior to TLS 1.2 since 2020. As with all things in computing security it’s best to be ahead rather than behind. There shouldn’t be any browsers and OSes out there that are still supported and can’t use at least TLS 1.2. I fully recommend keeping ahead with developments and plan accordingly to drop anything prior to TLS 1.2.
SQL Server and Applications Impacted
Although Microsoft believe that usage of deprecated versions of TLS are low via their telemetry it would be wrong to simply assume that you can turn off TLS 1.0/1.1 and job done.
If you aren’t sure about how this will impact your business it’s time to start with a review of your applications and how they will be affected. Soon there will be Windows desktops out there that definitely don’t support older versions of TLS out of the box. Whilst Microsoft have stated that you can re-enable TLS 1.0 and 1.1 via the Schannel registry keys in the meantime you absolutely shouldn’t bother with doing so. There’s a reason things move on. Microsoft will at some point do the right thing and completely remove deprecated versions of TLS from the operating system. Putting off the problem won’t solve anything long-term.
Possibly the most direct way this affects SQL Server based applications is indeed the front-end. Many applications now work via a web UI rather than a Windows application. This is perhaps where your investigations should start.
For internet facing applications you could run a test via Qualys which will produce a useful report on how your server is configured. Scroll down and you’ll see the projected impact regarding client browsers and OSes with what versions of TLS they might use.
Yes I do run this test against this blog.
If your applications are internal only it’s not wise to assume that your wires and airwaves are safe even if you own them. for these you can check the Schannel registry keys at the following location:
You can check individual protocols at this location to see if they are enabled or disabled.
Getting down to the SQL Server level things get more interesting. Support for TLS 1.2+ exists as of SQL Server 2016 so these versions are good to go out of the box.
For SQL Server 2014 if you have these configured with an encrypted connection this version needs a cumulative update applying before it will support TLS. By now you really, really should have applied an update beyond the version this was introduced anyway. for any new deployments of SQL Server 2014 instances you might have to do remember to apply CUs after you’ve done the install.
For SQL Server 2008, 2008 R2 and 2012 things are arguably beyond the point with those releases as they are no longer supported. You can get yourself a hotfix to apply to those too but unless this is for an application that’s segregated away in some corner of the network for legacy purposes you’ve either got bigger things to worry about or another good reason to upgrade if this is a production system for some reason.
Potentially you’ll need to update the client driver if they use the SQL Server Native driver. Check with the application vendor for the system requirements for this.
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:
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:
Step 4: Run mssql-conf setup to configure your newly installed SQL Server 2022 instance:
sudo /opt/mssql/bin/mssql-conf setup
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.
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.
Next will be the language. I chose 1 for English but as you require for this.
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.
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
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).
Actually not a lot. The world is on a stop but I’m still working, running, playing.
So far I’ve done a heck of a lotta jobs around the house because it’s not like we can go many places in the UK at the moment. I have completed an introductory course on Infor OS and I’ve also started one on PowerShell which is going to be useful for all the setup tasks we have to undertake.
Hopefully back soon. Looking forward to open water swimming.
The big news this week in SQL Server is the seemingly un-notable release of SQL Server Management Studio 18.5. Scanning the release notes there’s a few new features added but no black theme to the chagrin of many in the SQL Server community it seems. There’s also quite a lot of fixes in this release which is always welcomed however I don’t think that the annoying multi-monitor bug that leaves the query window blank has been fixed as of yet.
If you’re running SQL Server 2017 anywhere there’s also Cumulative Update 20 to test in UAT.
For me this week I got the opportunity to have a self-training day for the company I work for and experiment with (the bizarrely named I must say) Infor OS. I’m not quite there yet with the install but I did decide on creating a Windows Server Core based domain controller and SQL Server to test these on my company issued laptop. I have never really tried server core and I’ve never been asked about it in nearly 4 years of consulting either. It wasn’t as difficult to work with as I first thought and it makes a whole lot of sense to have many cut down Operating System Environments (OSE in Microsoft speak) supporting a single application or role each as opposed to a fully loaded OSE running absolutely everything. It also surely must have been easier on my 4 core, 16GB Laptop as well given that I ended up with 4 Virtual Machines to work on.
Oddly enough nothing much to report on this week. It’s been mostly about planning, fixing and patching. Nothing wrong with that every so often.
Of note Cumulative Update 2 for SQL Server 2019 was posted this week with a notably long list of fixes. Not had any deployments of SQL Server 2019 so far but looking forward to doing so. The memory optimised TempDB feature will likely be extremely beneficial for the applications that I deploy and my clients will surely benefit greatly for it.
It’s been a busy few (many) months at my consultancy job handling the influx of work that has come in. This is thanks to the retirement of Windows Server 2008 & 2008 R2 and also Windows 7. All of those releases were really solid ones (for Windows 7 perhaps the last great Microsoft desktop OS?) and even though they have been surpassed they were very capable OSes for their time.
In saying that it’s most definitely time to move away from them now that extended support has run out. A lot of my time recently has been an effort to get clients from the legacy OS based builds onto something newer. For some clients that means upgrades of the business applications as well as new server builds, for others patching their current applications before moving it to a new server and for the fortunate few just a patch to install. Things like this exemplify the reasons why it’s important in IT to maintain systems and plan for end of support. There is arguably more cost incurred by doing nothing then having to do essentially a project in order to end up with a supported set of OS and applications versus constant planned maintenance.
For the next few months we are concentrating on implementing a new version of the business intelligence tool we resell. It has moved from a direct database connected Excel add-in to one that communicates to a server over HTTP/S. There are many pros and cons to this change and we continue to explore these as we implement with our clients. This will surely keep us busy for a while.
This week in SQL Server largely was the release of Cumulative Update 19 for SQL Server 2017 which I am sure will find its way to a UAT or support server near me soon.
