SQL Server Supported Upgrade Paths

In-place upgrade paths of SQL Server are shown below.

Ahh the memories

Note: You’ll need to take into consideration:

Destination
2008

2008 R2

2012

2014

2016

2017

2019*
Source
2005
122
20083 455
2008 R26 7 8 8
20129 10 10
2014
2016
2017

1 Minimum SQL Server 2005 SP2

2 Minimum SQL Server 2005 SP4

3 Minimum SQL Server 2008 SP2

4 Minimum SQL Server 2008 SP3

5 Minimum SQL Server 2008 SP4

6 Minimum SQL Server 2008 R2 SP1

7 Minimum SQL Server 2008 R2 SP2

8 Minimum SQL Server 2008 R2 SP3

9 Minimum SQL Server 2012 SP1

10 Minimum SQL Server 2012 SP2

*  Unknown at this stage but SQL Server 2008 and SQL Server 2008 R2 are not blocked

Backup restores to a different SQL Server version

We know that upgrading of a SQL Server instance to another version has potential limitations e.g. you can not perform an in-place upgrade of SQL Server 2005 to SQL Server 2016.

Well what about restoring an older database backup to a newer version?

Good news, no such limitation currently exists.

Destination
2005

2008/R2

2012

2014

2016

2017

2019
Source
2005
11 1 1
2008/R2
2012
2014
2016
2017
2019

1 Database compatibility level automatically changes to SQL Server 2008 (100)

Microsoft loves PaaS Yeah Nah

Microsoft sure does push the cloud and why not it’s a big money earner for them.

These days lots of organizations are looking for cloud first solutions as it’s the next big thing and it theoretically will save them money, its robust etc.

Ok cool, so lets look at Microsoft’s all products page.

All Products page 2019

Can you spot how many of Microsoft’s latest key business products ‘support’ use of Azure SQL Database or Azure SQL Database managed instance for it’s back-end?

  • Microsoft Skype for Business Server 2019 – no
  • Microsoft Dynamics CRM 2016 – no
  • Microsoft Dynamics 365 (on-premises / IaaS) – no
  • Microsoft Dynamics GP 2018 – no
  • Microsoft SharePoint Server 2019 – no
  • Microsoft System Center 2019 – no
  • ….

Wow, so to me this means either Microsoft’s products teams:

  • Don’t have the expertise to make use of PaaS, or
  • Can’t make it work, or
  • Didn’t get the memo re using the cloud, or
  • Don’t trust it, or
  • Just don’t know?

So when your boss or client says, let lift to the cloud and hey what about using Azure SQL Database or Azure SQL Database managed instance as a backed for our latest Microsoft products, you can respond Yeah Nah.

Don’t pay for SQL Server Licenses if you don’t have to

Let’s face it SQL Server licensing is expensive and companies will jump at any opportunity to reduce costs where they can.

Every dollar counts

SQL Server licenses are actually bundled with some products!

What you say.

Yes true, for example, System Center Configuration Manager (SCCM) comes with a free SQL Server Standard Edition license. The catch is that if a database for any additional Microsoft or third-party product shares the SQL Server, you must have a separate license for that SQL Server instance.

Wow, so why would you ever host your SCCM databases on a shared instance. It just doesn’t make sense, unless you really need some enterprise features like Transparent Data Encryption (TDE) or you have a super complex environment.

Developer Edition became free with SQL Server 2014, so it’s a no brainier to use that for non-Production environments. Even pre-SQL Server 2014 you could have looked to use the much cheaper Developer Edition to help reduce costs.

So I’ll start a list and as it grows so can your potential cost savings:

Importing Data Migration Assistant JSON Findings

When using the Data Migration Assistant you’ll have an option to export the findings as CSV or JSON.

The following provides a guide to importing the JSON data into a database table for you to review.

Firstly, create a table to hold the information using:

