SharePoint 2013 SQL Server Always On

We will go through the process of using always on Availability group with a Content Farm 1 which is on SharePoint 2013, all components on Windows 2012

Environments

Server	Role	Operating System
SQL01	Primary Replica SQL Server 2012	Windows 2012 Standard
SQL02	Secondary Replica SQLServer 2012	Windows 2012 Standard
ContentFarmB	SharePoint 2013 Enterprise Edition	Windows 2012 Standard
ContosoDC	AD Domain Controller	Windows 2012 Standard
SQLAAG01	Always on Availability Group 1

I have used a script to install SharePoint 2013 and have mentioned a SQL Alias called ContentFarm1 pointing to Always on Availability Group 1 named SQLAAG01

If installing using the vanilla interface you can create a SQL Alias and point it to Always on Availability Group 1 or you can also use Always on Availability Group name

I have already configured Always on Availability group named SQLAAG01 consisting of two replica’s primary SQL01 and secondary SQL02

After you have installed SharePoint server and run the configuration wizard, all databases i.e. the configuration, admin and content databases are created on Primary replica.

You will have to manually add the databases into Always on Availability Group. Here are the steps to follow

The scripted install created two databases i.e. 

• ContentFarm1_Config
• ContentFarm1_Admin

We don't have it yet in the Always on Group.

First take a backup of the two databases as its a prerequisite to have the backup in the tail log

 Expand the AAG Group, Right Click on Availability Database, Click on Add Database

Click on Next 

Select the Database , Click Next

Specify  Data Synchronization location, I have already configured a shared network location called Backup 

Connect to the Secondary Replica , click on Connect

This does an availability group validation , Click Next

Click Next

Now I have got the databases synchronized 

Expand the Availability Database to check if the Databases have been added 

There we have it. Ideally I would script this when doing it in Production environment. This is just for the capturing this in the demo.

Project Web App (PWA) site loading pretty slow in Project Server 2013

We had an issue when trying to view My Tasks on Project Web App site on Project Server 2013 it consumed 20-30 seconds to load this. Here is how I started troubleshooting this

1)      Started with the ULS logs but couldn’t go any further.

2)     From some MSDN article suggestion was to disable the proxy server as it can cause the issue. Did so but didn’t help to solve the issue.

There is a good blog which talks about if server not connected to internet can result in PWA loading slow due to validation of certificate , having SP cert in Trusted root store etc.

Performed all those but it wasn’t applicable in my case as each and every unique environment is different.

3)     Enabled the Developer Dashboard, replicated by navigating to My Tasks

·         Understood from Developer Dashboard, that web services consumed lot of time to fetch the info from MS schemas

·         The stored procedures from SQL Server was taking time to respond to the application request

Developer Dashboard was handy to identify the bottle neck. Performed the following steps to resolve this:

• ·         DBCC SHOWCONTIG displays fragmentation information for the data and indexes
• ·         Rebuild the indexes on PWA DB
• ·         Updated the statistics

1) Run DBCC SHOWCONTIG

2) Rebuild the indexes on Project Web App DB:

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

You can change the number for SET @fillfactor between 70 to 100.

3) Update Statistics:

sp_updatestats

How to check memory allocation for distributed cache on SharePoint 2013?

Couple of weeks back, I built a proof of concept environment for SharePoint 2013 which is a 3 tier architecture on Windows 2012 and SQL Server 2012 servers. In this article I will try to check the memory allocation for Distributed Cache.

Server	Role	Memory	VCPU
Ls-sppr13wf-01	Web Front End	8 GB	4
Ls-sppr13si-01	App Server	8 GB	4
Ls-sppr13sql-01	SQL Server	12 GB	4

During the installation 5% of the server memory is automatically allocated to the Distributed Cache Service’s cache size. If you don’t want Distributed Cache to run on all the servers after the installation you can use PSConfig with skipregisterAsDistributedCacheHost flag on all servers that are not cache host.

In my case we didn’t do this we left what is configured by default for Distributed Cache during the installation.

Anyways, to identify how much of memory is allocated by default to Distributed Cache, it’s 5 % of the memory. The web front end has 8 GB i.e. 8*1024 = 8192 MB. So 5% of this shall be around 409.5 MB

Run the following Powershell to check what's been allocated by default.

Use-CacheCluster

Get-AFCacheHostConfiguration -ComputerName ls-sppr13wf-01 -CachePort "22233"

 And it allocated 5% of the memory i.e. 410 MB

Note:  All Servers running the Distributed Cache service must match exactly in terms of total memory specifications on all Servers in the cache cluster  

There is an excellent article and planning and using the distributed cache service must read before thinking of implementing the Distributed Cache.

I have just started working on with Distributed Cache, will continue my learning curve .

SharePoint 2010 October 2012 Cumulative Update a nightmare

 Got this error while running the Configuration Wizard on a Test environment which constitutes of a App and WFE server after installing the October 2012 Cumulative Update.

An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown.  Additional exception information: The upgrade command is invalid or a failure has been encountered.

Failed to upgrade SharePoint Products.

Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException: Exception of type 'Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException' was thrown.

   at Microsoft.SharePoint.PostSetupConfiguration.UpgradeTask.Run()

 Resource id to be retrieved is PostSetupConfigurationFailedEventLog for language English (United States)

Resource retrieved id PostSetupConfigurationFailedEventLog is Configuration of SharePoint Products failed.  Configuration must be performed in order for this product to operate properly.  To diagnose the problem, review the extended error information located at {0}, fix the problem, and run this configuration wizard again.

Leaving function StringResourceManager.GetResourceString

Configuration of SharePoint Products failed.  Configuration must be performed in order for this product to operate properly.  To diagnose the problem, review the extended error information located at fix the problem, and run this configuration wizard again.

Entering function StringResourceManager.GetResourceString

Upgrade Timer job is exiting due to exception: System.Data.SqlClient.SqlException: Cannot open database "Search_Service_Application_1_CrawlStoreDB  " requested by the login. The login failed.

Login failed for user

Steps performed to reach towards resolution:

Installed October 2012 Cumulative Update on WFE and APP Server

Ran Product Configuration Wizard and it failed

Tried PSConfig.exe -cmd upgrade -inplace b2b –wait  - This too failed at the last step

Tried psconfig -cmd upgrade -inplace b2b –force – this too failed at the last step

IISRESET to both SharePoint Servers

Restarted SharePoint Timer Service

Restarted SharePoint Search Service

Ran PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures – this one failed at the last step

Failed why: There was an issue with the culture it was finding the culture as United States rather than United Kingdom

Note: The cumulative updates are bundled into one package from October 2012 onwards i.e. contains the foundation, server , language packs in the same package

Ran psconfig -cmd installcheck –noinstallcheck  - this worked fine but failed again at the last step

Failed why: the error was due to the crawl and admin databases of search

Cleared the SharePoint Cache on btoh WFE and APP Servers

Ran Get-SPProduct -local

PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

This solved the issue phew. This was one nasty thing to install the October 2012 CU.

I don't know what I would have done without the ULS Log my good friend.