SQL Server Replication Management Pack SCOM
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.
Syncing local db with cloud db
I have a web application that runs on SQL 2012 locally in the office, We have a virtual server in the cloud hosting a copy of the web application so management connects to it and run report the next day after a backup of the local db is restored on the cloud server. How can i sync or replicate the local with the virtual server which will be near realtime.