CREATE TABLE DMA_Findings
(
 [Project_Status] nvarchar(128)
,[Project_Name] nvarchar(128)
,[Project_SourcePlatform] nvarchar(128)
,[Project_TargetPlatform] nvarchar(128)
,[ServerInstances_ServerName] nvarchar(128)
,[ServerInstances_Version] nvarchar(128)
,[ServerInstances_Status] nvarchar(128)
,[Databases_ServerName] nvarchar(128)
,[Databases_Name] nvarchar(128)
,[Databases_CompatibilityLevel] nvarchar(128)
,[Databases_SizeMB] decimal (20,2)
,[Databases_Status] nvarchar(128)
,[Databases_ServerVersion] nvarchar(128)
,[Databases_ServerEdition] nvarchar(128)
,[AssessmentRecommendations_CompatibilityLevel] nvarchar(128)
,[AssessmentRecommendations_Category] nvarchar(128)
,[AssessmentRecommendations_Severity] nvarchar(128)
,[AssessmentRecommendations_ChangeCategory] nvarchar(128)
,[AssessmentRecommendations_RuleId] nvarchar(128)
,[AssessmentRecommendations_Title] nvarchar(160)
,[AssessmentRecommendations_Impact] nvarchar(4000)
,[AssessmentRecommendations_Recommendation] nvarchar(4000)
,[AssessmentRecommendations_MoreInfo] nvarchar(4000)
,[ImpactedObjects_Name] nvarchar(128)
,[ImpactedObjects_ObjectType] nvarchar(128)
,[ImpactedObjects_ImpactDetail] nvarchar(4000)
,[ImpactedObjects_SuggestedFixes] nvarchar(4000)
);

Next, import a single DMA json file using:

