AzureEnterprise TechSQL

How to Upgrade to SQL Server 2016 from Earlier and Older Versions

3723 views0
How to Upgrade to SQL Server 2016 from Earlier and Older Versions
IGBy
Share

How to Upgrade to SQL Server 2016 from Earlier and Older Versions

SQL Server 2016 comes with multiple components. Upgrade considerations for each one of them is different. Before upgrading, ensure that the features are still supported and not deprecated. In this post, I am consolidating a list and talk about how to upgrade to SQL Server 2016 from earlier and older versions.

Note: If you buy something from our links, we might earn a commission. See our disclosure statement.

The first steps before upgrading is to check for the various factors that could affect your upgrade plans. Once that is validated, run the “Upgrade Advisor” to check for any existing issues in the code that might cause compatibility issues. You can download the “Upgrade Advisor” and run a set of upgrade rules against your existing installation.

The SQL Server 2016 Upgrade Advisor is not a part of the SQL Server Setup.

  • The Upgrade advisor checks the following:
  • Existing code that might break in SQL 2016.
  • Any deprecated features that is still in use on the older environment.
  • Help adopt newer features like stretch databases, In-memory OLTP, Azure SQL migration etc. by giving recommendations.

How to Upgrade to SQL Server 2016 from Earlier and Older Versions

Once you run the tests on the target servers, you will be able to see and resolve existing compatibility issues with your databases.

Check and Compare SQL Server Version Comparison from the Table Below

