Monitoring SharePoint 2013

Overview: SharePoint farms have several dependencies so to effectively monitor your farm there are a lot of components to review.  Basically there are 2 forms of monitoring: Preventative & Reactive.  Also see my post on performance monitoring SP 2013.

Preventative Monitoring: is reviewing your SharEPoint estate to try identify issues before they occur.  A simple example would be identifying that your database is running out of storage space allowing you to remediate before the system fails.

Reactive Monitoring: is ensuring you are notified as early as possible so you can identify the root cause and fix with minimal downtime.  This can be a simple as waiting for the help desk to escalate that the farm is down.  In maturing this a simple set of web requests that alert the administrators as soon as a service is down is an improvement.

Monitoring needs to be a combination of preventative and reactive monitoring done via automation and manual verification.  As the automation piece improves, there is less reliance on the manual monitoring.

Tooling:
There are a wealth of tools such as SolarWinds and it's competitors "Enterprise IT management from such vendors as CA Technologies (UniCentre), BMC, IBM (Tivili), and Hewlett-Packard (OpenView)."  Wikipedia

SolarWinds Monitoring Screen

Idera have monitoring tools specifically for SharePoint.  SolarWinds is a good option for monitoring SP farms and its dependencies: Windows OS, Machine resources, SQL, SP, WCA/Office Web Apps.  Couple this with web monitoring and you get a comprehensive reactive and preventative monitoring solution.  This will tell you before collapse if the server, OS, SQL or SP is slowing down or running out of resources.  If any or a complete service stop occurs the operations team are notified and it is highlighted where the error is as opposed to "it's not working".

AvePoints, DocAve 6 has a solid monitoring tool for SharePoint and the servers so if you already have DocAve this would be my choice.  The UI gets jumbled on big farms but overall the tool is easy to use and does a solid job.

Metalogixs Diagnostics Manager looks like a nice tool.  Very similar to the DocAve Monitor but you don't need to deploy as any pieces onto the farm.  The UI can be a bit busy but definitely a product to look at.

Metalogix's Diagnostic Manager sample screen shots

Other tools include ExtraHops traffic Monitor, by check how long response takes it determines with minimal interference how well the components/nodes in the infrastructure are performing.  DocAve 6.3 has a good monitoring solution specific to SharePoint, it will monitor down to the OS and report on CPU and Memory.

AlertFox is a monitoring service (SaaS).  It can perform http get requests at regular intervals (e.g. every 5 minutes).  This ensures your webservers stay warm, measures the response time from various location around the world and can check the speed of multistep actions such as loging into your web site and performing a search.  There are a lot of these but AlertFox is good.  It has dashboard, email and sms notification included. 

SharePoint Best Practices Analyser - CA > Monitor > Health Analyser provides a good place to see common problems.

EventViewer & ULS are also good places to do reactive and even preventative monitoring however these logs will need to be trawled manually.

Key items to monitor for me are:
1.> OS/VM: CPU, OS Memory, OS disk capacity/utilsation.
2.> Windows Services: Each role needs a set of services, so your monitoring tool can verify they are working.  An example for SharePoint servers services are shown in appendix A.  If you have agents such as DocAve from AvePoint, verify these are running.  Office Web Apps 2013's service is WACSM.
3.> SQL Server: Verify the services are running, monitor SQL performance ...
4.> SharePoint: Verify web requests are returning results and measure TTL, this may indicate a bottleneck is starting to occur.  If you are using multiple front end servers, check each server is working.

Appendix A. SharePoint 2013 Services to Monitor

WFE & APP Roles

Service	Name	Status	Startup Type	Log On As
SharePoint Administration	SPAdminV4	Started	Automatic	Local System
SharePoint Search Host Controller	SharePointSearchHostController		Disabled	Network Service
SharePoint Server Search 15	OSearch15		Disabled	Local System
SharePoint Timer Service	SPTimeV4	Started	Automatic	Demo\Sp_farm*
SharePoint Tracing Service	SPTraceV4	Started	Automatic	Demo\Sp_Service*
SharePoint User Code Host	SPUserCodeV4		Disabled	Demo\Sp_farm*

Search Role

