Skype For Business - My Notes Part 3

A few more notes, this time from the SQL Deep Dive Session

Why you should use SQL AlwaysOn...

...If you have deep pockets :). Note the requirement for Enterprise Edition for AlwaysOn AG. You can do a two node AlwaysOn Clustering Instance with Standard Edition. This is probably where most people will end up going if they don't want to stick with Mirroring.

 AlwaysOn AG only on Skype for Business and minimum of SQL 2012 SP1

 The limitations, on the SfB side, of AlwaysOn. SfB will not stop you from configuring this wrong but you will go into the Microsoft unsupported zone.

SQL In-Place Upgrade

When configuring SfB with AlwaysOn you will need to build the DBs on the first node before you configure AlwaysOn on the SQL side. Type the listener FQDN on the first box and the first node FQDN on the last one. After topology creates the DBs you can configure AlwaysOn in SQL and after that's all done you can come back to the topology, edit it and change the last box to the listener FQDN.

Main issues found with SQL AlwaysOn and SfB.

SQL Clustering now supported in Greenfield scenarios

Using SQL Mirroring and SQL Clustering

Lync Server 2013 supports the user of either SQL mirroring or SQL clustering for each Lync Server database. You can easily set up SQL mirroring with the Topology Builder tool in Lync Server 2013. For SQL failover clustering, you must use SQL Server for setup.
Lync Server 2013 supports SQL clustering topologies for all deployments, including greenfield deployments and organizations that have upgraded from previous versions of Lync Server.
SQL Clustering support is for an active/passive configuration. For performance reasons, the passive node should not be shared by any other SQL instance.
The following support is included:
Two-node failover clustering for the following:
Microsoft SQL Server 2012 Standard (64-bit edition). Additionally running the latest service pack is recommended.
Microsoft SQL Server 2008 R2 Standard (64-bit edition). Additionally running the latest service pack is recommended.
Up to sixteen-node failover clustering for the following:
Microsoft SQL Server 2012 Enterprise (64-bit edition). Additionally running the latest service pack is recommended.
Microsoft SQL Server 2008 R2 Enterprise database software (64-bit edition). Additionally running the latest service pack is recommended.

More information @ Technet

Changing the shared folders location

I recently i had to change the shared folders locations on a OCS R2 installation and it all went smoothly, or at least appeared to :)

Instructions are simple, insert your CD or go to the folder where you have OCS R2 setup and re-run the setup. Click on Prepare Prepare Environment and then Create Enterprise Pool.

You will then face the same questions that you had to fill when you installed it. At some point the wizard will ask for the share folders location and you can change then.
Just make sure on the Reuse Existing Database page, clear the Replace existing database check box or else all your existing data will be lost (not good!).


Looks simple and effective but... it lacks some steps.


First you will need to go over to the Front End IIS and re-map all the virtual directories to the new shares. This one is easy to notice as you get alot of warnings soon enough.

Second, you will find this warning on event viewer:


Event ID 32020 Task Cat. (1055)

Unable to create the Application Host data directory.

Unable to create the directory \\ocs old shares\AppStore\Microsoft.Rtc.Applications.Acd. The network path was not found.
.
Cause: See error message.
Resolution:
Create the directory manually.

Oops... looks like something wasn't changed. :-)

What i had to do to change it was (it might be another solution but i didn't found it):

Go to Start -> Run -> wbemtest
Click connect and type root\cimv2 and then connect.

Now, click on query and type this:

Select * from MSFT_SIPApplicationConfigSetting where Backend="sql_instance"

There will be a few instances returned but only one will have the DataLocation filled. You will have to change that to the new location.

And it's sorted.

Useful OCS SQL queries (Part II)

Another day, another query. This time, a more important one that can be used for accounting.

This query will show all the completed calls to the PSTN, showing call time, who dialed and where to. Advanced reporting could be built on top of this query to find out how much this calls cost.

Use LcsCDR
SELECT    CONVERT(varchar(10), VoipDetails.SessionIdTime, 103) [Start Date],
        CONVERT(varchar(10), VoipDetails.SessionIdTime, 108) [Start Time],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 103) [End Date],
        CONVERT(varchar(10), SessionDetails.SessionEndTime, 108) [End Time],
        CONVERT(varchar(10),SessionDetails.SessionEndTime - VoipDetails.SessionIdTime,108) [Time],
        REPLACE(REPLACE(Phones.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [From Number],
        Users1.UserUri [From Sip],

        REPLACE(REPLACE(Phones1.PhoneUri,';phone-context=LOCATIONPROFILEHERE',''),';phone-context=enterprise','') [Number Dialed],
        Users3.UserUri [On Behalf Of],
        Users4.UserUri [Referred By]

FROM         VoipDetails LEFT OUTER JOIN
                      SessionDetails ON VoipDetails.SessionIdTime = SessionDetails.SessionIdTime AND
                      VoipDetails.SessionIdSeq = SessionDetails.SessionIdSeq LEFT OUTER JOIN
                      Phones ON VoipDetails.FromNumberId = Phones.PhoneId LEFT OUTER JOIN
                      Phones Phones1 ON VoipDetails.ConnectedNumberId = Phones1.PhoneId LEFT OUTER JOIN
                      Users Users1 ON SessionDetails.User1Id = Users1.UserId LEFT OUTER JOIN
                      Users Users2 ON SessionDetails.User2Id = Users2.UserId
                      LEFT OUTER JOIN Users Users3 ON SessionDetails.OnBehalfOfId = Users3.UserId
                    LEFT OUTER JOIN Users Users4 ON SessionDetails.ReferredById = Users4.UserId

where SessionDetails.ResponseCode = 200 and
    Users2.UserUri is null and
    SessionDetails.SessionEndTime is not null
order by VoipDetails.SessionIdTime

You need to change the red text to match your profile(s) so it 'cleans' the number.

Outcome:

Useful OCS SQL queries (Part I)

After you get a fully functional OCS environment you want to check some statistics on it's use.
Since all of the info is on the DB it should be easy to get it and even create some reporting (Reporting Services) on top of it.

Next i'll show you some queries i find useful to have at hand.

Client versions

You can check a overall of client versions on your environment using OCS MMC:

But, you might want to get a little more details, as in, who's using what version. You can do that by running this query on the backend.

SELECT     rtc.dbo.Resource.UserAtHost, CAST(rtcdyn.dbo.Endpoint.ClientApp AS varchar(128)) AS ClientApp
FROM         rtcdyn.dbo.Endpoint INNER JOIN
                      rtc.dbo.Resource ON rtcdyn.dbo.Endpoint.OwnerId = rtc.dbo.Resource.ResourceId
order by UserAtHost

This will return a list showing what versions are being used and who's using them. It will even show if the same user is using different devices (as in one user on CWA, MOC, COMO, etc at the same time).

If you need the same query that MMC uses, this is it:

SELECT CAST(ClientApp AS varchar(128)) AS ClientApp, COUNT(*) AS Clients
FROM rtcdyn.dbo.Endpoint
Group by ClientApp

It's not something you will use on a daily base but it's useful to have.