BizTalk Server BAM Tools Error: Database ‘BAMAnalysis’ has compatibility level. Tabular databases with compatibility level must use StorageEngineUsed set to TabularMetadata

BizTalk Server BAM Tools Error: Database ‘BAMAnalysis’ has compatibility level. Tabular databases with compatibility level must use StorageEngineUsed set to TabularMetadata

Completing the enormous saga, aka nightmare, to install BAM in one of my latest BizTalk Server installations and once I thought I solve all the issues… BAM strikes again! Now complaining about the following error:

Error Configuration Framework Feature: BAM Tools

Failed to configure with error message

Exception Message=”Error configuring BAM Tools” Source=”BAMTools” HelpID=”idsErrorConfiguringTools”

Exception Message=”Error executing bm.exe with tracing enabled.” Source=”Microsoft.BizTalk.Bam.CfgExtHelper.Utility” HelpID=”error_ExecuteBm”

Exception Message=”Error executing process: C:Program Files (x86)Microsoft BizTalk ServerTrackingbm.exe” Source=”Microsoft.BizTalk.Bam.CfgExtHelper.Utility” HelpID=”error_ExecuteProcess”

Exception Message=”Microsoft (R) Business Activity Monitoring Utility Version 3.13.717.0
Copyright (C) Microsoft Corporation. All rights reserved.

ERROR: Failed to set up BAM database(s).
Database ‘BAMAnalysis’ has compatibility level larger than 1103 with StorageEngineUsed set to InMemory. Tabular databases with compatibility level at 1200 or above must use StorageEngineUsed set to TabularMetadata.

Source=”Microsoft.BizTalk.Bam.CfgExtHelper.Utility” HelpID=”error_ExecuteProcess”

Cause

My team didn’t follow the SQL Server installation, we just send to another team the requirements necessary for BizTalk Server and the installation guide, so we were a little surprised by this error.

And despite I never had seen this error before, the reason was quite simple: This error is caused by the SQL Server Analysis Services (SSAS) instance being installed using the tabular mode, and BizTalk Server needs a multi-dimensional mode SSAS instance.

Solution

You have at least two solutions on the table to solve this issue.

Solution 1: Uninstall and re-install SQL Server Analysis Services (SSAS)

The trick here is to be able to know how to uninstall SSAS and to do that you need to:

  • Open Command Prompt as Administrator model on the SQL Server and execute the following command
<F:setup.exe /ACTION=uninstall /FEATURES=AS /INSTANCENAME=YOUR_INSTANCE_NAME
  • In above command has below parameters.
    • SQL Server ISO setup path
    • Action performed. In here we are going to uninstall
    • Feature we are going to remove. AS indicates Analysis Services instance
    • The name of our AS instance

After that, you just need to add new features to the existing installation, in this case, a multi-dimensional mode SSAS instance.

Solution 2: Modify the msmdsrv.ini file

An alternative way is to modify the msmdsrv.ini file. Administrators can modify default server configuration properties of an SQL Server Analysis Services (SSAS) instance by using SQL Server Management Studio. However, properties pages in SQL Server Management Studio show a subset of the properties most likely to be modified. For SSAS, all properties are in the msmdsrv.ini file.

  • The default installation, msmdsrv.ini can be found in the C:Program FilesMicrosoft SQL ServerMSAS15.MSSQLSERVEROLAPConfig folder
    • Of course, this depend on the version you are using.

On the msmdsrv.ini file:

  • Configure the DeploymentMode property to be 0 (zero) and save the file

After that, restart the SSAS instance and these will change from a tabular instance to a multi-dimensional instance

The post BizTalk Server BAM Tools Error: Database ‘BAMAnalysis’ has compatibility level. Tabular databases with compatibility level must use StorageEngineUsed set to TabularMetadata appeared first on SANDRO PEREIRA BIZTALK BLOG.

BizTalk Server 2020 Configuration: Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server

BizTalk Server 2020 Configuration: Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server

Following my last blog post and while I was trying to fix some issues regarding a BizTalk Server 2020 installation and configuration provided by my intern as is learning process. We end up solving some critical problems that allow us to install the platform’s most important components. Nevertheless, several components like BAM Tools, BAM Portal and BizTalk EDI/AS2 Runtime failed to configure:

Once, we inspect the logfile provide the the BizTalk Server Configurarion Wizard we found the following error message:

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Tools] Failed to configure with error message [Feature is skipped due to failed validation.  Please go to Custom Configuration to fix the validation error.

Validation Error: <Exception Message=”Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server.” Source=”BAMTools” HelpID=”idsErrorValidateSSISCatalogDatabase”><Exception Message=”SSIS Catalog (SSISDB) does not exist on server BTS2020LABAVMRG. Please create SSIS Catalog.” Source=”Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper” HelpID=”error_SSISCatalogNotExists”/></Exception>]

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BAM Portal] Failed to configure with error message [The Configuration of Feature ‘BAM Portal’ failed because the dependent Feature ‘BAM Tools’ was not configured. ]

[2020-11-10 22:17:18:0674 Error Configuration Framework]Feature: [BizTalk EDI/AS2 Runtime] Failed to configure with error message [Feature is skipped due to dependent feature (BAM Tools) failed to configure correctly.]

Three distinct errors but the last two are a sequence of the first one, all of them are related to the same cause.

Cause

SSISDB was automatically created during the BizTalk Server configuration process on previous versions if we enable BAM Portal. However, BAM Portal was deprecated in this new BizTalk Server version, and the configuration process no longer created the SSIS Catalog. Instead, we need to create the catalog manually.

If you don’t create the SSIS Catalog manually, you will not be able to configure BAM Portal. Nevertheless, you can always perform this operation whenever you think is required, and after that, configure BAM Portal.

Make sure you also configure SQL Server Database Mail feature if you wish to configure BAM Alerts on your BizTalk Server 2020 environment

Solution

The solution is very simple, we need to manually create the catalog by using the following instructions:

  • Open SQL Server Management Studio and connect to the SQL Server Database Engine.
  • In Object Explorer, expand the server node, right-click the Integration Services Catalogs node, and then click Create Catalog.
  • On the Create Catalog window, do the following configuration and click OK.
    • Select Enable CLR Integration option.
    • Enter a password to protect the encryption key.

And run the BizTalk Server Configuration again. At the end you will be able to configure all BizTalk Server components.

The post BizTalk Server 2020 Configuration: Error validating the SSIS Catalog database. Please ensure SQL Server Integration Services is installed on the local machine and SSIS Catalog is created on target SQL Server appeared first on SANDRO PEREIRA BIZTALK BLOG.