Share via

SCOM DB Failover

Fadil Ck 381 Reputation points
2022-09-23T09:55:57.983+00:00

Hi All,

While failovering the SCOM database server from one server to other server in the cluster, the scom application is not working. We are not able to open the console. SCOM is only working in one of the DB server. It is required to failover during patching of the DB server. Please see the attached error screenshot while opening SCOM. Kindly advice on the same.

Thanks in advance.

Regards
Fadil CK
244176-scom-login-failure.png

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,446 questions
0 comments
Accepted answer
  1. SChalakov 10,371 Reputation points MVP
    2022-09-23T13:06:12.537+00:00

    Hi @Fadil Ck ,

    I have had this many times and I know the solution. You need to make two assembllies "trusted" in SQL. The solution for this comes from this post, I even planned to write a short blog post on this in order to make this more popular:

    Could not load file or assembly 'microsoft.enterprisemanagement.sql.userdefineddatatype' in an AlwaysOn Availability Group configuration.
    https://social.technet.microsoft.com/Forums/en-US/195c0bd5-115c-4cff-8ae3-4109f59c9b1e/could-not-load-file-or-assembly-microsoftenterprisemanagementsqluserdefineddatatype-in-an?forum=operationsmanagerdeployment

    So, you need to run this on zthe master DB of your Operations Manager DB SQL instance: 

    USE master;  
GO  
DECLARE @clrName1 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.DataAccessLayer'  
DECLARE @hash1 varbinary(64) = 0xEC312664052DE020D0F9631110AFB4DCDF14F477293E1C5DE8C42D3265F543C92FCF8BC1648FC28E9A0731B3E491BCF1D4A8EB838ED9F0B24AE19057BDDBF6EC;  
EXEC sys.sp_add_trusted_assembly @hash = @hash1,  
                                 @description = @clrName1;  
    
DECLARE @clrName2 nvarchar(4000) = 'Microsoft.EnterpriseManagement.Sql.UserDefinedDataType'  
DECLARE @hash2 varbinary(64) = 0xFAC2A8ECA2BE6AD46FBB6EDFB53321240F4D98D199A5A28B4EB3BAD412BEC849B99018D9207CEA045D186CF67B8D06507EA33BFBF9A7A132DC0BB1D756F4F491;  
EXEC sys.sp_add_trusted_assembly @hash = @hash2,  
                                 @description = @clrName2;    
    
USE OperationsManager;  
GO  
SELECT * FROM sys.assemblies  
SELECT * FROM sys.trusted_assemblies

    Please check also the SQL Service Broker as Torbörn sugests in the same posts, by running: 

    SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'  
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE  
ALTER DATABASE OperationsManager SET ENABLE_BROKER  
ALTER DATABASE OperationsManager SET MULTI_USER  
SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'

    Just pay attention to the fact that if SQL Service Broker is not enabled you need to get the DB out of the SQL Always ON AG, enable SQL Service Broker and then get it back in the AG.

    I hope I could help you!

    ----------

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
    Regards
    Stoyan Chalakov

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. JimmySalian-2011 42,071 Reputation points
    2022-09-23T11:37:27.497+00:00

    Hi,

    It seems SQL DB is having some issue after the failed event, can you check this article and it will help you to fix the DB error .

    sql-error-the-server-may-be-running-out-of-resources-or-the-assembly-may-not-be-trusted-with-permission_set-external_access-or-unsafe

    ==
    Please "Accept the answer" if the information helped you. This will help us and others in the community as well.

    0 comments