FeatureEnterpriseStandardWebExpress with Advanced ServicesExpress with ToolsExpressDeveloperSub-Feature
Maximum compute capacity used by a single instance - SQL Server Database Engine1Operating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 4 sockets or 16 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 coresOperating system maximumCross-Box Scale Limits
Maximum compute capacity used by a single instance - Analysis Services or Reporting ServicesOperating system maximumLimited to lesser of 4 sockets or 24 coresLimited to lesser of 4 sockets or 16 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 coresLimited to lesser of 1 socket or 4 coresOperating system maximumCross-Box Scale Limits
Maximum memory utilized per instance of SQL Server Database EngineOperating System Maximum128 GB64 GB1 GB1 GB1 GBOperating System MaximumCross-Box Scale Limits
Maximum memory utilized per instance of Analysis ServicesOperating System MaximumTabular: 16 GBN/AN/AN/AN/AOperating System MaximumCross-Box Scale Limits
Cross-Box Scale Limits
MOLAP: 64 GBCross-Box Scale Limits
Maximum memory utilized per instance of Reporting ServicesOperating System Maximum64 GB64 GB4 GBN/AN/AOperating System MaximumCross-Box Scale Limits
Maximum relational database size524 PB524 PB524 PB10 GB10 GB10 GB524 PBCross-Box Scale Limits
Server core support 1YesYesYesYesYesYesYesRDBMS High Availability
Log shippingYesYesYesNoNoNoYesRDBMS High Availability
Database mirroringYesYesWitness onlyWitness onlyWitness onlyWitness onlyYesRDBMS High Availability
RDBMS High Availability
Full safety onlyRDBMS High Availability
Backup compressionYesYesYesRDBMS High Availability
Database snapshotYesYesRDBMS High Availability
Always On failover cluster instancesYesYesYesRDBMS High Availability
RDBMS High Availability
Number of nodes is the operating system maximumSupport for 2 nodesNumber of nodes is the operating system maximumRDBMS High Availability
Always On availability groupsYesYesRDBMS High Availability
RDBMS High Availability
Up to 8 secondary replicas, including 2 synchronous secondary replicasRDBMS High Availability
Basic availability groupsYesRDBMS High Availability
RDBMS High Availability
Support for 2 nodesRDBMS High Availability
Connection directorYesYesRDBMS High Availability
Online page and file restoreYesYesRDBMS High Availability
Online indexingYesYesRDBMS High Availability
Online schema changeYesYesRDBMS High Availability
Fast recoveryYesYesRDBMS High Availability
Mirrored backupsYesYesRDBMS High Availability
Hot add memory and CPUYesYesRDBMS High Availability
Database recovery advisorYesYesYesYesYesYesYesRDBMS High Availability
Encrypted backupYesYesYesRDBMS High Availability
Smart backupYesYesYesRDBMS High Availability
Operational analyticsYesYesRDBMS Scalability and Performance
In-Memory Column StoreYesYesRDBMS Scalability and Performance
Stretch DatabaseYesYesYesYesYesYesYesRDBMS Scalability and Performance
Persistent Main MemoryYesYesYesYesYesYesYesRDBMS Scalability and Performance
Multi-instance support50505050505050RDBMS Scalability and Performance
Table and index partitioningYesYesRDBMS Scalability and Performance
Data compressionYesYesRDBMS Scalability and Performance
Resource GovernorYesYesRDBMS Scalability and Performance
Partition Table ParallelismYesYesRDBMS Scalability and Performance
Multiple Filestream containersYesYesRDBMS Scalability and Performance
NUMA Aware and Large Page Memory and Buffer Array AllocationYesYesRDBMS Scalability and Performance
Buffer Pool ExtensionYesYesYesRDBMS Scalability and Performance
IO Resource GovernanceYesYesRDBMS Scalability and Performance
In Memory OLTPYesYesRDBMS Scalability and Performance
Delayed DurabilityYesYesYesYesYesYesYesRDBMS Scalability and Performance
Row-level securityYesYesNoYesRDBMS Security
Always EncryptedYesYesRDBMS Security
Dynamic data maskingYesYesYesRDBMS Security
Basic auditingYesYesYesYesYesYesYesRDBMS Security
Fine grained auditingYesYesRDBMS Security
Transparent database encryptionYesYesRDBMS Security
Extensible key managementYesYesRDBMS Security
User-defined rolesYesYesYesYesYesYesYesRDBMS Security
Contained databasesYesYesYesYesYesYesYesRDBMS Security
Encryption for backupsYesYesYesRDBMS Security
Heterogeneous subscribersYesYesYesReplication
Merge replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)YesReplication
Oracle publishingYesYesReplication
Peer to peer transactional replicationYesYesReplication
Snapshot replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)YesReplication
SQL Server change trackingYesYesYesYesYesYesYesReplication
Transactional replicationYesYesYes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)Yes (Subscriber only)YesReplication
Transactional replication to AzureYesYesYesYesReplication
Transactional replication updateable subscriptionYesYesReplication
SQL Management Objects (SMO)YesYesYesYesYesYesYesManagement Tools
SQL Configuration ManagerYesYesYesYesYesYesYesManagement Tools
SQL CMD (Command Prompt tool)YesYesYesYesYesYesYesManagement Tools
SQL Server Management StudioYesYesYesYesYesYesManagement Tools
Distributed Replay - Admin ToolYesYesYesYesYesYesManagement Tools
Distribute Replay - ClientYesYesYesYesManagement Tools
Distributed Replay - ControllerYes (Up to 16 clients)Yes (1 client)Yes (1 client)Yes (1 client)Management Tools
SQL ProfilerYesYesNo 1No 1No 1No 1YesManagement Tools
SQL Server AgentYesYesYesYesManagement Tools
Microsoft System Center Operations Manager Management PackYesYesYesYesManagement Tools
Database Tuning Advisor (DTA)YesYes 2Yes 2YesManagement Tools
Deploy a SQL Server Database to a Windows Azure VM WizardYesYesYesYesYesYesYesManagement Tools
SQL Server Data Files in Windows AzureYesYesYesYesYesYesYesManagement Tools
User instancesYesYesYesYesManageability
LocalDBYesYesYesManageability
Dedicated admin connectionYesYesYesYes with trace flagYes with trace flagYes with trace flagYesManageability
PowerShell scripting supportYesYesYesYesYesYesYesManageability
SysPrep support 1YesYesYesYesYesYesYesManageability
Support for data-tier application component operations - extract, deploy, upgrade, deleteYesYesYesYesYesYesYesManageability
Policy automation (check on schedule and change)YesYesYesYesManageability
Performance data collectorYesYesYesYesManageability
Able to enroll as a managed instance in multi-instance managementYesYesYesYesManageability
Standard performance reportsYesYesYesYesManageability
Plan guides and plan freezing for plan guidesYesYesYesYesManageability
Direct query of indexed views (using NOEXPAND hint)YesYesYesYesManageability
Automatic indexed views maintenanceYesYesYesYesManageability
Distributed partitioned viewsYesPartial. Distributed partitioned views are not updatablePartial. Distributed partitioned views are not updatablePartial. Distributed partitioned views are not updatablePartial. Distributed partitioned views are not updatablePartial. Distributed partitioned views are not updatableYesManageability
Parallel indexed operationsYesYesManageability
Automatic use of indexed view by query optimizerYesYesManageability
Parallel consistency checkYesManageability
SQL Server Utility Control PointYesYesManageability
Contained databasesYesYesYesYesYesYesYesManageability
Buffer pool extensionYesYesYesManageability
Microsoft Visual Studio integrationYesYesYesYesYesYesYesDevelopment Tools
Intellisense (Transact-SQL and MDX)YesYesYesYesYesYesYesDevelopment Tools
SQL Server Data Tools (SSDT)YesYesYesYesYesDevelopment Tools
SQL query edit and design toolsYesYesYesDevelopment Tools
Version control supportYesYesYesDevelopment Tools
MDX edit, debug, and design toolsYesYesYesDevelopment Tools
Basic R integrationYesYesYesYesYesProgrammability
Advanced R integrationYesYesProgrammability
R Server (Standalone)YesYesProgrammability
Polybase compute nodeYesYesYesProgrammability
Polybase head nodeYesProgrammability
JSONYesYesYesYesYesYesYesProgrammability
Query StoreYesYesYesYesYesYesYesProgrammability
TemporalYesYesYesYesYesYesYesProgrammability
Common Language Runtime (CLR) IntegrationYesYesYesYesYesYesYesProgrammability
Native XML supportYesYesYesYesYesYesYesProgrammability
XML indexingYesYesYesYesYesYesYesProgrammability
MERGE & UPSERT capabilitiesYesYesYesYesYesYesYesProgrammability
FILESTREAM supportYesYesYesYesYesYesYesProgrammability
FileTableYesYesYesYesYesYesYesProgrammability
Date and Time datatypesYesYesYesYesYesYesYesProgrammability
Internationalization supportYesYesYesYesYesYesYesProgrammability
Full-text and semantic searchYesYesYesYesYesProgrammability
Specification of language in queryYesYesYesYesYesProgrammability
Service Broker (messaging)YesYesNo (Client only)No (Client only)No (Client only)No (Client only)YesProgrammability
Transact-SQL endpointsYesYesYesYesProgrammability
SQL Server Import and Export WizardYesYesYesYesYesYesYesIntegration Services
Built-in data source connectorsYesYesYesYesYesYesYesIntegration Services
Azure data source connectors and tasksYesYesYesYesYesYesYesIntegration Services
Hadoop / HDFS connectors and tasksYesYesYesYesIntegration Services
SSIS Designer and runtimeYesYesYesIntegration Services
Built in tasks and transformationsYesYesYesIntegration Services
Basic data profiling toolsYesYesYesIntegration Services
Change Data Capture Service for Oracle by AttunityYesYesIntegration Services
Change Data Capture Designer for Oracle by AttunityYesYesIntegration Services
High performance Oracle destinationYesYesIntegration Services - Advanced Adapters
High performance Teradata DestinationYesYesIntegration Services - Advanced Adapters
SAP BW source and destinationYesYesIntegration Services - Advanced Adapters
Data mining model training destination adapterYesYesIntegration Services - Advanced Adapters
Dimension processing destination adapterYesYesIntegration Services - Advanced Adapters
Partition processing destination adapterYesYesIntegration Services - Advanced Adapters
Change Data Capture components by AttunityYesYesIntegration Services - Advanced Adapters
Connector for Open Database Connectivity (ODBC) by AttunityYesYesIntegration Services - Advanced Adapters
Persistent (high performance) lookupsYesYesIntegration Services - Advanced Transforms
Data mining query transformationYesYesIntegration Services - Advanced Transforms
Fuzzy grouping and lookup transformationsYesYesIntegration Services - Advanced Transforms
Term extractions and lookup transformationsYesYesIntegration Services - Advanced Transforms
Master Data Services databaseYesYesMaster Data Services
Master Data Manager web applicationYesYesMaster Data Services
Create cubes without a databaseYesYesYesData Warehouse
Auto-generate staging and data warehouse schemaYesYesYesData Warehouse
Change data captureYesYesData Warehouse
Star join query optimizationsYesYesData Warehouse
Scalable read-only Analysis Services configurationYesYesData Warehouse
Parallel query processing on partitioned tables and indexesYesYesData Warehouse
xVelocity memory optimized columnstore indexesYesYesData Warehouse
Global batch aggregationYesYesData Warehouse
Scalable shared databasesYesYesAnalysis Services
Backup/Restore & Attach/Detach databasesYesYesYesAnalysis Services
Synchronize databasesYesYesAnalysis Services
Always On failover cluster instancesYesYesYesAnalysis Services
Analysis Services
Number of nodes is the operating system maximumSupport for 2 nodesNumber of nodes is the operating system maximumAnalysis Services
Programmability (AMO, ADOMD.Net, OLEDB, XML/A, ASSL, TMSL)YesYesYesAnalysis Services
Semi-additive measuresYesNo 1YesBI Multi-Dim
HierarchiesYesYesYesBI Multi-Dim
KPIsYesYesYesBI Multi-Dim
PerspectivesYesYesBI Multi-Dim
ActionsYesYesYesBI Multi-Dim
Account intelligenceYesYesYesBI Multi-Dim
Time intelligenceYesYesYesBI Multi-Dim
Custom rollupsYesYesYesBI Multi-Dim
Writeback cubeYesYesYesBI Multi-Dim
Writeback dimensionsYesYesBI Multi-Dim
Writeback cellsYesYesYesBI Multi-Dim
DrillthroughYesYesYesBI Multi-Dim
Advanced hierarchy types (parent-child and ragged hierarchies)YesYesYesBI Multi-Dim
Advanced dimensions (reference dimensions, many-to-many dimensions)YesYesYesBI Multi-Dim
Linked measures and dimensionsYesYesBI Multi-Dim
TranslationsYesYesYesBI Multi-Dim
AggregationsYesYesYesBI Multi-Dim
Multiple partitionsYesYes, up to 3YesBI Multi-Dim
Proactive cachingYesYesBI Multi-Dim
Custom assemblies (stored procedures)YesYesYesBI Multi-Dim
MDX queries and scriptsYesYesYesBI Multi-Dim
DAX queriesYesYesYesBI Multi-Dim
Role-based security modelYesYesYesBI Multi-Dim
Dimension and cell-level securityYesYesYesBI Multi-Dim
Scalable string storageYesYesYesBI Multi-Dim
MOLAP, ROLAP, and HOLAP storage modelsYesYesYesBI Multi-Dim
Binary and compressed XML transportYesYesYesBI Multi-Dim
Push-mode processingYesYesBI Multi-Dim
Direct writebackYesYesBI Multi-Dim
Measure expressionsYesYesBI Multi-Dim
HierarchiesYesYesYesBI Tabular
KPIsYesYesYesBI Tabular
PerspectivesYesYesBI Tabular
TranslationsYesYesYesBI Tabular
DAX calculations, DAX queries, MDX queriesYesYesYesBI Tabular
Row-level securityYesYesYesBI Tabular
Multiple partitionsYesYesBI Tabular
In-memory storage modeYesYesYesBI Tabular
DirectQuery storage modeYesYesBI Tabular
SharePoint farm integration based on shared service architectureYesYesPower Pivot for SharePoint
Usage reportingYesYesPower Pivot for SharePoint
Health monitoring rulesYesYesPower Pivot for SharePoint
Power Pivot galleryYesYesPower Pivot for SharePoint
Power Pivot data refreshYesYesPower Pivot for SharePoint
Power Pivot data feedsYesYesPower Pivot for SharePoint
Standard algorithmsYesYesYesData Mining
Data mining tools (Wizards, Editors, Query Builders)YesYesYesData Mining
Cross validationYesYesData Mining
Models on filtered subsets of mining structure dataYesYesData Mining
Time series: Custom blending between ARTXP and ARIMA methodsYesYesData Mining
Time series: Prediction with new dataYesYesData Mining
Unlimited concurrent DM queriesYesYesData Mining
Advanced configuration & tuning options for data mining algorithmsYesYesData Mining
Support for plug-in algorithmsYesYesData Mining
Parallel model processingYesYesData Mining
Time series: cross-series predictionYesYesData Mining
Unlimited attributes for association rulesYesYesData Mining
Sequence predictionYesYesData Mining
Multiple prediction targets for naïve Bayes, neural network and logistic regressionYesYesData Mining
Mobile reports and KPIsYesYesReporting Services
Supported catalog DB SQL Server editionStandard or higherStandard or higherWebExpressStandard or higherReporting Services
Supported data source SQL Server editionAll SQL Server editionsAll SQL Server editionsWebExpressAll SQL Server editionsReporting Services
Report serverYesYesYesYesYesReporting Services
Report designerYesYesYesYesYesReporting Services
Report designer web portalYesYesYesYesYesReporting Services
Role based securityYesYesYesYesYesReporting Services
Export to Excel, PowerPoint, Word, PDF, and imagesYesYesYesYesYesReporting Services
Enhanced gauges and chartingYesYesYesYesYesReporting Services
Pin report items to Power BIdashboardsYesYesYesYesYesReporting Services
Custom authenticationYesYesYesYesYesReporting Services
Report as data feedsYesYesYesYesYesReporting Services
Model supportYesYesYesYesReporting Services
Create custom roles for role-based securityYesYesYesReporting Services
Model Item securityYesYesYesReporting Services
Infinite click throughYesYesYesReporting Services
Shared component libraryYesYesYesReporting Services
Email and file share subscriptions and schedulingYesYesYesReporting Services
Report history, execution snapshots and cachingYesYesYesReporting Services
SharePoint IntegrationYesYesYesReporting Services
Remote and non-SQL data source support1YesYesYesReporting Services
Data source, delivery and rendering, RDCE extensibilityYesYesYesReporting Services
Custom brandingYesYesReporting Services
Data driven report subscriptionYesYesReporting Services
Scale out deployment (Web farms)YesYesReporting Services
Alerting2YesYesReporting Services
Power View 2YesYesReporting Services
Report Builder (.rdlx)YesYesYesReporting Services
Data Mining Add-ins for Excel and Visio 2010 (.xlsx, .vsdx)YesYesYesReporting Services
Power Pivot for Excel 2010 and 2013 (.xlsx)YesYesReporting Services
Master Data Services Add-in for Excel (.xlsx)YesYesReporting Services
SQL Server Mobile Report Publisher (.rsmobile)YesYesReporting Services
Power BI apps for mobile devices (iOS, Windows 10, Android) (.rsmobile)YesYesReporting Services

Upgrade Considerations for SQL Server Analysis Services

In-place upgrade will replace your older folder path with new binaries of SQL Server 2016. No manual backup and restoring of data is necessary.

Side-by-side migration will allow you to migrate your SQL Server to a different environment. Advantage of Side-by-Side migration over in-place method is that you can fall back to your previous environment easily, whereas in case of in-place it is much difficult. You can simply backup and restore your database on-to the new environment and test it before moving into production.

This process is applicable to move Tabular and Multi-dimensional data models. You can run post installation steps and run DBCC commands to check the database consistency and orphaned objects.

Tabular Models will get the maximum performance benefits when moved from 2012 to 2016 models.

In-place upgrade for Tabular Models running in direct-query models is not supported.

More upgrade details are available on the MSDN website.

Affiliate Disclosure: Faceofit.com is a participant in the Amazon Services LLC Associates Program. As an Amazon Associate we earn from qualifying purchases.

What's your reaction?

Excited
0
Happy
0
In Love
0
Not Sure
0
Silly
0
Previous
Next

You may also like

Comments are closed.

More in:Azure