SQL Server 2014 New Features.

SQL Server 2014 New Features

– Column store indexes are updatable

– Bufferpool can be extended onto SSD drives for read only pages

– Manual/Automatic Backups can be stored in the Azure cloud and restored to a Azure VM.

– New SSMS Migration Wizard for Windows Azure Infrastructure Services

– AlwaysOn now allows up to 8 readable secondaries and online indexing  Also a new SSMS Wizard to deploy AlwaysOn secondaries to an Azure VM

– With Windows 2012 R2 up to 640 logical processors and 4TB of memory in a physical environment and up to 64 virtual processors and 1TB of memory per VM

– CONNECT ANY DATABASE Permission, IMPERSONATE ANY LOGIN Permission, SELECT ALL USER SECURABLES Permission, and ALTER ANY DATABASE EVENT SESSION Permissions;

– The SELECT INTO statement can be executed in parallel

– Stored Procedure with the “WITH NATIVE_COMPILATION” directive. These require schema binding (SCHEMABINDING directive), “EXECUTE AS OWNER” and must be in atomic block context (BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)..END;.

– Support for Windows 2012 R2 Storage Spaces and network virtualization.

– Online index rebuild for a single partition and switch partitions in/out with DBA assigned lock priorities.

ALTER INDEX MyIndex ON MyTable
REBUILD PARTITION = 4
WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 5, ABORT_AFTER_WAIT = BLOCKERS)))

– Rebuild partition 4, wait for schema modification lock for up to 5 minutes.

– Kill queries causing the index rebuild to block, SELF means fail if there are blockers, NONE (default) mean wait for before.

– For AlwaysOn when the primary drops offline or the cluster loses quorum the readonly secondaries no longer go offline. Normally AlwaysOn AG connections go through the AG listener name, and then fetch the list of readable replicas from the primary, in this case they would have to connect directly to the readonly secondaries for reporting purposes.

– Failover Cluster Support for Clustered Shared Volumes

– On-premise SQL Server with data/log files in Azure storage.

CREATE DATABASE x
ON (NAME = x_dat, FILENAME = ‘https://internetstorage.windows.net/data/x.mdf’ )
LOG ON (NAME = x_log, FILENAME = ‘https://internetstorage.windows.net/data/xlog.ldf’);

– Better cardinality estimator (more than 200 histograms in data distributions)

– Resource Governor for IO

– integrated BI functionality for Office 365: Power BI for Office 365. Previous add-on options Data Explorer and Geoflow have been integrated into the BI Suite and have been renamed as Power Query (Data Explorer) and Power Map (Geoflow).

– Statistics at the partition level

– Enhancements to the algorithm that determines when statistics are out of date so statistics are invalidated sooner.

– For CTP1 Natively complied stored procedures have sort operator limitations – only up to  8000 rows and it cannot sort aggregate values and expressions not appearing verbatim in the GROUP BY list.

– Natively compiled stored procedures fail casting nchar(n) and binary(n) types to a larger fixed-length type.

– Running scripts of memory-optimized tables scripted via SSMS may fail if the scripting option Include Collation is not enabled

– Memory-optimized table row count invalid in SMO and SSMS table properties – it is returned as 0

– When querying the DMV sys.dm_db_xtp_hash_index_stats, the query returns multiple rows per hash index, in case the table has multiple nonclustered hash indexes. In addition, the index name reported in the column index_name is incorrect.

– The DMV sys.dm_xtp_memory_stats may report less memory than is actually allocated and used by memory-optimized objects, when the user does not have permission on all the memory-optimized objects on the instance, as well as VIEW SERVER STATE and VIEW DATABASE STATE permissions.

– Inserting a large batch of rows into a memory optimized table in a single transaction may lead to unbounded log growth

– In SQL Server 2014 CTP 1 there is no option to limit the memory consumed by memory-optimized tables and related structures – it can hang the server. In CTP2, you will have an option to use Resource Governor to limit the memory consumed by memory-optimized tables.

– Product Documentation is at http://msdn.microsoft.com/en-us/library/bb418471%28v=sql.10%29.aspx

Advertisement

---