DatabaseBackup - Azure Managed instance uses incorrect sqlserver version info. Should use Resource version and not Product Version

[dmackay-allvuesystems]

Description of the issue Azure Managed instance, The scripts are using the wrong sqlserver version due to taking product version = 12 instead of resource version = 16. This affects encrypting a backup as below. It is hitting this code below IF @Encrypt = 'Y' AND @BackupSoftware IS NULL AND NOT (@Version >= 12 AND (SERVERPROPERTY('EngineEdition') = 3) OR SERVERPROPERTY('EditionID') IN(-1534726760, 284895786)) SELECT 'The value for the parameter @Encrypt is not supported.' SERVERPROPERTY('EngineEdition')) = 8 , SERVERPROPERTY('EditionID') =1674378470, @version = <12.0200080000>

SQL Server version and edition Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 3 2024 13:09:32 Copyright (C) 2022 Microsoft Corporation BUT this is misleading. ResourceVersion = 16.00.5564 Product version is 12.0.2000.8 Compatibility level of Master = 160 This is an Azure Managed Instance

Version of the script Version: 2022-12-03 17:23:44

What command are you executing? DatabaseBackup

What output are you getting? Version: 12.0.2000.8 Edition: SQL Azure Platform: Windows Procedure: [SSadmin].[dbo].[DatabaseBackup] Parameters: @Databases = 'SYSTEM_DATABASES', @Directory = NULL, @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = NULL, @CleanupMode = 'AFTER_BACKUP', @Compress = 'Y', @CopyOnly = 'Y', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = 65536, @BufferCount = 27, @MaxTransferSize = 4194304, @NumberOfFiles = NULL, @MinBackupSizeForMultipleFiles = NULL, @MaxFileSize = 199680, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'Y', @EncryptionAlgorithm = 'AES_256', @ServerCertificate = 'SomeServerCert', @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = 'https://SomeBAckupStore.blob.core.windows.net/full', @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTERBACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}', @FileName = '{ServerName}${InstanceName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @Format = 'N', @ObjectLevelRecoveryMap = 'N', @ExcludeLogShippedFromLogBackup = 'Y', @DirectoryCheck = 'Y', @StringDelimiter = ',', @DatabaseOrder = 'DATABASE_SIZE_ASC', @DatabasesInPar... Version: 2022-12-03 17:23:44 Source: https://ola.hallengren.com

Msg 50000, Level 16, State 2, Procedure dbo.DatabaseBackup, Line 2277 [Batch Start Line 9] The value for the parameter @Encrypt is not supported.

Msg 50000, Level 16, State 1, Procedure dbo.DatabaseBackup, Line 2277 [Batch Start Line 0] The documentation is available at https://ola.hallengren.com/sql-server-backup.html.

[olahallengren]

Could you test this version? https://github.com/olahallengren/sql-server-maintenance-solution/tree/minor_fixes

[olahallengren]

I can also add that I have reached out to the Managed Instance team and asked about the version numbers, especially now since there are multiple flavors of MI. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/update-policy?view=azuresql&tabs=azure-portal

[olahallengren]

This issue has been fixed.

[olahallengren]

I can add that the Managed Instance team advised against using the ResourceVersion. So I am not using that.