AWS Database Blog

Best practices for upgrading SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server

In preparation for the deprecation of Microsoft SQL Server 2008 R2, this blog post focuses on how to upgrade from SQL Server 2008 R2 to SQL Server 2016 on Amazon RDS for SQL Server. In addition, you can apply many of the practices in this post to all versions of SQL Server or even other RDS engines.

Note: Currently, there is no upgrade path from SQL Server 2008 R2 to SQL Server 2017 in RDS. If you want to upgrade to SQL Server 2017, you first need to upgrade to 2012, 2014, or 2016.

On a self-managed platform, upgrading to the latest database version has a lot of risks associated with it. Many companies live by the motto “If it ain’t broke, don’t fix it.” Fortunately, many of the challenges you normally face on a self-managed platform are mitigated with the automation Amazon RDS provides.

For a SQL Server DB instance, upgrades come in two types:

  • Major version upgrade (for example, upgrading from SQL Server 2008 R2 to SQL Server 2012)
  • Minor version upgrade (for example, upgrading from SQL Server 2016 RTM to SQL Server 2016 SP1)

Amazon RDS for SQL Server releases new minor versions whenever the newest version released by Microsoft fixes bugs from previous versions or has security updates. (We skip some of the smaller releases if there are no significant updates.) To check which is the latest version, see the documentation. We highly recommend keeping up-to-date with the latest minor version.

For major versions, Amazon RDS in general supports a major version as long as the vendor (in this case Microsoft) supports them. Our customers expect Amazon RDS to maintain a consistent and reliable service. To help achieve this expectation, the Amazon RDS team never wants to have to choose between exposing a major security risk vs. maintaining the availability of a customer’s DB instance. Although Amazon RDS SQL Server has never deprecated a major version, the end of extended support for SQL Server 2008 R2 is July 9, 2019.

Important: Before taking the steps listed following, we recommend reading through the entire blog post first to make sure that you consider all of the best practices.

How to test and upgrade your instance

First and foremost, regardless of the platform or guarantees by any service out there, always test any changes to your database before implementing them on your production database. Testing on Amazon RDS is made very simple with all of the automation available, so you never have a reason not to test. With Amazon RDS, you can test through the AWS Management Console, AWS CLI, or RDS API. For simplicity, I use the console to upgrade my database in the example following.

  1. First, create a snapshot:
    • On the console, choose the instance you want to upgrade, and then choose Take snapshot on the instance Actions
  2. Restore the snapshot:
    • When the snapshot is complete, choose the snapshot and choose Restore snapshot on the Actions
    • For testing purposes, I recommend keeping all the instance specifications the same. If you need to, you can modify these when you upgrade your instance.
  3. Perform the upgrade on your snapshot:
    • When your snapshot has finished restoring, you can test the upgrade.
    • We recommend that you don’t allow write operations to the DB instance until you confirm that everything is working correctly.
    • In addition to all of your normal test cases, make sure to test all stored procedures and functions.
    • Under Instances, choose the restored snapshot and then choose Modify on the instance Actions When you modify a DB instance, you can make a few different updates beyond DB Engine Version, including the following:
      • DB Instance Class – You change the instance class to meet your compute or memory requirements.
      • Storage Type – You can change the storage type from Provisioned IOPS or General Purpose depending on your needs.
      • Enhanced Monitoring – I highly recommend enabling Enhanced Monitoring for production Instances.
    • Choose the database engine version that you want to upgrade to. In this case, I choose SQL Server 2016 13.00.4466.4.v1.
      Note: If you want to upgrade to SQL Server 2017 from 2008, you first need to upgrade to 2016, 2014, or 2012.
    • Make sure to choose Apply Immediately. Otherwise, your instance isn’t upgraded until the specified maintenance window.

    Before upgrading your instance, make sure to consider a number of things. Following is our recommended list of things to consider.

    Working with Multi-AZ instances

    When upgrading a Multi-AZ instance, RDS performs the upgrade on the standby instance first. It then performs a failover and then upgrades the old primary. Why is this important? For certain configurations, upgrading your database can reset it to its default settings.

    The best practice to help mitigate the risk with these configurations is to downgrade your Multi-AZ instance to Single-AZ first. You then reapply Multi-AZ to make sure that the secondary has the latest configurations from the primary.

    When you make this change, the secondary needs a certain period of time to fully populate with data. The volume is available immediately. However, to get the full allocated performance you need to wait until the volume warms up completely. When converting between Single-AZ and Multi-AZ, we recommend that you make this change three to four days before the actual upgrade. This timing gives the secondary more time to fully populate before the failover happens and it becomes the primary instance.

    You can’t replicate the Tempdb database on Multi-AZ instances. Any data that you store on your primary instances in Tempdb isn’t available on your secondary instances. During an upgrade, this functionality might cause problems for your application because Tempdb tries to autogrow based on the application activity.

    You have two options to mitigate this impact:

    1. Use the method mentioned earlier to modify your DB instance and turn Multi-AZ off. Then modify Tempdb, and finally turn Multi-AZ back on. This method doesn’t involve any downtime.
    2. Modify Tempdb on the original primary instance, then fail over manually, and modify Tempdb on the new primary instance. This method involves downtime.

    Provisioned IOPS – Suppose that your instance is on Single-AZ and you’re planning to move to Multi-AZ for minimal downtime before the upgrade. In this case, if you are currently using provisioned IOPS on Single-AZ and you are already close to the maximum I/O, we recommend doubling your I/O. We recommend this because your secondary instance needs additional resources to not have an impact on your primary’s performance. Because upgrades on Multi-AZ require a failover, inadequate I/O can lengthen failover times. Database recovery requires I/O!

    SQL Server Database Compatibility – To maintain compatibility with older versions of SQL Server while upgrading, set the compatibility level to the SQL Server version you currently use. For the commands and an overview of compatibility, see ALTER DATABASE (Transact-SQL) Compatibility Level in the SQL Server documentation. If you don’t change the compatibility of the database, you can’t use new features on the upgraded version. When performing an upgrade, RDS itself doesn’t change the compatibility level of the database.

    Option Groups – If your DB instance uses a custom option group, in some cases Amazon RDS can’t automatically assign your DB instance a new option group. For example, this is the case when you upgrade to a new major version. In that case, you must specify a new option group when you upgrade. We recommend that you create a new option group, and add the same options to it as in your existing custom option group. For more information, see Creating an Option Group or Making a Copy of an Option Group in the RDS documentation.

    Parameter Groups – If your DB instance uses a custom parameter group, in some cases Amazon RDS can’t automatically assign your DB instance a new parameter group. For example, this is the case when you upgrade to a new major version. In that case, you must specify a new parameter group when you upgrade. We recommend that you create a new parameter group, and configure the parameters as in your existing custom parameter group. For more information, see Creating a DB Parameter Group or Copying a DB Parameter Group in the RDS documentation.

    Migrating to RDS SQL Server – We offer a variety of different methods of migrating to Amazon RDS for SQL Server that can be found in the documentation. In addition, specific guides can be found in other blog posts, such as for native backup restores, ongoing replication with AWS DMS, and transactional replication.  After SQL Server 2008 R2 is no longer supported, you can still to migrate your on-premises database and lift and shift into RDS. We handle the upgrade based on the SQL Server instance that you provision, as described in the RDS documentation.

    Have other ideas or experiences with upgrading on RDS SQL Server? Post them in the comments!

    About the Author

    Justin Benton is the RDS Commercial Engines (SQL Server and Oracle) Product Manager at Amazon Web Services.