This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Alert - AG Data Loss Alert

DESCRIPTION

This alert will query data already collected by DPA to be used in a custom alert. It will trigger based on thresholds you establish for estimated data loss due to latent AG replication.

ALERT DEFINITION

To create this alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return making it look similar to this example.

Alert Name: AG Data Loss Alert

Execution Interval: 4 Minutes

Notification Text: Potential data loss if forced failover occurs

Available Instances: <choose any SQL instances>

SQL Statement:

select est_data_loss from con_ag_database

Execute Against: Repository

Units: Seconds

High Threshold: User Defined

Warning Threshold: User Defined

  • Forgive me, I am not very proficient with SQL queries/statements. When I use the statement and test the Alert, it says "Invalid object name 'con_ag_database'.

    What am I doing wrong?

  • You might have to fully qualify that object. Who is the schema owner of that object in your environment?

    For example, you might have to specify ignite.con_ag_database if you aren't querying as the schema owner...

  • I am logged into the DB and don't see ignite.con_ag_database. The closest thing is ignite.CON_AG_SUM_13.

    I know I need to update my DPA instance. Probably included in new release? Current version is 11.0.373.

  • That table should have been present in DPA 11.0 unless something happened during upgrade. The current version of DPA is 12.0 and we'll soon be releasing 12.1 (DPA 12.1 RC2 is currently in your portal).

    You might try the following query which will give you the data for each database/replica:

    SELECT 'DB: '+DB_NAME+', Replica: '+REPLICA_NAME AS db_and_replica, ISNULL(EST_DATA_LOSS , 0) AS estimated_data_loss

    FROM ignite.CON_AG_DATABASE

    If the tables are in the dbo schema vs ignite schema, just remove the "ignite." from the FROM clause.

  • seanfoic

    I just looked as I wasn't positive but, the table you list was added in DPA 11.1 not in 11.0 so you will, in fact, need to upgrade