Upgrade Analysis Services
Applies to: SQL Server - Windows only
Analysis Services instances can be upgraded to a SQL Server version of the same server mode to take advantage of features introduced in the current release, as described in What's new in Analysis Services.
You can upgrade each instance in-place, independently of other instances running on the same hardware. However, most administrators choose to install a new instance of the new version for application testing before transferring production workloads onto the new server. But for development or test servers, an in-place upgrade might be more convenient.
There are two basic approaches for upgrading servers and databases:
The compatibility levels of databases that are attached to a given server remain the same unless you manually change them.
The upgrade process automatically migrates existing databases from the old instance to the new instance. Because the metadata and binary data is compatible between the two versions, you will retain the data after you upgrade and you do not have to manually migrate the data.
To upgrade an existing instance, run Setup and specify the name of the existing instance as the name of the new instance.
Backup all databases and verify that each can be restored. To learn more, see Backup and restore Analysis Services databases.
Identify a subset of reports, spreadsheets, or dashboard snapshots to use later as the basis for confirming post-upgrade server operations. If possible, collect performance measurements so that you can run comparisons against the same workloads on an upgraded server.
Install a new instance of Analysis Services, choosing the same server mode (tabular or multidimensional) as the server you intend to replace.
Follow post-installation tasks for configuring ports and adding server administrators. To learn more, see Post-install configuration (Analysis Services).
Attach or restore each database.
Run DBCC to check for database integrity. Tabular models undergo more thorough checking, with tests for orphaned objects throughout the model hierarchy. For multidimensional models, only the partition indexes are checked. To learn more, see Database Consistency Checker (DBCC) for Analysis Services tabular and multidimensional databases.
Test reports, spreadsheets, and dashboards to confirm there is no adverse change to behavior or calculations. You should see faster performance for both multidimensional and tabular workloads.
Test processing operations, correcting any login or permission issues. If you are using default service account for connections, the new service runs under a different account. To learn more, see Configure service accounts (Analysis Services).
Test backup and restore operations on the upgraded server, adjusting scripts to use the new server name.
Databases that were created in previous versions run on the upgraded server with the original compatibility level setting. Generally, you can upgrade a database or model to operate at a higher compatibility level to gain access to new features, but be aware that doing so binds you to a specific server version.
To upgrade a database, you typically upgrade the model in SQL Server Data Tools (SSDT) and then deploy the solution to an upgraded server instance.
Tabular and multidimensional databases follow different version paths. It's coincidental that both multidimensional and tabular models have similar numbered compatibility levels. Modes will advance at different rates if feature changes impact only one of them.
For background purposes, the following table summarizes the compatibility levels, but you should review the detail articles to understand what each level provides.
|Database model||Compatibility level||Compatible versions|
|Tabular||1500||SQL Server 2019|
|Tabular||1400||SQL Server 2017|
|Tabular||1200||SQL Server 2016|
|Tabular||1103||SQL Server 2014|
|Tabular||1100||SQL Server 2012|
|Multidimensional||1100||SQL Server 2012 and later|
|Multidimensional||1050||SQL Server 2005, 2008, 2008 R2|
To learn more, see Compatibility level of a multidimensional database (Analysis Services) and Compatibility level for Analysis Services tabular models for more information.