Learn About Amazon VGT2 Learning Manager Chanci Turner
If you’re still managing Microsoft SQL Server 2008 and 2008 R2 instances, now is the perfect moment to initiate an upgrade. The end of support (EoS) date for these versions is swiftly approaching—July 9, 2023. Post this date, no further security updates will be available, posing significant security and compliance risks. Don’t delay any longer!
Today, I’m thrilled to introduce a new open-source tool available on GitHub that will assist you in automating the upgrade process for your SQL Server instances. With this automation, you can quickly execute the upgrade, safeguard your application’s security posture that handles sensitive data, and complete the project well before the EoS deadline.
Getting Started
The tool is named Upgrade-SqlServerStandaloneDatabaseEngineInstance.ps1 and can be found on GitHub. It can be initiated locally by database administrators (DBAs) who may not have access to the vSphere infrastructure or remotely by virtualization administrators. The upgrades have been tested with Microsoft Windows PowerShell and PowerShell Core, allowing for remote upgrades from macOS, Linux, and Windows.
In this article, I will demonstrate a remote batch upgrade of standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016, specifically on two Windows Server 2012 R2 virtual machines (VMs) running in VMware Cloud on AWS.
Prerequisites
The upgrade requirements are outlined in the online documentation, which mirrors the help content built within the tool, as illustrated in the attached screenshot.
Scenario
For this example, we have two Windows Server 2012 R2 VMs named MSSQL1 and MSSQL2, both running standalone SQL Server 2008 R2 SP3 database engine instances, which are identified as MSSQL2008R2. These instances are upgraded sequentially to SQL Server 2016. The process is executed from a bastion host that lacks network connectivity to either VM but has TCP access on port 443 to the vCenter Server and the ESXi hosts.
Before proceeding, load SQL Server Management Studio (SSMS) and connect to the database engine instance to perform validation tests to ensure both instances are in good health. As expected, the instance on MSSQL1 should show version 10.50.6220, indicating it is indeed SQL Server 2008 R2 SP3.
No additional connectivity is required to the target VMs, as the VMware Guest Operations API is utilized to send commands to each VM via VMware Tools, as depicted in the accompanying diagram. For further insights, you may want to check out this blog post about the importance of maintaining security.
Maintenance Window Preparation
Once you’ve satisfied all prerequisites, download the script to the bastion host or any machine with access to vCenter and the ESXi hosts. It’s crucial to double-check your backups before commencing preparation.
To get ready for the remote in-place upgrades of your SQL Server Database Engine instances, follow these steps:
- Import the VMware.PowerCLI PowerShell Module and establish a session with your vCenter Server.
- Since the ESXi hosts in a VMware Cloud on AWS Software Defined Data Center (SDDC) utilize self-signed certificates by default, PowerCLI does not allow untrusted certificates. You can bypass this restriction temporarily by allowing PowerCLI to ignore certificate warnings for the session using the
Set-PowerCLIConfigurationcmdlet. - Next, obtain the file hash of the SQL Server installation media. This ensures that the setup file on the target VMs meets expectations before running the upgrade. You can use the space-delimited hash format generated by
certutil.exeor the nondelimited format from theGet-FileHashcmdlet. - Then, mount your SQL Server installation media ISO file to each VM’s CD-ROM drive. Start with a dry run using the common
-WhatIfparameter to simulate the command’s effect.
When satisfied with the dry-run results, use the Set-CDDrive PowerCLI cmdlet to proceed with the mounting. Following this, conduct another dry-run to confirm that the correct installation media is loaded on the intended VMs.
SQL Server Remote Upgrade
Now that we’ve prepared everything for the SQL Server version upgrades and completed several dry-run tests, it’s time to proceed with the upgrades during our maintenance window.
The upgrade process generates extensive output, so it’s wise to pipe it to the Tee-Object cmdlet. This allows you to view the output on the screen while also saving it to a file for later review. For detailed guidance on piping in this context, refer to this resource.
If you forget to enter a product key, the script will prompt you for confirmation, as depicted in the screenshot. This acts as a safeguard against unintentionally upgrading your instances into evaluation mode. If this scenario aligns with your needs, such as for a demonstration, you can proceed.
Results
After a few minutes, the script concludes, indicating that both upgrades were successful without any errors. I am grateful for the time spent on generating and verifying backups, as they are crucial in case complications arise.
Next, reconnect to SQL Server Management Studio (SSMS) and perform validation tests to confirm that both instances are operating smoothly. The instance on MSSQL1 should now reflect version 13.0.1601.5, indicating a successful upgrade to SQL Server 2016, as anticipated. The database compatibility levels may display as blank if you connect from an older version of SSMS.
Should these tests pass, you can reconnect your applications and conduct your final validation checks.
Summary
In this post, I showcased the successful upgrade of two standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016, all completed within a single maintenance window. With this upgrade, I can now rest easy knowing that neither of these SQL Server instances will fall out of support. For further information, you can explore this excellent resource dedicated to leadership development and training.
Leave a Reply