AWS Database Blog

Upgrade your end-of-support SQL Server instances in VMware Cloud on AWS with ease

If you still have Microsoft SQL Server 2008 and 2008 R2 instances deployed, now is the time to upgrade them. Microsoft end of support (EoS) date for each is almost upon us—July 9, 2019. This means that after that there are no further security updates, which has security and also compliance implications, so don’t wait!

Today, I’m excited to share with you a new, open-source tool on GitHub to help you automate the upgrade of your SQL Server instances. With this automation, you can rapidly run the upgrade, maintain your security posture for your application storing critical data, and wrap this project up before EoS.

Getting started

The new tool is named Upgrade-SqlServerStandaloneDatabaseEngineInstance.ps1, and available on GitHub. It can be kicked off locally, such as by database administrators (DBAs) that might not have access to the vSphere infrastructure, or remotely, such as by virtualization administrators. Upgrades have been tested with both Microsoft Windows PowerShell as well as PowerShell Core. Thus, you can launch a remote upgrade from macOS, Linux, and Windows.

In this post, I perform a remote batch upgrade of standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016. I do this on two Windows Server 2012 R2 virtual machines (VMs) running in VMware Cloud on AWS.

Following is a quick preview of launching a remote upgrade.

Prerequisites

The upgrade requirements are detailed in the online documentation. The online documentation is built from the same help content built into the tool, as shown in the following screenshot.

Scenario

In this example, two Windows Server 2012 R2 VMs: MSSQL1 and MSSQL2, are running standalone SQL Server 2008 R2 SP3 database engine instances. Both of the instances are named MSSQL2008R2. These are upgraded in place sequentially to SQL Server 2016. This process is done from a bastion host that isn’t permitted network connectivity to either VM. The bastion host has TCP connectivity on port 443 permitted to the vCenter Server and the ESXi hosts.

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

Load SQL Server Management Studio (SSMS), connect to the database engine instance, and perform your validation tests to make sure that both are in healthy states. As shown in the following screenshot, the instance on MSSQL1 is running version 10.50.6220, or SQL Server 2008 R2 SP3, as expected.

No additional connectivity is required to or from the target VMs. This is because the VMware Guest Operations API is used to send the instructions to each VM through VMware Tools as depicted the following diagram. For more information, see Using PowerCLI and the Guest Operations API in the VMware documentation.

Maintenance window preparation

After you meet all of the prerequisites, download the script to the bastion host or wherever you have connectivity to vCenter and the ESXi hosts. Then, double‑check your backups and you’re ready to start preparation.

To prepare to perform remote in‑place version upgrades of your SQL Server Database Engine instances, take the following steps.

First, import the VMware.PowerCLI PowerShell Module and establish a session with your vCenter Server, as shown following. For more information on this module, see The VMware.PowerCLI module on the PowerCLI website.

At present, the ESXi hosts in a VMware cloud on AWS Software Defined Data Center (SDDC) use self‑signed certificates for the API interface by default. PowerCLI doesn’t permit use of untrusted certificates by default. To accommodate this, you can temporarily permit PowerCLI to ignore certificate warnings for the session. To do this, use the Set-PowerCLIConfiguration cmdlet, as shown following.

Next, get the file hash of the setup file. You use this to ensure that the installation media on the target VMs matches expectations before you run the upgrade. However, certain file properties are also evaluated to ensure that it is SQL Server installation media. You can use the space‑delimited hash format generated by certutil.exe on older versions of Windows. You can also use the nondelimited format generated by the Get-FileHash cmdlet.

The following screenshot shows generation of a file hash of the SQL Server setup file.

Next, mount your SQL Server installation media ISO file to each VM’s CD-ROM drive. To start, perform a dry run using the common -WhatIf risk management parameter to describe the effect of the command. The following screenshot shows this process.

When you’re satisfied with the dry-run results, use the Set-CDDrive PowerCLI cmdlet to perform the mount process.

Now, perform another dry‑run test to ensure that the expected VMs have the proper installation media loaded. This test also ensures that we are ready to execute the SQL Server upgrades. As in the previous step, you do this by adding the common -WhatIf risk management parameter. The following screenshot shows this process.

SQL Server remote upgrade

We have now staged everything for our SQL Server version upgrades and have performed a couple dry‑run tests. Our maintenance window is open, so let’s upgrade our instances.

The upgrade produces a lot of detailed output, so I recommend piping the output to the Tee-Object cmdlet. This cmdlet prints the output on the screen and also saves it to a file if you want to review afterward. For more information on piping in this scenario, see About PowerShell pipelines in the Microsoft PowerShell documentation.

The following screenshot shows how you run the remote, in-place upgrades of the target SQL Server instances and save the output in a log file.

If you forget to enter a product key, you receive an additional confirmation prompt, shown in the screenshot following. This prompt helps protect you from accidentally upgrading your instances into evaluation mode. If this is acceptable for your use case, such as for a demo, then proceed.

Results

A few minutes later, the script completes, as shown following. In my case, both upgrades did not report any errors. Still, I am glad that I invested time in generating and verifying backups in case there were complications.

Next, load SQL Server Management Studio (SSMS) again, connect to the database engine instances, and perform your validation tests to make sure that both are in healthy states. As shown in the next screenshot, the instance on MSSQL1 is now running version 13.0.1601.5, or SQL Server 2016, as expected. The compatibility level has not been modified. The database compatibility levels might appear as blank if you connect from a version of SSMS older than the database engine instance.

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

Summary

In this blog post, I demonstrate successfully upgrading two standalone SQL Server 2008 R2 SP3 instances to SQL Server 2016 deployed on two separate VMs in one maintenance window. With this upgrade, I no longer have to worry about either of these SQL Server instances falling out of support.

To learn more about this process, attend this AWS webinar.

Next steps to consider

  • Knock out your SQL Server 2008 and SQL Server 2008 R2 Database Engine instance upgrades quickly and before EoS with this tool that we created, hosted on GitHub.
  • Help improve this new open-source project by contributing a pull request.

Additional resources

For more background, see these topics in the Microsoft documentation:

About the Author

Troy Lindsay is a Solutions Architect at AWS facilitating the engineering partnership with VMware. He is a native Texan, husband of over 13 years to his best friend, and semi-pro wrangler of three wildlings.