Tag Archives: Administration

Reboot SharePoint Farm

Quick code snippet to share with you guys for rebooting the full SharePoint farm.   Invoke remote restart command to all peer machines with any valid SharePoint role, then reboot local machine last.   NOTE – SQL not included but can be with adjusting the filter.  Cheers. shades_smile

Code

Add-PSSnapIn "Microsoft.SharePoint.PowerShell"
$local = $env:COMPUTERNAME
$localFQDN = $env:COMPUTERNAME + "." + $env:USERDNSDOMAIN
$targets = Get-SPServer |? {$_.Role -ne "Invalid"} |? {$_.Address -ne $local -and $_.Address -ne $localFQDN} | Select Address
$targets |% {Write-Host "Rebooting $($_)"; Restart-Computer $_ -Force}
Restart-Computer -Force

Download

SPRebootFarm.ps1

Practical PowerShell Logs

All scheduled PowerShell files should generate LOGs.  “Start-Transcript” is a great cmdlet for this.  Adding a few more features can boost operations support.   LOGs answer essentials support questions like:

  • Which parent PS1 generated this LOG?
  • Did script perform needed functions?
  • Any errors?
  • How long did it take?
  • Which user and computer ran the script?

Below is code template I recommend to for scheduled PowerShell jobs to generate a LOG. Key features include:

  • Auto detect current folder and script PS1
  • Create  \LOG\ subfolder
  • Prefix to match parent PS1
  • Suffix with unique date time stamp
  • Elapsed run duration (Days, Hours, Minutes)

Thank you to @ToddKlindt for improvements to formula and elapsed time.  Cheers.  shades_smile

Video

Code

function Main() {
    ### YOUR CODE HERE
}
# Open Log
$prefix = $MyInvocation.MyCommand.Name
$host.UI.RawUI.WindowTitle = $prefix
$stamp = Get-Date -UFormat "%Y-%m-%d-%H-%M-%S"
Start-Transcript "$PSScriptRoot\log\$prefix-$stamp.log"
$start = Get-Date
Main
# Close Log
$end = Get-Date
$totaltime = $end - $start
Write-Host "`nTime Elapsed: $($totaltime.tostring("hh\:mm\:ss"))"
Stop-Transcript

Download

https://github.com/spjeff/spadmin/blob/master/Practical-PowerShell-LOGs.ps1

SharePoint – SQL Database Roles

Had a friend ask about SQL database Role Memberships that are needed for SharePoint Server on-premise.  Examined SQL roles on my dev farm.  Reference table below.

Cheers!    shades_smile

Get-SPServiceApplication

Get-SPDatabase | Select TypeName

SQL Permission Role

App Management

App Management Database

SP_DataAccess

N/A

Configuration Database

dbo, WSS_Content_Application_Pools

N/A

Content Database

SP_DataAccess

Microsoft SharePoint Foundation

Microsoft SharePoint Foundation Subscription Settings Database

SubscriptionSettingsService_Application_Pool

Secure Store

Microsoft.Office.SecureStoreService.Server.SecureStoreServiceDatabase

SP_DataAccess

User Profile

Microsoft.Office.Server.Administration.ProfileDatabase

SP_DataAccess

User Profile

Microsoft.Office.Server.Administration.SocialDatabase

SP_DataAccess

State Service

Microsoft.Office.Server.Administration.StateDatabase

WSS_Content_Application_Pools

User Profile

Microsoft.Office.Server.Administration.SynchronizationDatabase

db_owner

Search Service Application

Microsoft.Office.Server.Search.Administration.SearchAdminDatabase

SPSearchDBAdmin

Search Service Application

Microsoft.Office.Server.Search.Administration.SearchAnalyticsReportingDatabase

SPSearchDBAdmin

Search Service Application

Microsoft.Office.Server.Search.Administration.SearchGathererDatabase

SPSearchDBAdmin

Search Service Application

Microsoft.Office.Server.Search.Administration.SearchLinksDatabase

SPSearchDBAdmin

Machine Translation Service

Microsoft.Office.TranslationServices.QueueDatabase

SP_DataAccess

Word Automation Services

Microsoft.Office.Word.Server.Service.QueueDatabase

SP_DataAccess

PerformancePoint

Microsoft.PerformancePoint.Scorecards.BIMonitoringServiceDatabase

SP_DataAccess

Business Data Connectivity

Microsoft.SharePoint.BusinessData.SharedService.BdcServiceDatabase

SP_DataAccess

Managed Metadata

Microsoft.SharePoint.Taxonomy.MetadataWebServiceDatabase

db_owner

MySite – Export user Quick Links to CSV

When migrating from MySite on-premise to OneDrive in the cloud, Quick Links are not included by third party utilities (i.e. ShareGate).  However, with PowerShell we can export the original raw CSV data and provide a list of links to end users.  They can bookmark or add to Office 365.

Cheers!  shades_smile

 

Code

