SQL Server Upgrade Methods In-Place Upgrades and Differential Restore Upgrades - Part 2

By: Ameena Lalani   |   Comments (2)   |   Related: > Upgrades and Migrations

Problem

Choosing an upgrade method for your SQL Server upgrade or migration project is not an easy one. It depends on so many variables in your environment that you have to step back and approach it in a holistic way. It has to be a combined decision of both the business team and the technical team. Suppose your databases are larger than 50 GB and you also have budget constraints. In this case, which methods of upgrading SQL Server are suitable?

Solution

In previous tip, we discussed different ways in which we can do a database migration and\or upgrade. We studied two of the most basic methods of database migrations using some business scenarios; the Backup and Restore method and the Detach and Attach method. We also discussed differences in the definition of upgrading versus migration in a SQL Server environment.

In this tip we will look at two more upgrade methods of SQL Server.

1. Differential restore for large databases
2. In-place upgrade

SQL Server Differential Restore to Upgrade

Scenario: Suppose you have a large database, say 500 GB, in Simple recovery mode. You do a full backup weekly on Sunday night and the other 6 nights you do differential backups. This database is on SQL Server 2012 (Source). You have another SQL Server that has SQL Server 2017 instance (Destination) and you want to upgrade and migrate this large database. These two SQL Servers are in different data centers.  Your network bandwidth between the 2 data centers is 10 GB. The only available window to do the migration is on Saturday morning and the expectation is to complete the migration with testing afterwards within 2 hours. The SLA does not allow any more time.

Execution: We are assuming you have done your due diligence and testing of this upgrade beforehand.

1. You are a diligent DBA and you did your testing a couple of weeks before the actual migration. This is how one week of testing backup, copy and restore looks. Your plan is to do as much work beforehand as possible, before the actual migration.

2. You setup a file copy job to run after the full backup job on Sunday night. Monday morning you verify that backup file is available on the destination server.
3. Scheduled restore with no Recovery of full backup Monday night at the destination server.
4. From Tuesday to Friday, scheduled jobs to copy and restore with no recovery of differential backups at the destination server.
5. Saturday morning at 7 AM, an hour before the maintenance window, you started the last differential backup of database at the source server. Since you have calculated so meticulously, you timed it perfectly and the backup finishes at 8 AM. You immediately make the database read-only, so you don’t lose any data. Your maintenance window has started and you have 2 hours to finish your work.
6. Then you manually copy the last differential file to the destination server and restored it with Recovery.
7. Changed the compatibility mode to the latest and the database got upgraded to a new SQL Version.
8. You connected your application to point to this upgraded database. Tested your application.
9. Completed your task within 2 hours and application went live on new a database back end.
10. Started full backup job on the destination server.

This method of using differential restore works in simple cases, when complexity is low, but the database is large. Other upgrade methods such as” Backup and Restore” and “Detach and Attach” will not work because they will not meet the 2-hour SLA of application down time.

One observation on a roll back strategy. In step # 8, if the application test had failed, you just would have to change the database back to Read-Write mode on the Source server and connect your application. Do a smoke test and you are back in business. From a rollback perspective this method is a superstar.

SQL Server In-place Upgrade

This method cannot be used for migrations because all actions are happening on the same server and therefore it is called an “In Place Upgrade”. This is the cheapest and fastest upgrade option. But on the other hand, it is the most risky upgrade method and hence it is not recommended for your production SQL Server upgrade.

The problem is you are upgrading the existing SQL instance directly and if anything goes wrong, you will not be able to roll back. All the system and user databases’ system objects get upgraded. The only option will be to restore the database backups on a different server with your old SQL Server version with the exact same Service Packs and Cumulative Updates. Companies that have a low budget and cannot spend money for hardware and software licenses will opt for these drastic measures. But in the case of a failed upgrade, going back to their old version will cost more and will also take more time. Another caveat is that during an in-place upgrade you cannot add any additional SQL Server features. You have to do it after the installation is complete.

Scenario: Let’s take an example of a company XYZ technologies. The company is doing a proof of concept with their vendor on their new product. For that purpose, they need to test the vendor application on SQL Server 2012, SQL Server 2014 and SQL Server 2016. XYZ technologies have limited resources so they decide to install SQL Server 2012 first and install all the vendor tool databases and do complete testing. Then do in-place upgrade to SQL Server 2014 and do the same tests and repeat with SQL Server 2016.

Execution: This method saves XYZ Company time in doing the backups and restore of databases. With this method there is no need to script logins, jobs and linked serves etc. If something breaks during one of the upgrades, they don’t care. They will rebuild the server which will wipe out everything and they will start over. At a minimum, you will execute the following steps.

1. Apply all the Windows update\patches to an existing Windows server.
2. Install SQL Server 2012 and all Service Packs.
3. Connect to vendor tools and it creates all the required databases.
4. Run the tests and document.
5. Run SQL Server 2014 installation wizard and select the option to upgrade in-place. You will get several warnings.

6. Apply all the Service Packs
7. Change database compatibility Level.
8. Run tests and document.
9. Run SQL Server 2016 installation and select the option to upgrade in-place. Ignore the warnings.
10. Apply all the Service Packs
11. Change database compatibility level
12. Run tests and document.

Like this example, there may be some other use cases of an In-Place Upgrade method, but they are very few. Mostly you can do that in your testing environment to see how different SQL Server features work under different versions.

Summary

Hopefully, I was able to inspire you to look into these upgrade methods in more detail and test and play with various settings as a part of your disaster recovery strategy. All tools are built in SQL Server and you don’t need any 3^rd party tools to do these types of upgrades. The more you test, the more confident you will become in your normal day to day activities as well as any emergencies.

Next Steps

• Read this tip to understand advantages and disadvantages of using Differential Backups in your backup strategy.
• Here is a different take on in-place upgrade method.
• Look for Part 3 of this series.

About the author

Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips