Hi All,
I am listing SQL Server 2014 top 10 new features here.
Microsoft introduced some significant enhancements
inSQL Server 2014—especially
with In-Memory OLTP. However, as you might expect after such a short release
cycle, not every subsystem has been updated; there are no major changes to SQL
Server Integration Services (SSIS), SQL Server Replication Services, or SQL
Server Reporting Services (SSRS). Nonetheless, there are plenty of significant
enhancements. Here are 10 new features in SQL Server 2014.
1.
In-Memory OLTP Engine
SQL Server 2014
enables memory optimization of selected tables and stored procedures. The
In-Memory OLTP engine is designed for high concurrency and uses a new
optimistic concurrency control mechanism to eliminate locking delays.
Microsoft states that customers can expect performance to be up to 20 times
better than with SQL Server 2012 when using this new feature. For more
information, check out “Rev Up Application Performance with the In-Memory OLTP Engine.”
2.
AlwaysOn Enhancements
Microsoft has
enhanced AlwaysOn integration by expanding the maximum number of secondary
replicas from four to eight. Readable secondary replicas are now also available
for read workloads, even when the primary replica is unavailable. In addition,
SQL Server 2014 provides the new Add Azure Replica Wizard, which helps you
create asynchronous secondary replicas in Windows Azure.
3.
Buffer Pool Extension
SQL Server 2014
provides a new solid state disk (SSD) integration capability that lets you use
SSDs to expand the SQL Server 2014 Buffer Pool as nonvolatile RAM (NvRAM). With
the new Buffer Pool Extensions feature, you can use SSD drives to expand the
buffer pool in systems that have maxed out their memory. Buffer Pool Extensions
can provide performance gains for read-heavy OLTP workloads.
4.
Updateable Columnstore Indexes
When Microsoft
introduced the columnstore index in SQL Server 2012, it provided improved
performance for data warehousing queries. For some queries, the columnstore
indexes provided a tenfold performance improvement. However, to utilize the
columnstore index, the underlying table had to be read-only. SQL Server 2014
eliminates this restriction with the new updateable Columnstore Index. The SQL
Server 2014 Columnstore Index must use all the columns in the table and can’t
be combined with other indexes.
5.
Storage I/O control
The Resource
Governor lets you limit the amount of CPU and memory that a given workload can
consume. SQL Server 2014 extends the reach of the Resource Governor to manage
storage I/O usage as well. The SQL Server 2014 Resource Governor can limit the
physical I/Os issued for user threads in a given resource pool.
6.
Power View for Multidimensional Models
Power View used to
be limited to tabular data. However, with SQL Server 2014, Power View can now
be used with multidimensional models (OLAP cubes) and can create a variety of
data visualizations including tables, matrices, bubble charts, and geographical
maps. Power View multidimensional models also support queries using Data
Analysis Expressions (DAX).
7.
Power BI for Office 365 Integration
Power BI for Office
365 is a cloud-based business intelligence (BI) solution that provides data
navigation and visualization capabilities. Power BI for Office 365 includes
Power Query (formerly code-named Data Explorer), Power Map (formerly code-named
GeoFlow), Power Pivot, and Power View. You can learn more about Power BI atMicrosoft’s Power BI for Office 365 site.
8.
SQL Server Data Tools for Business Intelligence
The new SQL Server
Data Tools for BI (SSDT-BI) is used to create SQL Server Analysis Services
(SSAS) models, SSRS reports, and SSIS packages. The new SSDT-BI supports SSAS
and SSRS for SQL Server 2014 and earlier, but SSIS projects are limited to SQL
Server 2014. In the pre-release version of SQL Server 2014, SQL Server Setup
doesn’t install SSDT-BI. Instead, you must download SSDT-BI separately from
the Microsoft Download Center.
9.
Backup Encryption
One welcome
addition to SQL Server 2014 is the ability to encrypt database backups for
at-rest data protection. SQL Server 2014 supports several encryption
algorithms, including Advanced Encryption Standard (AES) 128, AES 192, AES 256,
and Triple DES. You must use a certificate or an asymmetric key to perform
encryption for SQL Server 2014 backups.
10.
SQL Server Managed Backup to Windows Azure
SQL Server 2014’s
native backup supports Windows Azure integration. Although I’m not entirely
convinced that I would want to depend on an Internet connection to restore my
backups, on-premises SQL Server 2014 and Windows Azure virtual machine (VM)
instances support backing up to Windows Azure storage. The Windows Azure backup
integration is also fully built into SQL Server Management Studio (SSMS).
