Fix Tracer Tokens in SQL Replication

Problem

We have replication setup on several SQL Servers to support business reporting. We received an alert about latency issues between a publisher and subscriber. The subscriber was more than 30 minutes behind the publisher. When looking into this issue, we were tried to use tracer tokens to troubleshoot the issue, but this feature was not working correctly. I will cover the reason why this was the case and how it was resolved.

Solution

Replication is a feature of SQL Server that allows you to transfer database objects and changes from one database to another within the same instance or to another instance. There are mainly 3 types of SQL Server replication:

Transactional replication is the most popular as data and object changes continuously transfer to subscribers with the help of replication agent jobs. The subscriber data can be near real time with the respective publisher data. If a subscriber database does not receive data frequently and lags behind the publisher, then this is known as latency. So, let’s say a subscriber is not getting updates from the publisher for 20 minutes it means you are 20 minutes behind the real time data and you can say you have 20 minutes of latency.

Now, there could be multiple reasons for this latency. There might be issue with the database, network, issue with data changes, duplicate entries, etc.

Understanding Tracer Tokens

Microsoft introduced a feature called Tracer Tokens for replication for versions of SQL Server 2005 and later to check and troubleshoot latency issues to see if data is properly flowing from publisher to distributor to subscriber. This is a very useful feature to test the data flow.

As per MSDN:

The Tracer Tokens tab allows you to validate connections and to measure the latency of a system that uses transactional replication. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:

How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.
How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

From these calculations, you can answer a number of questions, including:

Which Subscribers take the longest to receive a change from the Publisher?
Of the Subscribers expected to receive the tracer token, which, if any, have not received it?

Tracer Tokens Not Working

So, now let’s come to our issue.

The assigned DBA has decided to use tracer tokens to validate the data flow between publisher and subscriber. He launched replication monitor and then clicked the “Tracer Tokens” tab to access its page. He then got the below window saying “Tracer tokens are supported only or Publishers running SQL Server 2005 or later”. But the servers being used are SQL Server 2016, so this shouldn’t be an issue.

Next, I will show you why above issue was displaying while troubleshooting the latency issue.

Step by Step Approach to Address Tracer Tokens Warning

As the above warning shows, it says it is related to the database engine version, although we are running SQL Server 2016. We decided to check the compatibility level of the published database and here we got our first clue. There was nothing selected in the compatibility level option and it was blank.

To view or change the compatibility levels, we can either use the screens in SQL Server Management Studio or we can use T-SQL statements. I have given both ways to check and change this value.

Using GUI through SQL Server Management Studio:

Expand SQL Server instance name in Management Studio.
Expand Databases folder to correct database for which we must change the compatibility level
Right-click the identified database and then click Properties.
The Database Properties dialog box opens. In the Select a page pane, click Options.
The current compatibility level is displayed in the Compatibility level list box.
Change the compatibility level from drop down list if needed.

Using T-SQL Query:

You can also check and change your database compatibility level using the following query:

 --Run below code to check the compatibility level of your database.
USE DBNAME；
GO
SELECT COMPATIBILITY_LEVEL
FROM sys.databases WHERE name = 'DBNAME';
GO
 
--Change Compatibility level to desired value.
ALTER DATABASE DBNAME 
SET COMPATIBILITY_LEVEL = 130;  
GO

So, I followed the GUI approach to check this setting. We can see the compatibility level shows that nothing is selected in the below image.

This turned out to be the main problem for this warning as the database engine was not able to identify the version of the database. When we clicked the drop down list of compatibility levels to choose from, we were surprised that no values were in the drop down.

We tried to diagnosis why the compatibility level was not set and had no options in the drop down. One thing we identified was that we had connected to the SQL Server 2016 instance using SQL Server Management Studio for SQL Server 2014, so we decided to connect to the SQL Server instance using the same version of SQL Server Management Studio on which database are hosted. Once we established the database connection through SQL Server Management Studio for SQL Server 2016, we decided to check the compatibility level of this database.

We noticed that the compatibility level was not set, but we were getting values in the drop down as shown in the below image. So, we had selected the correct compatibility level as SQL Server 2016 (130) as per the database engine version and clicked OK to save the change.

We launched replication monitor again to see if we could pass Tracer Tokens. This time we did not have any issues and the tab was accessible to pass tokens to check the data flow between the publisher and subscriber.

Summary

From this we learned that it is best not to use a lower version of SQL Server Management Studio to connect to higher version of a SQL Server instance, because it might behave abnormally. In our case we spent time trying to fix an issue that didn’t even exist before we were able to fix the primary issue at hand.

Next Steps

There are two things learned from this experience:

Always use latest version of SSMS while doing administration job
Always validate compatibility levels post upgrading your instances

Resources

Community

Subscribe