This is a summary page for SQL server upgrade for 2019.
Reference:
Microsoft
Summary #
You can upgrade from SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 (11.x), and SQL Server 2014 (12.x). This article lists the supported upgrade paths from these SQL Server versions, and the supported edition upgrades for SQL Server 2019 (15.x).
SQL 2019 Hardware and software requirements #
Installation of SQL Server is supported on x64 processors only. It is no longer supported on x86 processors.
Microsoft SQL Server currently supports the following sector sizes that are equal to or less than 4 kilobytes (KB):
- SQL Server supports disk drives that have standard native sector sizes of 512 bytes and 4 KB.
- SQL Server does not support drives with sector sizes that are greater than 4K. For example, 8K sector size.
For many production and some development environments, a new installation upgrade or a rolling upgrade is more appropriate than an in-place upgrade.
Need to understand what services are used under MSSQL 2014
choose a database engine upgrade method
upgrade data quality service ?
upgrade integration services?
upgrade master data services?
upgrade master data services?
upgrade and migrate reporting services?
upgrade analysis services?
Pre-upgrade planning checklist #
- Hardware and software requirements: Review the hardware and software requirements to for installing SQL Server.
- Current environment: Research your current environment to understand the SQL Server components that are being used and the clients that connect to your environment.
- Client providers: While upgrading does not require you to update the provider for each of your clients, you may choose to do so. If you are upgrading from SQL Server 2014 (12.x) or older, some of SQL Server 2016 (13.x) features require an updated provider for each client.
- Third-party components: Determine the compatibility of third-party components, such as integrated backup.
- Edition: Determine the appropriate edition of SQL Server for your upgrade and determine the valid upgrade paths for the upgrade.
- Backward compatibility: Review the SQL Server database engine backward compatibility article to review changes in behavior between SQL Server and the SQL Server version from which you are upgrading. See SQL Server Database Engine Backward Compatibility.
- Data Migration Assistant: Run the Data Migration Assistant to assist in diagnosing issues that might either block the upgrade process or require modification to existing scripts or applications due to a breaking change. You can download the Data Migration Assistant here.
- System configuration checker: Run the SQL Server System Configuration Checker (SCC) to determine if the SQL Server setup program detects any blocking issues before you schedule the upgrade. For more information, see Check Parameters for the System Configuration Checker.
- Upgrading memory-optimized tables: When upgrading a SQL Server 2014 database instance containing memory-optimized tables to SQL Server 2016, the upgrade process requires additional time to convert the memory-optimized tables to the new on-disk format (and the database is offline while these steps are happening. The amount of time is dependent upon the size of the memory-optimized tables and the speed of the I/O subsystem. The upgrade requires three sizes of data operations for in-place and new installation upgrades.
Migrate to a new installation #
Attached storage environment
SAN storage environment
Data Migration Assistant #
Permission: sysadmin is required.
Managing upgrade risk with Compatibility Certification #
Using Compatibility Certification is a valuable approach to database modernization. By certifying based on compatibility level, developers set the technical requirements for an application to be supported on SQL Server and Azure SQL Database, but decouple the application lifecycle from the database platform lifecycle. This allows companies to keep the SQL Server Database Engine upgraded as needed by lifecycle policies, as well as leveraging new scalability and performance enhancements that are not code dependant, and connecting applications maintain their functional status through upgrades.
The possibilities of adversely affecting functionality and performance are the main risk factors for any upgrade. Compatibility Certification represents peace of mind in terms of managing these upgrade risks:
- In what relates to Transact-SQL behavior, any change means that an application needs to be recertified for correctness. However, the database compatibility level setting provides backward compatibility with earlier versions of SQL Server only for the specified database, not for the entire server. Keeping the database compatibility level as-is ensures that existing application queries continue to display the same behavior before and after a Database Engine upgrade. For more information about Transact-SQL behavior and compatibility levels, see Using compatibility levels for backward compatibility.
- In what relates to performance, because improvements in the Query Optimizer are introduced with every version, it could be expected to encounter query plan differences between different Database Engine versions. Query plan differences in the scope of an upgrade usually translate to risk, when there is potential that some changes may be detrimental for a given query or workload. In turn, this risk is a motivation for recertification, which can delay upgrades and pose lifecycle and support challenges. Mitigating upgrade risks is why Query Optimizer improvements are gated to the default compatibility level of a new release (in other words, the highest compatibility level available for any new version). Compatibility Certification includes query plan shape protection: the notion that maintaining a database compatibility level as-is immediately after a Database Engine upgrade translates into using the same query optimization model in the new version, as it was before the upgrade, and the query plan shape should not change. For more information, see the Why query plan shape? section in this article.
Add label