Is SQL 2016 older than SQL 2005? Or is your outdated SQL Server Management Studio (SSMS) lying to you?
Sometimes, the most interesting discoveries come from being lazy.
Case in point: I was working with a few other DBAs on a client system having issues with replication. Being that I was the only one among them who had access to that system, I shared my screen on a remote call and essentially “drove” while they offered advice and tried to brainstorm potential solutions. This is critical information because had I not been the one “driving” (and had it not been my call on what server we used to connect), we wouldn’t have investigated a replication issue using a severely outdated version of SQL Server Management Studio.
We were investigating an issue with SQL 2016 replication… on SSMS 2014.
At that point, unbeknownst to me, the “DUNCE” cap was securely on my head.
Surprisingly, the issue of which SSMS version we were using didn’t come up until later in our investigation, when we noticed some interesting error messages that, at first, made us think the client’s replication was worse off than it actually was. Thankfully, the situation was resolved in the end once we began using SSMS 2018, and, subsequently, the replication monitor began to work. However, this is an excellent opportunity to discuss those unusual errors in case you find yourself in this same situation and you’re wondering how your SQL 2016 replication setup could possibly be older than SQL Server 2005.
The Setup
In my lab environment, I created a small replication setup. The publisher was a SQL Server 2016 instance, and my two subscribers were hosted on SQL 2014 and 2008 R2 (the subscriber versions, I found, don’t really matter too much in this small of an environment; we’re focusing more on the SSMS version we’re using to troubleshoot). I have three versions of SSMS installed in my lab: 2018, 2014, and 2008 R2. For this experiment, to replicate what we encountered, I will use SSMS 2014.
So, in SSMS 2014, I right-click the “Replication” folder for my publisher (which also serves as a distributor) in Object Explorer and select “Launch Replication Monitor”. Standard enough so far. I navigate to my individual subscription, right-click it, select “Show Details”, and…
The Error
“Replication Monitor could not open the Detail Window.
Specified cast is not valid. (ReplicationMonitor)”
Well, that’s unusual.
We then navigate to the “Agents” tab, try to right-click and “Show Details” on my Snapshot Agent, and…
“Replication Monitor could not open the Detail Window.
The start context provided for this Detail Window is incomplete. The following input is missing: SnapshotAgentName (ReplicationMonitor)”
If you see this error, the first thing you might think is that replication hadn’t been set up properly on this publisher. However, that is not necessarily the case. Don’t panic, and make sure you’re using a supported version of SQL Server Management Studio when you’re troubleshooting your replication.
Finally (and this one was the most baffling of all, as well as my personal favorite, looking back), assume you’re troubleshooting a transactional replication subscription and you want to start a tracer token. You navigate to the “tracer token” tab of Replication Monitor, select “Insert Tracer”, and…
“Tracer tokens are supported only for Publishers running SQL Server 2005 or later.”
At this point during the initial investigation, we thought something was seriously wrong with the client’s replication setup. The publisher was running SQL Server 2016—in what universe was that older than 2005?! Just how old was the database they had set up for replication?! Looking back, yes, this was obviously a lousy catch-all output from SSMS’s back-end code (“if not ‘2005’, ‘2008’, ‘2008 R2’, ‘2012’, or ‘2014’, then…”), but given how tense we were during the troubleshooting process, a strange message like that just made matters much, much worse.
However, rest assured! As I said earlier, SSMS is mostly to blame for those strange messages. Here is a sample of what you should be seeing (in SSMS 2018) when you select “View Details” for the individual subscription, Snapshot Agent, and when starting a tracer token:
(Note: I repeated this experiment in SSMS 2008, and the same error messages appeared under the same circumstances. There were no significant differences that warranted separate screenshots.)
I hope this quick demo helped ease your mind when it comes to troubleshooting replication. Replication can be stressful to maintain, and pre-“SQL Server Tools” versions of SSMS certainly don’t help when thrown into the mix. If you feel it’s too much for you to handle, give one of our VirtualDBAs a call, or contact us. We’re available 24/7 to help you keep your SQL Server environment in the best shape possible so you can have peace of mind and a more productive life.