SharePoint Content database – maxsitecount and warningsitecount – PowerShell

Quick Help Script:

How to set the maxsitecount and warningsitecount  on a content database either after the upgrade or migration or even after moving the content database from different farm or web application.

Add-PSSnapin microsoft.sharepoint.powershell
$dbs = get-spcontentdatabase -webapplication https://webapplicationname
foreach($db in $ {Set-SPContentDatabase -Identity $db -MaxSiteCount 1 -WarningSiteCount 0}

Continue reading

Restore Project server databases and instances to another farm

As I said in my previous post, today we will see how can we move project instances to another farm which including all your project server databases.

Before we delete PWA databases from sql server we need to accomplish some tasks in the order:

  • Delete/remove all the pwa instances from project server provisioning center (PWA service app)
  • Connect to sql server analysis services and delete the respective pwa instance cube db’s.
  • Go to timer service job definition and remove any jobs related starts with  PWA*
  • Verify back with powershell commands below listed if you still have any orphaned PWA instances or timer jobs and remove them.
  1.   Open the SharePoint management shell and run these commands.$serviceapp = get-spserviceapplication | ? {$_.TypeName –like “*Project*”}
    This will get the project server service application
    $pwainstances = $serviceapp.Sitecollection

    $pwainstances | ft name, id
    This will list all PWA instances referenced in the config DB


    Repeat these steps for each PWA site to delete them individually

    $toberemoved = $pwainstances | ? {$_.Id –eq “a1a29814-983e-4cad-a730-9a80d40737f7”}
    Enter the instance ID from query results in the previous step
    This will confirm the instance you want to remove
    This will delete the instance

We can now go forward and backup the pwa db’s and restore them to this farm.  follow steps to restore db’s

Now comeback to Project Service Application and create a new PWA instance and provide the same PWA instance name which exists in your other farm (eg: http://spserver-webapp/PWA-name ) so PWA-name should be same as restoring farm pwa-name – *important*. Then provide the database names while creating the pwa instance as you restored from source farm. Once the pwa instance is created we need to create the PWA root site. Note: do not backup and restore the root site db from source farm, rather create a new db and assign max site count to 1. Basically we are storing each PWA instance root site separately for each PWA site.

Post Update tasks after the PWA instance and root site are created:

  • Open the PWA instance site and go to server settings -> manage users and add the service account or your admin account to the list or users and administrators group, as the current db does not contain the destination farm service account it will hold the source farm farm account.PWA-1
  • Then open up the “Basic Project Plan” under workflow and project detail pages –> Enterprise project Types. Open the Basic project plan and add the project detail pages as below.
  •  pwa-2
  • Either disable notifications E-mail settings or modify them to suit this farm. This setting under operational policies –> Alerts and Reminders
  • Go to OLAP database management which you can find under Database Administration. Edit the existing one listed and change the database server and instance details, give it a couple of seconds and click “build Now” and watch the status there it self as it refreshes.

We should be good, if every thing went well. Also follow the below links for further troubleshooting.


Required Permissions in SharePoint Farm for executing PowerShell scripts

Before you execute any of the SharePoint Cmdlet you must be sure that you have necessary permissions to execute in the farm. Also you must have access to local WSS_ADMIN_WPG group where you are executing the cmdlet. Also you must be member of the SharePoint_Shell_Access SQL role within the databases your cmdlet calls in to interact with. Some commands also require you to be a member of the local administrator and farm administrator grooups. For ex. to create a web application, you must be a local administrator and farm administrator.

In Regards to adding users to the WSS_ADMIN_WPG group and SharePoint_Shell_Access SQL role, you must not be adding users directly to these groups rather you should use Add_SpShellAdmin cmdlet. This cmdlets accepts optional database name as parameter. If you do not pass this parameter it will add the user to the SharePoint_Shell_Access SQL role in the SharePoint configuration database only.

Above third line of code, it will add Add_SPShellAdmin SQL role in all the SharePoint databases including configuration database. Also you must be farm administrator and you must have securityadmin SQL role in order to execute this cmdlet.

  • Use Remove-SPShellAdmin to reveke access

When it comes to removing access you should use the above command to remove permissions, however it will not remove access to the user from WSS_ADMIN_WPG group on any server. You should remove this access manually. I recommend you should create “SharePoint Shell Access” AD group or groups if you want more fine grained control and grant these group Add-SPShellAdmin access there by avoiding the case users retaining the rights, where they shouldn’t.

When we get to the point where we need to execute scripts you may have to set execution policy  to allow scripts to run. You could do this using Set-ExecutionPolicy  by passing the execution policy.

  • Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope Process
While setting policy for production farms I recommend using policy scope to Process so it does not persist other PowerShell instances.

Optimizing SharePoint 2013 Server Performance – Development Server (single server)

There were couple of new services introduced with SharePoint 2013 and raised the hardware resource requirements. Let’s only talk about those process and steps to control the resource consumption when it comes to a single server SharePoint 2013 installation.

  • ·         NodeRunner service
  • ·         Distributed Cache Service
  • ·         Count of Web Application
NodeRunner service
  1. Use Set-SPEnterpriseSearchService -PerformanceLevel Reduced to reduce the CPU impact the search service has on your test environment.
  1. Modify the C:Program FilesMicrosoft Office Servers15.0SearchRuntime1.0noderunner.exe.config so that it can only consume X amount of RAM.
    Change the value at to any amount of RAM you like to contain the memory leak. May be 250 MB per instance of this service.
Even with this 250 MB limit I experienced some NodeRunner crashes. The general advice is to NOT change the NodeRunner memory limit configuration less than 250 MB. And NEVER EVER do this in a production environment!
Some of the pain points by the above modifications:
·         Changing this configuration file is not supported. For test/dev deployments it may have a desired effect on memory usage if you are running with less memory than the recommended minimum.
·         This means it may reduce the initial allocation of memory, but if the application requires more memory than this limit, it will crash. Hence, do not make such a change on a production deployment.
·         You may see errors like: Unable to connect to system client with derived management URIs. Exception: Failed to connect to system manager. Microsoft.Office.Server.Search.Administration.Topology.ApplicationAdminLayer.Reconnect() c80fcf9b-cf6b-2083-a27f-5d57c7dc4ef3.   Deeper analysis of the ULS logs shows that the DBConnector created by the NodeRunner process threw an OutofMemory exception. Removing the Noderunner.Exe.Config memory restriction and rebooting the server allowed me to submit the topology change.
Distributed Cache Service
A new caching service is added in SharePoint 2013 called ‘Distributed Cache Service’ which is built based on Windows Server AppFabric Distributed Cache. Many features rely on this service to store data for fast retrieval when needed. This is used by services/features like Authentication Token Cache, Micro Blogging features, My Site Social Feeds etc.,
How to stop this service
This service can be managed from the ‘Services on Server’ page in the central admin. It can be started/stopped from here.
Allocate Less Memory
By default when SharePoint 2013 preview is installed, Distributed Cache Service’s memory allocation is set to 10 percent of the total physical memory allocation. Using the below PowerShell cmdlets we can change the memory allocation for this service.
$instanceName =”SPDistributedCacheService Name=AppFabricCachingService”
$serviceInstance = Get-SPServiceInstance | ? {($_.service.tostring()) -eq $instanceName -and ($ -eq $env:computername}
Set-CacheHostConfig -Hostname -cacheport 22233 -cachesize
The above cmdlets stops the Caching Service, and sets the memory allocation to the specified number of megabytes (MB), and then starts the Caching service.
Web Applications
As the number of web applications grow in these single server SharePoint Development server, the number of Application Pools grow (this is not true if we use the same application pool to create multiple web applications), but however, if we donot pay attention while creating new web application we end up creating new application pool as well. These application pools runs in their own memory space, which in terms consuming more memory or RAM. Each App Pool runs with a service called w3wp.exe. As these SharePoint development servers also runs visual studio and sql server on the same box we need to keep in mind on the amount of memory accessible to each application and service.
It was my attempt to throw some light on how we can restrict memory usage by these services and still have a server running optimally even with 6-8 GB of RAM.

Enable Desktop Experience on Servers to Access SharePoint Web Folders

All of us like to access our files like a network drive, and drag and drop and mapping this drive to windows explorer or write/upload to SharePoint using scripts.

In order to access SharePoint like a network drive we need to enable a windows feature on our OS which is called “Desktop Experience” which include a webDAV client which is required to open SharePoint documents as a folder on our machine. On most windows desktop OS it is by default enabled, but on servers we need to enable this feature.

If you see the below image, you can notice that if you click on “Open with Explorer” you may get this error.

The Solution is very simple on to enable this on Windows servers (2008/2012 servers)

  1.  Open Powershell command prompt
  2. Type “Import-Module ServerManager” without Quotes and execute it(enter)
  3. Next you need to execute “Add-Windowsfeature Desktop-Experience” this will install the necessary feature
  4. Or you can create a powershell file with the above two commands and execute that file in powershell window like below.

After the server restart you can open the sharepoint files as a web folders:

PowerShell Version 3 – Intellisense

Windows PowerShell 3.0 is now available to download for Windows 7, Windows Server 2008 R2, and for Windows Server 2008. Windows PowerShell 3.0 comes in the Management Framework 3.0. You can download Windows PowerShell 3.0 from the Microsoft Download Center.  

Not going into deep of powershell V3 features, the one most I like initially was intellisense.  Also you can run a single cmdlet with selecting its parameters in the GUI to the right of the powershell ISE window. 

Check out the pics. … More to come later posts

Reset SharePoint PassPhrase

Passphrase for SharePoint saves the farm from accidental addition of servers by known or unknown admins. One way its saves but if we does not document it somewhere or lost it then the only way to retreive it is to reset the passphrase

The passphrase resides some where in SharePoint Configuration database encrypted. We need to set a new passphrase using the cmdlet Set-SPPassPhrase. Before we need to pass the new passphrase we need to convert it to secure string.
Follow the steps:
Note: Run these cmdlets on a server which is still part of a server, if the server is disconnected already from the farm then it cannot connect to configuration database to update the passphrase.
Open a sharepoint Management (powershell) Shell

$Pass = ConvertTo-SecureString -AsPlainText -Force

It will prompt to enter the passphrase now
Set-SpPassPhrase -PassPhrase $Pass
It will ask you to reenter the passphrase you need to enter the passphrase you already gave it in earlier step.