Commands / Set-sqmSsasDeploymentMode
Set-sqmSsasDeploymentMode
Configuration sqmSQLTool v1.9.x · Set ✓ -WhatIf supported · High Impact
SQL Server Analysis Services fixes its server mode (Multidimensional, Tabular, or SharePoint/PowerPivot) permanently at setup time via SERVERMODE. Microsoft does not officially support changing it afterwards, the documented remedy is uninstall + reinstall with the correct SERVERMODE.
The unsupported-but-practical workaround: on an instance where SERVERMODE was chosen wrong and no databases have been deployed yet, the mode can be corrected by editing the <DeploymentMode> element in msmdsrv.ini (0=Multidimensional, 1=SharePoint, 2=Tabular) and restarting the service. This function does exactly that: locates msmdsrv.ini, backs it up, updates the element, and optionally restarts the service.
Multidimensional and Tabular are storage-incompatible. If databases already exist under the instance's Data directory, switching mode orphans them, they will not open in the new mode. By default the function refuses to proceed when existing databases are detected. -Force overrides this, only after you have verified there is nothing worth keeping or made your own backup.

Parameters

ParameterTypeRequiredDefaultNotes
-InstanceNamestringOptionalMSSQLSERVERSSAS instance name.
-ModestringRequired, Target mode: Multidimensional or Tabular. (SharePoint/PowerPivot is legacy and not offered as a target.)
-RestartServiceswitchSwitch$falseRestart the SSAS service so the change takes effect immediately. Without it, the change is written but needs a manual restart.
-ForceswitchSwitch$falseProceed even when existing databases are found under the Data directory. Only when you're certain they can be discarded.
-EnableExceptionswitchSwitch$falseThrow immediately instead of logging the error and returning it in the result.

Execution Flow

START "Server\Instance" given? → keep only the instance part SSAS service found? NO Status=Error service not found YES Resolve <ConfigDir>\msmdsrv.ini via the service command line -s "<dir>" switch, else fallback from the exe path Not found / ini missing → Status=Error Load msmdsrv.ini → read current DeploymentMode Node missing → treat as 0 (Multidimensional) PreviousMode recorded in the result either way current mode = -Mode already? YES Status=AlreadySet nothing to do → return NO Scan the Data directory for existing *.db database folders DataDir from the ini, or the default relative to ConfigDir databases found AND NOT -Force? YES Status=Blocked Multidimensional/Tabular are storage-incompatible — mode switch would orphan them. -Force to override. NO databases found + -Force: warn, proceed anyway ShouldProcess? (ConfirmImpact: High) WhatIf Status=WhatIf nothing written YES Copy-Item → msmdsrv.ini.bak_<timestamp> (backup first) Create <DeploymentMode> node if it doesn't exist yet Set InnerText to the target value (0/2), xml.Save(iniPath) Status=Changed -RestartService set? NO Message notes: manual restart needed to take effect YES Restart-Service -Force ServiceRestarted = $true restart itself fails → ERROR logged, Status stays Changed (ini write already done) Return $result Any error above → Status=Error, logged + Write-Error, or throw with -EnableException DONE — result returned

Output object

FieldContents
InstanceName / IniPathWhich instance, and the config file that was read/changed.
PreviousMode / NewModeMode before the change, and the requested target.
DatabasesFoundNumber of existing database folders detected under the Data directory.
BackupPathPath of the msmdsrv.ini backup — only set when a change was actually written.
ServiceRestartedWhether the SSAS service was restarted.
StatusAlreadySet, Changed, Blocked, WhatIf, or Error.
MessageDetail message.

Examples

Correct a default instance to Tabular, restart immediately
Set-sqmSsasDeploymentMode -InstanceName "MSSQLSERVER" -Mode Tabular -RestartService
Preview the change for a named instance, nothing written
Set-sqmSsasDeploymentMode -InstanceName "SSAS2019" -Mode Multidimensional -WhatIf
Force the switch despite existing databases (only after your own backup/check)
Set-sqmSsasDeploymentMode -InstanceName "SSAS2019" -Mode Tabular -Force -RestartService

Requirements

Requires local administrator rights on the SSAS server. This is not an officially supported operation, only safe on an instance without deployed databases. See Microsoft's documentation on SERVERMODE / DeploymentMode for the background.

See also

Get-sqmSsasDeploymentMode, the read-only companion — check before you change.

⬆ Index
All Commands