Upgrade Steps for Microsoft SQL Server 2016 using Upgrade Advisor

With SQL Server 2016 Upgrade Advisor, you can easily check for any issues in your databases that may interfere with the upgrade process so that you can pinpoint and fix them quickly. Then, it is a simple process to move your databases that aren’t using any high-availability or disaster-recovery features from their current instances to SQL Server 2016. Using MS SQL Server 2016 Upgrade Advisor to check for upgrade issues, how to fix non-ANSI join syntax issues, and how to migrate a database to a newer version of SQL Server.

Downloading SQL Server 2016 Upgrade Advisor

The first step is to download SQL Server 2016 Upgrade Advisor. This link will lead you to the most current version. Once you have downloaded and installed the program, you can open it up and get to work.

Running your initial database analysis

Hopefully at this point you already know which databases you want to move. Click the button labeled “Analyze and Migrate to SQL Server” and connect to the server hosting the databases you are migrating. Now, you can select the databases on this server that you will be upgrading and click “Run” to begin the initial analysis.

At this point, Upgrade Advisor will analyze the schema and look through stored procedures, functions, triggers, and more for any issues that may arise when migrating these databases. Upon completion, you will see a compatibility assessment for each database that indicates how many compatibility issues each may have. Clicking on the database will allow you to view the results in detail.

Reviewing and exporting the flagged issues

Upgrade Advisor will display the compatibility issues next to their associated version within the database under three categories. The first category, which will be shown in red, denotes High Severity issues that must be resolved before the upgrade can be completed. The second category, shown in yellow, indicates Medium Severity issues that may become a larger problem with future SQL Server versions and may cause problems with this version. The third category, shown in blue, are Low Severity issues that can typically be ignored at this point. If you click on the issues, you can view them in more detail.

By clicking the save icon in the upper-right corner, you can also export the compatibility report and save it as a separate file.

The report generated by the export function can be created as either an HTML or a CSV file, and it will allow you to review the report outside of Upgrade Advisor.

Resolving High Severity issues

Upon viewing the issues, click the red issues marked as High Severity to get more details on the cause. One of the most common issues you will encounter is an ANSI join issue related to improper syntax. Fortunately, this can be fixed easily. First, go to Management Studio and find the Stored Procedure where the error occurred. Open the stored procedure, and highlight the query where the error is. Then, simply right-click, choose “Design Query in Editor,” and click “OK” to automatically convert to the ANSI join syntax.

Finalizing the upgrade process

After fixing all of the High Severity issues, run another analysis of the databases using Upgrade Advisor. Hopefully, the analysis should return no more High Severity issues. At this point, click the “Migrate” button. Now, choose your Destination Server and click “Connect.” Finally, click the second “Migrate” button to complete the process.

Now, your databases should be successfully migrated to your newer version of SQL Server. If need be, you can verify this by refreshing the server in Management Studio and locating the migrated databases on that server.

Upgrade SharePoint 2013 to SharePoint 2016 Steps

There is no in-place upgrade from SharePoint 2013. You must upgrade each content database using the Database Attach and upgrade Method. SharePoint 2016 does not support classic mode authentication. Many SharePoint 2013 web applications still utilize classic mode authentication.  For content databases using classic mode authentication, you must migrate to Claims Based authentication before you begin migrating your data. According to the Microsoft TechNet Article, “SharePoint Server 2016 supports an upgrade from SharePoint Server 2013 with Service Pack 1 (SP1) with March 2013 PU, version 15.0.4481.1005 or higher. All database must be upgraded to version 15.0.4481.1005 or higher, otherwise upgrade to SharePoint Server 2016 will be blocked.”  https://technet.microsoft.com/en-us/library/cc262483(v=office.16).aspx

1. Create a new site collection in SharePoint 2013
2. Move the site to a temporary database
3. Make a copy of DB in SharePoint 2013 environment and move it to the SharePoint 2016 database server
4. Test the SharePoint 2013 attached DB in SharePoint 2016 environment, Mount the DB and upgrade DB
5. Change the site collection owner and Hit the site and test everything

STEP 1 – CREATE A NEW SITE COLLECTION IN THE SHAREPOINT 2013

Create a brand new site collection in the SharePoint 2013 environment to test the upgrade process from SharePoint 2013 to SharePoint 2016.

$template = Get-SPWebTemplate "STS#0"

New-SPSite -Url $varSharePoint2013SiteCol -OwnerAlias $varSharePoint2013admin -Template $template

in this Powershell cmdlet, we create a new site collection in SharePoint 2013 based on the team site template.

STEP 2 – MOVE THE SITE COLLECTION TO A TEMPORARY DATABASE

It is always good to move the site collection into a new database and then move the new database to SharePoint 2016. By doing this, you will not face any orphaned object problem while upgrading. Sometimes SharePoint doesn’t clear the database nicely when you delete items from the interface and this will cause the orphaned object problem. Orphaned object means you have deleted something from the SharePoint interface (list, subsite,..), but the object is still exist in the database level. So it is always a good practice to move the site collection to a new database before upgrading.

