It’s that time of the IT lifecycle again, where we talk upgrades and decommissioning.
On the 12th July 2022, Microsoft SQL Server 2012 will go End of Life. Not just to extended/security patch support, but completely out of support.
So, why am I talking about Veeam? If you’ve got an installation of Veeam that utilised a SQL Server Express database, and you installed Veeam prior to 9.5 Update 4, odds are you’ve got a SQL Server 2012 Express database. This creates a security risk as of the next Microsoft patch cycle, scheduled for the 12th July 2022.
I would also like to state that whilst this isn’t the focus of the article, if you’ve got a Veeam Backup & Replication or Veeam ONE environment that’s not running v11 yet, you really need to upgrade, as everything prior is at end of fix.
Which version of SQL Server should I migrate to?
Unless you are limited by the operating system support for SQL Server 2019, I don’t recommend going for anything older. This is because SQL Server 2017, whilst still in mainstream support for now, enters extended support in October 2022, mere months away. SQL Server 2022 is only in public preview, making it a poor candidate for production platforms at present.
How can I upgrade my SQL Server instance?
In two words, you can’t. In more words, you can’t perform an ‘in-place upgrade’. We’ll need to install the newer edition of SQL Server, either on a new server, or alongside your existing SQL Server instances, and then migrate the data across.
With that out of the way, lets start with installing the new SQL Server edition.
eUpdate 25/06/2022]
Maybe I over-simplified this point and created confusion, there are scenarios whereby in-place upgrades are supported for SQL Server by Microsoft. But it is far more complex than running an installer and a guarantee all will be okay.
For example, Microsoft here have a process flow guidance chart, detailing common scenarios and whether or not in-place upgrade is supported.
On paper it sounds great. But it doesn’t take long to realise that in-place upgrades are likely not going to be possible for a lot of users anyway as they’re probably going to have to perform some level of migration efforts.
SQL Server 2012 is only supported on Windows Server OS’ up to Windows Server 2012 R2. With SQL Server editions up to and including SQL Server 2017, supporting this operating system. SQL Server 2019 requires Windows Server 2016 or newer, so an in-place upgrade between 2012 and 2019 doesn’t become possible, because one of the two products would be operating on an unsupported operating system.
With Veeam only starting to utilise SQL Server 2016 Express in VBR 9.5 U4, which was released in the beginning of 2019, odds are good that SQL Server 2012 Express was being installed on Windows Server 2016 and existing in an unsupported state. But if we wind the clocks back to Veeam Backup & Replication 9.5 GA, this was released in November 2016, one month after Windows Server 2016 became available.
There’s a fair amount of unknown between these whether an installation may have been using unsupported SQL Server 2012 installation, on a Windows Server 2016 OS, in which case an in-place upgrade COULD work, but we also need to remember that theoretically this may not be supported as SQL Server 2012 in any context wouldn’t be supported on WS 2016 in the first place.
The alternate scenario would be that SQL Server 2012 was installed on Windows Server 2012, in which case we’d have to look at either migrating to a SQL Server installation either already in extended support or about to be (2014-2017), or we’d need to look at a new OS & SQL Server 2019 instance, due to the point I mentioned above about SQL Server 2019 not supporting Windows Server 2012/2012 R2. Which if you’re running Windows Server 2012/R2, you’ve got until October 2023 before the OS is out of support anyway, so a modernisation makes sense for both OS & SQL at the same time.
nEnd of Update]
Stage 1: Installing & Patching SQL Server 2019 Express
To begin, download the SQL Server 2019 Express Edition from here. You’ll want to pair this download with the latest update available for SQL Server from here.
Installing SQL Server 2019
Lets start by running the SQL Server 2019 Express Edition Setup executable.
Make sure to use custom installation mode, basic mode will not allow you to name your database instance, choose where you wish to place the instance, nor will it let you specify the accounts that require sysadmin privileges to the database, forcing you to use tools such as SSMS or SQLCMD later to make the appropriate changes.
After selecting the “Custom” installation mode you’ll be asked where you want to download the SQL Express edition media.
Once downloaded we’ll be prompted to choose our required task, we want to deploy a new SQL Server instance, so we’ll choose “New SQL Server stand alone installation or add features to an existing installation”
We’ll then be prompted to accept to license terms (I’m sure I can trust you all to read these thoroughly).
You’ll then be asked if you wish to enable Microsoft Update, this doesn’t circumvent our need to install the SQL Server CU, but it does sometimes help deliver SQL Server security updates.
Once you optionally agree to Microsoft Update you’ll then be able to see the core installation files being installed.
Afterwards you’ll see SQL Server performs an install rule check, to ensure that the SQL Server is being installed in a supported state without any known issues, I have a warning that I have Windows Firewall enabled, in my scenario I’m installing SQL Server Express edition onto the same VM, if you’re installing it elsewhere, be sure to enable connections to TCP port 1433 from the IP address(es) of your Veeam B&R or Veeam ONE servers.
We’ll then be asked what features to install, by default it wants to install a huge amount of features that aren’t required. You only require “Database Engine Services” itself, not the child options such as SQL Server Replication or the Machine Learning Services and Language Extensions. Leave the shared features as default.
At this point you can also choose to install the instance root or shared feature directories to different locations. It is generally recommended to deploy SQL Server databases to their own dedicated disks (with the database files and log files on separate drives to each other, but that’s configured later).
Now, the moment you’ve been waiting for, naming the instance! I’ve chosen “VEEAMSQL2019” to best match how Veeam name their instances by default, no ambiguous characters or funny business, it’s simple and to the point!
Under server configuration we can then define any service accounts we wish to run our services under, as this is a workgroup VM, I’ll leave this as NT Service.
Within the database engine configuration section, we can specify the accounts we wish to have SQL Server administrator access (sysadmin). We can also specify whether we want Windows Authentication mode only or mixed mode (whereby we can create SQL Server only accounts for database access).
You’ll typically require only Windows Authentication mode if you’re using dedicated service accounts, but if you’re using NT service accounts like me in this lab, you’ll want to use mixed mode. If you discover down the line you require mixed mode, it’s possible to change this at a later date, though this is out of the scope of this guide.
In this section you can also specify where to set your database, log and backup directories under the “Data Directories” tab and TempDB database and log directories within the TempDB tab.
Finally unless you installed additional features that require configuration, the installer will skip over the “Feature Configuration Rules” section and jump straight into installation progress. Wait for this to finish and you have SQL Server 2019 installed!
Patching SQL Server 2019
Now we have SQL Server 2019 installed, we need to upgrade to the latest CU to fix some of the numerous patches that SQL Server 2019 shipped with. To begin we start the executable for the latest CU that we downloaded earlier.
Once it unpacks, we’ll need to accept the license terms once more.
Under the “Select Features” section we’ll be shown our SQL Server instances and asked which ones we’d like to update, I only have a single instance and the shared features, so I’ll update it all at once.
SQL Server will then check to see if any files are in use, provided you haven’t started using your instance yet, you’ll pass this fine.
You’ll then be told you’re ready to update, click update and wait for it to finish installing!
Success!
Stage 2: Migrating Veeam Databases
Veeam Backup & Replication
To migrate the database itself, we’ll leverage the configuration database backup functionality built into Veeam Backup & Replication. Veeam have this process documented comprehensively already, available here.
Veeam Backup Enterprise Manager
To migrate the Veeam Backup Enterprise Manager database but leave Veeam Backup Enterprise Manager in-place, Veeam have a detailed KB article, explaining this process.
Veeam ONE
To migrate the Veeam ONE database to a new SQL Server, Veeam have created another KB article, detailing this process.
In addition to the database migration instructions, Veeam ONE has some maintenance tasks that are created as part of the initial installation, that will need to be recreated onto the new database. As the necessity of these tasks depends on the build of Veeam you’re using, please read the notes and then refer to KB articles KB1954, KB4175 and be sure to download the index defragmentation maintenance job from the original KB article linked.
Stage 3: Validation & Decommissioning
Before we decommission the legacy databases, we need to perform some functionality testing, for all the database instances that have been migrated, stop their old SQL Server service, and then perform basic connectivity tests, to ensure Veeam is communicating to the new databases.
Once this has been completed, we can proceed to the uninstallation of SQL Server 2012. To do this, access Programs & Features from the Control Panel, navigate to “Microsoft SQL Server 2012”, and uninstall. The installer will ask you which instance(s) you wish to uninstall, as it’s possible to install multiple instances onto the same server. Provided you don’t have any other applications to migrate, you can instruct it to uninstall all instances remaining, and the shared components.
Afterwards you’ll have some left over applications to uninstall that are no longer needed by SQL Server, or Veeam.
Below is an image showing all of the applications installed in a Veeam Backup & Replication v11 instance, utilising a SQL Server 2012 installation.
Compared to below, an image of a Veeam Backup & Replication v11 instance, utilising a SQL Server 2016 installation.
With these installations compared, we can see that we need to uninstall the following packages:
- Microsoft SQL Server 2012 (64-bit)
- Microsoft SQL Server 2012 Setup (English)
- Microsoft SQL Server 2012 Transact-SQL ScriptDom
- Microsoft VSS Writer for SQL Server 2012
- SQL Server Browser for SQL Server 2012
To start, uninstall Microsoft SQL Server 2012 (64-bit). When prompted, choose to uninstall all instances and the shared components. Once this is complete, refresh your programs and features, then remove any applications that may have been left behind from the installer.
Conclusion
Microsoft SQL Server 2012 did bring some great new features, but it’s time to remove the application from your environment, Veeam recommend a minimum of SQL Server 2016, hence this is the bundled SQL Server Express instance. Whatever version of SQL Server you replace SQL Server 2012 with, be sure it’s supported, from a patching perspective, and from a compatibility perspective with hardware & operating system. See you in 2026 when we start discussing getting rid of your SQL Server 2016 instances. Unless you migrate to PostgreSQL, available from v12 for Veeam Backup & Replication and Veeam Backup Enterprise Manager, once generally available.
