SharePoint Database Size Planning

In order to manage your SharePoint farm, and especially for planning for backup/recovery you need to understand data sizing of your farm. Here are the steps you can take to gather the information needed to understand the existing farm and estimate its growth. This will give you a clear understanding of the size of your backups, so you can plan for recovery timeframes, and will also give insights into the rate of growth and on quotas that can govern growth of databases.

Size of all SharePoint Databases

To plan for DR one needs to know the size of all databases to be backed up and restored. This small script will produce a CSV report of the bytes per database attached to the SharePoint farm:

Get-SPDatabase | select name,DiskSizeRequired | convertto-csv | set-content "C:DBsize.csv"

RBS Report

There is no direct mechanism in Central Admin to view RBS configuration. This script will give you a report of the RBS settings throughout your farm:

Get-SPContentDatabase | foreach {$_;
  try {
 
      $rbs = $_.RemoteBlobStorageSettings;
      write-host "Provider  Name=$($rbs.GetProviderNames())";
      write-host "Enabled=$($rbs.enabled)";
      write-host "Min Blob  Size=$($rbs.MinimumBlobStorageSize)"
      }
  catch
  {write-host -foregroundcolor red "RBS not installed on this database!`n"}
  finally {write-host "End`n"}
}

Site Collection size report

It is useful to know the sizes of your Site Collections, and their distribution among your Content Databases. You can report on the size of each Site Collection within each Content DB within a given Web Application with the script below. The output is a CSV (Comma Separated Value) file easily read into Excel. If you have a lot of Site Collections, just convert to a PivotTable, to see the distribution and sizes of Site Collections across Content Databases.

get-spwebapplication http ://SharePoint | Get-SPSite -Limit all | select url,contentdatabase,@{label="Size in GB";Expression={$_.usage.storage/1GB}} | convertto-csv | set-content "C:TEMPDBsize.csv"

Site Collection sizes help inform how to rebalance Content Databases for optimal sizing to allow you to meet your RTO.
One common situation is for MySites to be distributed unevenly across Content Databases, leading to one Content Database being much larger than others. As discussed earlier, managing Content Database sizes is key to meet your RTO.

Quota Report

Setting quotas puts in place limits on Site Collection growth. It also gives the Administrator weekly notification of Site Collections that have exceeded a preset warning size.
This report gives you a list of all the quotas in place across a Web Application:

$webapp = Get-SPwebapplication "http ://SharePoint"
$webapp | get-spsite -Limit ALL | ForEach-Object {
$site = $_;
$site;
$site.quota;
}
$site.dispose()
$webapp.dispose()

What you want to look for first are Site Collections that have no quotas. These represent opportunities for unconstrained growth without notification that could result in Content Database growth that exceeds your RTO targets.

How to safely stop and start SharePoint for a reboot

I recommend in the SharePoint Server(s), Start, Run, services.msc:
Stop: SharePoint 2013 Timer
Stop: World Wide Web Publishing Service
A reboot is optional

To restart, the order is reversed:
Start: World Wide Web Publishing Service
Start: SharePoint 2013 Timer

Best done before taking SQL Server offline, or any other major maintenance.

AppFabric

In SharePoint 2013 the AppFabric needs to be taken into account. This is a serious concern, as rebooting the AppFabric machine can leave the AppFabric cluster in a non-functional state.

To check the AppFabric status, start with:

Get-CacheHost

To avoid this, run a graceful AppFabric Shutdown:

Stop-CacheHost -HostName sp15-search-idx.sfb-testnet.local -CachePort 22233 -Graceful

We then wait, and once it is down we run:

Remove-SPDistributedCacheServiceInstance

After reboot we run:

Add-SPDistributedCacheServiceInstance

To fix broken social feeds use:

Update-SPRepopulateMicroblogLMTCache
Update-SPRepopulateMicroblogFeedCache

Save your hide by restoring a SharePoint farm configuration

I had the scare of the week last night. I was doing some pre-approved SharePoint farm cleanup. Part of that was removing some Farm Solutions (WSPs). The Retract of the solution partially failed, and hosed Central Admin (ouch).

Last time I had this, the uninstall of UMT software left a dozen dangling references in all Web.Configs that I cleaned up by hand. Those caused workflows to all stop. This time, I was getting security and page rendering errors.

Good thing we had done a backupSPFarm recently with ConfigurationOnly option.

Given Central Admin was hosed, I used PowerShell:
Backup-SPFarm
Restore-SPFarm

Once the restore was done, Central Admin appeared to behave erratically. This was simply the farm sync’ing up, with a series of IISResets associated with feature re-deployment.

Resetting a SharePoint Farm Passphrase