Service	Name	Status	Startup Type	Log On As
SharePoint Administration	SPAdminV4	Started	Automatic	Local System
SharePoint Search Host Controller	SharePointSearchHostController	Started	Automatic	Demo\SP_SearchService
SharePoint Server Search 15	OSearch15	Started	Manual	Demo\SP_SearchService
SharePoint Timer Service	SPTimeV4	Started	Automatic	Demo\Sp_farm*
SharePoint Tracing Service	SPTraceV4	Started	Automatic	Demo\Sp_Service*
SharePoint User Code Host	SPUserCodeV4		Disabled	Demo\Sp_farm*

Issues Generating wsp's using MSBuild in TFS2012

Problem: I am using a Build Definition within TFS2012 to try create a WSP package.  I am building a solution that contains multiple projects.  1 project needs to generate the wsp from it.  I have made minor customisations such as:

My Custom Build Definition

My environment is TFS 2012, VS2012 & SharEPoint 2013.

 Tip:  To clone Build Definitions you need to add the VSIX "Team Foundation Server PowerShell Tools 2012".

When I run the build I get the following error:

C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v11.0\SharePointTools\Microsoft.VisualStudio.SharePoint.targets (387): The "ValidatePackage" task failed unexpectedly.System.InvalidOperationException: The project service does not contain the specified project: 17939c4e-xxec-xxxx-xxxx-e18209d495db. at Microsoft.VisualStudio.SharePoint.Tasks.ValidatePackage.OnCheckParameters() at Microsoft.VisualStudio.SharePoint.Tasks.BuildTask.Execute() at Microsoft.Build.BackEnd.TaskExecutionHost.Microsoft.Build.BackEnd.ITaskExecutionHost.Execute() at Microsoft.Build.BackEnd.TaskBuilder.d__20.MoveNext()
Exception Message: MSBuild error 1 has ended this build. You can find more specific information about the cause of this error in above messages. (type BuildProcessTerminateException)Exception Stack Trace: at System.Activities.Statements.Throw.Execute(CodeActivityContext context) at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocal

Resolution:
Install Microsoft Office Developer Tools for Visual Studio 2012 - RTM on the build controller (In my case I have TFS and I have a seperate Build Controller on my CI box so I install MS Developer Tools for VS 2012 - RTM on the CI box.

More Info:
http://social.msdn.microsoft.com/Forums/vstudio/en-US/dc6e39e6-331d-42b2-ac16-b530ccb74d46/tfs-build-and-creating-a-sharepoint-2013-app-file

Tip: Another Point Worth Noting is the build definition can have problems if files names/locations are tool long created by the build controller.

   Tip: When setting up Visual Studio, shorten the length/location where the project/workspace is held locally as shopwn below:

 

 

Generating WSP's in CI for SP2013

There are some comprehensive blog posts on setting up Build automation.  This post is a short summary of creating SharePoint wsp files from a Visual Studio 2012 solution within TFS2012.

Prep Steps:

1. TFS2012 containing source files.
2. Need a build controller and a build agent, this can be on the TFS server or a separate server, in my case it is separated out.
3. On the Server holding the build controller, you need the SP binaries.
4. Install "Microsoft Office Developer Tools for Visual Studio 2012 - RTM"
5. Create a new template or clone the default template and adjust it as shown below (You will need the VS extension (VSIX) "Team Foundation Server PowerShell Tools 2012" to clone an existing Build Template):

 

Add the Packaging switch under the advanced Tab in the Process tab

More Info:
http://msdn.microsoft.com/en-us/library/ff622991.aspx

The Developer Dashboard in SharePoint 2013

Overview:  The Developer Dashboard is a great tool added to SharePoint in the 2010 version.  SharePoint 2013 has additional features and a new look.  This post explores the new developer dashboard.

SharePoint 2013 Dev dashboard, note the new tabs that have been added.

 

Performance Testing SharePoint 2013

Performance can be broken down into many aspects, in this post I examine my approach to verifying SP performance.

Added 2021: Breakdown for types of testing used for a SaaS solution setup on Azure PaaS.

Added 2015: Marcel De Vries Performance Testing Session at Dev Intersections Conference Amsterdam 2015

Marcel De Vries breaks it down into 4 forms of Performance Testing:

• Performance Testing
• Load Testing
• Stress Testing
• Capacity Planning

Note: Baseline each test type so that these can be compared to deployed solutions or application.

Fiddler:  I create a publishing site and use fiddler from as close to the server with a minor adjustment so I can easily see the TTL.  You need Fiddler and make this adjustment (alternatively you can see this in the page response anyway).  This tells me how quickly my page is being returned, you can also use this to prove if network connectivity is your issue.  For instance a remote site in Asia regardless of request may have poor connectivity.

PageSpeed Insights:  This is a Google service to check the speed of your public website and provides a nice summary of items you can optimise.

Developer Dashboard: Brilliant tool and even better in SP2013.  You need to enable the developer dashboard.  You can also add custom custom monitored scopes that allow you to dive down into bottlenecks using the dev dashboard.

Visual Studio Web Tests:  Available in VS2010 and VS2012, these allow you to record browser actions, replay them and validate responses. 

• Create a New Visual Studio Project of Type "Web And Load Test Project", The little red icon allows you open the web browser and record the browser activity.  I am using VS 2012 SP3 Ultimate Edition.
• The images below show a simple recorded web test and it being run against individual WFE's and a load balancer.

Visual Studio Load Tests:  Add web tests or other tests together and create a load using TFS.  You can simulate load and add multiple agents to test your farm or code works under stress.  Integration, Ocded UI and Unit tests can be added to load tests but this can get fairly tricky so I find it easier to simulate load using combinations of web tests.

To create a load test, you need 1 or more web tests in the Load and web test project.  Right click the project and create a new load test.  Follow the wizard thru as shown below.

Run the Test and watch the performance counters on the SharePoint web, app and DB servers.

You can export the results into excel that provides detailed information about the load test.

Read More:
http://msdn.microsoft.com/en-us/library/vstudio/jj710162.aspx
http://blog.sharepointsite.co.uk/2013/07/identifying-page-load-times-using.html (Fiddler)
http://blog.sharepointsite.co.uk/2010/06/sharepoint-2010-developer-dashboard.html (Dev Dashboard)
http://blog.sharepointsite.co.uk/2011/01/developer-dashboard-custom-scoped.html (Dev dashboard & monitoring)

Update 26 Aug 2014
AlertFox is a useful tool for verifying page performance.  It has multiple useful functions such as monitoring the website is up and running macros to login, perform searches.  All these tasks record the time it takes and you can monitor performance from various locations such as the US or Europe. 

SQL Server 2012 for SharePoint 2013 checklist

 Checklist for SQL Server 2012 for SharePoint 2013

1. Use multiple: SQL Aliases (separate 1 for search).
2. Dedicate SQL Server for SharePoint.
3. Set max degree of parallelism (MAXDOP) to 1 for instances of SQL (SP will do this when SP is installed).  Number of processes for each SQL statement.
4. Mixed mode authentication - Don't install SQL 2012 for SP in mixed mode auth unless you have good reason (the only reason I have heard of is from Todd Klindt's podcast that mentions Access Services needs to use the SA account.  If you have other database that need SQL permission access consider moving them to a dedicate SQL instance.
5. SQL Server 2012 AlwaysOn Availability Groups are a new high availability and disaster recovery solution that are an alternative to database mirroring and log shipping solutions. AlwaysOn Availability Groups support a set of primary read-write databases and up to four sets of secondary databases that can be set as read-only.
6. Memory: You can set the max memory each SQL instance can use.  If the machine is dedicate to only provide SQL for SharePoint, the max setting is total memory minus 4GB for the OS.  See image 3.
7. Model DB: Increase initial size and autogrowth settings - fix growth sizes.  I would start  with 100MB for the mdf and 20MB for the ldf for initial sizes.  Autogrowth on content db's I start with 50MB growth for the mdf and 25MB for the log file (ldf).  See image1 below.
8. Model DB: Don't modify DB Collation after install.
9. Model DB: Use Full recovery model on the Model system database - Simple prevents large log files. 
10. Avoid giant ldf log files ... (don't use DCC_Shrink to resize ldf files with switching to the simple recovery model, it breaks the LSN/log backup chain).  ldf growth is far more resource intensive than mdf files growth, the problem I see with Content db growth is the IT pro lets the ldf get out of control, then backs up and shrinks the database.  Usage causes the ldf to autogrow periodically and the farm goses back to needing the process repeated with heavy growth issues.  Key is to ensure the lfd has a decent initial size (you can work this out between full backup cycles), the ldf for content db's should rarely need to autogrow and when it does make it a fixed amount.
11. TempDB: Having multiple mdf tempdb files speeds up SQL performance.  The tempdb is a system db that has resource available to all users.  And from the expert
12. TempDB: Increase it's initial size  & autogrowth to MB as opposed to percent (see image2).
13. TempDB: Simple recovery model for TempDB is correct.
14. TempDB: The default is 1, you need more than this depending on how many CPU cores are on your database server.  1 option is set the number of TempDB's to the same as the number of CPUI cores (1-to-1).  Some folks recommend the number of tempDB's should be 1 less than the number of cpu cores, other folks go for 1 TempDB per 2 CPU cores.   I start with 4 and tune in performance testing or once it is running.  Saying that I normally have 16 cores.  I can't see performance gains from my testing after 4 TempDB's but as a rule I'd start with 1 TempDB for each 2 Cores and tune from there. 
15. TempDB: Move the tempdb.mdf files and the TempDB .ldf file to their own fast as possible drive.
16. Content DBs: CA or PS when creating a new content db won't take all the model settings, it does take initial sizes but not the autogrowth settings.
17. Content DBs: Workaround- create the db outside of PS, get the SP collation right "Latin1_General_CI_AS_KS_WS" collation.
18. If your SQL db uses spinning disk split the mdf and ldf files onto separate disks.  Order the db files as follows: tempdb must be on the fastest disk, content db log files next and content db's next.
19. Change the default backup location to a separate disk (pretty obvious but it is the default setting). 
20. Set the default for the database instance file locations  - set the default location where new mdf, ldf and backups will go on disk (per your fasteset disk calcs).
21. Set the default for the database instance backup compression - I'd go with compression for all backups.
22. mdf and ldf should be on separate drives for 2 reasons : IOPS speed (provided this is spinning disk) & DR (you don't want to loose both).
23. OS: NTFS allocation unit, by default on Windows 2008 this is 4096 byte (4kb), generally much faster to have it set to 64kb allocation unit size.  e.g. cmd>Chkdsk c: 
24. Use RAID 10 where possible.
25. Windows firewall - if using it you will need to open the incoming SQL port i.e. 1433
26. Avoid huge transaction logs and size them appropriately.  Pref don’t use simple recovery model. Ldf content is not removed every 60 seconds when it is written to the mdf files. Backup – get last full backup and last differential to get you to the lasted backup version.  Or get the current ldf, restore the last full backup and play the current ldf through the db.  To slim the ldf down, after a successful full backup, can backup the transaction with "truncate the transaction log" (it zeros all transaction before the checkpoint made by the transacion log backup or (sic. delete the log file) to get it back to a reasonable size.  Hint: BACKUP LOG databasename TO devicename
27. Watch the size of the Content Databases, they take time to recover.  Max up to 4TB, try stick to around 200GB (exception will be for blob storage).  This makes backup and restore quick however AlwaysOn also changes the scenario.
28. Format the Drives with 64K NTFS Allocation Units.
29. Antivirus software must exclude LDF/MDF/NDF files.
30. Don't shrink database log files by switching the recovery model to Simple.
31. Ensure you are within the latency recommendation for SP to SQL (< 20ms).

Image 1. Change the model database initial size and autogrowth settings.

Tip: AutoGrowth of SharePoint 2013 Content databases - Changing the initial size of the model db will affect the content db's - nice, issues is that the autogrowth settings in the model db are not pushed to the content databases created through SharePoint (either CA or PowerShell).

Image 2. Change the TempDB to have multiple mdf files.

Image 3. Setting Memory on SQL Server instances.

SharePoint Sizing Starting point notes:
SP_Config database - "Transaction log files. We recommend that you back up the transaction log for the configuration database regularly to force truncation." Technet.   The Full recovery model is the default, switch this to Simple.  If you need Full, your ldfs be busy.  Suggest ldf at least 1000MB per day growth, can be a lot more.

Suggested Search database sizing.  If the Search databases are in the Full Recovery mode you also need to set the ldf sizing.

Database	mdf	mdf growth	ldf	ldf grow
SP_Search_Admin	100 MB	10 MB	100 MB	50 MB
SP_Search_CrawlStore	100 MB	50 MB	300 MB	100 MB
SP_Search_AnalyticsReportingStore	100 MB	50 MB	25 MB	25 MB
SP_Search_LinksStore	100 MB	50 MB	25 MB	25 MB

Update 23 Jan 2014:  Todd Klindt has a good set of blog posts on SQL 2012 for SharePoint 2013.

How do ldf files work with mdf in SQL Server:
Content goes into .ldf file temporarily, checkpoint occurs every minute and moves from .ldf to mdf.  If the "Full recovery model" is used the content in the the ldf file is retained. Hence large trans logs but recovery is better.  If a simple recovery model is used, the ldf data is dumped.


Keith Tuomi provide the code to automatically change the autogrowth sizing.

01	$Server="SQLSRV2012"

02	[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

03	$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server

04	$databases = $SMOserver.Databases;

05	foreach ($DB in $databases | where{$_.Name -like '*Content*'}) {

06	#Set Log File growth

07	foreach ($DBLF in $DB.logfiles) {

08	$DBLF.set_GrowthType("KB");

09	$DBLF.set_Growth("51200"); #50mb

10	$DBLF.Alter();

11

}

12	#set File Growth

13	$DBFG = $DB.FileGroups;

14	foreach ($DBF in $DBFG.Files) {

15	$DBF.set_GrowthType("KB");

16	$DBF.set_Growth("102400"); #100mb

17	$DBF.Alter();

18

}

19

}

SQL Licencing:
There are numerous licensing models available to SQL server on the different versions and I find them extremely complex. For large SQL using the Enterprise Edition of SQL 2012, per-core licensing at the hardware (hypervisor) level is an option. The SQL instances can be tied to specific hardware. Affinity rules do need to be setup to prevent vMotion moving the VM to another hardware host.  In a HA situation using AOAG in a passive situation, the secondary SQL servers et al. will require licencing.

SQL Installation:  I slipstream and automatically install SQL Server 2012.  The checklist below lists the SQL features you can install.  Determine your needs makes creating the config.ini files used by the install much easier to do.  The example below is used to create both my primary SQL Server and the secondary (AOAG) server, they are identical.  The choices are pretty standard, you may want to move the Reporting Services features to another server or remove if you are not using them.

Feature	Install
Database Engine Services	Y
SQL Server Replication	Y
Full-Text Search	N
Data Quality Service	N
Analysis Services	N
Reporting Services: Native	N
Reporting Services: SharePoint	Y
Reporting Services Add in for SharePoint	Y
Data Quality Client	N
SQL Server Data Tools	N
Integration Services	Y
Client Tools Connectivity	Y
Client Tools Backward Compatibility	Y
Client Tools SDK	N
Documentation Components	N
Management Tools Basic	Y
Management Tools Complete	Y
SQL Client Connectivity SDK	N
Master Data Services	N
Distributed Replay Controller	N
Distributed Replay Client	N

More Info:
http://yalla.itgroove.net/2013/03/sql-server-powershell-sharepoint-set-autogrowth-on-content-dbs/
http://blog.cloudshare.com/2013/08/28/how-to-use-the-same-autogrowth-value-for-sharepoint-content-databases/
http://technet.microsoft.com/en-us/library/hh292622.aspx
http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-03-Server-Settings-for-SQL-Server (Excellent)
http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/
http://www.brentozar.com/blitz/blitz-result-percent-growth-use/
http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/
http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
http://www.toddklindt.com/default.aspx
SharePoint database types and description

Thanks to:
@allanSQLIS - Allan Mitchell - great sitting next to a SQL expert.

Steve Goodyear has a blog post on Farm Install and build guide.  I haven't used it but it is a good post to check you are ready for your install and you have done the big steps.

SQL Hardening:

From http://blogs.technet.com/b/rycampbe/archive/2013/10/14/securing-sharepoint-harden-sql-server-in-sharepoint-environments.aspx
Hardening SQL Server is done in a 3 phased approach:

1. Encryption at Rest (Encrypt the data sitting on the hard drives)
2. Encrypt Connections (Encrypt the data in flight on the network between servers)
3. Server Isolation (Configure SQL Server's firewall to ignore requests from unauthorized servers)

Transparent Data Encryption (TDE) can be used to encrypt any SharePoint database.  This will encrypt the mdf and ldf files, this ensures that even is the hard disk storage is comprimised, the mdf and ldf cannot be used to restore the databases using the SQL restore tools.  There are a lot of ramifications to using TDE so review the decision to use TDE carefulkly before implementing.