Tuesday, April 22, 2014

Top Five New Features of Microsoft SQL Server 2014

Now that SQL Server 2014 has been released, I’d like to share the most exciting features that should make this release popular with organizations searching for data base technology as well as existing SQL Server customers.

1. In-Memory OLTP (On-Line Transactional Processing)

In SQL Server 2014, Microsoft made a major architectural enhancement to the database engine by adding a memory-optimized OLTP engine. Data base administrators can increase throughput and reduce the latency of database applications significantly by moving applicable (accessed/updated frequently) tables to in-memory tables.

2. In-Memory Columnstore Index

A columnstore index is a technology for storing, retrieving and managing data by using a columnar format, called a columnstore. This type of index is most often used in data warehousing scenarios for accessing large data sets. Microsoft introduced the non-clustered columnstore index feature in SQL Server 2012. SQL Server 2014 now includes the capability for a clustered columnstore index which is updateable and can perform many insert, update and delete operations, as compared to a non-clustered columnstore index. Clustered columnstore indexes can be configured to use columnstore archival compression to achieve very high compression over the uncompressed data size.

3. Backup to Windows Azure

SQL Server 2014 ships with the ability to backup databases to the cloud using SQL scripting (T-SQL), Powershell, and GUI integration to SQL Server Management Studio. Backup can be done manually or managed. SQL Server Managed Backup to Windows Azure is a service that SQL Server provides to manage and schedule database and log backups. In this release, only backup to Windows Azure is supported. SQL Server Managed Backup to Windows Azure can be configured both on SQL Server instances running on-premise and SQL Server instances running in Windows Azure virtual machines. SQL Server also ships with a tool to backup database to the cloud for previous versions of SQL Server (2005/2008/2008R2/2012). Additionally, a new backup encryption feature to support encrypted backup without the need to turn on Transparent Data Encryption is fully supported.

4. Data files in Windows Azure

SQL Server 2014 enables native support for SQL Server database files stored as Windows Azure Blobs. This feature allows you to create a database in SQL Server running on-premises or in a virtual machine in Windows Azure with a dedicated storage location for your data in Windows Azure Blob Storage. This enhancement especially simplifies the movement of databases between machines by using detach and attach operations. In addition, it provides an alternative storage location for your database backup files by allowing you to restore from or to Windows Azure Storage. Therefore, it enables hybrid solutions by providing benefits for data virtualization, data movement, security and availability.

5. Deploy data base to Windows Azure

SQL Server 2014 ships with a new wizard to deploy a database from an instance of the Database Engine to SQL Server in a Windows Azure Virtual Machine. This deployment feature is available in SQL Server Management Studio for SQL Server 2014. The source SQL Server database versions include SQL Server 2008, 2008R2, 2012 and 2014. The destination SQL Server database version include SQL Server 2012 and 2014.