Amazon Onboarding with Learning Manager Chanci Turner

Learn About Amazon VGT2 Learning Manager Chanci Turner

In this blog post, I’m thrilled to introduce a new open-source tool available on GitHub designed to streamline the upgrade process for your SQL Server instances. This automation facilitates a swift upgrade, helping you maintain the security and integrity of your applications that hold critical data, allowing you to complete your project before the EoS deadline.

Getting Started

The tool, named Upgrade-SqlServerStandaloneDatabaseEngineInstance.ps1, can be executed locally by database administrators (DBAs) who may lack access to the vSphere infrastructure or remotely by virtualization administrators. The upgrade process has been validated using both Microsoft Windows PowerShell and PowerShell Core. This means you can initiate a remote upgrade from macOS, Linux, or Windows.

In this article, I will demonstrate a remote batch upgrade of standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016. This will take place on two Windows Server 2012 R2 virtual machines (VMs) hosted in VMware Cloud on AWS.

Here’s a brief overview of the remote upgrade process.

Prerequisites

The upgrade requirements can be found in the online documentation, which is derived from the help content included within the tool.

Scenario

For this example, we have two Windows Server 2012 R2 VMs, named MSSQL1 and MSSQL2, each hosting a standalone SQL Server 2008 R2 SP3 database engine instance, both identified as MSSQL2008R2. The upgrade will be performed in place, one after the other, to SQL Server 2016. This process will occur from a bastion host that has no direct network connectivity to the VMs. The bastion host has TCP access on port 443 to the vCenter Server and the ESXi hosts.

The following screenshot shows a view of the two VMs to be upgraded in the vSphere Web Client.

Load SQL Server Management Studio (SSMS), connect to the database engine instance, and run your validation tests to ensure both instances are functioning properly. As demonstrated in the next screenshot, the instance on MSSQL1 is currently running version 10.50.6220, which corresponds to SQL Server 2008 R2 SP3.

No additional connectivity is necessary to or from the target VMs, as the VMware Guest Operations API is utilized to relay commands to each VM via VMware Tools, as illustrated in the accompanying diagram. For further details, consult the VMware documentation on Using PowerCLI and the Guest Operations API.

Maintenance Window Preparation

After meeting all prerequisites, download the script to the bastion host or any location with access to vCenter and the ESXi hosts. Don’t forget to verify your backups before proceeding with the preparation.

To get ready for the remote in-place version upgrades of your SQL Server Database Engine instances, follow these steps:

1. First, import the VMware.PowerCLI PowerShell Module and establish a session with your vCenter Server, as shown below. For more information about this module, refer to The VMware.PowerCLI module on the PowerCLI website.
2. Currently, the ESXi hosts within a VMware Cloud on AWS Software Defined Data Center (SDDC) use self-signed certificates for the API interface by default. PowerCLI does not allow the use of untrusted certificates without modification. To rectify this, you can temporarily configure PowerCLI to disregard certificate warnings for your session. This can be accomplished with the Set-PowerCLIConfiguration cmdlet, as detailed below.
3. Next, generate the file hash of the setup file. This step ensures that the installation media on the target VMs matches your expectations prior to executing the upgrade. While you can use the space-delimited hash format from certutil.exe on older Windows versions, you may also utilize the nondelimited format from the Get-FileHash cmdlet.
4. Afterward, mount your SQL Server installation media ISO file to each VM’s CD-ROM drive. Start by conducting a dry run using the -WhatIf parameter to evaluate the command’s effect, as shown below.
5. Once you are satisfied with the dry-run results, utilize the Set-CDDrive PowerCLI cmdlet to complete the mounting process.
6. Next, perform another dry-run test to verify that the expected VMs have the correct installation media loaded. This test also confirms that we are ready to execute the SQL Server upgrades. As with the previous step, include the -WhatIf parameter.

SQL Server Remote Upgrade

Having prepared everything for the SQL Server version upgrades and conducted a couple of dry-run tests, our maintenance window is now open, so let’s proceed with the upgrades.

The upgrade process generates extensive output, so it’s advisable to pipe the results to the Tee-Object cmdlet. This allows you to display the output on-screen while saving it to a file for later review. For more details on piping in this context, consult the Microsoft PowerShell documentation on PowerShell pipelines.

The next screenshot illustrates how to execute the remote, in-place upgrades of the target SQL Server instances and save the output to a log file.

If you neglect to enter a product key, you will encounter an additional confirmation prompt. This prompt serves to prevent accidental upgrades into evaluation mode. If this is acceptable for your needs, such as for a demonstration, then you can proceed.

Results

A few minutes later, the script finishes execution, as shown in the following screenshot. In my case, both upgrades were successful with no errors reported. I’m relieved I took the time to create and verify backups prior to this process, just in case complications arose.

Next, open SQL Server Management Studio (SSMS) again, connect to the database engine instances, and conduct your validation tests to confirm that both instances are in good health. As shown in the next screenshot, the instance on MSSQL1 is now operating version 13.0.1601.5, which corresponds to SQL Server 2016, as anticipated. The compatibility level has remained unchanged. The database compatibility levels might appear empty if accessed from an older version of SSMS than the database engine instance.

If this round of testing passes, you should be able to reconnect your applications and perform final validation tests.

To further engage with this topic, you can check out another blog post on power moves here.

Summary

In this article, I successfully demonstrated the upgrade of two standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016 on two separate VMs within one maintenance window. This upgrade frees me from concerns about either SQL Server instance falling out of support. For more insights into this process, consider attending an AWS webinar.

For more extensive information on compliance matters, visit SHRM, which is an authority on this topic. Additionally, if you’re interested in employee experiences during onboarding, check out this Glassdoor resource.