SQL Server 2016 SP 1 Standard Editions All

SQL Server 2016 SP 1 Standard Editions All the Features!!!

Joey D’Antoni • Joey has over 20 years of experience with a wide variety of data platforms, in both Fortune 50 companies as well as smaller organizations • He is a frequent speaker on database administration, big data, and career management • MVP, MCSE BI and Data Platform • VMWare v. Expert • He is the co-president of the Philadelphia SQL Server User’s Group • He wants you to make sure you can restore your data

Licensing changes in 2016 More built-in to what you are already buying SQL Server 2014 Enterprise Mission critical apps, large-scale data warehousing Business Intelligence SQL Server 2016 SP 1 Enterprise Highly available mission-critical apps, enterprise-scale data warehousing, end-to-end mobile BI and advanced analytics Premium corporate and self-service BI Standard Fully-featured database for modern, mid-tier applications and data marts Basic database, reporting and analytics Express Easy to get started—free to use Developer Fully-featured SQL Server Express Lightweight applications—free to use Developer For non production workloads. Now free to use —get it at Dev Essentials

SQL Server 2016 Service Pack 1 Making innovation more accessible to all applications Delivers common programming surface across editions—no application re-write Advanced featured across editions 1 Faster transactions, faster queries with In-memory* Real-time operational analytics without impacting performance Only data solution to encrypt your data at rest and in motion Connect your relational data to big data with Poly. Base T-SQL PHP Java Node. js C/C++ Python C#/VB. NET Ruby Unparalleled choice for developer tools and languages

Solution - Right SQL Features for the workload • Common Programmability Surface Area (CPSA) across SQL Editions • Optimal Design: Enables application to use the right features for the workload • Freedom to deploy the same application to any SQL Server Edition • Choice of Edition – Based on Performance, Scalability and Availability requirements Feature Row-level security Dynamic Data Masking Change data capture* Database snapshot Columnstore Partitioning Compression In Memory OLTP Always Encrypted Poly. Base Fine grained auditing Multiple filestream containers Standard Yes No No No RTM Web Express No No No No No No Local DB No No No Standard Yes Yes Yes Web Yes Yes Yes SP 1 Express Yes No* Yes Yes Yes * Requires SQL Server Agent which is not part of SQL Server Express Editions. ** Requires creating filestream file groups which is not possible in Local DB due to insufficient permissions. Local DB Yes No* Yes Yes No** Yes No Yes

In-memory OLTP • Limits (see Features Supported by the Editions of SQL Server 2016) • Lower Editions: Memory limited to ¼ of the edition buffer pool memory limit • Per database SQL Server 2016 SP 1 Edition Max Buffer pool size per instance In-Memory OLTP quota (per DB) Express Web Standard 1410 MB 64 GB 128 GB 352 MB 16 GB 32 GB Developer Unlimited (OS Max of 24 TB) Enterprise Unlimited (OS Max of 24 TB) Unlimited (OS Max of 24 TB) • In-memory OLTP blog http: //blogs. msdn. microsoft. com/sqlserverstorageengine/2016/11/17/in-memoryoltp-in-standard-and-express-editions-with-sql-server-2016 -sp 1

Columnstore • Limits (see Features Supported by the Editions of SQL Server 2016) • Lower Editions: Memory limited to ¼ of the edition buffer pool memory limit • Per instance SQL Server 2016 SP 1 Edition Columnstore memory limit (per instance) MAXDOP Aggregate pushdown Filter pushdown SIMD support Express Web Standard 256 MB 16 GB 32 GB 1 1 2 N N N N N Developer Unlimited (OS Max of 24 TB) Unlimited Y Y Y Enterprise Unlimited (OS Max of 24 TB) Unlimited Y Y Y • Columnstore blog https: //blogs. msdn. microsoft. com/sql_server_team/columnstore-index-standardand-express-editions-with-sql-server-2016 -sp 1

