What's New in Microsoft SQL Server 2014 CTP2

What's New in Microsoft SQL Server 2014  CTP2 

SQL Server 2014

1.            Memory optimized OLTP database engine -   (#Performance gains) 
2.            Windows Azure Storage Integration  

3.            Backup and Restore Enhancements

3.1          SQL Server Backup to URL

3.2          SQL Server Managed Backup to Windows Azure

3.3          Encryption for Backups

4.            Delayed Durability-   (#Performance gains)

5.            Partition Switching and Indexing-   (#Performance gains)

5.1          Managing the lock priority of online operations -   (#Performance gains)

5.2          Updateable clustered columnstore indexes.

5.3          Archival data compression.

6.            Buffer pool extension (BPE)  (#Fater paging)

7.            Query plans

8.            Incremental Statistics

9.            Resource Governor Enhancements for physical IO control (#Predictable performance)

10.          Inline specification of CLUSTERED and NONCLUSTERED

11 .         Parallel SELECT INTO

12.          System Table Enhancements

13.          Security Enhancements

14.          AlwaysOn enhancements (#High availability Enhancements)

15.          Power Query  & Power Map ( # Easy access to internal and external data )

16.          Power View for Multi-Dimensional Models  (BI  Enhancement)

17.          Parallel Data Warehouse with Polybase (Querying Big Data with T-SQL)

SQL Server 2014 delivers: 

·         Mission critical performance across all database workloads with in-memory for online transaction processing (OLTP), data warehousing and business intelligence built-in as well as greater scale and availability.

·         Hybrid cloud platform enabling organizations to more easily build, deploy and manage database solutions that span on-premises and cloud.

·         Faster insights from any data with a complete BI solution using familiar tools like Excel.

SQL Server 2014

1.      Memory optimized OLTP database engine for SQL Server   (#Performance gains).

Memory-optimized OLTP database engine (In-Memory OLTP)  support tables which reside in memory ( Memory-Optimized Tables)  and  Natively Compiled Stored Procedures ( Create Procedure code translated into native machine code and compiled into a DLL and loaded into memory )

SQL Server 2014

Advantages:  

• Memory-Optimized Tables

·         High Performance increase: Unlike the usual Disk-based tables, all the data is stored in memory, all the time. Pages do not need to be read into cache from disk  when the memory-optimized tables are accessed.

·         For memory-optimized tables, there are no locks acquired, and thus no waiting because of blocking.

·         Concurrency improvements: When accessing memory-optimized tables, SQL Server implements an optimistic multi-version concurrency control.

·         Hash Indexes on memory-optimized tables uses Bw-tree structure and resides in memory.

• Native stored procedures:

·         Procedure loaded into memory in native code consisting of processor instructions that can be executed directly by the CPU, without the need for further compilation or interpretation.  Syntax:

Eg:

-Memory-Optimized Tables

Create table dbo.T1 (

  C1 int not null  primary key NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000)

  )   with (MEMORY_OPTIMIZED=ON)

- Natively Compiled Stored Procedures

Create procedure dbo.p1 WITH  NATIVE_COMPIALATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC with (TRANSACTION ISOLATION LEVEL=snapshot, LANGUAGE=N'us_english')

Select 1

END

                                                                                                                                     

2.      Windows Azure Storage Integration   
 SQL Server Integration with Windows Azure Storage feature allows to create a database in SQL Server running in ‘on-premises’ or in a ‘virtual machine’ in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage.

(Note:  Windows Azure Blob storage is a service for storing large amounts of unstructured data that can be accessed from anywhere in the world via HTTP or HTTPS. A single blob can be hundreds of gigabytes in size.  )

Host a SQL Server Database in a Windows Azure Virtual Machine 

Following SQL Server database versions that can be deployed to a Windows Azure VM using the Wizard: “Deploy a SQL Server Database to a Windows Azure VM” :  SQL Server 2008/2008 R2/2012/2014

3.      Backup and Restore Enhancements

SQL Server 2014 contains the following enhancements for SQL Server Backup and Restore:

3.1   SQL Server Backup to URL

In SQL Server 2014 allows to use SQL Server Management Studio to ‘backup to’ or ‘restore from’ Windows Azure Blob storage service.

Eg:

BACKUP DATABASE AdventureWorks2014 TO URL = 'https://mystorageaccount.blob.core.windows.net/mycontainer/AdventureWorks2014.bak'

 WITH CREDENTIAL = 'TestCredential' , COMPRESSION , STATS = 5;

3.2   SQL Server Managed Backup to Windows Azure

SQL Server Managed Backup to Windows Azure manages and automates SQL Server backups to the Windows Azure Blob storage service. 

The backup strategy used by SQL Server Managed Backup to Windows Azure is based on following things on the database: Retention period & Transaction workload

SQL Server Managed Backup to Windows Azure can be configured both at the database and at instance level 

3.3 Encryption for Backups SQL Server 2014

To encrypt the backup file during a backup operation

4.      Delayed Durability-   (#Performance gains)

SQL Server In-Memory OLTP transaction commits allow either one of the following:

1.  Fully durable (dafault)
2.  Delayed durable.

When to use delayed transaction durability

•  If you are experiencing a bottleneck on transaction log writes.
•  If your workloads have a high contention rate.
•  If you can tolerate some data loss.

Delayed Transaction Durability Guarantees:

·         Once transaction commit succeeds, the changes made by the transaction are visible to the other transactions in the system.

·         Transaction durability is guaranteed only following a flush of the in-memory transaction log to disk. 

5.      Partition Switching and Indexing-   (#Performance gains)SQL
 Server 2014

5.1 Managing the lock priority of online operations:

WAIT_AT_LOW_PRIORITY option allows to control the locking mechanism that is required to rebuild an online index and causing deadlock situations.

Syntax:

-WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time>[MINUTES],

-ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  } 

1.NONE - current behavior

2.SELF - abort DDL after MAX_DURATION has elapsed

3.BLOCKERS – abort user blockers after MAX_DURATION has elapsed

Eg:

ALTER INDEX ALL ON Table1  REBUILD

WITH (

FILLFACTOR = 80,SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON  

 ( WAIT_AT_LOW_PRIORITY

MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS )

 ),  DATA_COMPRESSION = ROW

)
SQL Server 2014 

5.2 Updateable clustered columnstore indexes.

SQL Server 2012 have the option to create columnstore indexes with the potential to offer 10x performance improvements(queries with aggregates involving large scans) and 7x compression over traditional tables, but come with heavy restrictions. Like, they trip their underlying table into read-only mode.

In SQL Server 2014 Clustered Columnstore Index, allows for highly efficient column-ordered data while still allowing more options like:

• ·         making tables updatable when it comes to DML operations
• ·         schema modification is available
• ·         more datatypes included
• ·         mixed execution modes support (batch & row)
• ·         more operations support for the batch mode ( outer join, union all, etc )
• ·         improved global dictionaries for segments compression
• ·         support for even better data compression (aka Archival)
• ·         seek operation support
• ·         spill  bulk insert operation support and associated

Eg:

CREATE CLUSTERED COLUMNSTORE INDEX [IX_Detail_ColumnStore] ON [OrderDetail](ProdID)

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.
SQL Server 2014 

5.3  Archival data compression.

-ALTER INDEX … REBUILD has a new COLUMNSTORE_ARCHIVE data compression option that further compresses the specified partitions of a columnstore index .

-Helps to compress the data which results in less disk space being used.

SQL Server 2014 

6. Buffer pool extension (BPE)

Memory is one of the key assets available for the SQL Server and especially buffer pool memory, which plays key role in storing data pages and in turn reducing the I/O demand.

The Buffer Pool Extension feature (BPE) 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.

Solid-state drives (SSD) have some advantages over direct memory :

·         the higher flexibility of storage options over memory options;

·         is the greater price efficiency of available storage vs. memory.

·         Buffer Pool Extension offer:

·         Performance gains on OLTP workloads, mostly on the read-heavy OLTP.

·         No risk of data loss. BPE only deals with clean pages.

·         No application modification required. Just enable the feature and you’re ready to go.

·         Simple feature syntax

Eg:

ALTER SERVER CONFIGURATION
 SET BUFFER POOL EXTENSION ON
 SIZE = 50 GB
 FILENAME = 'F:\SSDCACHE\EXAMPLE.BPE'

SQL Server 2014 

7.      Query plans

SQL Server 2014 includes substantial improvements to the component that creates and optimized query plans. To take advantage of these improvements new database applications should be developed using database compatibility level 120.

• ObjectType/StorageType – new enumeration member
• TableScanType or RowsetType: New Attribute on (Predicate/PartitionId/IndexedViewInfo) – IsMemoryOptimized
•  IndexKindType enumeration now includes: NonClusteredHash
•  SelectiveXML and SecondarySelectiveXML – for Selective XML Index feature introduced in SQL Server 2012 SP1.
• New Attribute on HashType: BitmapCreator
•  New Attribute on RelOpType: StatsCollectionId
•  BaseStatementInfoType  has some new attributes,
• StatementSqlHandle
• DatabaseContextSettingsId
• ParentObjectId
• StatementParameterizationType
• NoParallelForMemoryOptimizedTables
• EffectiveDegreeOfParallelism. This is the maximum parallelism used by a columnstore index build.

SQL Server 2014 

8.      Incremental Statistics

CREATE STATISTICS and related statistic statements now permit per partition statistics to be Statistics are maintained at the partition level now. This enables better query result due frequent refresh at partition level.

[INCREMENTAL = { ON | OFF }

When ON, the statistics created are per partition statistics. When OFF, stats are combined for all partitions. The default is OFF.

Eg:

CREATE STATISTICS statistics_name ON { tablename } ( ccolumn1)  [ WITH [INCREMENTAL = { ON | OFF } ] 

SQL Server 2014 

9.      Resource Governor Enhancements for physical IO control

SQL Server 2014 added the new IO Resource Governor feature in addition to the existing CPU and Memory governor pools. Resource Governor in general enables us to distribute server resources among resource pools. Now in SQL Server 2014 we not only have the option to deal with CPU and Memory but also the capability of distributing IO.

We could achieve that by simply adding these two statements to our resource pool.

·         MIN_IOPS_PER_VOLUME =value : Specifies a value for the minimum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.

·         MAX_IOPS_PER_VOLUME =value : Specifies a value for the maximum I/O operations per second (IOPS) per disk volume for workloads and groups of workloads.

Eg:

CREATE RESOURCE POOL PoolAdmin

WITH (

    MIN_IOPS_PER_VOLUME = 20,

    MAX_IOPS_PER_VOLUME = 100

      ) 
SQL Server 2014 

10.  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.

CREATE TABLE T1          (

                            Col1 INT PRIMARY KEY CLUSTERED,

                            Col2 INT INDEX idx_Col2             )

SQL Server 2014 

11 . Parallel SELECT INTO

The SELECT … INTO statement is improved and can now operate in parallel. Data-loading is significantly faster because data insertion into a table can occur in parallel through the SELECT INTO operation.  The database compatibility level must be at least 110.

SQL Server 2014 

12.   System Table Enhancements 
      sys.xml_indexes

·         sys.xml_indexes (Transact-SQL) has 3 new columns: xml_index_type, xml_index_type_description, and path_id.

·         sys.dm_exec_query_profiles

·         sys.dm_exec_query_profiles (Transact-SQL) monitors real time query progress while a query is in execution.

·         sys.column_store_row_groups

·         sys.column_store_row_groups (Transact-SQL) provides clustered columnstore index information on a per-segment basis to help the administrator make system management decisions.

SQL Server 2014

13.    Security Enhancements 

13.1          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. 

13.2. 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.

13.3. 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.

13.4. ALTER ANY DATABASE EVENT SESSION Permissions

A new database level permission. Grant this permission to allow a role to read all metadata for monitoring purposes, for example SCOM.

14.  AlwaysOn enhancements

SQL Server 2014 contains the following enhancements for AlwaysOn Availability Groups:

Migration to Windows Azure is made simpler with the Add Azure Replica Wizard.

For hybrid IT environments this wizard creates a new replica for the availability group in Windows Azure end-to-end, from creating the Windows Azure virtual machine to configuring the availability group listener in the Windows Azure subnet. 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.

Enhancements are made to increase the efficiency and ease of troubleshooting availability groups, including:

·         A new system stored function called sys.fn_hadr_is_primary_replica

·         Simplified and more helpful error messages and extended events

·         Additional explanatory warnings in the New Availability Group wizard

·         More information on data synchronization behavior in the AlwaysOn Dashboard in asynchronous commit mode

·         In addition, SQL Server 2014 contains the following enhancements for AlwaysOn Failover Cluster Instances:

·         Failover cluster instances (FCIs) can now use Cluster Shared Volumes as cluster shared disks in Windows Server 2012 and above.

The following three dynamic management views now return information for FCIs:

sys.dm_hadr_cluster (Transact-SQL)

sys.dm_hadr_cluster_members (Transact-SQL)

sys.dm_hadr_cluster_networks (Transact-SQL)
 

15.            Power Query  & Power Map ( # Easy access to internal and external data)

• Power Query (formally codename “Data Explorer”) in Excel makes easy to access Internal and External data (both Relational and Non-Relational). enables users to pull in information from the web or external sources and merge it with local data, providing for an easier method for enriching your internal data.

• Power Map (formally codename “Geo Flow”) in Excel provides enhanced mapping capabilities and the ability to interact with any geographic data you may have. Can be used to get insights with 3D visualization.

16.          Power View for Multidimensional Models (BI  Enhancement)  
Power View for Multidimensional Models functionality is now included as part of SQL Server 2014.

Power View Report for a Multidimensional Model

This functionality helps organizations maximize existing BI investments by enabling multidimensional models (also known as OLAP cubes) to be used with the latest client reporting tools.

Depending on the types of data in the multidimensional model, users can easily create a variety of dynamic visualizations from tables and matrices, to bubble charts and geographical maps. Multidimensional models now also support queries using Data Analysis Expressions (DAX).

Power View for Multidimensional Models requires the built-in Power View reporting capability in SQL Server 2014 Reporting Services (in SharePoint mode). Other versions of Power View, specifically the Power View Add-in in Excel 2013, do not support multidimensional models.

17.       Parallel Data Warehouse with Polybase (querying Big Data with T-SQL)

Parallel Data Warehouse with Polybase to support querying Big Data with T-SQL. Parallel Data Warehouse (PDW) get extended to support querying Hadoop-based data via a technology called Polybase.