Redigera

Dela via

Upgrade Analysis Services

  • Artikel

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.

Server upgrade

There are two basic approaches for upgrading servers and databases:

Note

The compatibility levels of databases that are attached to a given server remain the same unless you manually change them.

In-place upgrade

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.

Side-by-side upgrade

  • 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.

Database upgrade

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.

See also

Planning a SQL Server installation
Upgrade Power Pivot for SharePoint