While working on database we always feel greedy to have numerous features in the DBMS. When in comes to the SQL Server we always want to go for highly enhanced version. Microsoft has introduced few significant features in SQL Server 2014.
Below are 10 features that everyone should know about it.
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.
SQL Server 2014 now supports 8 secondary replicas instead of 4, which is great particularly for scaling out the read-only portions of your workload. 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.
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.
Standard and BI Editions now support 128 GB of memory (SQL Server 2008 R2 and 2012 only supports 64 GB). Enterprise Edition now supports up to 640 cores, and 4 TB of RAM.
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.
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).
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, Power Map, Power Pivot, and Power View. You can learn more about Power BI at Microsoft’s Power BI for Office 365 site.
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.
SQL Server 2014 is providing 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.
Several new permissions have been added in SQL Server 2014:
CONNECT ANY DATABASE : Using this permission you can allow a login to connect to any database without having to configure connect privileges on every database and doing it again every time a new database is created. This can be very useful for auditing scenarios.
IMPERSONATE ANY LOGIN : This allows a login to impersonate any other login, without giving them sysadmin privileges. Conversely, you can use it to deny impersonation even for logins that inherently have the right to do so.
There are many more new features and enhancements have been incorporated in the SQL Server 2014. Above are few which I liked very much.