February 7, 2018

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

May 31, 2017

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

May 22, 2017

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

Download from https://github.com/spjeff/spadmin/tree/master/SPContentDatabase-Replace-Site-GUIDs

Context Diagram

Screenshots

References

Recover site collection from SQL backup on same SharePoint Farm

April 6, 2015

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
IISGo

1 2 3 4 … 14

SPBestWarmup	SPLaunch
SPPatchify	SPCrud

spcrud	101 commits
sppatchify	159
spbestwarmup	115
office365	95
insanemove	56
spquery	10
sosgrid	18
HelloTime	18
dbftw	11
todo-karma-coverage	14
sp-peoplepicker	10

spbestwarmup	20,386 downloads
splaunch	133
spupgradehelper	75
infopathdb	1,270
spdash	896
comb	649

Administration

SharePoint – SQL Database Roles

MySite – Export user Quick Links to CSV

Code

Screenshot

Clone Content Database – SQL Copy + Generate SPSite GUID

PowerShell Source Code

Context Diagram

Screenshots

References

IISFlush.ps1

Cloud Ready

Videos

Administration

SharePoint – SQL Database Roles

MySite – Export user Quick Links to CSV

Code

Screenshot

Clone Content Database – SQL Copy + Generate SPSite GUID

PowerShell Source Code

Context Diagram

Screenshots

References

IISFlush.ps1

Connect

Tags

Cloud Ready

Videos