Managing the Over-Subscribed in OCS and Lync

Article
05/17/2011

MaxCategorySubscriptions is the system-wide parameter that determines how many users can be watching a given user for presence changes. By default, this limit is 1000 subscriptions. When a given user reaches this limit (more than 200 users are ‘watching’ them by having them on their Contact List for example), they can still be added to another user’s contact list but presence data is not displayed. In OCS, a message is given that says Presence Data Unavailable. In Lync, the message is more precise: Maximum Followers Reached.

Managing the oversubscribed can be a challenge for many enterprise customers as Microsoft does not provide out of the box tools for monitoring and identifying the oversubscribed, much less managing the challenge itself. This blog post provides an approach to identifying the degree of oversubscription, the users who are oversubscribed and who they are oversubscribed to, and a method for managing the challenge.

Note: The queries and sample code-sets provided in this document are not actual product code and are best tested first in a lab environment before being run against a production pool. While the queries do not result in a change to the product, they will introduce an additional load to the backend database when run. Likewise, since the SQL trigger offered as a potential solution was not released with the product, it is not supported code.

How MaxSubscription Works

When a user is oversubscribed (they are at the MaxSubscription limit), other users who attempt to add them to their contact list will not see Presence data. However, the experience is somewhat inconsistent because the nature of “subscriptions” is dynamic and the number of subscriptions do not directly correlate to the number of users due to MPOP and modalities (for more information, see this 3^rd party blog: https://msunified.net/2010/10/25/contacts-vs-subscribers-in-ocs-2007-r2/ ).

In short, there are at least five category subscriptions taken-out on a user for each person who has them on their contact list. As such, 200 users=1000 subscriptions.

Consider the following scenario for “Alice”:

Alice logs into Lync in the morning. 200 other users who have Alice on their contact list login between 9am and lunch. Each user’s client takes five category subscriptions out on Alice: calendarData, contactCard, note, device, services, state. At this point, Alice is “oversubscribed”. Note that I stated each user's "client": if a user is logged into OCS R2 on multiple devices/endpoint (such as a Client Web Access and on a desktop client), then that user will take 10 subscriptions for Alice.
As such, when Bob (who has Alice on his contact list) logs into Lync just before lunch, he sees no presence data for Alice, only a message that states: Max Followers Reached. And then he calls the Help Desk - :)

However – Bob will not always have this experience. For example:

When everyone leaves for lunch (including Bob), half of the users don't just lock their machines but actually “logoff” - so their subscriptions to Alice are cancelled/cleared.
After lunch, Bob logs in before most of the other users do. When he does, he will be able to take out a subscription on Alice and see her presence. However, users who login after Alice is eventually oversubscribed later that afternoon will not - even if they could earlier that day.

Obviously, the nature of this dynamic can give a user community the impression that the product isn't working effectively, and while oversubscribed users are still accessible via IM - the lack of presence data is certainly a challenge to those who work consistently with that person.

To eliminate the challenge, only two options exist:

The subscriptions taken-out against that user must be reduced – which requires the IT Administrator to either augment the subscription process or to simply augment contact lists. The former requires a tool while the latter requires that the Administrator know who is oversubscribed and what users are regularly taking out subscriptions against them.
The limit for "subscriptions" be raised for certain users. Microsoft does allow for the use of highly-targeted presence policies that enable this configurable limit of MaxCategorySubscriptions to be adjusted. However there is no data published from Microsoft to indicate or calculate how the raising of this limit affects performance of the SQL backend. As such, it is recommended to be used in either limited scope (few users) on a large pool or in a wider scope within a smaller pool (such as for executive-level users) where backend resources are not likely to be running near their limits (see more below).

Augmenting the Subscription Process via SQL Trigger

An unsupported sample code-set was developed for the Lync/OCS backend (a SQL trigger) by Microsoft engineer Tommy Mhire (tmhire). This SQL trigger manages the subscription process for those who are already at 900 current subscriptions (though this value is configurable). Our intent is that this sample code should be evaluated and monitored within the Innovate environment first and then gradually introduced to production pools. This is a custom SQL trigger, and while not complex it is certainly not code from the Microsoft Product Group. It is very likely that certain updates to the backend (Cumulative Updates) would result in a resetting of the RTCDYN SQL database which would require the re-application of the trigger, so seeing this as an add-on management “tool” is probably best until a better solution is found.

Below is some information on the trigger as well as the SQL code itself:

Behavior of the trigger

Checks who the incoming category subscription is for then checks whether that publisher is over the trigger defined limit of category subscriptions (900)
If the publisher is above the limit, it itemizes the subscribers who have category subscriptions for that publisher who ARE NOT on the publisher’s contact list
It then take the subscriber with the oldest category subscription who is not on the publisher’s contact list and deletes ALL of their category subscriptions

Behavior in the environment

Subscribers who are on a publisher’s contact list WILL ALWAY see the publisher’s presence

New subscribers ARE ALWAYS given precedence over old subscribers (i.e. old subscribers are pruned first)
NO CHANGE to behavior is experienced if the publisher is below the category subscription limit

SQL Code

