Windows Server 2019
SQL Server Standard 2019 CU 15
SCOM 2019
This server has four sql instances configured. Three of the sql instances has replication installed and configured.
To monitor the OS, SQL and SQL Replication we have SCOM configured with the appropriate MP.
The monitoring for SQL and SQL replication is configured for low level monitoring by following the guide in the Microsoft docs for the MP.
For the Instance where we have the problem, I can under Replication\Local Publication see two configured BiDirectional replications, and under Local Subscriptions there is two Subscriptions.
After the setup the replication monitoring I get two errors in scom, one is for monitoring and the other is for discovery.
"Module: Microsoft.SQLServer.Replication.Windows.Module.Discovery.Discoveries.PublicationDiscovery
Version: 7.0.28.0
Error(s) was(were) occurred:
Message: An error occurred during discovery.
---------- Exception: ----------
Exception Type: System.Data.SqlClient.SqlException
Message: Column names in each table must be unique. Column name 'allow_drop' in table '#TranPublTemp_PublicationDiscovery' is specified more than once."
and
"Module: Microsoft.SQLServer.Replication.Windows.Module.Monitoring.Monitors.MonitorDistributorSnapshotFreeSpace
Version: 7.0.28.0"
"Error(s):
An error occurred while parallel querying SQL: One or more errors occurred.
at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)
at Microsoft.SQLServer.Module.Helper.Sql.SqlParallelQueryHelper`1.<>c__DisplayClass7_0.<FillCollectionParallel>b__1(IGrouping`2 parallelQueryParameterGroup)
mscorlib"
When i collected the exceptions and got the statements it looks like it for each database that is part of a replication the MP is trying to do
"ALTER TABLE #TranPublTemp_GetPublisherInfoQuery ADD allow_drop int;"
But since that column is added for the first database, when it tries to add it for the second database it fails with the above error.
Is this a bug in the monitoring MP?
Can it be something in our configuration of the replication?
I don't know if this is effecting the second alert I have or not, but I haven't found anything for that error in the logs.
