Awhile back we had a pretty significant series of issues with our Lync implementation that took some time to resolve. Now that it is, I thought I’d write up something for one of them to help others that have run into the same problem so that it can be fixed up once and for all.
Note: these notes come from my own personal experience with a dash of a case we had open with MSFT at the time.
Lync, for those of you who aren’t familiar with it, is Microsoft’s corporate instant messenger suite – which brings voice, (both individual and conferencing), sharing, and IM into one package. If there was ever any program I’ve ever used that’s changed how I work, this is it. Calls, conferences, desktop sharing, all from the same app. Truly well done.
That’s on the front end.
On the backend, however, it gets a little more involved – and for those running the app, there’s a lot of reporting that will allow you to monitor things like call quality, what the system is doing, and if there is trouble in paradise, as it were, you can use this reporting to start narrowing down the issues you’re running into, and that will help you both troubleshoot and resolve them.
And that’s the part that was giving us trouble.
It’d work for a bit, then give us weird errors, and the folks trying to troubleshoot global call/connection issues were completely blocked. So they were frustrated, and we needed to figure out how to fix it – not just this once, but fix it period.
Lync reporting, when we got to the Monitoring Dashboard report, would often render an error like this:
If you can’t read the image, there are two error messages:
“Report processing stopped because too many rows in summary tables are missing in the call detail recording (CDR) database. To resolve this issue, run dbo.RtcGenerateSummaryTables on the LcsCDR database.”
“Report processing stopped because too many rows in summary tables are missing in the Quality of Experience (QoE) database. To resolve this issue, run dbo.RtcGenerateSummaryTables on the QoEMetrics database.”
Bottom line – the error is the same: Some level of activity that’s been happening on the server is not reflected in the troubleshooting reports you want – and the error message has both good and bad parts to it.
The good: As far as error messages go, this one is surprisingly clear. Basically do what it says, and, depending on how much it has to summarize, it takes a few minutes and sums up a bunch of information that is amazingly useful in the reports our Lync team needs.
The bad: It exposes names of objects that someone might not actually want to have exposed, though people who see this are often the people who need to, so it’s a bit of a two edged sword. The other thing is that there’s nothing to give us any indication of how often what’s mentioned in the message needs to run. I figured (as did many of the others who’ve run into this) there was some process that called this set of stored procedures at some defined period, but from what I saw in my research and what I experienced myself, that was not happening for the folks who were running into this. On a hunch, while was on a call with MSFT on a related issue, I discovered that the stored procedure referenced in the above screenshot needs to be run at least daily.
Well that’s not hard…
So my suggestions below are based on the following assumptions:
That you’ve got SQL installed on your server with SQL Agent running – this was something that seemed to be the culprit in a lot of the issues in the links above.
We depend on SQL Agent to run our automation, so it was running but the process/job/scheduler to run the needed code wasn’t there at all. The below instructions fix that.
So I created a SQL job, and scheduled it to run once daily. Since the stored procedures are in different databases, I just wrote the execution commands (below) fully qualified, so you could do the same.
I also created some output files on the jobs just to be sure I could monitor what the jobs were doing for the first little bit, and guess what?
So – if you’re experiencing the issues described above and don’t have a job scheduled on your Lync SQL Server, do this:
- Create a SQL job on the SQL server that has your QoEMetrics and LcsCDR databases on it.
- Give the job a name that makes sense and fits your job naming conventions..
- I named mine: Lync Reporting Monitoring Dashboard Summary Numbers
- Put something like the below in the description field so that once the braincells you’re now using to solve this problem have been overwritten, you’ll still have a clue as to why you did this:
- LYNC – Error appears in reporting when tables haven’t been populated frequently enough. This code was missing and it is confirmed that it needed to be run daily with MSFT. It is scheduled as such. Questions? Contact <you or your SQL operations team>
- Click on ‘steps’
- Add one step – you’ll have to name it… I named mine: RTC Generate Summary Tables – because that’s what it does.
- I leave the database set to master and paste the following code in.
–this code should be run daily per MSFT
- If you want an output file, find your log drive and paste this in there (editing the <drivepath> below as appropriate):
- <drivepath>\MSSQL\Log\LyncPopulateSummaryTables_$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STEPID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).txt (that’s a little snippet from some of the best code I’ve seen at http://ola.hallengren.com)
- I scheduled mine to run at some hour when I’m asleep – and it now takes about 12 seconds to run daily. You may want to adjust the schedule as needed for your environment
- Do that, and you should be able to forget about that report, and your Lync team should be able to know that it’s there every day – whenever they need it.
Take care – and good luck.