Lesson 3: Monitoring Transactional Replication

Activity 3: Configuring an Agent Failure Error

Solution:

1. 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

2. 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

3. 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

4. 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.

5. 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.

Activity 4: Troubleshooting Transactional Replication

Solution:

1. 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).

2. 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.

3. 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.

4. 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.

   Note

   You can also skip this error, as mentioned in Exercise 24: Problem 4 – Row Not Found at the Subscriber.