Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

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


By:   |   Last Updated: 2018-09-10   |   Comments (2)   |   Related Tips: More > 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.
Weekly Backup and Restore Info 
Day of Week Backup Time Backup Type Backup Size Backup Duration Copy Duration Restore Duration
Sunday 11:00 PM Full 500 GB 5 hours 2 hours 6 hours
Monday 11:00 PM Differential 50 GB 30 min 20 min 35 min
Tuesday 11:00 PM Differential 60 GB 35 min 22 min 42 min
Wednesday 11:00 PM Differential 70 GB 40 min 24 min 49 min
Thursday 11:00 PM Differential 80 GB 50 min 26 min 56 min
Friday 11:00 PM Differential 90 GB 55 min 28 min 63 min
Saturday 11:00 PM Differential 100 GB 60 min 30 min 70 min
             
Maintenance Window
Day of Week Start Time End Time        
Saturday 8:00 AM 10:00 AM        
  1. 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.
  2. Scheduled restore with no Recovery of full backup Monday night at the destination server.
  3. From Tuesday to Friday, scheduled jobs to copy and restore with no recovery of differential backups at the destination server.
  4. 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.
  5. Then you manually copy the last differential file to the destination server and restored it with Recovery.
  6. Changed the compatibility mode to the latest and the database got upgraded to a new SQL Version.
  7. You connected your application to point to this upgraded database. Tested your application.
  8. Completed your task within 2 hours and application went live on new a database back end.
  9. 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.
sql server warning message
  1. Apply all the Service Packs
  2. Change database compatibility Level.
  3. Run tests and document.
  4. Run SQL Server 2016 installation and select the option to upgrade in-place. Ignore the warnings.
  5. Apply all the Service Packs
  6. Change database compatibility level
  7. 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 3rd 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.


Last Updated: 2018-09-10


next webcast button


next tip button



About the author
MSSQLTips author Ameena Lalani 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.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, September 10, 2018 - 5:59:30 PM - Ameena Lalani Back To Top

Hi David,

Thanks for your comment. Please note this example is about 500 GB database in simple recovery mode. Differential backup starting at 7 AM on Saturday morning (step 5) will take about 60 minutes. Full and Differential backup includes all the committed transactions and read them till the backup completes. There will be few minutes of data loss from when the Differential backup completes around 8 am and you make the database read-only. This is the risk company is will to take by making the decision to keep this database is in a simple recovery mode. Maybe this is a staging or DW kind of database and not heavily transactional. I hope I am able to clarify the point.


Monday, September 10, 2018 - 5:56:39 AM - David Hutton Back To Top

 

 It Is my understanding that when you back up SQL Server, the back up is from the time that you started the back up not when the back up is complete is that not correct?  If so then you would lose changes that are made from 7am to 8am when you cut over.  You would need another diff or log backup at 8am. 


Learn more about SQL Server tools