If you don’t have the SharePoint farm passphrase, you can’t join a server to the farm. And there’s no way to get it back once you lose it. However it’s quite easy to reset it. First, let’s be aware fo the stiff requirements for a passphrase. You should ensure that the passphrase meets the following criteria:
Contains at least eight characters
Contains at least three of the following four character groups: ◦English uppercase characters (from A through Z)
+ English lowercase characters (from a through z)
+ Numerals (from 0 through 9)
+ Nonalphabetic characters (such as !, $, #, %)

$passphrase = ConvertTo-SecureString -String "P1ckAg00dPa$$w0rd" -asPlainText -Force
Set-SPPassPhrase -PassPhrase $passphrase -Confirm

The purpose of the passphrase is to prevent unauthorized servers from joining to a farm, and using their newfound access for malicious purposes.

What is less well known is that there is a job that runs that propagates the Passphrase amongst the SharePoint servers in the farm. It is only run when the passphrase is changed or when a server is added to the farm.
SPMasterPassphraseDeploymentJobDefinition. This Timer Job can run into trouble, so it is good to be aware of it for diagnosis and manual retry.

Tuning SharePoint Search Ranking in the object model

SharePoint Search results are returned in order of relevancy, which is determined by a ranking model. There are a number of ranking models cooked into SharePoint 2010. These can be refined to a limited extent, with a bit of insight, to better serve users.

To see the models and their definition, let’s query the SharePoint Search application DB:

SELECT * FROM [Search_Service_Application_DB].[dbo].[MSSRankingModels]

The resultset has the models; the GUID, whether it is default, and the underlying XML that specifies the model. The model name is at the beginning of the XML.

Using PowerShell, we can get the array of ranking models, and is the only supported approach for manipulating the models, changing the default, and for creating new ranking models. Here’s how to get the models:
their definition, let’s query the SharePoint Search application DB:

Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel

Now we can assign the ranking model array to a variable and index into it:
their definition, let’s query the SharePoint Search application DB:

$A = Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel

Or we can grab the one ranking model that we like by using the GUID, which we have to predetermine, but that’s easy, as it’s returned by the above query and is unchanging. For new models, we get to specify the GUID as well.

Once you know your rank model GUID, you can switch to it by getting it, and setting it as default:

their definition, let’s query the SharePoint Search application DB:

$r = Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel 8f6fd0bc-06f9-43cf-bbab-08c377e083f4$r.MakeDefault()

To create a custom rank model, first identify the Managed Properties, by PID. The name is part of the XML, but it is the PID that drives the ranking. Here’s how to get all the Managed Properties and their PIDs:
their definition, let’s query the SharePoint Search application DB:

Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchMetadataManagedProperty
Now we create a new ranking model called MyRank, note i want LastModified property to be relevant.
 their definition, let’s query the SharePoint Search application DB:

Get-SPEnterpriseSearchServiceApplication | New-SPEnterpriseSearchRankingModel –rankingmodelxml "<!--?xml version='1.0'?-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
"

There are two parts to the model; the query dependent section that is associated with the actual query and it’s metadata, and the query independent part that ranks based on number of slashes (URLDepth) and click frequency etc.

As soon as a model is default, you can see the effect of the new ranking model.

Here’s how to change this model, note I add a new field called MyCompany and boost its relevance:

<!--?xml version="1.0" encoding="utf-8"?-->
Get-SPEnterpriseSearchServiceApplication | Get-SPEnterpriseSearchRankingModel 8447b4bc-3582-45c5-9cb8-ba2a319d850e | Set-SPEnterpriseSearchRankingModel –rankingmodelxml "<!--?xml version='1.0'?-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
"

I admittedly did not have success ranking by how recent a document was updated. This is known as “Freshness”. SP2010 has very limited ability to customize ranking. I have not succeeded in getting it to respect “freshness”. A simple freshness ranking seems infuriatingly out of reach. However SP2013 supports it explicitly. While the default SharePoint 2013 ranking model doesn’t boost the rank of search results based on their freshness, we can achieve this by adding a tuning of the static rank that combines information from the LastModifiedTime managed property with the DateTimeUtcNow query property, using the freshness transform function. These Transform functions are used to customize ranking in SP2013. The freshness transform function is the only transform that we can use for this freshness rank feature, because it converts the age of the item from an internal representation into days. In SP2010 the transforms are much more obscure and not really usable. Microsoft reports that the freshness transform in SP2013 can be used. Even before getting to SP2013, we can have an SP2013 farm configured to crawl production SP2010 and return results tuned in this way, and can use the SP2013 search results to serve any client we choose to point to SP2013, including a simple search site in SP2013.

Reporting on features across a SharePoint Farm

Before retracting a SharePoint solution, it is best to deactivate the associated feature in each Site Collection. Here’s a script that reports on which Site Collections have a feature enabled, and optionally deactivate the feature. This is easily adapted to Web or Web Application features as well. Best is to first get the SPFeature GUID, using get-spFeature CmdLet. Just flip the $Deactivate switch to $true to actually deactivate. Note any SPFeature not found will generate an error that a try/catch clause will not catch. The quick solution is to capture my output into $OutLine, and clear the console and output the summary at the end of the run.

$OutLine=$null;
$Deactivate = $false;
$a=Get-SPFeature 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788     #my site collection feature, substitute for yours
 
Get-SPWebApplication | Get-SPSite -Limit all | % {
$b = Get-SPFeature -Identity 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788     -site $_.url
 
if ($b -ne $null)
{
    $OutLine = $OutLine + "Found active in $($_.url)`n"
    $b=$null;
    if ($Deactivate)
    {
        Disable-SPFeature 359d84ef-ae24-4ba6-9dcf-1bbffe1fb788 –url $_.url
    }
}
 
}
cls
if ($Deactivate)
{
    Write-Host "Deactivated all; Here are Site Collections where it was active:"
}
else
{
    Write-Host "Here are Site Collections where it is active:"
}
write-Host $OutLine