Tuesday, October 4, 2016

SQL Server 2016 Version Differences

Updated Nov 16, 2016:

With the release of SP 1 for 2016, all things have changed, and in a good way!


  1. Many features only on Enterprise are now on all, or almost all editions
  2. they did a better job formatting the page, so go to:

    https://msdn.microsoft.com/en-us/library/cc645993.aspx

    to get all the updated features in Ent, Std, Web and Express.





Technet posted a detailed comparison of the different SQL Server 2016 versions (see below), dated May 26, 2016.  Alas, the web page was not well formatted. So, below is the same data (again as of May 26, 2016) but with improved formatting.

Source:  https://technet.microsoft.com/en-us/windows/cc645993(v=sql.90)
Naturally, reference Microsoft for any changes, adjustments or inaccuracies.


Cross-Box Scale Limits
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Maximum compute capacity used by a single instance - SQL Server Database Engine1
Operating system maximum
Limited to lesser of 4 sockets or 24 cores
Limited to lesser of 4 sockets or 16 cores
Limited to lesser of 1 socket or 4 cores
Limited to lesser of 1 socket or 4 cores
Limited to lesser of 1 socket or 4 cores
Maximum compute capacity used by a single instance - Analysis Services or Reporting Services
Operating system maximum
Limited to lesser of 4 sockets or 24 cores
Limited to lesser of 4 sockets or 16 cores
Limited to lesser of 1 socket or 4 cores
Limited to lesser of 1 socket or 4 cores
Limited to lesser of 1 socket or 4 cores
Maximum memory utilized per instance of SQL Server Database Engine
Operating System Maximum
128 GB
64 GB
1 GB
1 GB
1 GB
Maximum memory utilized per instance of Analysis Services
Operating System Maximum
Tabular: 16 GB
N/A
N/A
N/A
N/A

MOLAP: 64 GB
Maximum memory utilized per instance of Reporting Services
Operating System Maximum
64 GB
64 GB
4 GB
N/A
N/A
Maximum relational database size
524 PB
524 PB
524 PB
10 GB
10 GB
10 GB
1. Enterprise Edition with Server + Client Access License (CAL) based licensing
 (not available for new agreements) is limited to a maximum of 20 cores per SQL Server instance.
There are no limits under the Core-based Server Licensing model. For more information,
see Compute Capacity Limits by Edition of SQL Server.
RDBMS High Availability
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Server core support 1
Yes
Yes
Yes
Yes
Yes
Yes
Log shipping
Yes
Yes
Yes
No
No
No
Database mirroring
Yes
Yes
Witness only
Witness only
Witness only
Witness only

Full safety only
Backup compression
Yes
Yes




Database snapshot
Yes





Always On failover cluster instances
Yes
Yes






Number of nodes is the operating system maximum
Support for 2 nodes
Always On availability groups
Yes






Up to 8 secondary replicas, including 2 synchronous secondary replicas
Basic availability groups

Yes





Support for 2 nodes
Connection director
Yes





Online page and file restore
Yes





Online indexing
Yes





Online schema change
Yes





Fast recovery
Yes





Mirrored backups
Yes





Hot add memory and CPU
Yes





Database recovery advisor
Yes
Yes
Yes
Yes
Yes
Yes
Encrypted backup
Yes
Yes




Smart backup
Yes
Yes




1 For more information on installing SQL Server 2016 on Server Core, see Install SQL Server 2016 on Server Core.
RDBMS Scalability and Performance
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Operational analytics
Yes





In-Memory Column Store
Yes





Stretch Database
Yes
Yes
Yes
Yes
Yes
Yes
Persistent Main Memory
Yes
Yes
Yes
Yes
Yes
Yes
Multi-instance support
50
50
50
50
50
50
Table and index partitioning
Yes





Data compression
Yes





Resource Governor
Yes





Partition Table Parallelism
Yes





Multiple Filestream containers
Yes





NUMA Aware and Large Page Memory and Buffer Array Allocation
Yes





Buffer Pool Extension
Yes
Yes




IO Resource Governance
Yes





In Memory OLTP
Yes





Delayed Durability
Yes
Yes
Yes
Yes
Yes
Yes
RDBMS Security
Feature
Enterprise
Standard
Web
Express
Express with Advanced Services
Express with Tools
Row-level security
Yes
Yes
No



Always Encrypted
Yes





Dynamic data masking
Yes
Yes




Basic auditing
Yes
Yes
Yes
Yes
Yes
Yes
Fine grained auditing
Yes





Transparent database encryption
Yes





Extensible key management
Yes





User-defined roles
Yes
Yes
Yes
Yes
Yes
Yes
Contained databases
Yes
Yes
Yes
Yes
Yes
Yes
Encryption for backups
Yes
Yes




Replication
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Heterogeneous subscribers
Yes
Yes




Merge replication
Yes
Yes
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
Oracle publishing
Yes





Peer to peer transactional replication
Yes





Snapshot replication
Yes
Yes
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
SQL Server change tracking
Yes
Yes
Yes
Yes
Yes
Yes
Transactional replication
Yes
Yes
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
Yes (Subscriber only)
Transactional replication to Azure
Yes
Yes
Yes



Transactional replication updateable subscription
Yes