# User login parameter
$userLogin = "johndoe"
$mySiteWebApp = Get-SPWebApplication |? {$_.Name -like "*MySite*"}
# Connect to MySite
$site = Get-SPSite $mySiteWebApp.Url
$context = Get-SPServiceContext $site
$profileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context);
# Lookup user profile in UPS
$userProfile = $profileManager.GetUserProfile($env:USERDNSDOMAIN + $userLogin);
# Display on console
$userProfile
$userProfile.QuickLinks.GetItems() | Select Title, Url
# Export to CSV file
$userProfile.QuickLinks.GetItems() | Select Title, Url | Export-Csv "C:\temp\$($userLogin)-FollowedLinks.csv" –NoTypeInformation -Force

 

Screenshot

image

 

image

Clone Content Database – SQL Copy + Generate SPSite GUID

Tried of waiting for Backup-SPSite / Restore-SPSite?  Me too.  Why not clone the SQL content database with SQL backup and restore?  

 

Well, SharePoint requires every Site Collection on a given to have a unique GUID.  When we clone a SQL content database and attempt Mount-SPContentDatabase an error comes up that the GUID already exists on the local farm.  No bueno.

Why not generate and replace the GUID?   In theory, we can UDPATE the SQL database internally with fresh new GUID numbers and SharePoint would recognize as new site collections.  No conflict.  That is exactly what the PowerShell below does.  By walking the schema, finding “SiteID” and “tp_SiteID” columns, and replacing the old GUID with a new GUID.

The larger the site collection, the better this works on time savings.  SQL internally can backup and restore a database much faster than waiting for Backup-SPSite/Restore-SPSite to export/import binary data across the wire to the SharePoint front end file system.

In the example below you can see a brand new team site created with GUID “82dad5a8-aa6e-4480-a10e-16cd2597c18b” in a dedicated SQL content database, taken offline, updated with PowerShell to replace old GUID with new GUID “0f59c302-92ea-4fac-b32b-799f3dd41264” and then successfully consumed again.

 

  • NOTE – You still need a unique URL, so be sure to run Mount-SPContentDatabase against a secondary web application.
  • NOTE – This is completely unsupported.  Use at your own risk.   Worked well on Dev and Test environments for me.

 

PowerShell Source Code

 

Context Diagram

image

Screenshots

image

image

image

image

image

image

image

image

image

 

 

References

IISFlush.ps1

While troubleshooting an error with Excel Web Access I found recycle wasn’t sufficient and IISRESET was needed.   However, this raised questions about daily scheduled IIS recycle and if that would be enough to give a stable IIS footprint each business day.   I decided to err on the side of caution and schedule a full IISRESET along with code to ensure all sites and pools started up again OK.   PowerShell below.  

 

Also, I would recommend installing SPBestWarmUp (http://spbestwarmup.codeplex.com)  by running “spbestwarmup.ps1 –install”    This creates a Scheduled Task repeating every 15 minutes to simulate user traffic and keep those IIS pools responsive for end users.   No more wait on first visit.   It even warms up Central Admin to help out SharePoint admins.  

 

Enjoy!  shades_smile

 

# Reset IIS and verify 100% started.  Twice to be sure  =)
Import-Module WebAdministration -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
function IISGo {
	NET START W3SVC
	Get-ChildItem IIS:\AppPools |% {Start-WebAppPool $_.Name}
	Get-WebSite | Start-WebSite
}
IISRESET
Sleep 5
IISGo
Sleep 5
IISGo

IIS – no default pool recycle, set 1AM

Background

When SharePoint 2013 installs IIS application pools are created for content, service applications, central admin, and workflow.    Only the content pools appear to be given a daily recycle time.    However, I have noticed errors with Service Applications which can be fixed after recycling the pool.    Personally I prefer to recycle pools daily and ensure maximum stability.   Below is PowerShell code to accomplish that.   shades_smile

 

image

 

Symptoms

  • Excel Services Web Part error visible to end users –  “We don’t know what happened, but something went wrong.  Could you please try that again?”
  • WCF errors on eventlog
  • Slow or unresponsive HTTP endpoints with “.svc”

image

Resolution

Apply 1AM IIS daily recycle time to any SharePoint pools without a schedule.

PowerShell Code

# Bypass IIS Defaults
$exclude=@(".NET v2.0",".NET v2.0 Classic",".NET v4.5",".NET v4.5 Classic","Classic .NET AppPool","DefaultAppPool");
# Loop SharePoint pools
$pools = Get-ChildItem IIS:\AppPools;
foreach ($p in $pools) {
	if (!$exclude.contains($p.name)) {
		$pn = $p.Name;
		$s = $p.recycling.periodicRestart.schedule.collection.value;
		if (!$s) {
			# Missing recycle schedule.  Set 1AM daily
			Set-ItemProperty -Path "IIS:\AppPools\$pn" -Name Recycling.periodicRestart.schedule -Value @{value="01:00"}}
		}
	}
}

FIXED – UPS missing Edit Connection LDAP filter menu

Recently while working on SharePoint 2013 I wasn’t able to edit the LDAP filters of the User Profile Service connection.   The hover menu wasn’t even visible.

 

Enabling Compatibility View for the Central Admin URL fixed it.   This page has older HTML which doesn’t work well in the IE 11 modern render engine.   Once Compatibility View was enabled everything worked as expected.     shades_smile

 

image

 

image

 

image

Return to Top ▲Return to Top ▲