共用方式為

Issue deploying Skype/Lync Reports when mirroring SQL - Cannot grant ReportsReadOnlyRole to user

  • 發行項

Hi All

I hit an issue recently deploying Skype reports into a SQL Server backend store that was mirrored. The error you get occurs when you go to deploy reports via the "Deploy Monitoring Reports" in the Skype Deployment Wizard, and the error that is presented is "Cannot grant ReportsReadOnlyRole to user....".

 

This error occurs when you are using SQL Mirroring and the Principal node (active db owner) for the LCSCDR and QoEMetrics databases (the monitoring DBs) is not sitting on the Primary SQL Node for the mirror. For example in the diagram below, I am saying that my active Monitoring Databases should be on the node Lab1-3-SQ1 in the Skype SQL instance. This SQL Server has been configured in a SQL Server Mirror and so it is possible for the LCSCDR and QoEMetrics databases to be hosted on either Lab1-3-SQ1 or Lab1-3-SQ2 depending on the state of the mirror. In my lab's case, for some reason the DBs had failed over to node 2.

So the error occurs when the LCSCDR and QoEMetrics databases are sitting on the secondary node, and so the Deployment Wizard is attempting to hit the primary SQL node and configure SQL permissions on these two databases. As the primary node currently is not the Principal (active node) it is not responsible for making changes to the database. As a result, the deployment wizard's attempt to make permission changes fails. (The below diagram shows what a DB that is not active on the SQL Server I am trying to target looks like in SQL Management Studio). You can also check database mirror state with the Get-CSMirrorDatabaseState cmdlet.

So the trick is to run the Invoke-CSDatabaseFailover cmdlet prior to deploying your reports to Skype. In this particular example I could just run the Invoke-CSDatabaseFailover cmdlet for just the Monitoring database type. (Typically you would run the Invoke-CSDatabaseFailover cmdlet for all of the database types).

Invoke-CsDatabaseFailover -PoolFqdn SkypePool.lab1.org -DatabaseType "Monitoring" -NewPrincipal "Primary" -confirm:$false

This command is saying to move the Monitoring databases for the SkypePool to the Primary SQL Mirror node, which in my lab is the Lab1-3-SQ1 SQL Server. Once I have run that command, LAB1-3-SQ1 database status looks like the below picture. The two monitoring databases are where they should be which is good!

The final comment I need to make is that as you can see from diagram, the other Skype databases are not sitting on Lab1-3-SQ1 as they are marked as "Mirror, Synchronized". This means Lab1-3-SQ2 is currently hosting the active copy of these databases. In a Skype SQL Mirror, we want you to keep all your databases active on 1 node or another. It can happen where you have DBs active on different nodes, and so as a Skype Administrator it is worth checking on this on a daily basis and correcting the issue. To correct the issue I should have run the commands:

Invoke-CsDatabaseFailover -PoolFqdn skypepool.lab1.org -DatabaseType "User" -NewPrincipal "Primary" -confirm:$false
Invoke-CsDatabaseFailover -PoolFqdn skypepool.lab1.org -DatabaseType "Application" -NewPrincipal "Primary" -confirm:$false
Invoke-CsDatabaseFailover -PoolFqdn skypepool.lab1.org -DatabaseType "CentralMgmt" -NewPrincipal "Primary" -confirm:$false
Invoke-CsDatabaseFailover -PoolFqdn skypepool.lab1.org -DatabaseType "Monitoring" -NewPrincipal "Primary" -confirm:$false
Invoke-CsDatabaseFailover -PoolFqdn skypepool.lab1.org -DatabaseType "Archiving" -NewPrincipal "Primary" -confirm:$false

To confirm that the DBs are now on the right SQL Server you can also use the Get-CSMirrorDatabaseState cmdlet that I mentioned above. Here is an example of the cmdlet showing that the state on the Primary node is Principal which means the primary node is currently hosting the active copy of the databases.

If the DBs have failed over to the secondary node, the cmdlet will show the StateOnPrimary as Mirror, as shown below.

The other interesting error I have shown in the SQL Management Studio diagrams were that the LCSLog database is not configured to be mirrored. The LCSLog database is the Skype archiving DB. In my lab, I turned this on after the fact and so I hadn't setup mirroring yet for this database. You can setup mirroring with Topology Builder or the Install-CSMirrorDatabase cmdlet. So if you're being careful and deploying all of your required components together you won't hit this issue. If you wanted to fix this situation you would run the command:

Install-CsMirrorDatabase -SqlServerFqdn "lab1-3-sq1.lab1.org" -SqlInstanceName "skype" -DatabaseType "Archiving" -FileShare \\lab1-3-sq1.lab1.org\temp -Verbose -report c:\archiving.report.html -confirm:$false

Anyway, enough waffling on....the key thing is if you hit that SQL Reporting error, go and check if the Primary node of your SQL Mirror is currently hosting the LCSCDR and QoeMetrics databases!

 

Happy Skype'ing.

 

Steve

Comments

  • Anonymous
    August 12, 2015
    Thanks for the tips and awesome article as usual, Steve. I've seen something similar before that all the default Lync/S4B databases resided on node 1 while LCSLog and QoE databases reside on node 2. So using the above command will definitely fix the problem with the -DatabaseType Monitor.

  • Anonymous
    August 15, 2015
    Thanks Michael, much appreciated