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