SQL Server 2016 SP 1 Editions Standard Express SMALL-SCALE APPLICATIONS ü Development and management tools ü Easy backup and restore to Microsoft Azure FULLY FEATURED DATABASE FOR MID-TIER APPLICATIONS AND DATA MARTS Enterprise MISSION CRITICAL IN-MEMORY PERFORMANCE AND SCALE, SECURITY AND HIGH AVAILABILITY ü Mission critical high availability ü Most secure database with Always Encrypted ü Enhanced in-memory performance for all workloads ü PB scale data warehousing ü Basic reporting ü Unparalleled data security ü End-to-end mobile BI with rich visualizations on all major platforms ü Basic analytics ü In-database advanced analytics built-in at scale with R plus in-memory; 1 M predictions/second ü Hybrid scenarios: Stretch Database, backup ü Enhanced hybrid scenarios including Stretch Database, HA, DR and backup Common programming surface area - develop once and scale across editions

Dramatically simplify HA & DR The 9 s you need for mission critical workloads Enterprise Edition Availability with Enhanced Always. On. NEW Cost effective to run backups and scale BI reporting MISSION CRITICAL HA + hybrid DR e rid Hyb zur th A i w DR Enterprise Azure VMs Asynchronous replicas High availability Synchronous replicas Primary • Multiple node failover clustering (3 synchronous, up to 8 replicas) • Multiple database failover as a group • Fully readable secondaries for backup or reporting operations • No domain join required with WS 2016 Standard Edition High Availability NEW BASIC HA • Two node failover (1 active, 1 passive) • Single database failover to non-readable secondary • No domain join required with WS 2016 High availability Primary Non-readable synchronous replica Enterprise includes all Standard features.

Data warehousing and data marts Query across relational and non-relational data Enterprise Poly. Base Quote: ************ T-SQL query *********************** SQL Server Name DOB State Denny Usher 11/13/58 WA Gina Burch WA 04/29/76 Hadoop $658. 39 • Petabyte Enterprise Data Warehouse scale • Poly. Base in scale-out configuration—head and compute nodes • Scale out, read-only Analysis Services configuration • Global batch aggregation • Star query join optimization • Advanced data mining Standard • Data Marts and OLAP Cubes • Poly. Base—compute node only • In-memory columnstore • Partitioning • Compression • Change data capture Enterprise includes all Standard features.

Unparalleled security for all your apps Protect data at rest and in motion Enterprise Always Encrypted • Transparent data encryption (TDE) Server side Client side Query Standard Enhanced ADO. NET Library Result Set • • • Always Encrypted Row-level security Data masking Fine-grained auditing Separation of duties Enterprise includes all Standard features.

Pricing & licensing summary Licensing Pricing and licensing Pricing SQL Server 2014 SP 2 Migration Net Change • Standard Per-Core and Server + CAL • Enterprise Per-Core • None (Price per core/server/CAL and pricing models are unchanged) • Primary License with active SA grants secondary running “Warm” instance • License Mobility for SA overrides 90 day reassignment rule. • None • SQL PDW licensed through PDW software SKU • SQL PDW software SKU discontinued • PDW licensed through SQL EE Per Core • SQL BI SQL Server 2016 SP 1 Introduction of Failover Servers benefit for cloud deployments using license mobility • SQL BI Server offered for Server + CAL deployments • SQL BI Server is no longer offered • Introduction of Failover Servers benefit for cloud deployments (introduced in Dec. 2015). • Transition paths in place for customers who purchased BI edition with SA

So What’s Not in Standard Edition? 13

Scalability and Performance in EE Database engine • Advanced R integration (parallel and streaming, non-memory bound, processing) • Standalone R Server • Polybase head node • Master Data Services • Data Quality Services • Resource Governor • IO Resource Governor • Partition Table Parallelism • NUMA • Star join query optimizations • Parallel query processing on partitioned tables and indexes • Global batch aggregation • Distributed partitioned views • Online index operations • Auto use of indexed views by query optimizer • Parallel consistency check • Utility Control Point Analysis Services • Scalable shared databases • Data Mining advanced algorithms • Perspectives (MOLAP and Tabular) • Multiple partitions (Tabular) • Writeback dimensions (MOLAP) • Proactive caching (MOLAP) • Direct writeback (MOLAP) • Measure expressions (MOLAP) • Push-mode processing (MOLAP) • Direct. Query storage mode (Tabular) • Power Pivot for Share. Point integration Reporting Services • Custom branding • Data driven report subscription • Scale our deployment (Web farms) • Alerting • Power View • Mobile BI (Datazen)

Availability, Manageability and Security in EE • • Always On availability groups up to 8 secondary replicas including 2 synchronous secondary replicas (Standard Edition is limited to 1 non-readable secondary) Connection director • Online page and file restore • Online index operations • • • Online schema change Fast recovery Mirrored backups Hot add memory and CPU Stream. Insight high availability • Transparent Database Encryption • • • Extensible key management Oracle publishing Peer to peer transactional replication • Change Data Capture for Oracle • • Advanced Adapters for Integration Services (e. g. Oracle, Teradata, SAP BW, Analysis Services) Advanced Transforms for Integration Services

Missing Diagnostics In query plans • Showplan extended in SQL Server 2016 to support: • Expose max memory enabled for the query • Memory fractions for optimized nested loop join • Information about enabled trace flags • Memory grant warning

Missing Diagnostics In query plans • Showplan extended in SQL Server 2016 SP 1 to support: • Expose max memory enabled for the query • Memory fractions for optimized nested loop join • Information about enabled trace flags • Memory grant warning • Information about parameters data type • CPU and execution elapsed time for entire query (root node) • Top waits (using sys. dm_exec_session_wait_stats)

Missing perf insights on query plan nodes • Per operator performance statistics for node and threads • Showplan extended to include Run. Time. Counters. Per. Thread • Node costs for parent and children: • Cumulative values for Row mode operators • Singleton values for Batch mode operators Runtime Info Up to SQL 2016 / SQL 2014 SP 2 SQL 2016 SP 1 X X X Actual. Rows. Read X X Batches X X Actual. Rows Actual. End. Of. Scans X X X Actual. Execution. Mode X X Actual. Elapsedms X X Actual. CPUms X X Actual. Scans X X Actual. Logical. Reads X X Actual. Physical. Reads X X Actual. Read. Aheads X X Actual. Lob. Logical. Reads X X Actual. Lob. Physical. Reads X X Actual. Lob. Read. Aheads X X Input. Memory. Grant X Output. Memory. Grant X Used. Memory. Grant X

Missing perf insights on query plan nodes • Per operator performance statistics for node and threads • Showplan extended to include Run. Time. Counters. Per. Thread • Node costs for parent and children: • Cumulative values for Row mode operators • Singleton values for Batch mode operators <Run. Time. Information> <Run. Time. Counters. Per. Thread="0" Actual. Rows="8001" Actual. Rows. Read="10000000" Batches="0" Actual. End. Of. Scans="1" Actual. Execution. Mode="Row" Actual. Elapsedms="965" Actual. CPUms="965" Actual. Scans="1" Actual. Logical. Reads="26073" Actual. Physical. Reads="0" Actual. Read. Aheads="0" Actual. Lob. Logical. Reads="0" Actual. Lob. Physical. Reads="0" Actual. Lob. Read. Aheads="0" /> </Run. Time. Information>

Per-operator level performance stats • New x. Event query_thread_profile in SQL Server 2016 • Showplan time scale = milliseconds • x. Event time scale = microseconds for CPU and total time

Detecting predicate search inefficiencies? • Actual number of rows returned are rows after the predicate is applied. • Not the actual number of rows that are scanned from a table or index. • Scenario hidden from an actual execution plan: • SCAN or SEEK returns only 10 rows, why is it taking so long? • You see high CPU or many logical reads, but the query plan doesn't reflect that. • Now what? ?

Predicate Pushdown as seen in Showplan SELECT * FROM [Production]. [Transaction. History] WHERE [Product. ID] = 870 AND [Quantity] > 10

Influencing query execution without sysadmin SELECT Address. ID FROM Person. [Address] WHERE City = N'Ballard' AND [Postal. Code] = '98107' OPTION (QUERYTRACEON 9481) SELECT Address. ID FROM Person. [Address] WHERE City = N'Ballard' AND [Postal. Code] = '98107' OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) • USE HINT option does not require sysadmin privileges. • 9 different hints are supported to enable functionality which was previously only available with trace flags. • Use sys. dm_exec_valid_use_hints DMV to see the list of all supported hints under the USE HINT notation.