New-SPContentDatabase $varSharePoint2013TempDB -DatabaseServer $varSharePoint2013DBServerName -WebApplication $varSharePoint2013WebApp

Move-SPSite $varSharePoint2013SiteCol -DestinationDatabase $varSharePoint2013TempDB

After moving the site into a new db, you have to do IIS reset

STEP 3 – MOVE THE DB

This step is done on SQL Servers. First you have to login to your SharePoint 2013 SQL Server machine and connect to your SQL Server instance with the SQL Server management studio.

Expand databases node, right click on the database name -> tasks -> backup

Copy the backup file to the SharePoint 2016 database server.

Open SQL server management studio in SharePoint 2016 database server.

Connect to the database server

Right click on database, select restore database

Select the device and select your backup file

Double check everything, and click on ok

The database name will show in the list of databases

Now you have to add the farm account to the database as the db_owner role.

Expand the database name -> security – > users and add the SharePoint 2016 farm account

[I have different accounts for setup and farm as Microsoft suggested in the best practice, I added both of them as the user of this db and made them db_owner]

STEP 4 – TEST AND MOUNT THE DB

If the managed path does not exist in SharePoint 2016, you can create it with this cmdlet:

New-SPManagedPath "Teams" -WebApplication "http://somesite"

These powershells must be run on your SharePoint 2016 farm

Test-SPContentDatabase -Name $varSharePoint2013TempDB -WebApplication $varSharePoint2016WebApp

Mount-SPContentDatabase -Name $varSharePoint2013TempDB -DatabaseServer $varSharePoint2016DBServerName -WebApplication $varSharePoint2016WebApp

$wa = Get-SPWebApplication -identity $varSharePoint2016WebApp

$wa.GrantAccessToProcessIdentity($varSharePoint2016ServiceApp)

STEP 5 – VERIFICATION

now the upgrade process is completed. It is likely that you have different accounts to access your SharePoint 2013 and SharePoint 2016 environment. So now if you hit the site you will see an access denied message. You have to login to the SharePoint 2016 central administration and change the site collection administrator for the newly upgraded site to your SharePoint 2016 site admin. Then you can hit the site and see the home page of the site.

In order to verify that the upgrade process is done without any error, you have to go to the site settings and under the “Site Collection Administration” section, click on the Site collection upgrade link.

You see the upgrade page, you can now click on the Review Site Collection Upgrade Status to see the report.

As you see in the upgrade process is done without any error or warnings

Here you have the whole powershell script for this blogpost. You can use this powershell to upgrade your sites from SharePoint 2013 to SharePoint 2016. Just remember step 1 and 2 are done in SharePoint 2013 environment, step 3 are done on SQL servers of both environment via GUI and step 4 and 5 are in SharePoint 2016 environment

$snapin = Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue -PassThru

if ($snapin -eq $null) {

    Write-Error "Unable to load the Microsoft.SharePoint.PowerShell Snapin! Have you installed SharePoint?"

    return

}

#############

# Parameters

#############

$varSharePoint2013admin = ""

$varSharePoint2013WebApp = ""

$varSharePoint2013SiteCol = ""

$varSharePoint2013TempDB = ""

$varSharePoint2013DBServerName = ""

$varSharePoint2016admin = ""

$varSharePoint2016WebApp = ""

$varSharePoint2016SiteCol = ""

$varSharePoint2016ManagedPath = ""

$varSharePoint2016DBServerName = ""

$varSharePoint2016ServiceApp =" "

###################

# step 1 - create site collection

###################

$template = Get-SPWebTemplate "STS#0"

New-SPSite -Url $varSharePoint2013SiteCol -OwnerAlias $varSharePoint2013admin -Template $template

###################

# step 2 - Move the site collection to a new DB

###################

New-SPContentDatabase $varSharePoint2013TempDB -DatabaseServer $varSharePoint2013DBServerName -WebApplication $varSharePoint2013WebApp

Move-SPSite $varSharePoint2013SiteCol -DestinationDatabase $varSharePoint2013TempDB

###################

# step 3 - Move DB to 16 env [done via GUI]

###################

###################

# step 4 - upgrade

###################

Test-SPContentDatabase -Name $varSharePoint2013TempDB -WebApplication $varSharePoint2016WebApp

Mount-SPContentDatabase -Name $varSharePoint2013TempDB -DatabaseServer $varSharePoint2016DBServerName -WebApplication $varSharePoint2016WebApp

$wa = Get-SPWebApplication -identity $varSharePoint2016WebApp

$wa.GrantAccessToProcessIdentity($varSharePoint2016ServiceApp)

###################

# step 5 - verify

###################

Start iexplore $varSharePoint2016SiteCol