create trigger CategorySubscriptionFixer
on rtcdyn.dbo.CategorySubscription
FOR INSERT as
begin
                declare @PublisherId int
                set @PublisherId = (select top 1 PublisherId from inserted)
                if (select COUNT(*) from rtcdyn.dbo.CategorySubscription where PublisherId=@PublisherId) > 900 begin
                                declare @categorySubscriptionToDelete table (PublisherId int,DeliveryId int)
                                insert @categorySubscriptionToDelete
                                select top 1 categorySubscription.PublisherId,categorySubscription.DeliveryId
                                                from rtcdyn.dbo.CategorySubscription categorySubscription, rtcdyn.dbo.DeliveryContext deliveryContext
                                                where categorySubscription.PublisherId=@PublisherId
                                                and categorySubscription.DeliveryId=deliveryContext.DeliveryId
                                                and deliveryContext.SubscriberId not in (select BuddyId from rtc.dbo.Contact where OwnerId=@PublisherId)
                                delete from rtcdyn.dbo.CategorySubscription where
                                CategorySubscription.PublisherId = (select top 1 PublisherId from @categorySubscriptionToDelete)
                                and CategorySubscription.DeliveryId = (select top 1 DeliveryId from @categorySubscriptionToDelete)
                end
end

Risk / Impact of Use

The trigger introduces minimal impact to SQL so any perf-related impact is seen as negligible at this time – to be validated during testing
Subscribers who are not on the publisher’s contact list MAY BE pruned and left with a stale presence subscription. Given that any contact list falling into this scenario would have hundreds of subscriptions/contacts already, it seems very unlikely a user will ever be impacted by this since the most current subscriptions will be preserved.

Assessing the OverSubscribed

Some administrators may wish to understand the scope of this challenge for their environment before determining their next steps (such as adjusting their deployed topology or applying Presence Policies). In my experience, even very large deployments (over 100,000 users) have only 1-2% of the population in this situation. The queries below provide several appraoches for estimating level of over-subscription. Since both OCS R2 and Lync Server leverage a SQL backend and both manage the MaxSubscription and MaxCategory parameters the same way, we can leverage these queries against either backend to gather the relevant data. Once the data is gathered, the choice can be made to simply “reset” a user to zero subscriptions (affecting all subscribed users’ contact lists), to eliminate certain subscribers (affecting only their contact list), or apply a Presence Policy to the oversubscribed user.

Queries

Note: The following queries are not shipped with the product and thus are not officially supported. Microsoft recommends testing these queries before use in production. Special thanks goes - again - to Tommy Mhire for the work done to both draft and validate these for use with some of my customers.

Query 1: Identify the Oversubscribed

Use this to see which users are near the max value of category subscriptions (in the problem state or close to the problem state). Based on the default max of 1000 category subscriptions.

select Publishers.UserAtHost Publisher, SubscriberCountByPublisher.numSubscribers from (
     select PublisherId, count(*) numSubscribers from
     rtcdyn.dbo.CategorySubscription CatSubscription
     group by PublisherId
) as SubscriberCountByPublisher
inner join rtc.dbo.Resource Publishers on SubscriberCountByPublisher.PublisherId = Publishers.ResourceId
where numSubscribers > 990

Query 2: Identify Users Subscribed to a Specific Publisher

Use this to see all users subscribed to a publisher who's showing as "Maximum Followers Reached". Modify the "where UserResource.UserAtHost = 'user@domain.com' line.

select SubscriberResource.UserAtHost Subscriber, UserResource.UserAtHost Publisher, Categories.Name Category from
rtcdyn.dbo.CategorySubscription CatSubscription
inner join rtcdyn.dbo.DeliveryContext DeliveryContext on CatSubscription.DeliveryId = DeliveryContext.DeliveryId
inner join rtc.dbo.Resource UserResource on CatSubscription.PublisherId = UserResource.ResourceId
inner join rtc.dbo.Resource SubscriberResource on DeliveryContext.SubscriberId = SubscriberResource.ResourceId
inner join rtc.dbo.CategoryDef Categories on CatSubscription.CategoryId = Categories.CategoryId
where UserResource.UserAtHost = <'user@domain.com'>
order by Subscriber

Query 1 and Query 2 are both effective for “inter-pool” subscriptions. Since they look to the tables for the subscribed user, they provide data on subscribers homed on other pools.

Query 3: Identify all Subscriptions/Subscribers for an entire Pool

Use this to see all the category subscriptions on a given pool (Alice is subscribed to Bob three times).

Note: Query 3 does a combination of the work of the first two in that it will not only provide you a list of all subscriptions, but also the details about who the subscribers are. As such, this query should be leveraged with great care as a live pool with significant load would certainly be impacted by running this query (as opposed to running Query 1 first, followed by a targeted search of subscribers for a given user (Query 2).

A Word about Presence Policies

Since OCS, Presence Policies have been available to administrators for managing subscription levels. These parameters are referenced here: https://technet.microsoft.com/en-us/library/dd572188(office.13).aspx. In Lync, these can be set via PowerShell (CSPresencePolicy).

Two elements are detailed in the TechNet article: CategorySubscriptions and PromptedSubscribers. One might read this and assume "prompting" implies the "asking of permission before you are granted a subscription", but that is simply not the case. When you are prompted you are prompted, when you are not you are not. Nothing about permitting or denying a subscription is within the control of a given user.

As such, the time may come where extending the presence policy for a specific population becomes necessary. I would encourage great care in applying these policies. Some blogs/posts I have seen reference a global setting for this - but there is absolutely no data to suggest what happens when these levels are raised. Subscription processing is on the SQL backend, and for large pools raising these levels simply introduces extra load to the backend on what may or may not be an already busy pool. Care must be exercised in such a way that the backend is monitored when this is done for pools homing any great population. It may be better to re-home users who need a larger subscription base to a smaller pool with more available resources. In that case, an entire pool can be set for a higher than 1000 MaxCategorySubscriptions limit - but that pool may not have more than hundreds of such high-profile users.

Share via