onelehmus

I’ve run in to two cases where Windows Azure Pack (WAP) started to act strange after an upgrade.

After some investing I’ve found that the WAP databases where not upgraded, in both cases they where still in UR 6 version instead of 9.1. I found this TechNet article:(https://technet.microsoft.com/en-us/library/dn747884.aspx) where there are two scripts you need to run, one to check your current version and one to upgrade.

After the update script was completed everything started to work as they should.

So if you have any issues with WAP I would recommend to run the following SQL script from the TechNet article to check that the database is on the correct version:

-- SQL Script
-- 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]

Script copied from: https://technet.microsoft.com/en-us/library/dn747884.aspx

And if there is a miss match between the install WAP version and the database you need to run the following PowerShell script:

#PowerShell 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"
        }
    }
}

Script copied from: https://technet.microsoft.com/en-us/library/dn747884.aspx

I hope that this will help those that have issues with WAP after an upgrade

//Mattias

All scripts in this blog post  is copied from: https://technet.microsoft.com/en-us/library/dn747884.aspx