Choosing a SQL Server Upgrade Method - Part 1

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


Problem

You are planning to upgrade your SQL Server to the latest version. Which SQL Server upgrade method will you choose? There are many options available and some will make sense in your business settings and others will not.  What are the different types of upgrade methods and which one should I select?

Solution

The method you choose to upgrade your SQL Server boils down to two business objectives: either your SQL Server upgrade requires as little downtime as possible or it requires the least amount of expenditure or you want both; cheaper and faster.

There are many ways you can go about upgrading your SQL Server. The method you chose will determine how much risk you are willing to take. For example, one method will upgrade your system much faster without much risk to your data and save time on your application downtime; meaning you are saving time, but you are spending more on your hardware and licensing costs. The other option provides a cheaper solution and also can go faster, but there is a risk that if anything goes wrong you cannot roll back. One is expensive and the other is cheaper and riskier. One option requires a big application outage and the other does not.

There are pros and cons of each method, therefore choosing the correct method of upgrade, depending on your business situation, is very important.

The following are the most common options used in the industry to upgrade or migrate SQL Server instances:

  1. Backup and Restore
  2. Attach and Detach
  3. Differential Restore for larger databases
  4. In-place upgrade
  5. Side by side upgrade (without High Availability)
  6. Rolling upgrade (with High Availability)

SQL Server Upgrade vs. Migration

Let me clarify the terms migrating and upgrading in scope of SQL Server. I have seen these terms to be used interchangeably. But there are some differences.

Upgrading SQL Server can mean you are upgrading only an Edition of SQL Server. Maybe you are upgrading from SQL Server 2014 Standard Edition (SE) to SQL Server 2014 Enterprise Edition (EE). Upgrading also means maybe upgrading the Version of SQL Server, say for example from SQL Server 2012 (EE) SP4 to SQL Server 2016 (EE) SP2. Or you are upgrading the Edition and Version. This will be the situation if you are upgrading from SQL Server 2012 (SE) to SQL Server 2017 (EE). Moreover, whenever you apply Service Packs (SP) or Cumulative Updates (CU) to your SQL Server as a part of your regular maintenance, you are actually doing a mini upgrade.

Migration on the other hand, may or may not include an upgrade of SQL Server, but it means moving your databases from one instance of SQL Server to another. Maybe you are just doing a hardware and OS refresh of your SQL Server. Or maybe there are just too many databases on one instance of SQL Server and maintenance job executions are overlapping business hours. You want to split the load. You spin up another instance of SQL Server on the same VM machine or request a new separate VM. Then you install SQL Server and divide and move some of the databases to the new instance. It could also mean you are moving your databases from on-premises to the cloud, such as Azure. Finally, migration can also include upgrading of SQL Server.

In short, an upgrade refers to a SQL Server instance upgrade which will result in the upgrade of user and system databases. An upgrade can be part of database migration. Migration, on the other hand, refers to moving of databases between instances of SQL Servers either on the same Version\Edition of SQL Server or upgraded Version\Edition of SQL Server.

In this tip, we are going to discuss first 2 methods in the list above.

SQL Server Upgrade using Backup and Restore

This is the most basic and simplest method of upgrading your databases during migration. Depending on resources on your virtual machine (VM), generally, databases of sizes under 50 GB are good candidates for this method of upgrade.

Scenario: You are planning to upgrade and migrate your SQLDev instance from SQL Server 2012 (Developer Edition) to SQL Server 2016 (Developer Edition). You have 10 Dev databases on your SQLDev instance. You want to upgrade them on a new SQL instance SQL2016. The VM machine name is SQLVM1. You installed SQL Server 2016 (DE) on SQLVM1\SQL2016. Each of the dev databases are less than 50 GB. You chose to do the upgrade using the Backup and Restore method.

Execution: Here we are taking very simplistic approach and skipping many details that go into planning of any upgrade project. Since this is a dev environment, application downtime duration is not an issue. These are minimum steps we are going to take to migrate and upgrade dev databases.

Create a job “Do Backup” in advance of the upgrade, with the script to backup each database on the SQLVM1\SQLDev instance.

BACKUP DATABASE [Db1] TO DISK = N'C:\MSSQL\SQLBackups\Db1.bak' 
GO
BACKUP DATABASE [Db2] TO DISK = N'C:\MSSQL\SQLBackups\Db2.bak' 
GO
backup job step

Create a job “Do Restore” in advance of the upgrade, with scripts to restore each databases on the SQLVM1\SQL2016 instance.

RESTORE DATABASE [Db1] FROM  DISK = N'C:\MSSQL\SQLBackups\Db1.bak' 
GO
RESTORE DATABASE [Db2] FROM  DISK = N'C:\MSSQL\SQLBackups\Db2.bak' 
GO

Run job “Do Backup” on SQLVM1\SQLDev to backup databases.

Run job “Do Restore” on SQLVM1\SQL2016 to restore databases.

After verification of successful database upgrade, uninstall SQL server 2012 instance SQLVM1\SQLDev. Rename SQLVM1\SQL2016 instance as SQLVM1\SQLDev.

EXEC master.sys.sp_dropserver @server = 'SQLVM1\SQL2016 ';  
GO  
EXEC master.sys.sp_addserver @server= 'SQLVM1\SQLDev', @local = local;  
GO 
SELECT @@SERVERNAME

SQL Server Upgrade using Detach and Attach Database

This is another basic and simple method of upgrading SQL Server databases during migrations. It is not a recommended best practice method. Basically, it does the same thing as “Backup and Restore” method. It is much safer and reliable to do an upgrade with “Backup and Restore” for databases with smaller sizes.

Scenario: Your QA database instance SQLQA is sitting on very old physical machine and you want to retire the machine. There are 8 QA databases of sizes less than 30 GB. Application downtime duration is not important, but you have to do it in a 1 business day, so QA can be back in business the next day. You chose to use the Detach and Attach method. You want to move your SQL instance to a new VM, SQLVM2 that your infrastructure team created for this purpose. You do not want to upgrade the SQL Server Version or Edition.

Execution: Again, we are omitting many of the steps of a successful migration in order to keep the examples simple. These are the minimum steps we need to take.

Install SQL Server instance SQLQA2 on SQLVM2 machine.

Issue a Detach command for each database on SQLQA.

USE [master]
GO
ALTER DATABASE [DB1] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
EXEC master.dbo.sp_detach_db @dbname = N'DB1'
GO

Copy the .mdf, .ndf and .ldf files to the new location on SQLVM2 machine.

Run an Attach command for each database on SQLVM2\SQLQA2 instance.

USE [master]
GO
CREATE DATABASE DB1  
    ON (FILENAME = 'C:\SQLBackups\DB1_Data.mdf'), (FILENAME = 'C:\SQLBackups\DB1_Log.ldf')   
    FOR ATTACH;

After verification of successful migration, shut down the physical server SQLQA and decommission it after an appropriate time.

The reason this is not a recommended way to migrate your databases is because you can run into various types of issues. For example, if there is a SQL Server collation difference between the source and destination SQL Server, your attach command may fail. If the directory where you copied your database files on the new server does not have proper permission for the SQL Server service account, it might fail. Another big reason is if some time has elapsed, say a day or two, between the times you have detached a database and trying to attach, files can be deleted by mistake. If server reboots, files might get corrupted.

Summary

In this tip, we discussed different ways in which we can do database migration and\or upgrade. We studied 2 of the most basic methods of database migrations using some business scenarios. We also discussed slight difference in definition of upgrading versus migration in SQL Server environment.

Next Steps
  • Read more tips on Backup and Restore database.
  • Read more tips on Attach and Detach database.
  • Stay tuned to Part 2 of this series.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

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

View all my tips


Comments For This Article




Monday, August 27, 2018 - 1:06:09 PM - Ameena Lalani Back To Top (77311)

 Nadesan, Your comment makes sense. Altering databases to read-only will make sure that nobody can connect to them while you are doing the backups. And after restoring to new SQL instance, altering databases back to read-write will make sure no data is lost. Since this was dev instance example, I did not go into such details to keep it simple.

Monday, August 27, 2018 - 10:51:17 AM - Sureindran Nadesan Back To Top (77310)

I will setup the current database to READ_ONLY and then do the backup. Retoring the database to  new server will not cause of any loss of data. After testing the restored database, set it to MULTI_USER setting. Shut down the older server. Turn on the new server. No data is lost.














get free sql tips
agree to terms