SQL Database Synchronization Failure Alert

I tried this script Re: Database Failover Alert  but it conflicts with 2 select statement.. how do i write to fix it

As you have figured out, you cannot edit the prepopulated select statement.  Therefore, you would have to join the prepopulated with the query.  However, this prevents you from specifying the additional fields that you are trying to select.  It would look something like the following.

JOIN sys.databases d ON d.databaseid = APM_SqlBbDatabaseAlertsData.databaseid
JOIN msdb.sys.database_mirroring m ON m.database_id = d.database_id

WHERE (m.mirroring_state_desc IS NOT NULL AND m.mirroring_state_desc <> 'SYNCHRONIZED' ) 

OR (m.mirroring_witness_name IS NOT NULL AND m.mirroring_witness_name <> '' AND m.mirroring_witness_state_desc <> 'CONNECTED' )

The problem appears to be that SAM is not able to look inside the sys and msdb tables to do the join.  If you try something simple such as the below join, you can see that the query validates.

JOIN APM_SqlBbDatabaseAlertVariable d ON d.databaseid = APM_SqlBbDatabaseAlertsData.databaseid

But simply changing it to the below causes it to error out.

JOIN sys.databases d ON d.databaseid = APM_SqlBbDatabaseAlertsData.databaseid

While I don't have an answer for your initial question, it doesn't appear that the query you found is going to work.

Hi.. Thanks for the reply.. i tried the query like you said but it still gives me error.. not sure if i did it right

JOIN APM_SqlBbDatabaseAlertVariable d ON d.databaseid = APM_SqlBbDatabaseAlertsData.databaseid

JOIN msdb.sys.database_mirroring m ON m.database_id = d.database_id

WHERE (m.mirroring_state_desc IS NOT NULL AND m.mirroring_state_desc <> 'SYNCHRONIZED' )

OR (m.mirroring_witness_name IS NOT NULL AND m.mirroring_witness_name <> '' AND m.mirroring_witness_state_desc <> 'CONNECTED' )

I don't think it's going to work as long as you are trying to query the system databases.

Is there any way to trigger the alert for synchronization failed?? some event id or something ??

The custom alert area you are working on is to allow you to create custom conditions for alerts within the solarwinds db directly, you wouldn't use this to query a remote system.

System databases are definitely accessible, but you don't set them up as custom alerts, you are looking at the wrong place entirely.  You need to create a SAM template and add in a SQL User Experience monitor and you can have it execute the query there against any database you point it at.

Monitor a SQL Server item using a custom query - SolarWinds Worldwide, LLC. Help and Support

Accessing sys databases are fine as long as the credential you use for the SAM monitor can access them.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

Hi.. thank you for the reply.. can you also help me with query where the user would experience problem if the synchronization failed or got hung up. I am not a scripting person so not sure how to write it.

You would probably want to coordinate that one with a DBA or your SME for the application, but I would assume a reasonable test would be to execute a modified version of one of the queries that the client systems execute, if it fails or exceeds the time threshold then that tells you the client can't execute it.

The modification would be because SAM user experience monitors are only supposed to return one row.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems