Overview of Microsoft® SQL Server® 2014 CTP2 Database Engine - It's Faster .... It's Better !!!

The new database engine is designed for higher reliability, improved performance and handle larger database. Many new features and enhancements have been put forward that enhance the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems.

This release of Microsoft SQL Server is one step forward to tackle the challenges of Big Data i.e. fast and efficient way to handle huge unstructured data, and Hybrid Cloud. With this release of the SQL Server, Microsoft tried to establish a bridge between Microsoft SQL Server and Hadoop which add new dimensions for not only this release of SQL Server but also for next releases of SQL Server in future.

For this, Microsoft enhanced the Database Engine in following Areas:

·         Database Engine Feature Enhancements

§  Memory-optimized Tables: In-Memory OLTP, a memory-optimized database engine integrated into the SQL Server engine, is optimized for OLTP.

§  SQL Server Data Files in Windows Azure: enables users to create a database in SQL Server running in on-premises or in a virtual machine in Windows Azure.

§  Windows Azure Virtual Machine Wizard: to Deploy a SQL Server Database to host a database from an instance of SQL Server in a Windows Azure Virtual Machine

§  Backup and Restore Enhancements: SQL Server Management Studio can be used to backup to or restore from Windows Azure Blob storage service. SQL Server Managed Backup (Windows Azure only) to manage and schedule database and log backups. User can now choose (AES 128, AES 192, AES 256, and Triple DES) to encrypt the backup file during a backup operation and must use either a certificate or an asymmetric key to perform encryption during the backup.

§  New Cardinality Estimation Logic (Cardinality Estimator):  incorporates assumptions and algorithms that work well on modern OLTP and data warehousing workloads. This logic improves the quality of query plans as a result query performance optimized.

§  Delayed Durability: returns control of delayed durable transaction to the client before the transaction log record is hardened to disk. The Delayed Durability can be controlled at the database level, COMMIT level, or ATOMIC block level.

§  AlwaysOn Enhancements: For this,

§  Migration to Windows Azure is made simpler

§  Azure Replica Wizard Added

§  Maximum number of secondary replicas is increased from 4 to 8.

§  Enhancements are made to increase the efficiency and ease of troubleshooting.

§  Partition Switching and Indexing: The individual partition of partitioned tables can be rebuilt in SQL Server 2014.

§  Managing the Lock Priority of Online Operations: Enhanced ONLINE = ON option (which now contains a WAIT_AT_LOW_PRIORITY option) permits to specify how long the rebuild process should wait for the necessary locks.

§  Columnstore Indexes: improves data compression and query performance for data warehousing workloads that primarily perform bulk loads and read-only queries.

§  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 improve I/O throughput.

§  Incremental Statistics: CREATE STATISTICS and related statistic allows or reports related statements to incremental statistics.

§  Resource Governor Enhancements for Physical IO Control: Now physical IOs issued for user threads for a given resource pool can be governed.

§  Online Index Operation Event Class: has two new data columns: PartitionId and PartitionNumber

·         Transact-SQL Enhancements:

§  Inline specification of CLUSTERED and NONCLUSTERED indexes is now allowed for disk-based tables.

§  The SELECT … INTO statement is improved and can now operate in parallel (compatibility level 110 or higher)

§  Enhanced Transact-SQL for In-Memory OLTP

·         System Table/Views Enhancements:  Enhanced sys.xml_indexes, sys.xml_indexes (Transact-SQL), sys.dm_exec_query_profiles, sys.dm_exec_query_profiles (Transact-SQL), sys.column_store_row_groups, and sys.column_store_row_groups

·         Security Enhancements:

§  CONNECT ANY DATABASE Permission: to a login that must connect to all databases

§  IMPERSONATE ANY LOGIN Permission: to impersonate the account of clients connecting to it, as it connects to databases

§  SELECT ALL USER SECURABLES Permission: a login such can view data in all databases that the user can connect to.

·         Deployment Enhancements: enables deployment of a SQL Server database to a Windows Azure VM. 

Concise Summary: The new Microsoft SQL Server 2014 CTP2 Database Engine is faster, better and future ready. It is specially designed to perform large, complex structured and unstructured data at on-premises or in a virtual machine in Windows Azure.

 

DelveSys	Data, Application & IT Services
www.delvesys.com sales@delvesys.com ; delvesys@gmail.com	Server up gradation, Server / Database Migration expert from Oracle, MySQL, MS SQL Server (older Version) to MS SQL Server 2014, Remote Database Services, App Development, Data Processing Services.