Open the replication monitor and select the publication you wish to set the alert for. Select the Warnings tab in the right-hand pane:

Figure 3.69: The Warnings tab

In the Warnings tab, click on the Configure Alerts button. In the Configure Replication Alerts dialog box, select the Replication: agent failure replication alert and click on the Configure button:

Figure 3.70: The Configure Replication Alerts dialog box

In the Replication: agent failure alert properties window, change the database name to AdventureWorks and check the Enable checkbox besides the alert name:

Figure 3.71: The Replication: agent failure alert properties window

Under Select a page, select the Response page. In the Response page, check Notify operators and then check the E-mail checkbox for the Alerts operator:

Figure 3.72: The Response page

Click on OK to create the alert.

Open SSMS, if it's not already open. Connect to the publisher SQL2016. Expand SQL Server Agent | Alerts. Locate and double-click on Replication: agent failure to open the alert properties. Observe that the alert has the same settings as configured in the previous steps. In the alert properties dialog box, under Select a page, select History:

Figure 3.73: The History page

Observe that the alert history tells us how many times the alert has occurred. You can also change the alert properties from this dialog box.

Execute the following query at the publisher server to find out the error:

SELECT 
  time,
  error_text,
  xact_seqno 
FROM distribution.dbo.MSrepl_errors 
ORDER BY [time] DESC

You should get the following error in the error_text column:

Violation of PRIMARY KEY constraint 'PK_Currency_CurrencyCode'. Cannot insert duplicate key in object 'Sales.Currency'. The duplicate key value is (XYZ).

As per the error, the transaction can't be applied at the subscriber database because it violates the primary key constraint. Execute the following query at the subscriber database to find out the primary key column for the Sales.Currency table:

SELECT  si.name AS PrimaryKey,
        OBJECT_NAME(sic.OBJECT_ID) AS TableName,
        COL_NAME(sic.OBJECT_ID,sic.column_id) AS ColumnName
FROM    sys.indexes AS si INNER JOIN 
        sys.index_columns AS sic ON  si.OBJECT_ID = sic.OBJECT_ID
                                AND si.index_id = sic.index_id
WHERE   si.is_primary_key = 1
AND si.object_id = object_id('Sales.Currency')

You should get the following output:

Figure 3.74: The primary key column

The primary key column is CurrencyCode.

Execute the following query to check if the currency code XYZ already exists at the subscriber database:

SELECT 
  * 
FROM Sales.Currency 
WHERE currencycode='XYZ' 
ORDER BY ModifiedDate DESC

You should get the following output:

Figure 3.75: Checking if the currency code exists at the subscriber

The currency code XYZ does exist at the subscriber database.

To fix the replication, execute the C:\Code\Lesson03\Activity\Fix.sql query at the subscriber. The script deletes the currency code XYZ at the subscriber. This allows the replication command to succeed and fixes the replication.