“Is it worth upgrading to SQL Server 2014?”

Problem:

“Why should we upgrade to the next version of SQL Server?”

This would be a frequently asked question from client whenever there is a new version released, and for SQL Server there always seems to be new versions around the corner. For some systems the process of upgrading SQL Server is time consuming and expensive. 

Solution:

Before deciding on upgrading, DBA should make sure the client system will really benefit by the upgrade.

This document helps DBA to understand new features and improvements available in SQL Server 2014 CPT2.

  

•  What's New in Microsoft SQL Server 2014 CTP2:
  

This latest release of SQL Server engine introduces following new features and enhancements:

1.         Memory optimized OLTP database engine

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

5.         Partition Switching and Indexing

5.1       Managing the lock priority of online

5.2       Updateable clustered columnstore indexes.

5.3       Archival data compression.

6.         Buffer pool extension (BPE) 

7.         Query plans

8.         Incremental Statistics

9.         Resource Governor Enhancements for physical IO control

10.       Inline specification of CLUSTERED and NONCLUSTERED

11.       Parallel SELECT INTO

12.       System Table Enhancements

13.       Security Enhancements

14.      AlwaysOn enhancements

15.       Power Query  & Power Map

16.       Power View for Multi-Dimensional Models 

17.       Parallel Data Warehouse with Polybase

1.      Memory optimized OLTP database engine

The main differences of Hekaton, In-Memory Databases is that it is memory optimized but durable, a very high performance OLTP engine, fully integrated into SQL Server 2014 and architected for modern CPUs.

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 )

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

1.2  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.cust (

  Cidn  INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=2000000)

  )   WITH (MEMORY_OPTIMIZED=ON)

- Natively Compiled Stored Procedures

CREATE PROCEDURE dbo.custid

WITH  NATIVE_COMPIALATION, SCHEMABINDING, EXECUTE AS OWNER

AS

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

SELECT cidn FROM dbo.cust

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

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

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

)

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 data types 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.

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.

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\DB14.BPE'

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.

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 } ]

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

      );

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                   )

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.

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.

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

sys.dm_hadr_cluster_members

sys.dm_hadr_cluster_networks

15.  Power Query  & Power Map

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

Power View for Multidimensional Models functionality is now included as part of SQL Server 2014.

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

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.

 - Useful Links:

http://msdn.microsoft.com/en-us/library/ee210467%28v=sql.120%29.aspx SQL Server 2014 Upgrade Advisor.

http://msdn.microsoft.com/en-us/library/bb500435%28v=sql.120%29.aspx What's New in SQL Server 2014.