New Memory Grant Showplan Warning • 3 conditions: • Excessive Grant: when max used memory is too small compared to the granted memory. This scenario can cause blocking and less efficient usage when large grants exist and a fraction of that memory was used. • Used More Than Granted: when the max used memory exceeds the granted memory. This scenario can cause OOM conditions on the server. • Grant Increase: when the dynamic grant starts to increase too much, based on the ratio between the max used memory and initial request memory. This scenario can cause server instability and unpredictable workload performance. • SQL Server 2014 SP 2 and SQL Server 2016 SP 1

Tracking query progress (estimated) • To have in-flight query execution statistics, the query execution statistics profile infrastructure must be enabled on demand. • Can be enabled for a target session: • Specifying Include Live Query Statistics in SSMS. • SET STATISTICS XML ON • SET STATISTICS PROFILE ON KB 3170113 • Or globally to view the LQS from other sessions (such as from Activity Monitor): • Enabling query_post_execution_showplan extended event. • Data is stored in DMV (sys. dm_exec_query_profiles) which is not highly scalable for widespread use.

What is the impact? Query Execution Statistics Profiling Infrastructure tests with TPC-C like workloads Infra Type Legacy Lightweight in SQL Server 2014 SP 2/2016 Lightweight in SQL Server 2016 SP 1 Overhead percent (up to) no active Active x. Events query_post_execution_showplan 75. 5 93. 17 3. 5 62. 02 2 14. 3

Database Cloning • Schema only copy of database for testing and/or troubleshooting purposes. • DBCC CLONEDATABASE • Fast, minimally invasive and consistent. • Introduced first in SQL Server 2014 SP 2. • Introduced & Enhanced in SQL Server 2016 SP 1 with support for: • CLR Objects Not supported • Filestream/File. Table Objects as production database. • In-Memory tables KB 3177838 • Query Store (Persisted Query Plan Store)

DBCC CLONEDATABASE examples • Default CLONE includes schema, statistics and query store data. -- Default settings generate cloned database with statistics and query store metadata. DBCC CLONEDATABASE (source_database_name, target_database_name) -- Schema and query store only clone DBCC CLONEDATABASE (source_database_name, target_database_name) WITH NO_STATISTICS Recommended for functional tests – no statistics means no data shown in histograms -- Schema and statistics only clone DBCC CLONEDATABASE (source_database_name, target_database_name) WITH NO_QUERYSTORE Recommended for perf testing with existing set of heavy hitter queries -- Schema only clone DBCC CLONEDATABASE (source_database_name, target_database_name) WITH NO_STATISTICS, NO_QUERYSTORE Recommended for dev/test environment – query store not available in SQL Server 2014 SP 2 anyway

CREATE OR ALTER • Increase Developer productivity. • CREATE OR ALTER support to make it easier to modify and deploy objects like Stored Procedures, Triggers, UDFs and Views. • CREATE OR ALTER PROCEDURE <…> • Why not Tables and Schemas? • Syntax for CREATE and ALTER related to TABLE or SCHEMA are two very different commands from a syntax and usability perspective. CREATE TABLE T 3 (C 1 int PRIMARY KEY, C 2 varchar(50) NULL, C 3 int NULL, C 4 int ) ; GO ALTER TABLE T 3 ALTER COLUMN C 2 varchar(50) COLLATE Latin 1_General_BIN; GO

Summary SQL Server 2016 SP 1 has a ton of new features Microsoft wants to offer common dev platforms Any O/S, any Edition, same code

