| title | Upgrade SQL Server to SQL Server |
|---|---|
| description | Step-by-step guidance for modernizing your data assets |
| ms.date | 03/19/2021 |
| ms.prod | sql |
| ms.technology | migration-guide |
| ms.topic | conceptual |
| author | cawrites |
| ms.author | chadam |
As you prepare to migrate your SQL Server database to a later version of SQL Server, consider the versions of SQL Server that are supported and to address any prerequisites. This will help to ensure an efficient and successful migration.
This section describes all supported scenarios and options for an upgrade from and older version of SQL Server to a newer version of SQL Server, current as of March 2019.
#To be deleted - supported versions to be validated / reviewed.
Supported source and target versions are shown in the following list.
- SQL Server source versions
- SQL Server 2005
- SQL Server 2008, SQL Server 2008 R2
- SQL Server 2012
- SQL Server 2014
- SQL Server 2016
- SQL Server target versions
- SQL Server 2014
- SQL Server 2016
- SQL Server 2017 on Linux
- SQL Server 2017 on Windows
The following table details all available methods to migrate / upgrade your SQL Server:
| Upgrade option | Description |
|---|---|
| Backup & Restore | This is a simple and well-tested technique for moving databases across machines. Use compression to minimize backup size for transfer. You can use Transact-SQL (T-SQL) to backup databases using scripting. |
| Transactional Replication | Replicate data from source SQL Server database tables to target SQL Server database by providing a publisher-subscriber type migration option while maintaining transactional consistency. |
| Always On availability groups | Introduced in SQL Server 2012, Always On availability groups are a feature that provides an enterprise-level high-availability and disaster-recovery solution. The feature provides secondary replicas that support synchronous or asynchronous availability modes. |
| Azure Database Migration Service (DMS) | Azure Database Migration Service (DMS) is a fully managed migration service that help migrate databases to Azure at scale. |
| Data Migration Assistant (DMA) | Data Migration Assistant DMA assesses SQL Server on-premises and then seamlessly upgrades to later versions of SQL Server or migrates to SQL Server on Azure VMs, Azure SQL Database or Azure SQL Managed Instance. |
| Log Shipping | Log shipping replicates transactional log files from on-premises on to an instance of SQL Server on an Azure VM. This provides minimal downtime during failover and has less configuration overhead than setting up an Always On availability group. |
| Bulk load | SQL Server supports exporting data in bulk (bulk data) from a SQL Server table and importing bulk data into a SQL Server table or nonpartitioned view. Learn more on Methods for bulk importing and exporting data |
Note
It's now possible to lift and shift both your failover cluster instance and availability group solution to SQL Server on Azure VMs using Azure Migrate.
When upgrading from SQL Server 2005, the following migration options are supported.
| Target version | Options |
|---|---|
| SQL Server 2017 | - Migration tools: Migration is supported through Data Migration Assistant (DMA). - Backup and restore: A backup taken on SQL Server 2005 can be restored to SQL Server 2017. - Bulk load: Tables can be bulk copied from SQL Server 2005 to SQL Server 2017. |
| SQL Server 2016 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2005 can be restored to SQL Server 2016. - Bulk load: Tables can be bulk copied from SQL Server 2005 to SQL Server 2016. |
| SQL Server 2014 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2005 can be restored to SQL Server 2014. - Bulk load: Tables can be bulk copied from SQL Server 2005 to SQL Server 2014. |
When upgrading from SQL Server 2008 or SQL Server 2008 R2, the following migration options are supported.
| Target version | Options |
|---|---|
| SQL Server 2017 | - Migration tools: Migration is supported through Data Migration Assistant (DMA). - Backup and restore: A backup taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2017. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and mirror is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes principal, SQL Server 2008 or SQL Server 2008 R2 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and secondary is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes primary, SQL Server 2008 or SQL Server 2008 R2 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2017 |
| SQL Server 2016 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2016. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and mirror is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes principal, SQL Server 2008 or SQL Server 2008 R2 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and secondary is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes primary, SQL Server 2008 or SQL Server 2008 R2 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2016. |
| SQL Server 2014 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2008 or SQL Server 2008 R2 can be restored to SQL Server 2014. - Transactional replication: SQL Server replication from SQL Server 2008/2008R2 to SQL Server is supported. Replication upgrade options are outlined in detail in the blog here. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and mirror is running SQL Server 2014. If a failover, either automatic or manual, happens such that SQL Server 2014 instance becomes principal, SQL Server 2008 or SQL Server 2008 R2 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2008 SP3 or later, or SQL Server 2008 R2 SP2 or later, and secondary is running SQL Server 2014. If a failover, either automatic or manual, happens such that SQL Server 2014 instance becomes primary, SQL Server 2008 or SQL Server 2008 R2 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2008 or SQL Server 2008 R2 to SQL Server 2014. |
When upgrading from SQL Server 2012, the following migration options are supported.
| Target version | Options |
|---|---|
| SQL Server 2017 | - Migration tools: Migration is supported through Data Migration Assistant (DMA). - Backup and restore: A backup taken on SQL Server 2012 can be restored to SQL Server 2017. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2012 SP2 or later and secondary replicas are running SQL Server 2017. If a failover, either automatic or manual, happens such that a SQL Server 2017 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2012 SP1 or later and mirror is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes principal, SQL Server 2012 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2012 SP1 or later and secondary is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2012 to SQL Server 2017. |
| SQL Server 2016 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2012 can be restored to SQL Server 2016. - Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2016 is supported. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2012 SP2 or later and secondary replicas are running SQL Server 2016. If a failover, either automatic or manual, happens such that a SQL Server 2016 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2012 SP1 or later and mirror is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes principal, SQL Server 2012 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2012 SP1 or later and secondary is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2012 to SQL Server 2016. |
| SQL Server 2014 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2012 can be restored to SQL Server 2014. - Transactional replication: SQL Server transactional replication from SQL Server 2012 to SQL Server 2014 is supported. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2012 SP1 or later and secondary replicas are running SQL Server 2014. If a failover, either automatic or manual, happens such that a SQL Server 2014 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2012 SP1 or later and mirror is running SQL Server 2014. If a failover, either automatic or manual, happens such that SQL Server 2014 instance becomes principal, SQL Server 2012 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2012 SP1 or later and secondary is running SQL Server 2014. If a failover, either automatic or manual, happens such that SQL Server 2014 instance becomes primary, SQL Server 2012 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2012 to SQL Server 2014. |
When upgrading from SQL Server 2014, the following migration options are supported.
| Target version | Options |
|---|---|
| SQL Server 2017 | - Migration tools: Migration is supported through Data Migration Assistant (DMA). - Backup and restore: A backup taken on SQL Server 2014 can be restored to SQL Server 2017. - Transactional replication: SQL Server transactional replication from SQL Server 2014 to SQL Server 2017 is supported. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2014 and secondary replicas are running SQL Server 2017. If a failover, either automatic or manual, happens such that a SQL Server 2017 instance becomes primary, SQL Server 2014 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2014 and mirror is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes principal, SQL Server 2014 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2014 and secondary is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes primary, SQL Server 2014 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2014 to SQL Server 2017. |
| SQL Server 2016 | - Migration tools: Migration is supported through DMA. - Backup and restore: A backup taken on SQL Server 2014 can be restored to SQL Server 2016. - Transactional replication: SQL Server transactional replication from SQL Server 2014 to SQL Server 2016 is supported. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2014 and secondary replicas are running SQL Server 2016. If a failover, either automatic or manual, happens such that a SQL Server 2016 instance becomes primary, SQL Server 2014 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2014 and mirror is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes principal, SQL Server 2014 instance becomes mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2014 and secondary is running SQL Server 2016. If a failover, either automatic or manual, happens such that SQL Server 2016 instance becomes primary, SQL Server 2014 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2014 to SQL Server 2016. |
When upgrading from SQL Server 2016, the following migration options are supported.
| Target version | Options |
|---|---|
| SQL Server 2017 | - Migration tools: Migration is supported through Data Migration Assistant (DMA). - Backup and restore: A backup taken on SQL Server 2016 can be restored to SQL Server 2017. - Transactional replication: SQL Server transactional replication from SQL Server 2016 to SQL Server 2017 is supported. - Availability group: Always On availability groups are supported if primary replica is running SQL Server 2016 and secondary replicas are running SQL Server 2017. If a failover, either automatic or manual, happens such that a SQL Server 2017 instance becomes primary, the SQL Server 2016 instance becomes secondary and will NOT be able to receive changes from primary. - Database mirroring: Database mirroring is supported if principal is running SQL Server 2016 and mirror is running SQL Server 2017. If a failover, either automatic or manual, happens such that a SQL Server 2017 instance becomes principal, the SQL Server 2016 instance becomes a mirror and will NOT receive changes from principal. - Log shipping: Log shipping is supported if primary is running SQL Server 2016 and secondary is running SQL Server 2017. If a failover, either automatic or manual, happens such that SQL Server 2017 instance becomes primary, the SQL Server 2016 instance becomes secondary and will NOT receive changes from primary. - Bulk load: Tables can be bulk copied from SQL Server 2016 to SQL Server 2017. |
For additional assistance with completing this migration scenario, see the following resources, which were developed in support of a real-world migration project engagement.
| Title/link | Description |
|---|---|
| Data Workload Assessment Model and Tool | This tool provides suggested “best fit” target platforms, cloud readiness, and application/database remediation level for a given workload. It offers simple, one-click calculation and report generation that greatly helps to accelerate large estate assessments by providing and automated and uniform target platform decision process. |
| Optimization Guide for Mainframe App/Data recompiled to .NET & SQL Server | This guide offers optimization advice for executing point-lookups against SQL Server from .NET as efficiently as possible. Customers wishing to migrate from mainframe databases to SQL Server may desire to migrate existing mainframe-optimized design patterns, especially when using third-party tools (such as Raincode Compiler) to automatically migrate mainframe code (COBOL/JCL etc.) to T-SQL and C## .NET. |
Note
The Data SQL Engineering team developed these resources. This team's core charter is to unblock and accelerate complex modernization for data platform migration projects to Microsoft's Azure data platform.
-
To start upgrading your SQL Server databases to Azure SQL Managed Instance, see the SQL Server upgrade guide.
-
For a matrix of services and tools that can help you with database and data migration scenarios as well as specialty tasks, see Services and tools for data migration.
-
To assess the application access layer, see Data Access Migration Toolkit (Preview).
-
For details on how to perform A/B testing at the data access layer, see Database Experimentation Assistant.
Before beginning your migration project, it is important to address the associated prerequisites. To prepare for the migration, download and install the:
- Latest version of the MAP Toolkit.
- Data Migration Assistant v3.3 or later.
- Latest version of the Database Experimentation Assistant.
After verifying that your source environment is supported and ensuring that you have addressed any prerequisites, you are ready to start the Pre-migration stage. This part of the process involves conducting an inventory of the databases that you need to migrate, assessing those databases for potential migration issues or blockers, and then resolving any items you might have uncovered. For heterogenous migrations (such as Oracle to Azure Database for PostgreSQL), this stage also involves converting the schema(s) in the source database(s) to be compatible with the target environment. For homogenous migrations, such as SQL Server to SQL Server, conversion of the source schema to work in the target environment is not required.
The goal of the Discover phase is to identify existing data sources and details about the features that are being used to get a better understanding of and plan for the migration. This process involves scanning the network to identify all your organization’s SQL instances together with the version and features in use.
To use the MAP Toolkit to perform an inventory scan, perform the following steps.
- Download the MAP Toolkit, and then install it.
- Run the MAP Toolkit.
-
Open the MAP Toolkit, and then on the left pane, select Database. You will be on the following screen:
-
Select Create/Select database.
-
Ensure that Create an inventory database is selected, enter a name for the database, a brief description, and then select OK.
The next step is to collect data from the database created.
-
Select Collect inventory data.
-
In the Inventory and Assessment Wizard, select SQL Server and SQL Server with Database Details, and then select Next.
-
Select the best method option to search the computers on which Microsoft Products are hosted, and then select Next.
-
Enter credentials or create new credentials for the systems that you want to explore, and then select Next.
-
Set the order of the credentials, and then select Next.
Now, you need to specify the credentials for each computer that you want to discover. You can use unique credentials for each computer/machine, or you can choose to use the All Computer Credentials list.
-
After setting up the credentials, select Save, and then select Next.
-
Verify your selection summary, and then select Finish.
-
Wait for a few minutes (depending on the number of databases) for the Data Collection summary report.
-
Select Close.
The Main window of the tool appears, showing a summary of the Database Discovery completed so far.
-
- Report generation and data collection.
On the top-right corner of the tool, an Options page appears, which you can use to generate report about the SQL Server Assessment and the Database Details.
-
Select both options (one by one) to generate the report.
This will take a couple of seconds to a few minutes depending on the size of the inventory completed during discovery.
After identifying the data sources, the next step is to assess the on-premises SQL Server instance(s) upgrading to a later version of SQL Server so that you understand the gaps between the source and target instances. Use the Data Migration Assistant (DMA) to assess your source database before upgrading your SQL Server instance.
To use DMA to create an assessment, perform the following steps.
- Download the DMA tool, and then install it.
- Create a New Assessment project.
-
Select the New (+) icon, select the Assessment project type, specify a project name, select SQL Server as the source and target, and then select Create.
-
Select the target SQL Server version that you plan to migrate to and against which you need to run an assessment, select one or both of the assessment report types (Compatibility Issues and New features’ recommendation), and then select Next.
-
In the Connect to a server fly-out, specify the name of the SQL Server instance to connect to, specify the Authentication type and Connection properties, and then select Connect.
-
In the Add Sources fly-out, select the database(s) you that want to assess, and then select Add.
-
Select Start Assessment.
Now wait for the assessment results; the duration of the assessment depends on the number of databases added and the schema size of each database. Results will be displayed per database as soon as they are available.
-
Select the database that has completed assessment, and then switch between Compatibility issues and Feature recommendations by using the switcher.
-
Review the compatibility issues by analyzing the impacted object and its details for every issue identified under Breaking changes, Behavior changes, and Deprecated features.
-
Review feature recommendations across the Performance, Storage, and Security areas.
Feature recommendations cover a variety of features such as In-Memory OLTP and Columnstore, Stretch Database, Always Encrypted (AE), Dynamic Data Masking (DDM), and Transparent Data Encryption (TDE).
-
- Review assessment results.
- After all database assessments are complete, select Export report to export the results to either a JSON or CSV file for analyzing the data at your own convenience.
After assessing the source database instance(s) you are migrating, for heterogenous migrations, you need to convert the schema to work in the target environment. Since upgrading to a newer version of SQL Server would be considered a homogeneous migration, the Convert phase is unnecessary.
After you have the necessary prerequisites in place and have completed the tasks associated with the Pre-migration stage, you are ready to perform the schema and data migration.
After assessing your databases, the next step is to begin the process of migrating the schema and database by using DMA.
To use DMA to create a migration project, perform the following steps.
- Create a New Migration project
-
Select the New icon, select the Migration project type, select SQL Server as source and target types, and then select Create.
-
Provide source and target SQL server connection details, and then select Next.
-
Select databases from the source to migrate, and then specify the Shared location accessible by source and target SQL servers for backup operation.
[!NOTE] Be sure that the service account running the source SQL Server instance has write privileges on the shared location and that the target SQL Server service account has read privileges on the shared location.
-
Select Next, select the logins that you want to migrate, and then select Start Migration.
-
Now, monitor the progress of migration in the View Results screen.
-
- Review Migration Results
- Select Export report to save the migration results to a .csv or .json file.
- Review the saved file for details about data and logins migration and verify successful completion of the process.
For minimal-downtime migrations, the source you are migrating continues to change after the one-time migration occurs, and it will drift from the target in terms of data and schema. During this phase, you need to ensure that all changes in the source are captured and applied to the target in near real time. After you verify that all changes in source have been applied to the target, you can cutover from the source to the target environment.
Support for minimal-downtime migrations is not yet available for this scenario, so the Data sync and Cutover phases are not currently applicable.
After you have successfully completed the Migration stage, you need to go through a series of post-migration tasks to ensure that everything is functioning as smoothly and efficiently as possible.
After the data is migrated to the target environment, all the applications that formerly consumed the source need to start consuming the target. Accomplishing this will in some cases require changes to the applications.
After the data is migrated to target, perform tests against the databases to verify that the applications perform well against the after the migration. Use the Database Experimentation Assistant (DEA) to assist with evaluating the target SQL Server.
Note
For assistance with developing and running post-migration validation tests, also consider the Data Quality Solution available from QuerySurge.
To use DEA for database migration testing, perform the following steps.
- Download the DEA tool, and then install it.
- Run a trace capture
-
On the left navigation tree, select the camera icon the go to All Captures.
-
To start a new capture, select New Capture.
-
To configure the capture, specify the trace name, duration, SQL Server instance name, database name, and the share location for storing the trace file on the computer running SQL Server.
-
Select Start to begin trace capture.
-
- Run a trace replay
-
On the left navigation tree, select the play icon the go to All Replays.
-
To start a new replay, select New Replay.
-
To configure the replay, specify the replay name, controller machine name, path to source trace file on controller, SQL Server instance name, and the path for storing the target trace file on the computer running SQL Server.
-
Select Start to begin replay of your capture.
-
- Create a new Analysis Report
-
On the left navigation tree, select the checklist icon to go to Analysis Reports.
-
Connect to the SQL Server on which you will store your report databases.
You will see the list of all reports in the server.
-
Select New Report.
-
To configure the report, specify the report name, and specify paths to the traces for the source and target SQL Server instances.
-
- Review an analysis report
-
On the first page of the report, the version and build information for the target servers on which the experiment was run displays.
Threshold allows you to adjust the sensitivity or tolerance of your A/B Test analysis.
[!NOTE] By default, threshold is set to 5%; any performance improvement that is >= 5% is categorized as ‘Improved’. The drop-down selector allows you to evaluate the report using different performance thresholds.
-
Select the individual slices of the pie chart to view drill-down metrics on performance.
On the drill-down page for a performance change category, you will see a list of queries in that category.
-
Select an individual query to get performance summary statistics, error information, and query plan information.
-
The post-migration phase is crucial for reconciling any data accuracy issues and verifying completeness, and addressing performance issues with the workload.
Note
For additional detail about these issues and specific steps to mitigate them, see the Post-migration Validation and Optimization Guide.