INSERT INTO DMA_Findings( 
 [Project_Status]
,[Project_Name]
,[Project_SourcePlatform]
,[Project_TargetPlatform]
,[ServerInstances_ServerName]
,[ServerInstances_Version]
,[ServerInstances_Status]
,[Databases_ServerName]
,[Databases_Name]
,[Databases_CompatibilityLevel] 
,[Databases_SizeMB]
,[Databases_Status]
,[Databases_ServerVersion]
,[Databases_ServerEdition]
,[AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
,[ImpactedObjects_SuggestedFixes]
)
SELECT 
 Project.[Status] AS [Project_Status]
,Project.[Name] AS [Project_Name]
,Project.[SourcePlatform] AS [Project_SourcePlatform] 
,Project.[TargetPlatform] AS [Project_TargetPlatform]
,[ServerInstances].[ServerName] AS [ServerInstances_ServerName] 
,[ServerInstances].[Version] AS [ServerInstances_Version]
,[ServerInstances].[Status] AS [ServerInstances_Status]
--,[ServerInstances].[AssessmentRecommendations] AS [ServerInstances_AssessmentRecommendations]
,[Databases].[ServerName] AS [Databases_ServerName]
,[Databases].[Name] AS [Databases_Name]
,[Databases].[CompatibilityLevel] AS [Databases_CompatibilityLevel] 
,[Databases].[SizeMB] AS [Databases_SizeMB]
,[Databases].[Status] AS [Databases_Status]
,[Databases].[ServerVersion] AS [Databases_ServerVersion] 
,[Databases].[ServerEdition] AS [Databases_ServerEdition]
,[AssessmentRecommendations].[CompatibilityLevel] AS [AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations].[Category] AS [AssessmentRecommendations_Category]
,[AssessmentRecommendations].[Severity] AS [AssessmentRecommendations_Severity]
,[AssessmentRecommendations].[ChangeCategory] AS [AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations].[RuleId] AS [AssessmentRecommendations_RuleId]
,[AssessmentRecommendations].[Title] AS [AssessmentRecommendations_Title]
,[AssessmentRecommendations].[Impact] AS [AssessmentRecommendations_Impact]
,[AssessmentRecommendations].[Recommendation] AS [AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations].[MoreInfo] AS [AssessmentRecommendations_MoreInfo]
,[ImpactedObjects].[Name] AS [ImpactedObjects_Name]
,[ImpactedObjects].[ObjectType] AS [ImpactedObjects_ObjectType]
,[ImpactedObjects].[ImpactDetail] AS [ImpactedObjects_ImpactDetail]
,[ImpactedObjects].[SuggestedFixes] AS [ImpactedObjects_SuggestedFixes]
FROM
OPENROWSET(BULK N'C:\pathtoyourJSONFile\yourfile.json', SINGLE_CLOB) AS json
OUTER APPLY OPENJSON(BulkColumn)
WITH (
 [Status] nvarchar(128)
,[Name] nvarchar(128)
,[SourcePlatform] nvarchar(128)
,[TargetPlatform] nvarchar(128)
,[Databases] nvarchar(MAX) AS JSON
,[ServerInstances] nvarchar(MAX) AS JSON
) AS [Project]
OUTER APPLY  OPENJSON([ServerInstances])
WITH (
 [ServerName] nvarchar(128)
,[Version] nvarchar(128)
,[Status] nvarchar(128)
--,[AssessmentRecommendations] nvarchar(4000)
) AS [ServerInstances]
OUTER APPLY  OPENJSON([Databases])
WITH (
 [ServerName] nvarchar(128)
,[Name] nvarchar(128)
,[CompatibilityLevel] nvarchar(128)
,[SizeMB] decimal (20,2)
,[Status] nvarchar(128)
,[ServerVersion] nvarchar(128)
,[ServerEdition] nvarchar(128)
,[AssessmentRecommendations] nvarchar(MAX) AS JSON
) AS [Databases]
OUTER APPLY OPENJSON([AssessmentRecommendations])
WITH (
 [CompatibilityLevel] nvarchar(128)
,[Category] nvarchar(128)
,[Severity] nvarchar(128)
,[ChangeCategory] nvarchar(128)
,[RuleId] nvarchar(128)
,[Title] nvarchar(160)
,[Impact] nvarchar(4000)
,[Recommendation] nvarchar(4000)
,[MoreInfo] nvarchar(4000)
,[ImpactedObjects] nvarchar(MAX) AS JSON
) AS [AssessmentRecommendations]
OUTER APPLY OPENJSON([ImpactedObjects])
WITH (
 [Name] nvarchar(128)
,[ObjectType] nvarchar(128)
,[ImpactDetail] nvarchar(4000)
,[SuggestedFixes] nvarchar(4000)
) AS [ImpactedObjects];

Or, if you have lots of DMA json files you can quickly load them all using the following PowerShell

$dma_json_folder = 'C:\....\' # Location of the the DMA json files
$instance_name = '...' # SQL instance which will contain the findings
$database_destination = '...' # Database on the SQL instance

# Get a list of the audit queries
$dma_json_files = @(Get-ChildItem $dma_json_folder *.json) # Get a list of audit queries

if ($dma_json_files.Length -ne 0) # Proceed as queries to run
{

try 
{
Foreach ($dma_json_file in $dma_json_files) # Cycle through each query
    {
    [string]$json_file = $dma_json_file.BaseName + '.json' # Set output file name

$query = @"
INSERT INTO DMA_Findings( 
 [Project_Status]
,[Project_Name]
,[Project_SourcePlatform]
,[Project_TargetPlatform]
,[ServerInstances_ServerName]
,[ServerInstances_Version]
,[ServerInstances_Status]
,[Databases_ServerName]
,[Databases_Name]
,[Databases_CompatibilityLevel] 
,[Databases_SizeMB]
,[Databases_Status]
,[Databases_ServerVersion]
,[Databases_ServerEdition]
,[AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
,[ImpactedObjects_SuggestedFixes]
)
SELECT 
 Project.[Status] AS [Project_Status]
,Project.[Name] AS [Project_Name]
,Project.[SourcePlatform] AS [Project_SourcePlatform] 
,Project.[TargetPlatform] AS [Project_TargetPlatform]
,[ServerInstances].[ServerName] AS [ServerInstances_ServerName] 
,[ServerInstances].[Version] AS [ServerInstances_Version]
,[ServerInstances].[Status] AS [ServerInstances_Status]
--,[ServerInstances].[AssessmentRecommendations] AS [ServerInstances_AssessmentRecommendations]
,[Databases].[ServerName] AS [Databases_ServerName]
,[Databases].[Name] AS [Databases_Name]
,[Databases].[CompatibilityLevel] AS [Databases_CompatibilityLevel] 
,[Databases].[SizeMB] AS [Databases_SizeMB]
,[Databases].[Status] AS [Databases_Status]
,[Databases].[ServerVersion] AS [Databases_ServerVersion] 
,[Databases].[ServerEdition] AS [Databases_ServerEdition]
,[AssessmentRecommendations].[CompatibilityLevel] AS [AssessmentRecommendations_CompatibilityLevel]
,[AssessmentRecommendations].[Category] AS [AssessmentRecommendations_Category]
,[AssessmentRecommendations].[Severity] AS [AssessmentRecommendations_Severity]
,[AssessmentRecommendations].[ChangeCategory] AS [AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations].[RuleId] AS [AssessmentRecommendations_RuleId]
,[AssessmentRecommendations].[Title] AS [AssessmentRecommendations_Title]
,[AssessmentRecommendations].[Impact] AS [AssessmentRecommendations_Impact]
,[AssessmentRecommendations].[Recommendation] AS [AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations].[MoreInfo] AS [AssessmentRecommendations_MoreInfo]
,[ImpactedObjects].[Name] AS [ImpactedObjects_Name]
,[ImpactedObjects].[ObjectType] AS [ImpactedObjects_ObjectType]
,[ImpactedObjects].[ImpactDetail] AS [ImpactedObjects_ImpactDetail]
,[ImpactedObjects].[SuggestedFixes] AS [ImpactedObjects_SuggestedFixes]
FROM
OPENROWSET(BULK N'$dma_json_folder$json_file', SINGLE_CLOB) AS json
OUTER APPLY OPENJSON(BulkColumn)
WITH (
 [Status] nvarchar(128)
,[Name] nvarchar(128)
,[SourcePlatform] nvarchar(128)
,[TargetPlatform] nvarchar(128)
,[Databases] nvarchar(MAX) AS JSON
,[ServerInstances] nvarchar(MAX) AS JSON
) AS [Project]
OUTER APPLY  OPENJSON([ServerInstances])
WITH (
 [ServerName] nvarchar(128)
,[Version] nvarchar(128)
,[Status] nvarchar(128)
--,[AssessmentRecommendations] nvarchar(4000)
) AS [ServerInstances]
OUTER APPLY  OPENJSON([Databases])
WITH (
 [ServerName] nvarchar(128)
,[Name] nvarchar(128)
,[CompatibilityLevel] nvarchar(128)
,[SizeMB] decimal (20,2)
,[Status] nvarchar(128)
,[ServerVersion] nvarchar(128)
,[ServerEdition] nvarchar(128)
,[AssessmentRecommendations] nvarchar(MAX) AS JSON
) AS [Databases]
OUTER APPLY OPENJSON([AssessmentRecommendations])
WITH (
 [CompatibilityLevel] nvarchar(128)
,[Category] nvarchar(128)
,[Severity] nvarchar(128)
,[ChangeCategory] nvarchar(128)
,[RuleId] nvarchar(128)
,[Title] nvarchar(160)
,[Impact] nvarchar(4000)
,[Recommendation] nvarchar(4000)
,[MoreInfo] nvarchar(4000)
,[ImpactedObjects] nvarchar(MAX) AS JSON
) AS [AssessmentRecommendations]
OUTER APPLY OPENJSON([ImpactedObjects])
WITH (
 [Name] nvarchar(128)
,[ObjectType] nvarchar(128)
,[ImpactDetail] nvarchar(4000)
,[SuggestedFixes] nvarchar(4000)
) AS [ImpactedObjects];
"@
   
    #
    Write-Host "Processing" $dma_json_folder$json_file
    # Use Invoke-Sqlcmd to extract the information
    Invoke-Sqlcmd -Query $query -ServerInstance $instance_name -Database $database_destination -QueryTimeout 600 -ErrorAction Stop 
    }
}
catch 
{
Write-Host 'SQL query error: ' $_.Exception.Message -ForegroundColor Red
#Exit; # Exit and go no further
}
}

Now you’ll be able to query the findings:

SELECT * FROM [DMA_Findings];

An example of finding issues for a migration to SQL Server 2017 you could use a query like:

SELECT 
 [Databases_ServerName] AS Instance
,[Databases_ServerEdition] AS InstanceEdition
,[ServerInstances_Version] AS InstanceBuild
,[Project_SourcePlatform] AS SourcePlatform
,[Project_TargetPlatform] AS TargetPlatform
,[Databases_Name] AS DatabaseName
,[Databases_CompatibilityLevel] AS DatabaseCompatibilityLevel
,[AssessmentRecommendations_CompatibilityLevel] AS TargetCompatibilityLevel
,[Databases_SizeMB] AS Database_SizeMB
,[AssessmentRecommendations_Category]
,[AssessmentRecommendations_Severity]
,[AssessmentRecommendations_ChangeCategory]
,[AssessmentRecommendations_RuleId]
,[AssessmentRecommendations_Title]
,[AssessmentRecommendations_Impact]
,[AssessmentRecommendations_Recommendation]
,[AssessmentRecommendations_MoreInfo]
,[ImpactedObjects_Name]
,[ImpactedObjects_ObjectType]
,[ImpactedObjects_ImpactDetail]
FROM [DMA_Findings]
WHERE 1=1
AND [AssessmentRecommendations_CompatibilityLevel] = 'CompatLevel140'
ORDER BY [Databases_ServerName],[Databases_Name],[AssessmentRecommendations_Severity],[AssessmentRecommendations_ChangeCategory];

Database settings lost on restore or attach

If you’ve ever restored/attached a database and stuff doesn’t work e.g. Service Broker, yes some database settings are not automatically re-set when a database is restored or re-attached.

The following code will help generate these settings to apply after a database restore/attach.

Of course you’ll need to have run this query before you remove the database(s) and only run the appropriate generated code section(s) for the restored/attached database(s).

SELECT 'PRINT ''[' + [name] + '] is_trustworthy_on enabled''; ALTER DATABASE [' + name + '] SET TRUSTWORTHY ON;' AS [run_me] FROM sys.databases WHERE is_trustworthy_on = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_broker_enabled enabled''; ALTER DATABASE [' + name + '] SET ENABLE_BROKER WITH NO_WAIT;' FROM sys.databases WHERE is_broker_enabled = 1 AND [name] <> 'tempdb'
UNION
SELECT 'PRINT ''[' + [name] + '] is_db_chaining_on enabled''; ALTER DATABASE [' + name + '] SET DB_CHAINING ON;' from sys.databases WHERE is_db_chaining_on = 1 AND [name] NOT IN ('master','tempdb')
UNION
SELECT 'PRINT ''[' + [name] + '] db_owner changed to [' + SUSER_SNAME(owner_sid) + ']''; ALTER AUTHORIZATION ON DATABASE::[' + name + '] TO [' + SUSER_SNAME(owner_sid) + '];' from sys.databases WHERE owner_sid <> 0x01  AND [name] <> 'tempdb'

SQL Server Supported Operating Systems

Below is a quick reference guide of operating systems supported by the various SQL Server versions

Windows

Windows Server20082008 R220122012 R220162019
SQL Server 2005
SQL Server 2008✔ 7✔ 5
SQL Server 2008 R2✔ 6✔ 4
SQL Server 2012✔ 3✔ 2
SQL Server 2014✔ 9✔ 8✔ 1
SQL Server 2016✖ 10
SQL Server 2017
SQL Server 2019
  1. Requires SQL Server 2014 SP1 or later ref: here
  2. Requires SQL Server 2012 SP2 or later ref: here
  3. Requires SQL Server 2012 SP1 or later ref: here
  4. Requires SQL Server 2008 R2 SP2 or later ref: here
  5. Requires SQL Server 2008 SP3 or later ref: here
  6. Requires SQL Server 2008 R2 SP1 or later ref: here
  7. Requires SQL Server 2008 SP3 or later ref: here
  8. Requires Windows Server 2008 R2 SP1 or later ref: here
  9. Requires Windows Server 2008 SP2 or later ref: here
  10. Reporting Services – SharePoint & Reporting Services Add-in for SharePoint products can both be installed on Windows Server 2008 R2 SP2 or later ref: here

Linux


SQL Server 2017SQL Server 2019
Red Hat Enterprise Linux 7.3, 7.4, 7.5, or 7.6 Server
Red Hat Enterprise Linux 8 Server
SUSE Enterprise Linux Server v12 SP2
SUSE Enterprise Linux Server v12 SP3, SP4, or SP5
Ubuntu 16.04LTS
Ubuntu 18.04
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here

Docker

SQL Server 2017SQL Server 2019
Docker Engine 1.8+ on Windows, Mac, or Linux
  • SQL Server 2017 ref: here
  • SQL Server 2019 ref: here