Pricing & licensing with details for Standard & BI Licensing Pricing and Licensing Pricing SQL Server 2014 SP 1 Migration Net Change • Standard Per-Core and Server + CAL • Enterprise Per-Core • None (Price per core/server/CAL and pricing models are unchanged) • Primary License with active SA grants secondary running “Warm” instance • License Mobility for SA overrides 90 day reassignment rule • Standard edition licensed for 16 cores. • Primary License with active SA grants secondary running “Warm” instance • License Mobility for SA overrides 90 day reassignment rule. • Standard edition licensed for 24 cores. • Change in Standard edition which is licensed for up to 24 cores (from 16). • SQL PDW licensed through PDW software SKU • SQL PDW software SKU discontinued • PDW licensed through SQL EE Per Core • SQL BI SQL Server 2016 SP 1 Introduction of Failover Servers benefit for cloud deployments using license mobility • SQL BI Server offered for Server + CAL deployments • SQL BI Server is no longer offered • Introduction of Failover Servers benefit for cloud deployments (introduced in Dec. 2015). • Customers with active SA at SQL 2016 GA will be migrated to SQL EE Server + CAL at SA renewal • EA customers get a single true up, capped at 25% of BI server license count

SQL Server Reporting Services Reporting for all devices and formats Enterprise Tabular models • Create mobile reports using the SQL Server Mobile Report Publisher • Consume with Power BI mobile apps Standard NEW • • Export to Excel, PDF, Word, PDF and PPT Modern paginated reports Report Server Report Designer SQL Server Reporting Services Web Portal Role-based security Share. Point integration Enterprise includes all Standard features.

SQL Server 2016 SP 1 Enterprise • OS max cores and memory • Enhanced inmemory OLTP performance • Always Encrypted • Row-level security • Dynamic data masking • In database Advanced Analytics • Stretch Database • Direct query of indexed views • Enhanced backup to Azure • Multiple filestream containers • In-memory analytics • R integration with massive parallel processing for performance and scale • Enhanced inmemory Column. Store • End-to-end mobile BI on all major platforms • Enhanced direct query • Operational analytics • Enhanced separation of duties • Poly. Base in scaleout configuration (head and compute nodes) • Enhanced Always. On with no domain join (WS 2016) • Enhanced SQL Server auditing • Deployment rights for APS • Advanced data mining • Works with inmemory technology • Transparent data encryption • Distributed query processing • Advanced tabular • Run in database or standalone • Query Store • Temporal • Support for JSON • Web portal experience (all reports in 1 place) Enterprise • Modernized reports • Pin report to Power BI • Enhanced multidimensional models Enterprise includes all Standard features. • Connectivity to R Open • Enhanced HA and DR with Azure – ease of use, no domain join (WS 2016) • SSIS integration with Azure Data Factory and Azure SQL Data Warehouse • Service broker

SQL Server 2016 SP 1 Standard • In. Memory OLTP • Always Encrypted • Operational analytics • Row-level security • 24 cores max and 128 GB max memory • 2 -node single database failover (non-readable secondary) • Query Store • Basic tabular (16 GB memory per instance) • Support for JSON • Modernized reports • Basic auditing • Database Snapshot • Pin report to Power BI • Separation of duties • Partitioning • Enhanced multidimensional models • Dynamic data masking • Compression • In-memory Column. Store* • Change data capture Standard • Temporal • Poly. Base (compute node only) *In-memory columnstore and Operational Analytics limited to 32 GB of memory and 2 parallel cores • Single-threaded for RRE • Stretch Database • Direct query of indexed views • Connectivity to R Open • Enhanced backup to Azure • Multiple filestream containers • Service broker

What’s new in SQL Server 2016 since 2014 Real-time operational analytics with in-memory OLTP or on disk In-memory for more applications Unparalleled scalability with Windows Server 2016, with 12 TB memory and Windows Server 2016 max cores Multiple node failover clustering (3 synchronous, up to 8 replicas) SQL Server Development Tools in Visual Studio Query Store Temporal support Transparent Data Encryption Operational analytics Mobile BI R built-in to your T-SQL Stretch database Always Encrypted In-memory Column. Store Enhanced SSIS Partitioning for efficient data loading Deployment rights for APS Enterprise-grade Analysis Services RRE APIs with full parallelism and no memory limits for scale/performance Enhanced In-memory Column. Store for DW Advanced tabular models Poly. Base for simple T-SQL to query structured and unstructured data Enhanced database caching Up to 15, 000 partitions JSON support Enhanced DQS Enhanced MDS Enhanced Reporting Services Temporal tables In-memory analytics Advanced data mining Access to reports online or offline Create mobile reports using the SQL Server Mobile Report Publisher Consume with Power BI mobile apps Built-in In-memory Advanced Analytics Hybrid scenarios with SSIS Enhanced backup to Azure Advanced tabular model Easy migration to the cloud Direct query Simplified cloud DR with Always. On replicas Advanced data mining

What’s new in SQL Server 2016 since 2012 Real-time operational analytics with in-memory OLTP or on disk Transparent Data Encryption Operational analytics In-memory for more applications Always Encrypted In-memory Column. Store Enhanced separation of duties Deployment rights for APS CC certification at High Assurance Level for 2014 Enhanced In-memory Column. Store for DW Enhanced multidimensional models Backup encryption support Poly. Base for simple T-SQL to query structured and unstructured data Enhanced DQS Enhanced database caching Enhanced Reporting Services Unparalleled scalability with Windows Server 2016, with 12 TB memory and Windows Server 2016 max cores Enhanced Always. On, with 8 secondaries and Replica Wizard Multiple node failover clustering (3 synchronous, up to 8 replicas) In memory OLTP Buffer Pool Extension to SSDs Enhanced query processing Up to 15, 000 partitions Resource Governor adds IO governance Analytics Platform System Sys. Prep as cluster level Predictable performance with tiering of compute, network and story with Windows Server 2012 R 2 Delayed Durability Clustered Shared Volume support, VHDX support (Windows Server 2012 R 2) Manage on-premises and cloud apps (System Center 2012 R 2) Query optimization enhancements Query Store Temporal support Mobile BI Enhanced SSIS Enterprise-grade Analysis Services Advanced tabular models JSON support Enhanced MDS Temporal tables In-memory analytics Advanced data mining Create mobile reports using the SQL Server Mobile Report Publisher Consume with Power BI mobile apps on all major platforms Azure HDInsight Service Pin report items to Power BI Power Map for Excel Mobile BI interfaces for Power BI R built-in to your T-SQL Stretch database RRE APIs with full parallelism and no memory limits for scale/performance Partitioning for efficient data loading Built-in In-memory Advanced Analytics Hybrid scenarios with SSIS Enhanced backup to Azure Advanced tabular model Easy migration to the cloud Direct query Simplified cloud DR with Always. On replicas Advanced data mining Simplified backup to Azure Support for backup of previous versions of SQL Server to Azure Cloud back-up encryption support Simplified cloud Disaster Recovery with Always. On replicas in Azure VMs New Azure Deployment UI for SQL Server Larger SQL Server VMs and memory sizes available in Azure

