Overview.
You can find many articles, white papers, and books demonstrating the myriad features found in SQL Server 2014.
However it is sometimes easy to forget that there’s a big disparity in the features that the different editions of SQL Server 2014 support.
Chances are that the exciting new features you’re reading about are supported only by
SQL Server 2014 Enterprise Edition or SQL Server 2014 Developer Edition.
(Although Enterprise
The following is a list of the most important features, there are others that are restricted to Analysis Services, but those are not covered here :-
1 No Upper Memory Limit.
Maximum memory utilized (per instance of SQL Server Database Engine) Is limited by the OS Limits only for Enterprise Edition, BI and Standard Edition are limited to 128GB per Instance. Web edition is limited to 64GB with all other editions being limited to 1GB.
1 Online page and file restore.
Online restore is supported only on SQL Server Enterprise Edition. In this edition, a file, page, or piecemeal restore is online by default.
2 Online Indexing - Online Schema Change.
SQL Server introduced Online Index maintenance and partitioned tables with SQL Server 2005. Though operations around switching partitions of a partitioned table out or into a table are considered ‘online’ operations as well, they require an exclusive Schema Modification lock (SCH_M lock). A SCH_M lock against a table is usually used by DDL operations that structurally change the table. From an architecture and design point SQL Server should use the SCH_M lock for small time periods only, especially when using online options offered by some of the DDL commands of SQL Server.
3 Global Batch Aggregation.
Standard query processing in SQL Server is based on a row-at-a-time iterator model, that is, a query operator processes one row at a time. To reduce CPU time a new set of query operators have been introduced that instead process a batch of rows at a time.
4 Fast Recovery.
After installation, SQL Server sets recovery interval to zero. As long as the recovery interval setting is at the default setting and long-running transactions are not present, recovery for each database should take approximately 1 minute or less.
When recovering restored data, if long-running transactions were active at the time of the data loss, recovery time is controlled by the time it takes to roll back the effects of these transactions.
However, in SQL Server Enterprise Edition, the database is available during the undo phase of a crash recovery or a database mirroring failover, a feature known as fast recovery.
When recovering restored data, if long-running transactions were active at the time of the data loss, recovery time is controlled by the time it takes to roll back the effects of these transactions.
However, in SQL Server Enterprise Edition, the database is available during the undo phase of a crash recovery or a database mirroring failover, a feature known as fast recovery.
5 Mirrored Backups.
Mirroring a media set increases backup reliability by reducing the impact of backup-device malfunctions.
6 AlwaysOn Availability Groups .
An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. Secondary databases are not backups. Continue to back up your databases and their transaction logs on a regular basis.
7 Table and Index Partitioning.
The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data.
8 Data Compression.
SQL Server 2014 supports row and page compression for rowstore tables and indexes, and supports columnstore and columnstore archival compression for columnstore tables and indexes.
For rowstore tables and indexes, use the data compression feature to help reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk. However, extra CPU resources are required on the database server to compress and decompress the data, while data is exchanged with the application.
9 Resource Governor.
SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use.
10 Partition Table Parallelism.
Enables the use of separate threads for queries over multiple partitions.
11 Multiple Filestream Containers.
Provides the ability to have multiple data containers in a single filegroup. This feature comes in handy when you want to load-balance the I/O across multiple disk systems, or when you’ve run out of space in the data container and you need to expand your storage capacity.
12 NUMA Aware Large Page Memory and Buffer Array Allocation.
Allows the use of The intelligent, adaptive NUMA scheduling and memory placement policies in vSphere enabling transparent management of virtual machines transparently, so administrators do not need to deal with the complexity of balancing virtual machines between nodes by hand.
13 Hot Add Memory and CPU.
Hot add CPU is the ability to dynamically add CPUs to a running system. Adding CPUs can occur physically by adding new hardware, logically by online hardware partitioning, or virtually through a virtualization layer.
14 In-Memory OLTP.
New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.
15 Fine Grained Auditing.
SQL Server Enterprise enhances the auditing capabilities of the server by introducing native auditing functionality into the database engine.
16 Transparent Database Encryption.
Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module. TDE protects data "at rest", meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established in various industries.
17 Extensible Key Management.
SQL Server provides data encryption capabilities together with Extensible Key Management (EKM), using the Microsoft Cryptographic API (MSCAPI) provider for encryption and key generation. Encryption keys for data and key encryption are created in transient key containers, and they must be exported from a provider before they are stored in the database. This approach enables key management that includes an encryption key hierarchy and key backup, to be handled by SQL Server.
18 Oracle Replication Publishing.
Publishing servers can be deployed on any Oracle supported hardware and operating system. The feature is built on the well-established foundation of SQL Server snapshot replication and transactional replication, providing similar performance and usability.
Oracle Publishing is deprecated. Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.
Oracle Publishing is deprecated. Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.
19 Peer to Peer Transactional Replication.
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
20 Parallel Indexed Operations.
The query plans built for the index operations that create or rebuild an index, or drop a clustered index, allow for parallel, multi-threaded operations on computers that have multiple microprocessors.
21 Automatic Use of Indexed View By Query Optimizer.
Indexed views can be created in any edition of SQL Server. In SQL Server Enterprise, the query optimizer automatically considers the indexed view. To use an indexed view in the Standard edition or the Datacenter edition, the NOEXPAND table hint must be used
22 Parallel Consistency Check - DBCC CheckDB.
DBCC CHECKDB is supported on databases that contain memory-optimized tables but validation only occurs on disk-based tables. However, as part of database backup and recovery, a CHECKSUM validation is done for files in memory-optimized filegroups.
Since DBCC repair options are not available for memory-optimized tables, you must back up your databases regularly and test the backups. If data integrity issues occur in a memory-optimized table, you must restore from the last known good backup.
23 SQL Server Utility Control Point.
The SQL Server Utility models an organization’s SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in SQL Server Management Studio (SSMS) provide administrators a holistic view of SQL Server resource health through an instance of SQL Server that serves as a utility control point (UCP).
24 Change Data Capture Service for Oracle by Attunity.
The CDC Service for Oracle is a Windows service that scans Oracle transaction logs and captures changes to Oracle tables of interest into SQL Server change tables. The SQL change tables where the changes captured from Oracle are stored are the same type of change tables used in the native SQL Server Change Data Capture feature. This makes consuming these changes as easy as consuming changes made to SQL Server databases.
25 Change Data Capture.
Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
26 Star Join Query Optimizations.
Performs Query Optimizations on Data Warehouse Star Joins.
27 Parallel Query Processing On Partitioned Tables and Indices.
SQL Server Enterprise improves query processing performance on partitioned tables for many parallel plans, changes the way parallel and serial plans are represented, and enhances the partitioning information provided in both compile-time and run-time execution plans.
28 xVelocity memory optimized ColumnStore indexes.
xVelocity memory-optimized columnstore index is a feature which enables high performance query processing especially in Data Warehouses and Data Marts.
Unlike "Standard" indexes, columnstore indexes store data in memory (as the name memory-optimized columnstore index suggests) and the storage organization is not row-oriented.
29 SSIS - Persistent (high performance) lookups.
The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.
The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset.
30 SSIS - Data Mining Query Transformation .
The Data Mining Query transformation performs prediction queries against data mining models. This transformation contains a query builder for creating Data Mining Extensions (DMX) queries. The query builder lets you create custom statements for evaluating the transformation input data against an existing mining model using the DMX language.
Attunity connector provides an improved performance even with few records. The gain is around 15% if you have a lot of rows to transfer.
32 SSIS - Fuzzy Grouping and Lookup Transformations.
The Fuzzy Grouping transformation performs data cleaning tasks by identifying rows of data that are likely to be duplicates and selecting a canonical row of data to use in standardizing the data.
33 SSIS - Data Mining Model Training Destination Adapter .
The Data Mining Model Training destination trains data mining models by passing the data that the destination receives through the data mining model algorithms. Multiple data mining models can be trained by one destination if the models are built on the same data mining structure.
34 SSIS - Connector for Open Database Connectivity (ODBC) by Attunity .
The Connector for Open Database Connectivity (ODBC) by Attunity for SQL Server 2014 Integration Services (SSIS) helps SSIS developers easily create packages that load and unload data from ODBC-supported databases.
The ODBC Connector is designed to achieve optimal performance when loading data into or unloading data from an ODBC-supported database in the context of SQL Server 2014 Integration Services (SSIS).
