SQL Server 2014 released… Let’s discover what’s new in SQL Server 2014 now. There are versions in SQL Server out of which Express version is still free . Download SQL Server from Microsoft Site or your company volume licensing depending on your version you require.
New features will be listed here which are really fantastic….
Note: Database Compatibility Level of 90 compatibility level is not valid in SQL Server 2014.
I will also post some examples of how to use these features in coming days…
Database Engine Feature Enhancements For more info click here for microsoft site
Memory-Optimized Tables: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine.
Buffer Pool Extension: The Buffer Pool Extension provides the seamless integration of solid-state drives (SSD) as a nonvolatile random access memory (NvRAM) extension to the Database Engine buffer pool to significantly improve I/O throughput.
Incremental Statistics: CREATE STATISTICS and related statistic statements now permits per partition statistics to be created by using the INCREMENTAL option. Related statements allow or report incremental statistics. Affected syntax includes UPDATE STATISTICS, sp_createstats, CREATE INDEX, ALTER INDEX, ALTER DATABASE SET options, DATABASEPROPERTYEX, sys.databases, and sys.stats.
SQL Server in Windows Azure: SQL Server Data Files in Windows Azure enables native support for SQL Server database files stored as Windows Azure Blobs. Also Host a SQL Server Database in a Windows Azure Virtual Machine
Backup and Restore Enhancements:
SQL Server Backup to URL: SQL Server Backup to URL was introduced in SQL Server 2012 (SP1 CU2) supported only by TSQL, PowerShell and SMO. In SQL Server 2014 you can use SQL Server Management Studio to backup to or restore from Windows Azure Blob storage service. The new option is available both for the Backup task, and maintenance plans.
SQL Server Managed Backup to Windows Azure: Built on SQL Server Backup to URL, SQL Server Managed Backup to Windows Azure is a service that SQL Server provides to manage and schedule database and log backups. In this release only backup to Windows Azure storage is supported.
Encryption for Backups:
You can now choose to encrypt the backup file during a backup operation. It supports several encryption algorithms including AES 128, AES 192, AES 256, and Triple DES. You must use either a certificate or an asymmetric key to perform encryption during backup. For more information, see Backup Encryption.
New Design for Cardinality Estimation: The cardinality estimation logic, called the cardinality estimator, is re-designed in SQL Server 2014 to improve the quality of query plans, and therefore to improve query performance.
Delayed Durability: SQL Server 2014 introduces the ability to reduce latency by designating some or all transactions as delayed durable. A delayed durable transaction returns control to the client before the transaction log record is written to disk. Please be aware of down side of using this feature like you may lose some transactions where they are not written to disk yet. Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.
AlwaysOn Enhancements:
An Add Azure Replica Wizard simplifies creating hybrid solutions for AlwaysOn availability groups.
The maximum number of secondary replicas is increased from 4 to 8.
When disconnected from the primary replica or during cluster quorum loss, readable secondary replicas now remain available for read workloads.
Failover cluster instances (FCIs) can now use Cluster Shared Volumes (CSVs) as cluster shared disks.
A new system function, sys.fn_hadr_is_primary_replica, and a new DMV, sys.dm_io_cluster_valid_path_names, is available.
Partition Switching and Indexing:
The individual partitions of partitioned tables can now be rebuilt.
Managing the Lock Priority of Online Operations
The ONLINE = ON option now contains a WAIT_AT_LOW_PRIORITY option which permits you to specify how long the rebuild process should wait for the necessary locks. The WAIT_AT_LOW_PRIORITY option also allows you to configure the termination of blocking processes related to that rebuild statement.
Columnstore Indexes: Use a clustered columnstore index to improve data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries. Since the clustered columnstore index is updateable, the workload can perform many insert, update, and delete operations.
SHOWPLAN: displays information about columnstore indexes. The EstimatedExecutionMode and ActualExecutionMode properties have two possible values: Batch or Row. The Storage property has two possible values: RowStore and ColumnStore.
Archival data compression : ALTER INDEX … REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index. Use this for archival, or for other situations that require a smaller data storage size and can afford more time for storage and retrieval.
Resource Governor Enhancements for Physical IO Control:The Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within a resource pool. In SQL Server 2014, you can use the new MIN_IOPS_PER_VOLUME and MAX_IOPS_PER_VOLUME settings to control the physical IOs issued for user threads for a given resource pool.
Online Index Operation Event Class:The progress report for the online index operation event class now has two new data columns: PartitionId and PartitionNumber.
Transact-SQL Enhancements:
Inline specification of CLUSTERED and NONCLUSTERED. Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables. Creating a table with inline indexes is equivalent to issuing a create table followed by corresponding CREATE INDEX statements. Included columns and filter conditions are not supported with inline indexes.
SELECT … INTO
The SELECT … INTO statement is improved and can now operate in parallel. The database compatibility level must be at least 110.
Also Transact-SQL Enhancements for In-Memory OLTP
System View Enhancements include below
sys.xml_indexes
sys.dm_exec_query_profiles
sys.column_store_row_groups
sys.databases
System View Enhancements for In-Memory OLTP
Security Enhancements
CONNECT ANY DATABASE Permission
A new server level permission. Grant CONNECT ANY DATABASE to a login that must connect to all databases that currently exist and to any new databases that might be created in future. Does not grant any permission in any database beyond connect. Combine with SELECT ALL USER SECURABLES or VIEW SERVER STATE to allow an auditing process to view all data or all database states on the instance of SQL Server.
IMPERSONATE ANY LOGIN Permission
A new server level permission. When granted, allows a middle-tier process to impersonate the account of clients connecting to it, as it connects to databases. When denied, a high privileged login can be blocked from impersonating other logins. For example, a login with CONTROL SERVER permission can be blocked from impersonating other logins.
SELECT ALL USER SECURABLES Permission
A new server level permission. When granted, a login such as an auditor can view data in all databases that the user can connect to.
Top
Also I will post comparison of new features introduced in 2014 vs 2012 vs 2008 R2 in another post.