AzurePack: How to check if there is version mismatch in WAP components and database schema

After installing a rollup update on AzurePack, you might sometimes get into an issue where the version of WAP component doesn’t match for database schema version. Most commonly seen error message or symptom of this behavior as seen in event logs is:

System.Data.SqlClient.SqlException (0x80131904): Could not find stored procedure 'mp.UpdatePlan_vx_xx'.

Stored procedure name in the error message would differ based on what operation you are performing on the portal. But the underlying issue is that database schema is not updated to the version of WAP components that we have on the servers.

So first we need to verify if there is indeed a version mismatch.

You can determine the versions of your Windows Azure Pack installations with the Get-WmiObject Windows PowerShell command. Run the following command on each computer that has a Windows Azure Pack installed, for example:

Get-WmiObject -Class Win32_Product | Where-Object { $_.Name -like "Windows Azure Pack *" } | Format-Table -AutoSize

To determine the versions of your Windows Azure Pack databases, run the following query in SQL Server Management Studio on the SQL Server instance that hosts the Windows Azure Pack databases:

-- WAP database versions
SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.Config' AS [Database],
N'Config' AS [Schema], *
FROM [Microsoft.MgmtSvc.Config].[Config].[Version]
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.PortalConfigStore' AS [Database],
N'Config' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.PortalConfigStore].[Config].[Version]
UNION
SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.PortalConfigStore' AS [Database],
N'PortalAspNet' AS [Schema],
N'' AS [Version], [CompatibleSchemaVersion] AS [Major], 0 AS [Minor], 0 AS [Build], 0 AS [Revision], N'' AS [VersionInfo]
FROM [Microsoft.MgmtSvc.PortalConfigStore].[dbo].[aspnet_SchemaVersions]
WHERE [Feature] = N'membership'
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.Store' AS [Database],
N'Config' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.Store].[Config].[Version]
UNION
SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.Store' AS [Database],
N'Management' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.Store].[mp].[Version]
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.Usage' AS [Database],
N'Usage' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.Usage].[usage].[Version]
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.WebAppGallery' AS [Database],
N'WebAppGallery' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.WebAppGallery].[WebAppGallery].[Version]
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.SQLServer' AS [Database],
N'SQLServer' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.SQLServer].[SqlServer].[Version]
UNION

SELECT SERVERPROPERTY(N'ServerName') AS [Server],
N'Microsoft.MgmtSvc.MySQL' AS [Database],
N'MySQL' AS [Schema],
[Version], [Major], [Minor], [Build], [Revision], [VersionInfo]
FROM [Microsoft.MgmtSvc.MySQL].[MySql].[Version]

If Windows Azure Pack component version is greater than the database version, run the following script as administrator on the servers that contain the MgmtSvc-PowerShellAPI module. These servers include the Usage, Web App Gallery, SQL Server, and MySQL extensions.

Note that this script may need modification for some distributed installations. In particular, the schemas for Usage, WebAppGallery, SQLServer, and MySQL are installed with those components so those portions of the script would need to run on the computers with those components installed.

Caution: Take a backup of your databases before running the script.

# Update-WapDatabases
Import-Module -Name MgmtSvcConfig

function New-SqlConnectionString([string]$masterConnectionString, [string]$database)
{
    $builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder($masterConnectionString)
    $builder.Database = $database
    return $builder.ConnectionString
}
function Get-WapSchemas([string]$database)
{
    switch ($database)
    {
        "Microsoft.MgmtSvc.Config"            { @("Config") }
        "Microsoft.MgmtSvc.MySQL"             { @("MySQL") }
        "Microsoft.MgmtSvc.PortalConfigStore" { @("Config","PortalAspNet","PortalNotification") }
        "Microsoft.MgmtSvc.SQLServer"         { @("SQLServer") }
        "Microsoft.MgmtSvc.Store"             { @("Config","Management") }
        "Microsoft.MgmtSvc.Usage"             { @("Usage") }
        "Microsoft.MgmtSvc.WebAppGallery"     { @("WebAppGallery") }
        default { throw New-Object System.ArgumentOutOfRangeException($database) }
    }
}

# Prompt for the SQL Server name:
$sName = Read-Host "Specify the name of the SQL Server that hosts the Windows Azure Pack databases."

$wapMasterConnectionString = "Server=" + $sName + ";Database=master;Integrated Security=True"

$wapDatabaseNames = (Get-MgmtSvcDefaultDatabaseName).DefaultDatabaseName
foreach ($wapDatabaseName in $wapDatabaseNames)
{
    $wapConnectionString = New-SqlConnectionString -masterConnectionString $wapMasterConnectionString -database $wapDatabaseName
    Write-Verbose -Message "Connection string: $wapConnectionString" -Verbose

    $wapSchemas = Get-WapSchemas -database $wapDatabaseName
    foreach ($wapSchema in $wapSchemas)
    {
        $wapSchema = Get-MgmtSvcSchema -Schema $wapSchema
        if ($wapSchema)
        {
            Write-Verbose -Message "BEGIN UPDATE database '$wapDatabaseName' schema '$wapSchema'." -Verbose
            Install-MgmtSvcDatabase -ConnectionString $wapConnectionString -Schema $wapSchema
            Write-Verbose -Message "END UPDATE database '$wapDatabaseName' schema '$wapSchema'." -Verbose
            $version = Test-MgmtSvcDatabase -ConnectionString $wapConnectionString -Schema $wapSchema
            Write-Output "Version: database '$wapDatabaseName' schema '$wapSchema' version $version"
        }
    }
}

Reference: https://technet.microsoft.com/en-us/library/dn747884.aspx