Tag Archives: Administration
FIXED – Create User Profile Service – Sync database not editable
Quick demo for how to customize the Central Admin GUI default Sync database name (prefilled with GUID) to custom value more readable and admin friendly.
Simply press F12, select the text field, and remove “disabled=disabled” from the <INPUT> HTML element. Now able to enter any value and click “Create” UPS.
Cheers 
Video
Screenshot
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. 
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 -ForceDownload
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. 
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-TranscriptDownload
https://github.com/spjeff/spadmin/blob/master/Practical-PowerShell-LOGs.ps1
VIDEO – Disable Quick Edit for long running PowerShell
When running PowerShell administration jobs for many hours within RDP session, we are open to risk of accidental pause. Other admins might RDP into server, click around, switch windows, and accidentally pause.
Disabling the default “Quick Edit” PowerShell window feature ensures protection for script to continue uninterrupted.
Cheers. 
Video
Screenshot
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! 
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! 
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
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
Screenshots
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! 
# 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
IISGoIIS – 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. 
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”
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. 
