SharePoint Performance : After SQL upgrade

Issues : Performance problems

Root Cause :  SQL compatibility level  of 130 or 140 which caused decreased query performance and increased CPU usage on the database server.

Resolution : Changing the compatibility level to 110 resolved the performance problems

How to fix :

We strongly recommend that you set the database compatibility level to 110 for SharePoint Server 2016 content databases. To change the compatibility level, run the following TSQL command:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 110

You can view the compatibility level of all the databases in an instance of SQL Server by using the following TSQL query:

SELECT name, compatibility_level FROM sys.databases

 Detail URL :
https://blogs.technet.microsoft.com/stefan_gossner/2018/12/02/sp2016-performance-problems-caused-by-sql-database-compatibility-level-130-or-140/


This also applied to SharePoint2013 ( when you upgrade to SQL server and new content database version will created under compatibility level 120 )

OWA License

Issues : User unable to edit the excel online (OWA - Office Web Apps ) 

root cause : User is not inside the license group

Step to verify your valid license is for which user  :

• log in to sharepoint server run following command : Get-SPUserLicenseMapping
• Result will show as following :

Identity       : 6a120a5b-a14c-4cb7-b37e-xxxxxxx

License        : OfficeWebAppsEdit

Name           : ABC\arepointOWALic  >> this is the domain group have license to use edit at OWA

ClaimValue     : S-1-5-21-1008349960-1601062091-620655208-60439

ClaimType      : http://schemas.microsoft.com/ws/2008/06/identity/claims/groupsid

OriginalIssuer : Windows

ValueType      : http://www.w3.org/2001/XMLSchema#string

Solution : add use to the group (make sure your business policy is allow) then solved.

for more information about the  map user license enforcement can get from here :

https://www.petri.com/sharepoint-2013-user-license-enforcement

Managing licenses can already be a headache, but it can be even more painful when SharePoint is involved because it has so many features that are specific to a license type. User License Enforcement is a new SharePoint 2013 feature that allows administrators to assign specific license features by mapping those license features to users in specific Active Directory groups. This article will show you how to utilize SharePoint’s new licensing capabilities, which gives administrators better control of how licensed features are managed and assigned to users.

New SharePoint Licensing Features = New Admin Abilities

In previous versions of SharePoint, when a server was activated with the Enterprise feature, it was very difficult to determine which users actually used the Enterprise and Standard features. In most cases, administrators would deploy another server that only had Standard features activated, and they would try to restrict users to accessing only that server. Sometimes they would even resort to deploying separate SharePoint farms to ensure the correct licensing was used. These methods resulted in over-deploying servers, which increased deployment costs.

The new licensing features will give administrators the ability to deploy a server with all the features, both Enterprise and Standard, but they will restrict users to use only the licensed features to which they are assigned. This can be cost effective for many organizations, as it can reduce the amount of servers that need to be deployed. Smaller organizations can utilize a single farm for blended licenses, such as deploying Project Server for certain users while still providing Standard Cals for majority of their business users. Note: SharePoint web applications must use Claims-based authentication; however, since that is the default in SharePoint 2013, you either will need to be moving towards Claims-Based or already be using Claims-Based Authentication.

SharePoint 2013’s user license enforcement is disabled by default and must be enabled before enforcement can begin. User License Enforcement configurations can only be completed in PowerShell using the eight available cmdlets.

The license CAL assignments that can be mapped to users are divided into five categories:

• Standard – Delivers the core capabilities of SharePoint, including basic Search functions and Communities platform
• Enterprise – Provides all the Standard Cal features, including the following Enhanced Search, Business Solutions (Access Services, InfoPath Services), and Business Intelligence (Excel services, PerformancePoint Services, and Visio Services)
• Project – Access to Project Server that is installed within the SharePoint farm
• DUET – Allows interoperability with SAP applications
• WAC – Access to Office Web Apps

Enforcement is completed by mapping a security group in Active Directory to one of the five license categories, then adding users to the security groups. When the user in the AD group logs into SharePoint, usage data gets logged onto the farm. When the user attempts to access an unlicensed feature, they are blocked. License enforcement can be applied at different levels, such as site pages, document libraries and lists, so proper license mapping planning should be done to ensure users are correctly mapped.

Enable and Map User License Enforcement

Enable user licensing:

• Start the SharePoint Management Shell.
• Type the following cmdlet to enable license enforcement on the farm: Enable-SPUserLicensing

• To confirm that licensing enforcement has been abled, use the Get-SPUserLicensing cmdlet. You should see the “true” value.

Map licenses to AD group:

• Start the SharePoint Management Shell.
• Type the following cmdlet to map license categories to AD security groups (change “yoursecuritygroup” with the name of your AD group to which you want it to map): $a=New-SPUserLicenseMapping -SecurityGroup yoursecuritygroup -License Enterprise

• $a | Add-SPUserLicenseMapping

The User License Enforcement in SharePoint 2013 adds more management capabilities for licensing than previous versions, but it is the not the complete solution for ensuring licensing compliance. You will still need to do your reporting and license reconciliation to ensure that you have purchased the appropriate amount of licenses used. If you’d like, check out this further reading on SharePoint 2013 licensing.