What’s new in SQL Server 2016 since 2008 R 2 Real-time operational analytics with in-memory OLTP or on disk In-memory for more applications Unparalleled scalability with Windows Server 2016, with 12 TB memory and Windows Server 2016 max cores Enhanced Always. On, with 8 secondaries and Replica Wizard Multiple node failover clustering (3 synchronous, up to 8 replicas) In memory OLTP SQL Server Data Tools Transparent Data Encryption Mobile BI Enhanced productivity and performance Stretch database Local DB runtime (Express) Always Encrypted Enhanced SSIS Power View Enterprise-grade Analysis Services Configurable reporting alerts Partitioning for efficient data loading Advanced tabular models Reporting as Share. Point Shared Service In-memory analytics Build organization knowledge base Enhanced backup to Azure Enhanced multidimensional models Connect to 3 rd party data cleansing providers Easy migration to the cloud Data-tier application component project template Data-Tier Application Framework (DAC Fx) Interoperability support (ADO. NET, ODBC, JDBC, PDO, ADO APIs and. NET C/C++, Java, Linux and PHP platforms) Enhanced support for ANSI SQL standards Enhanced separation of duty CC certification at High Assurance Level for 2014 Backup encryption support JSON support Enhanced separation of duties Enhanced DQS Default schema for groups Enhanced MDS SQL Server Audit Modern Reporting Services Buffer Pool Extension to SSDs Transact-SQL Static Code Analysis tools Enhanced query processing Transact-SQL code snippets Resource Governor adds IO governance Intellisense Sys. Prep as cluster level File. Table build on FILESTREAM Predictable performance with tiering of compute, network and story with Windows Server 2012 R 2 Remote Blob Storage with Share. Point 2010 Azure HDInsight Service Statistical Semantic Search Power Map for Excel Delayed Durability Clustered Shared Volume support, VHDX support (Windows Server 2012 R 2) Manage on-premises and cloud apps (System Center 2012 R 2) Spatial features, include Full Globe and arcs Large user-defined data types Distributed Replay SQL Server fine-grained auditing Temporal tables Advanced data mining Create mobile reports using the SQL Server Mobile Report Publisher Consume with Power BI mobile apps Master Data Hub Master Data Services Add-in for Excel In-memory Column. Store Simplified backup to Azure Extensible object model Support for backup of previous versions of SQL Server to Azure SSIS as a Server Broader data integration with more sources: DB vendors, cloud, Hadoop Pipeline improvements Deployment rights for APS SQL Server Data Tools support for BI Import Power. Pivot models into Analysis Services DAC enhancements: Import/export with Azure SQL Database R built-in to your T-SQL Server Data Tools RRE APIs with full parallelism and no memory limits for scale/performance License Monthly (with SA) Query optimization enhancements Contained Database Authentication Recovery Advisor Windows Server Core System Center Management Pack for SQL Server 2012 Poly. Base for simple T-SQL to query structured and unstructured data Live Migration Windows Power. Shell 2. 0 support Enhanced database caching Advanced tabular model Online operations enhancements Multi-server Management with SQL Server Utility Control Point Up to 15, 000 partitions Direct query Analytics Platform System Advanced data mining Query Store Temporal support Data Tier Application Component Simplified cloud Disaster Recovery with Always. On replicas in Azure VMs Larger SQL Server VMs and memory sizes available in Azure Enhanced In-memory Column. Store for DW Change Data Capture for Oracle Cloud back-up encryption support New Azure Deployment UI for SQL Server Mash up data from different sources, such as Oracle & Hadoop HA for Stream. Insight, complex event processing Simplified cloud DR with Always. On replicas Graphical tools in SSIS Power BI Operational analytics Hybrid scenarios with SSIS Built-in In-memory Advanced Analytics SSDT in Visual Studio Resource Governor enhancements Snapshot backups to Azure via SQL Server Management Studio

SQL Server 2016 SP 1 editions – what’s new • Operating system max cores and memory • Transparent data encryption • Enhanced in-memory Column. Store • Enhanced Always. On with no domain join (WS 2016) • Enhanced SQL Server auditing • Poly. Base in scale-out configuration (head and compute nodes) • Unlimited virtualization with SA • Deployment rights for APS Express Standard Enterprise • Distributed query processing • In-memory OLTP • Operational analytics (HTAP) • 24 cores max and 128 GB max memory • 2 -node single database failover (non -readable secondary) • Temporal • Always Encrypted • 1 GB memory, max 10 GB memory • Policy-based management • Basic OLTP • Row-level security • Query Store • Dynamic data masking Mission critical performance Security • Fine-grained auditing • Partitioning and compression • Enhanced separation of duties Enterprise includes all Standard and Express features. • End-to-end mobile BI on all major platforms • Enhanced direct query • In-memory analytics • Advanced data mining • Advanced tabular • Web portal experience (all reports in one place) • In database advanced analytics • Enhanced backup to Azure • R integration with massive parallel processing for performance and scale • Enhanced HA and DR with Azure – ease of use, no domain join (Windows Server 2016) • Works with in-memory technology • SSIS integration with Azure Data Factory and Azure SQL Data Warehouse • Basic tabular (16 GB memory per instance) • Single-threaded for RRE • Pin reports to Power BI • Connectivity to R Open • Run in database or standalone • Direct query of indexed views • Multiple filestream containers • Enhanced multidimensional models • Support for JSON • Poly. Base • Service broker • Basic reporting and analytics • Stretch Database • Backup to Azure • Web portal experience • Modernized reports Data warehousing Business intelligence Advanced analytics Hybrid cloud Programmability