Configuration wizard error message after upgrading to SolarWinds Platform 2024.2: "An exception occurred during the registration of types for managed entities" with Memory Optimized Table "Cloud_Azure_LoadBalancerPoll_rel_1" with Microsoft SQL 2022

While upgrading to SolarWinds Platform 2024.2 (where SolarWinds data is stored utilizing Microsoft SQL 2022), you may run into one of the below errors during configuration wizard:

Error 1: Cannot Create Memory optimized tables

ERROR ConfigureDatabase - An exception occurred during the registration of types for managed entities:---> 
System.Data.SqlClient.SqlException (0x80131904): dbm_EnsureMEObjects: dbm_CreateMEObjects: dbm_CreateMETypeTable: 
Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container. CREATE TABLE Cloud_Azure_LoadBalancerPoll_rel_1
(
     [ID] bigint NOT NULL,
     [UpdatedTime] datetime2 NULL
CONSTRAINT [PK_Cloud_Azure_LoadBalancerPoll_rel_1] PRIMARY KEY NONCLUSTERED
(
     [ID]
)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Error 2: The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables

ERROR TaskAdapter - Error running task Orion.ConfigureDatabase SolarWinds Platform Database
SolarWinds.ConfigurationWizard.Common.ConfigurationException: An exception occurred during the registration of types for managed entities.---> 
System.Data.SqlClient.SqlException: dbm_EnsureMEObjects: dbm_CreateMEObjects: dbm_CreateMETypeTable: 
The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables. CREATE TABLE Cloud_Azure_LoadBalancerPoll_rel_1
(
     [ID] bigint NOT NULL,
     [UpdatedTime] datetime2 NULL
CONSTRAINT [PK_Cloud_Azure_LoadBalancerPoll_rel_1] PRIMARY KEY NONCLUSTERED
(
     [ID]
)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Error 3: Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables

ERROR TaskAdapter - Error running task Orion.ConfigureDatabase SolarWinds Platform Database
SolarWinds.ConfigurationWizard.Common.ConfigurationException: An exception occurred during the registration of types for managed entities. ---> 
System.Data.SqlClient.SqlException: dbm_EnsureMEObjects: dbm_CreateMEObjects: dbm_CreateMETypeTable: 
Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables. CREATE TABLE Cloud_Azure_LoadBalancerPoll_rel_1
(
[ID] bigint NOT NULL,
[UpdatedTime] datetime2 NULL
CONSTRAINT [PK_Cloud_Azure_LoadBalancerPoll_rel_1] PRIMARY KEY NONCLUSTERED
(
[ID]
)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Error 4: The code generation directory cannot be created or set up correctly

ERROR TaskAdapter - Error running task Orion.ConfigureDatabase SolarWinds Platform Database
SolarWinds.ConfigurationWizard.Common.ConfigurationException: An exception occurred during the registration of types for managed entities. ---> 
System.Data.SqlClient.SqlException: dbm_EnsureMEObjects: dbm_CreateMEObjects: dbm_CreateMETypeTable:
The code generation directory cannot be created or set up correctly.

CREATE TABLE Cloud_Azure_LoadBalancerPoll_rel_1
(
     [ID] bigint NOT NULL,
     [UpdatedTime] datetime2 NULL 
CONSTRAINT [PK_Cloud_Azure_LoadBalancerPoll_rel_1] PRIMARY KEY NONCLUSTERED
(
     [ID]
)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
dbm_CreateMETypeTable: Table: Cloud_Azure_LoadBalancerData_man_1 already exists.

Error 5: Unable to load the compiled DLL for database ID ##

ERROR TaskAdapter - Error running task Orion. ConfigureDatabase SolarWinds
Platform Database
SolarWinds.ConfigurationWizard.Common.ConfigurationException: An exception occurred during the registration of types for managed entities. --› 
System. Data. SqlClient.SqlException: dbm_EnsureMEObjects: dbm_CreateMEObjects: dbm_CreateMETypeTable: 
Unable to load the compiled DLL for database ID 5. 
CREATE TABLE Cloud_Azure_AppServicePoll_rel_1

Or

System.Data.SqlClient.SqlException (0x80131904): dbm_EnsureMEObjects: dbm_CreateMETabType: 
Unable to load the compiled DLL for database ID 5. 
CREATE TYPE CiscoAciApicStatus_poll_1_Type AS TABLE ... WITH (MEMORY_OPTIMIZED = ON);
indicates a problem related to memory-optimized tables in SQL Server.

This issue occurs due to the following reasons:

• Cannot create memory optimized tables due to a possible Microsoft SQL Server 2022 bug
• The feature 'EVENT NOTIFICATION' is not supported with memory optimized tables. "Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database event notification for that DDL operation. Remove the server and database event notifications on CREATE TABLE or DROP TABLE and CREATE PROCEDURE or DROP PROCEDURE." For more information, refer to the following document.
• Custom SQL triggers enabled on memory optimized tables. For more information, refer to the following document

Error 1: Cannot Create Memory optimized tables

1. Open SQL Server Management Studio (SSMS) ™, ensuring the credentials used on the SQL Server have SA privileges and DBO permissions on our DBs.

2. Check the state of the database file groups (right-click the database > Properties > Filegroups).
3. There should be a row under the MEMORY OPTIMIZED DATA section, and the FILESTREAM Files column should be 1. If it is set to 0, it means the in_mem1 file is missing. 

This can be further verified by checking the Files page of the same database properties window.

4. Remove the file group under the database file groups (right-click the database > Properties > Filegroups > MEMORY OPTIMIZED DATA) by selecting the group (IN_MEM) and clicking the Remove button.
5. Re-add the file group under the database file groups (right-click the database > Properties > Filegroups > MEMORY OPTIMIZED DATA) by clicking the Add Filegroup button and entering IN_MEM as the Name.
   1. In case the error below is encountered, set the value of AUTO_CLOSE to False (right-click the database > General > AUTO_CLOSE)
      1. 
6. Add the file to the created file group (right-click the database > Properties > Files) by clicking Add and using the details as in the example below.
    
   
   
7. To verify, check the file group under the database file groups (right-click the database > Properties > Filegroups > MEMORY OPTIMIZED DATA).
    
    
8. Re-run the Configuration Wizard.

Error 2: The feature 'EVENT NOTIFICATION' is not supported with memory-optimized tables

1. Open SQL Server Management Studio (SSMS) ™, ensuring the credentials used on the SQL Server have SA privileges and DBO permissions on our DBs.

2. Execute the below query against the SQL Server instance hosting the SolarWinds database to check for Event Notifications.

   -- Scripts are not supported under any SolarWinds support program or service.
   -- Scripts are provided AS IS without warranty of any kind. SolarWinds further
   -- disclaims all warranties including, without limitation, any implied warranties
   -- of merchantability or of fitness for a particular purpose. The risk arising
   -- out of the use or performance of the scripts and documentation stays with you.
   -- In no event shall SolarWinds or anyone else involved in the creation,
   -- production, or delivery of the scripts be liable for any damages whatsoever
   -- (including, without limitation, damages for loss of business profits, business
   -- interruption, loss of business information, or other pecuniary loss) arising
   -- out of the use of or inability to use the scripts or documentation.
   --
   SELECT * FROM sys.all_objects where type_desc = 'EVENT_NOTIFICATION'
   
   or
   
   SELECT * FROM sys.all_objects where type_desc like '%NOTIFICATION%'
   

3. Ask your DBA to drop these Event Notifications.
4. Re-run the Configuration Wizard.
5. If the initial scripts does not show any result, run the next 2 scripts

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.
--

SELECT * 
FROM sys.event_notifications;
---
SELECT * 
FROM sys.server_event_notifications;

6. Ask your DBA to disable or drop these Event Notifications.

7. Re-run the Configuration Wizard.

Error 3: Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables

Resolution: Work with your DBA to disable any triggers on Orion DB. To find them, run this query with an SA account and re-run the Configuration Wizard.

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.
--
SELECT * FROM sys.triggers
WHERE parent_id = 0 or Name like '%DDL%'

-- or

SELECT * FROM sys.Server_triggers
WHERE parent_id = 0 or Name like '%DDL%'

Error 4: The code generation directory cannot be created or set up correctly

1. Compare the Server Properties "Database default locations" with that of the in_mem1 files path via the Solarwinds Database Properties.

Server Properties:
SSMS > right click the SQL Server Instance node > Properties > Database Settings > Database default locations

Database Properties:
SSMS > right click the SolarWinds database > Properties > Files > in_mem1 file > Path column