Upgrade Analysis Services

Upgrade Analysis Services


Applies To: SQL Server 2016 Preview

Analysis Services instances can be upgraded to a SQL Server 2016 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 independently of other instances running on the same hardware. Most administrators will choose side-by-side upgrades so that server availability can be verified before transferring databases and workloads onto the new server, but for development or test servers, an in-pace upgrade might be more convenient.

Before upgrading to SQL Server 2016 Analysis Services, review the following:

  • SQL Server 2016 Release Notes describes known problems and workarounds, if any.

  • Analysis Services Backward Compatibility summarizes discontinued, deprecated, and changed features. You should review these lists periodically to assess the impact of product changes to your models, scripts, or custom code. Typically, feature transitions are announced during pre-release of the next major release.


Upgrade is not supported for production servers in the CTP3 release of SQL Server 2016.

There are two basic approaches for upgrading servers and databases:

  • In-place upgrades replace the existing program files with SQL Server 2016 program files. Databases remain in the same location. Program folders are updated to reflect the new name.

  • Side-by-side upgrades create a new installation of SQL Server 2016, usually on the same computer unless you are upgrading hardware at the same time. This approach requires you to move databases over to the new instance, and then optionally uninstall the previous version to free up disk space.

You can upgrade an existing instance of Analysis Services to SQL Server 2016 Analysis Services and, as part of the upgrade process, automatically migrate 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. See Backup and Restore of 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. See Install Analysis Services.

    Follow post-installation tasks for configuring ports and adding server administrators. 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. 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. See Configure Service Accounts (Analysis Services) for more information about startup accounts for 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 of Analysis Services run on the upgraded server under an older database 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.

Tabular and multidimensional databases follow different version paths. It's coincidental that both multidimensional and tabular models have compatibility level 1100. 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 topics to understand what each level provides.



SQL Server 2016



SQL Server 2014



SQL Server 2012



SQL Server 2012 and later.



SQL Server 2005, 2008, 2008 R2

See Compatibility Level of a Multidimensional Database (Analysis Services) and Compatibility Level for Tabular models in Analysis Services for more information.

© 2016 Microsoft