The SharePoint Migration Assessment Tool (SMAT) is a simple command line executable that will scan the contents of your SharePoint farm to help identify the impact of migrating your server to SharePoint Online with Office 365.
Because the tool is designed to run without impacting your environment, you may observe the tool requires one to two days to complete a scan of your environment. During this time, the tool will report progress in the console window. After the scan is complete, you can find output files in the Logs directory. This is where you will find the summary and more detailed insights into the scenarios that could be impacted by migration.
The tool offers two modes; the default Assessment and Identity Mapping modes. I typically recommend starting with the Assessment mode to get a solid understanding of what issues come up prior to migration. Then you can run the Identity Mapping mode once most issues are corrected or mitigated prior to the actual migration.
Extract the file before running (I create a c:\SMAT folder and extract there to keep it simple)
The tool must run as the Farm service account. A farm administrator account is acceptable as long as the account has been given read access to all web applications. The account also needs explicit Full Control permissions on both Operations | Administrators and Sharing | Permissions on the User Profile Service Application. There are a series of checks to ensure the account has enough permissions prior to scanning the environment.
If the checks fail it will look similar to the below screenshot:
If any validation checks fail, do not continue to run the tool. Correct the issues reported, and run the tool again until it runs successfully without pre check warnings. The most common issue is the first message in the screenshot above. This simply means you are not using the “farm” service account which access to all components including SQL.
Execution
Once extracted you can run SMAT2010.exe for SharePoint 2010 OR SMAT.exe for SharePoint 2013 from a command line or launch from PowerShell as shown below. When all pre checks are successful the output will look similar to the cmd window below.
When complete, the program will prompt the operator to press Enter to continue. This ensures the operator is able to see the results in the console.
Log Files
You may see up to 3 log files in the output directory.
Log File
Description
SMAT.log
The SMAT.log file contains all the logging from the tool execution. This will contain 3 levels of logging. Information, Warning, and Errors. Information entries help track down progress and troubleshooting issues. Warnings are typically expected error conditions. Errors are unexpected conditions that our tooling was unable to determine if they will be a blocker to moving forward. These typically need to be investigated by someone familiar with the environment.
SMAT_Errors.log
The SMAT_Errors.log file contains only the Error events. If this file is missing after the tooling completes, it indicates there were no errors found.
SMATTelemetry.log
The SMATTelemetry.log file contains logging for the telemetry upload tooling. Any issues in here will not impact generating your reports.
Next Steps: Bring it all together with PowerBI
The output from SMAT can be compiled into a report that can be used to present the findings and highlight areas, sites, issues that need addressing before migration. I highly recommend following the steps Brian Jacket outlines in his articles that walks through exactly how to take the CSV files from the SMAT and transform them into an awesome PowerBI dashboard. By following his guidance, the following reports are possible.
Conclusion
If you are still hosting a SharePoint farm on-premises and are looking to migrate to SharePoint Online, the SMAT is the way to go.
Helpful Links
Here are some helpful links related to the topics covered in this article:
There are a number of SharePoint Server related paths that should be excluded from being scanned by the server Antivirus.
Web Server Extensions
You may have to configure your antivirus software to exclude the following folders and subfolders from antivirus scanning:
Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions
If you do not want to exclude the whole Web Server Extensions folder from antivirus scanning, you can exclude only the following two folders:
Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Logs
Drive:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\Data\Applications
Note The Applications folder must be excluded only if the computer is running the SharePoint Foundation Search service. If the folder that contains the index file is located elsewhere, you must also exclude that folder.
.NET Framework
Drive:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files
Drive:\Program Files\Microsoft Office Servers\15.0\Synchronization Service
BLOB Cache
<BlobCache Drive>:\<BlobCache Directory>
Search Index
<Index File Drive>:\<Index File Directory>
SQL Server Exclusions
When you configure your antivirus software settings, make sure that you exclude the following files or directories (as applicable) from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them. However, if these files become infected, your antivirus software cannot detect the infection.
SQL Server data files
These files usually have one of the following file-name extensions:
.mdf
.ldf
.ndf
SQL Server backup files
These files frequently have one of the following file-name extensions:
.bak
.trn
Full-Text catalog files
Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA
Trace files
These files usually have the .trc file-name extension. These files can be generated either when you configure profiler tracing manually or when you enable C2 auditing for the server.
SQL audit files (for SQL Server 2008 or later versions)
These files have the .sqlaudit file-name extension. For more information, see the following topic in SQL Server Books Online: Audits (General Page)
SQL query files
These files typically have the .sql file-name extension and contain Transact-SQL statements.
Do follow the Principle of Least Privilege: Give people the lowest permission levels they need to perform their assigned tasks.
Do give people access by adding them to standard, default SharePoint groups (such as Members, Visitors, and Owners). Make most people members of the Members or Visitors groups and limit the number of people in the Owners group.
Do use permissions inheritance to create a clean, easy-to-visualize hierarchy. That is, avoid granting permissions to individuals, instead work with SharePoint groups. Where possible, have sub-sites simply inherit permissions from your team site, rather than having unique permissions.
Do organize your content to take advantage of permissions inheritance: Consider segmenting your content by security level – create a site or a library specifically for sensitive documents, rather than having them scattered in a larger library and protected by unique permissions.
Do always assign a group as the group owner. The default group owner is whomever created the site or group. Assign the site collection owner or site owner group as the group owner to minimize issues when a user account may become deleted.
Do use AD and SharePoint permission groups to grant permissions.
SharePoint Groups – Site owners can manage the site permissions by adding people to groups within just their site.
Active Directory Distribution Groups – Can be re-used for other services and when a user needs to be added to a group, you only need to add them once to the appropriate Directory groups.
Don’ts
Do not give Full Control to everyone. Ensure the user is properly trained on SharePoint, verify what the SharePoint governance policy dictates for providing this permission to users, and consider other permission levels that may fulfill the user requirements (e.g. Design, Contribute, etc.).
Do not assign permissions to individual users or grant them item level permissions. Instead, always use SharePoint permission groups to grant user permissions. Item level permission management is a manual process and may be laborious in addition to leading to negative permissions issues.
Example SharePoint Permission Scenario
A possible SharePoint permissions scenario is to restrict certain users’ capabilities to file transfers where they cannot create Site collections, edit page layout, and other tasks beyond file transfers.
A recommendation would be to create a new SharePoint site collection permission level that is copied from the “Contribute” permission level. This will allow users granted this permission level the capabilities to add, edit, delete, and open files without the capabilities to create sub-sites, change the site design, and manage permissions.
Edit this new permission level and uncheck the following boxes to limit what’s allowed.
Use Self-Service Site Creation
Add/Remove Personal Web Part
Update Personal Web Part
NOTE: This new permission level will need to be created at each site collection.
Helpful Links
Here are some helpful links related to the topics covered in this article:
This SharePoint patching guidance defines the logical installation and configuration of SharePoint 2013/16/19 Cumulative Updates, Service Packs and Security Updates.
Patching SharePoint can be a time intensive and tedious process. In an effort to reduce the installation time, a series of procedures have been developed to reduce the effort required for pre-patching, patching, and post-patching steps. Each procedure and script described in this guide are designed to be followed in a serial manner.
These steps personally helped me reduce the manual time it took to patch a single SharePoint 2013 Server farm from over 40 hours all the way down to roughly four!
NOTE: The SharePoint patching guidance provided in this document is designed to streamline the patching process and allow for as much automation as typically possible within the common security constraints that many customers share.
This document includes best practices that have been accumulated from years of supporting large enterprise SharePoint environments. Microsoft has published additional SharePoint patching automation guidance that extends beyond this document’s guidance scope. This additional patching automation guidance includes automation and centralized control using remote PowerShell, credential passing for remote sessions, and other procedures. This guidance can be accessed via the following link.
Many enterprise customers operating SharePoint maintain a policy of ensuring Cumulative Updates (CU) are always kept a month behind the latest release version. This decision is due to issues and bugs that a premature CU installation can cause in the environment with the 3rd party products.
SharePoint CU patching is recommended to remaining at least one month behind the most current release CU patch. This approach to patching is the recommended best practice since there may be CU regressions and rereleases that could impact the support and stability of the SharePoint environments. Supportability between SharePoint and all third party applications is imperative by remaining at least one CU release behind the current release significantly decreases the chance of any issues arising.
If there are SharePoint security updates contained within the CU that are required to be installed, those can be individually installed on each SharePoint server.
Prior to any SharePoint maintenance that involves installing security updates, cumulative updates, service packs, or any other task that will modify the databases, it is recommended to execute the following pre patching procedures.
It is important to verify Windows and SQL updates have been applied prior to patching the SharePoint environment.
NOTE: Windows and SQL updates should always occur prior to installing SharePoint updates.
This helps ensure service continuity as security and features updates are applied.
CRITICAL: SharePoint updates are integrated with the Windows updates and should not be advertised to any SharePoint server. Administrators must verify that no SharePoint servers have been pushed SharePoint updates during Windows and SQL patching.
It is important to verify that application downtime notifications are sent to the necessary parties prior to patching the SharePoint environment since service outages will occur.
For environments that have active users, it’s recommended to add a maintenance banner to notify of an upcoming outage due to maintenance. The “Set_MaintenanceModeNotifications.ps1” script will query all content databases and set a maintenance message to an entire farm at the Content database level. Ensure that the proper timezone is considered for the environment.
It’s recommended practice to have a staging server that can be leveraged as the execution server for most of the patching steps. Using the staging server will help streamline the patching process as it provides a common location for hosting scripts, patches, and logs for every environment. An example of where the SharePoint updates are located for an environment is below:
The SharePoint update files to be installed should be available on the staging server at the directory path location described in the previous section. This folder where the updates are saved is required to be a shared folder that is reachable from every SharePoint server in the environment.
Copy all necessary SharePoint update files to the staging server at a common directory path location. This location will be used for the following steps. Ensure the “Update_SPFarmServers.ps1” script is copied into the same folder as the SharePoint update files. This script will speed up the patch installation process significantly but it must be copied along with the update files to every server in the environment.
An example of the typical files required to be present in the SharePoint CU folder.
To configure the script, you will need to update the source and destination directory paths. The first value (yellow) is the UNC path to the source files that need to be copied to the farm servers. The second value (green) is the destination directory path on the SharePoint server. The destination drive letter (C:\, I:\, etc.) must exist on all servers. The script will create the specified folder structure automatically, therefore it is not required to pre-create the specified folder structure.
Run the “Update-SPFarmServers.ps1” PowerShell script as an administrator from within the SharePoint CU folder on each SharePoint server.
NOTE: Do not run this script from the PowerShell ISE, this script must be executed in PowerShell as an administrator from the SharePoint CU folder.
This script will stop and disable the IISADMIN, OWSTIMER and Search Services on the SharePoint servers prior to patch installation. The script will enable and restart the respective services after the patch installation completes. Testing by the Microsoft team has shown significant time reductions when the services are stopped on the server during patch installations.
NOTE: Select option “2” if the script prompts to stop the Search services. It may not prompt for input if the Search services are not running on the server.
Many SharePoint updates require a restart after installation. It is best practice to always perform a server restart after applying an update. After confirming every server has been updated, restart every server in the environment.
The script “Restart-SharePointServers.ps1” can be used to remotely resart all servers in the SharePoint farm at once.
NOTE: The server where the script is executed from will not be restarted automatically and will need to be restarted manually.
OPTIONAL: This step is optional and may not be required depending on the information architecture configuration of the SharePoint environment.
Prior to running the SharePoint 2013 Product Configuration Wizard (PSCONFIG), you may need to remove the SharePoint Usage logging database from the SQL AlwaysOn Availability Group (AOAG). The SharePoint Configuration Wizard has been known to fail in some environments during the upgrade process if it cannot upgrade this specifc database. This is due to the database not being able to be synchronized from the primary to the secondary SQL node. The process involves removing the usage logging database from the SQL AOAG, complete the SharePoint Configuration Wizard processes, then finally adding the usage logging database back to the SQL AOAG.
To remove the Usage logging database, on the primary SQL server, connect to the SQL instance that hosts the SharePoint databases. Run the follow SQL command to remove the SharePoint usage logging database from the SQL AOAG.
SQL Command:
ALTER AVAILABILITY GROUP group_name REMOVE DATABASE database_name
Once the database is removed from the primary node, its status will automatically be updated on the second node and the patching process can continue.
Run the “ContentDB-1-CreateDatabaseMappingCSV.ps1” script as an administrator to create a content database mapping file of every content database prior to dismounting them from the SharePoint farm.
This script will query all content databases and create a CSV file with required information for dismounting and mounting the content databases to their respective web applications. This script must be ran before dismounting the content databases to preserve information such as the web application they belong to and other relevant information.
The script will report progress as it generates the database mapping file.
An example of what is contained in the ContentDB_DatabaseMappings.csv file.
Run the “ContentDB-2-DismountDatabases.ps1” script as an administrator to dismount all content databases contained with the “ContentDB_DatabaseMappings.csv” file. The $directory location must be the same directory path as the “ContentDB_DatabaseMappings.csv” file. The script will recurse through the content databases contained within the CSV file and dismount them from the SharePoint farm. It will also create a log file with logging for every action with a time stamp.
To configure both scripts, you will need to edit the directory path location. This directory path location will be where the files will be exported to and imported from.
Script Parameters:
# Directory to where the “SharePoint-ContentDatabases.csv” file is located
$directory = “E:\SharePoint\”
The script will report progress as it dismounts each content database.
A log of database dismounting and mounting will be created at the directory path location.
It is advisable to execute PSCONFIG on the Central Administration server in the farm successfully before running the next procedures on all remaining servers. Any errors that occur during the first PSCONFIG execution should be remediated prior to running PSCONFIG on any other server.
Follow these steps to run the SharePoint 2013 PSCONFIG and begin the SharePoint patching:
Run the SharePoint 2013 PSCONFIG on the Central Administration server. When prompted click Next, click Yes, click Next, then click Finish.
Verify PSCONFIG completed successfully. Log into Central Administration to verify the server status of all servers except the Central Administration server show “Update Required” or “Upgrade Available”.
Run the SharePoint 2013 PSCONFIG on all remaining servers in the environment.
From Central Administration, browse to System Settings > Manage Servers in the Farm. Verify all servers display a “No Action Required” status.
From Central Administration, browse to Upgrade and Migration > Check Upgrade Status. Verify that the upgrade status displays “Succeeded”.
From Central Administration, browse to Upgrade and Migration > Review Database Status. Verify that “No Action Required” is displayed for all databases.
Run the “ContentDB-3-MountDatabases.ps1” script as an administrator to mount all content databases contained with the “ContentDB_DatabaseMappings.csv” file. The $directory location must be the same directory path as the “ContentDB_DatabaseMappings.csv” file.
This script mounts all content databases contained with the “SharePoint-ContentDatabases.csv” file that is located at the $directory directory path. The script will mount each content database to the correct web application as well as reset other metadata like “WarningSiteCount” and “MaximumSiteCount”. It will also add to the log file with logging for every action with a time stamp.
To configure both scripts, you will need to edit the directory path location. This directory path location will be used to export the file to and where to import the file from.
Script Parameters:
# Directory to where the “SharePoint-ContentDatabases.csv” file is located
$directory = “E:\SharePoint”
The script will report progress as it mounts each content database.
Run the “ContentDB-4-UpgradeDatabases.ps1” script as an administrator to upgrade all content databases contained within the SharePoint farm that show upgrade required.
This script will attempt to upgrade, in parallel, the specified content databases. The number of parallel threads is limited to the number of logical processors on the machine. All database upgrade actions will be logged to a newly created ContentDB_UpgradeResults.csv file located in the same directory path.
To configure both scripts, you will need to edit the directory path location. This directory path will be used to export the log. The MaxUpgradeThreads can also be modified to a maximum of the logical processors the server has.
Script Parameters:
# Directory to where the “SharePoint-ContentDatabases.csv” file is located
$directory = “E:\SharePoint”
# Number of current threads to upgrade content databases in parallel.
$maxUpgradeThreads = 4
An example of the what is contained in the ContentDB_UpgradeResults.csv file.
The script will report progress as it mounts each content database.
If the usage logging database was removed from the SQL AOAG prior to running PSCONFIG, then follow this step to resume replication of the database between the primary and secondary SQL nodes.
To resume replication for the usage logging databse, on the primary SQL server, connect to the SQL instance that hosts the SharePoint databases. Run the following SQL command to resume the replication of the SharePoint usage logging database in the SQL Always On Availability Group (AOAG).
OPTIONAL: This step is optional and may not be required depending on the information architecture configuration of the SharePoint environment.
It is good practice to remove the SharePoint CU files from the server after patching. To automate the removal process, administrators can leverage the “Remove-SharePointUpdateFromServer.ps1” script. The script will delete the specified directories from all the servers in the farm.
To configure the script, you will need to edit the update directory path location. This script will remove the destination folder and all files from all servers in the environment, including the staging server.
Script Parameters:
$foldersToDelete = @(
# June 2014 CU
“E:\_SharePointUpdates\2014_06_CU\”,)
$confirmDeletion = $true
The script will report progress as it deletes the files from each server.
After patching a SharePoint farm or upgrading a content database it is recommended to review the respective upgrade log(s) for errors and warnings. To aid in the collection of upgrade logs, an administrator can leverage the “Copy-SharePointServerUpgradeLogs.ps1” script. This script will copy all log files that match the *upgrade*.log name convention to the specified directory. The script will create a sub-folder under the target directory for each server.
To configure this script, edit the $logCollectionBasePath to the directory path desired to save the upgrade logs from every SharePoint server in the environment.
Script Parameters:
# Destination for the upgrade logs.
$logCollectionBasePath = “E:\SharePoint\Logs\”
# SharePoint log directory
$logdirectory = “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\LOGS”
# how many days from the past should the script pull logs?
$maxAgeOfLogs = 5
# Do multi-threaded copies with n threads (default 8).
After patching a SharePoint farm, an administrator should confirm all the servers have been patched by executing the “Get-PatchReferenceCount.ps1” script. This script will query the registry on each server and total the number of references to the specified KB number(s). To retrieve the KB number that was installed, browse to Control Panel > Programs > Program and Features > Installed Updates. The KB number will be listed in the name of the SharePoint update in parentheses.
To configure the script, you will need to edit the KB number to the update number(s) that were recently installed.
NOTE: Searching for the KB number is best performed one at a time due to timeout issues that may occur from querying all the server in the SharePoint farm.
Script Parameters:
#kb numbers to search for
$kbs = @(“2899558”)
The screenshot below shows that each server in the farm has 36 reference to KB 2883068. If the reference counts are not identical on all servers, further investigation is required by an administrator.
Following the completion of any SharePoint maintenance that involves installing security updates, cumulative updates, service packs, or any other task that will modify the databases, it’s recommended to execute the following post-patching procedures.
Perform the following checks to ensure the servers in the environment are operational and no issues are present in the server event logs and patch/upgrade logs.
Remote desktop to each server
Verify all automatic services are started. App pools and sites are started in IIS on web servers.
RDP to every server in the SharePoint farm. On Web servers, ensure all app pools and IIS sites are started if they should be. On App servers, ensure all services (SharePoint and third party) are running if they should be.
Check event logs
Check Application, System, Security event logs for new errors, warnings, or failures that will affect normal operations of SharePoint.
Log onto every server to verify any events in the different logs show errors, warnings, or failures that require remediation.
Third Party Apps
Verify third party products installed in the SharePoitn farm are operational.
Log onto servers where third party products are installed and running to verify if they are operational and running as expected.
Perform the following checks to ensure all services, websites, and applications are operational.
Check
Description
Instructions
Site Access
This check verifies that all web applications are online and serving content to clients.
Log into every URL that is hosted in the environment to verify it is accessible and operational.
Search
This check verifies that search is available and has a current index.
Perform a few searches from each web application and verify that fresh results are returned and not broken links. Test a few scopes including people, this site, and other custom scopes. Also check all Search Service Applications to verify crawls are being run according to schedule and there are minimal errors or warnings.
Office Web Apps
This check verifies that Office documents can be opened and edited in a browser.
Perform at least four checks on each web application for each office document type. Search for xlsx, pptx, docx, and vdw. Verify these file types can be opened by Office Web Applications in a browser. Some files may not open due to certain features used in the document that are not supported by OWA. In most cases, OWA will alert you to any unsupported features.
Project Server
This check verifies that all Project Server sites are online and serving content to clients.
Log into every Project Server (PWA) site collection that is hosted in the environment to verify it’s accessible and operational.
Email enabled lists and alerts
This check verifies that email enabled lists and outgoing email is functional.
Subscribe for an immediate alert at a library. Then send an un-signed email to an email enabled library. Verify that you received the alert email indicating the message was added to the library. NOTE: Please allow several minutes to receive the alert email and emails should send/receive within 30 minutes.
Reporting Services
This check verifies that Reporting Services and Portal Management Reports are available.
Check reports to verify results are displayed and data is current for reports.
Timer jobs
This check verifies that all timer jobs are running on schedule.
Log into Central Admin and check the status of timer jobs. Look for long running and failed timer jobs.
User Profile Sync
This check verifies that SharePoint user profiles are in sync with Active Directory.
Open the User Profile Service Application and verify the nightly sync was run. Click the idle link under Profile Synchronization Settings on the right-hand side. This will open the sync log. Verify that there are no major failures and minimal warnings. More detail can be obtained in the miisclient.exe application on the Central Administration server if necessary.
If a maintenance banner was used for the notifying users of an outage due to maintenance, the “Remove_MaintenanceModeNotifications.ps1” script will query all content databases and remove the maintenance banner from all sites in the environment.
Conclusion
There you go. That’s everything you should need to practically automate 90% of patching an on-premises SharePoint farm.