Management Tools
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
SQL Management Objects (SMO)
Yes
Yes
Yes
Yes
Yes
Yes
SQL Configuration Manager
Yes
Yes
Yes
Yes
Yes
Yes
SQL CMD (Command Prompt tool)
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Management Studio
Yes
Yes
Yes
Yes
Yes

Distributed Replay - Admin Tool
Yes
Yes
Yes
Yes
Yes

Distribute Replay - Client
Yes
Yes
Yes



Distributed Replay - Controller
Yes (Up to 16 clients)
Yes (1 client)
Yes (1 client)



SQL Profiler
Yes
Yes
No 1
No 1
No 1
No 1
SQL Server Agent
Yes
Yes
Yes



Microsoft System Center Operations Manager Management Pack
Yes
Yes
Yes



Database Tuning Advisor (DTA)
Yes
Yes 2
Yes 2



Deploy a SQL Server Database to a Windows Azure VM Wizard
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Data Files in Windows Azure
Yes
Yes
Yes
Yes
Yes
Yes







1 SQL Server Web, SQL Server Express, SQL Server Express with Tools, and SQL Server Express with Advanced Services can be profiled using SQL Server Standard and SQL Server Enterprise editions.













2 Tuning enabled only on Standard edition features






RDBMS Manageability
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
User instances



Yes
Yes
Yes
LocalDB



Yes
Yes

Dedicated admin connection
Yes
Yes
Yes
Yes with trace flag
Yes with trace flag
Yes with trace flag
PowerShell scripting support
Yes
Yes
Yes
Yes
Yes
Yes
SysPrep support 1
Yes
Yes
Yes
Yes
Yes
Yes
Support for data-tier application component operations - extract, deploy, upgrade, delete
Yes
Yes
Yes
Yes
Yes
Yes
Policy automation (check on schedule and change)
Yes
Yes
Yes



Performance data collector
Yes
Yes
Yes



Able to enroll as a managed instance in multi-instance management
Yes
Yes
Yes



Standard performance reports
Yes
Yes
Yes



Plan guides and plan freezing for plan guides
Yes
Yes
Yes



Direct query of indexed views (using NOEXPAND hint)
Yes
Yes
Yes



Automatic indexed views maintenance
Yes
Yes
Yes



Distributed partitioned views
Yes
Partial. Distributed partitioned views are not updatable
Partial. Distributed partitioned views are not updatable
Partial. Distributed partitioned views are not updatable
Partial. Distributed partitioned views are not updatable
Partial. Distributed partitioned views are not updatable
Parallel indexed operations
Yes





Automatic use of indexed view by query optimizer
Yes





Parallel consistency check
Yes





SQL Server Utility Control Point
Yes





Contained databases
Yes
Yes
Yes
Yes
Yes
Yes
Buffer pool extension
Yes
Yes




1 For more information, see Considerations for Installing SQL Server Using SysPrep.
Development Tools
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Microsoft Visual Studio integration
Yes
Yes
Yes
Yes
Yes
Yes
Intellisense (Transact-SQL and MDX)
Yes
Yes
Yes
Yes
Yes
Yes
SQL Server Data Tools (SSDT)
Yes
Yes
Yes
Yes


SQL query edit and design tools
Yes
Yes




Version control support
Yes
Yes




MDX edit, debug, and design tools
Yes
Yes




Programmability
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Basic R integration
Yes
Yes
Yes
Yes


Advanced R integration
Yes





R Server (Standalone)
Yes





Polybase compute node
Yes
Yes




Polybase head node
Yes





JSON
Yes
Yes
Yes
Yes
Yes
Yes
Query Store
Yes
Yes
Yes
Yes
Yes
Yes
Temporal
Yes
Yes
Yes
Yes
Yes
Yes
Common Language Runtime (CLR) Integration
Yes
Yes
Yes
Yes
Yes
Yes
Native XML support
Yes
Yes
Yes
Yes
Yes
Yes
XML indexing
Yes
Yes
Yes
Yes
Yes
Yes
MERGE & UPSERT capabilities
Yes
Yes
Yes
Yes
Yes
Yes
FILESTREAM support
Yes
Yes
Yes
Yes
Yes
Yes
FileTable
Yes
Yes
Yes
Yes
Yes
Yes
Date and Time datatypes
Yes
Yes
Yes
Yes
Yes
Yes
Internationalization support
Yes
Yes
Yes
Yes
Yes
Yes
Full-text and semantic search
Yes
Yes
Yes
Yes


Specification of language in query
Yes
Yes
Yes
Yes


Service Broker (messaging)
Yes
Yes
No (Client only)
No (Client only)
No (Client only)
No (Client only)
Transact-SQL endpoints
Yes
Yes
Yes



Data Warehouse
Feature
Enterprise
Standard
Web
Express with Advanced Services
Express with Tools
Express
Create cubes without a database
Yes
Yes




Auto-generate staging and data warehouse schema
Yes
Yes




Change data capture
Yes





Star join query optimizations
Yes





Scalable read-only Analysis Services configuration
Yes





Parallel query processing on partitioned tables and indexes
Yes





xVelocity memory optimized columnstore indexes
Yes





Global batch aggregation
Yes







No comments: