MySQL storage engines

A storage engine is a software module that a database management system uses to create, read, update data from a database. There are two types of storage engines in MySQL. Transactional and non-transactional.

The default storage engine for MySQL prior to version 5.5 was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB. Choosing the right storage engine is an important strategic decision, which will impact future development. In this tutorial, we will be using MyISAM, InnoDB, Memory and CSV storage engines. If you are new to MySQL and your are studying the MySQL database management system, then this is not much of a concern. If you are planning a production database, then things become more complicated.

List of storage engines

MySQL supported storage engines:

• MyISAM
• InnoDB
• Memory
• CSV
• Merge
• Archive
• Federated
• Blackhole
• Example
• XTraDB
• NDB Cluster
• TokuDB
• The IBMDB2I

• ·         MyISAM is the original storage engine. It is a fast storage engine. It does not support transactions. MyISAM provides table-level locking. It is used most in Web, data warehousing.
• ·         InnoDB is the most widely used storage engine with transaction support. It is an ACID compliant storage engine. It supports row-level locking, crash recovery and multi-version concurrency control. It is the only engine which provides foreign key referential integrity constraint.
• ·         Memory storage engine creates tables in memory. It is the fastest engine. It provides table-level locking. It does not support transactions. Memory storage engine is ideal for creating temporary tables or quick lookups. The data is lost when the database is restarted.
• ·         CSV stores data in csv files. It provides great flexibility, because data in this format is easily integrated into other applications.
• ·         Merge operates on underlying MyISAM tables. Merge tables help manage large volumes of data more easily. It logically groups a series of identical MyISAM tables, and references them as one object. Good for data warehousing environments.
• ·         Archive storage engine is optimized for high speed inserting. It compresses data as it is inserted. It does not support transactions. It is ideal for storing, retrieving large amounts of seldom referenced historical, archived data.
• ·         The Blackhole storage engine accepts but does not store data. Retrievals always return an empty set. The functionality can be used in distributed database design where data is automatically replicated, but not stored locally. This storage engine can be used to perform performance tests or other testing.
• ·         Federated storage engine offers the ability to separate MySQL servers to create one logical database from many physical servers. Queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables. It is good for distributed environments.
•  XTraDB Percona XtraDB is an enhanced version of the InnoDB storage engine for MySQL® and MariaDB®. It has much faster performance than InnoDB and better scalability on modern hardware. XtraDB has more features than InnoDB; these are useful in high-load environments. It is backwards-compatible with InnoDB, so you can use it as a drop-in replacement. Percona XtraDB includes all of InnoDB's reliable ACID-compliant design and advanced MVCC architecture. It adds features, tunability, metrics, and scalability. In particular, it is designed to scale better than InnoDB on many cores, to use memory more efficiently than InnoDB, and to be more convenient and usable than InnoDB. The new features are specially designed to overcome some of InnoDB's limitations. We choose enhancements based on customer requests, and on our best judgment of real-world needs as a high-performance consulting company
•      NDB Cluster is the distributed database system underlying MySQL Cluster. It can be used independently of a MySQL Server with users accessing the Cluster via the NDB API (C++).From the MySQL Server perspective the NDB Cluster is a Storage engine for storing tables of rows.From the NDB Cluster perspective, a MySQL Server instance is an API process connected to the Cluster. NDB Cluster can concurrently support access from other types of API processes including Memcached, JavaScript/Node.JS, Java, JPA and HTTP/REST. All API processes can operate on the same tables and data stored in the NDB Cluster.For a full description of NDB Cluster capabilities, use-cases and resources, see the entry for MySQL Cluster.
• TokuDB is a storage engine for MySQL and MariaDB that is specifically designed for high performance on write-intensive workloads. It achieves this via Fractal Tree indexing. TokuDB is a scalable, ACID and MVCC compliant storage engine that provides indexing-based query improvements, offers online schema modifications, and reduces slave lag for both hard disk drives and flash memory.A Community Edition of TokuDB was released under a modified GNU General Public License in April 2013.
•     The IBMDB2I storage engine is designed as a fully featured transaction-capable storage engine that enables MySQL to store its data in DB2 tables running on IBM i. With the IBMDB2I storage engine, data can be shared between MySQL applications and applications coded for native DB2 for i interfaces.IBMDB2I provides ACID-compliant transactions, support for foreign key constraints, full crash recovery, radix-tree-based indexes, and the unique ability to enable DB2 for i applications to see and update table data in real time.

In some cases, the answer is clear. Whenever we are dealing with some payment systems, we are obliged to use the most secure solution. We cannot afford to loose such sensitive data. InnoDB is the way to go. If we want full-text search, than we must choose MyISAM. Only InnoDB supports foreign key referential integrity constraint and if we plan to use this constraint, then the choice is clear. In many situations we must have enough experience to choose the right engine.

Specifying and altering storage engines

·         The storage engine is specified at the time of the table creation.

       mysql> CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), 

       -> Cost INTEGER) ENGINE='MyISAM';

·         The ENGINE keyword specifies the storage engine used for this particular table.

·         If we do not specify the storage engine explicitly, then the default storage engine is used. Prior to MySQL 5.5 the default storage engine was MyISAM. For MySQL 5.5 and later, the default storage engine is InnoDB.

       mysql> SHOW VARIABLES LIKE 'storage_engine';

      +----------------+--------+

         | Variable_name  | Value  |

         +----------------+--------+

         | storage_engine | InnoDB |

         +----------------+--------+

         1 row in set (0.00 sec)

·         The default storage engine can be found in the storage_engine variable.

·         It is possible to migrate to a different storage engine. Note that migrating a large table might take a long time. Also we might run into some problems when migrating tables. Some features might not be supported in both tables.

       mysql> SELECT ENGINE FROM information_schema.TABLES

           -> WHERE TABLE_SCHEMA='mydb'

       -> AND TABLE_NAME='Cars';

    +--------+

        | ENGINE |

        +--------+

      | InnoDB |

      +--------+

         1 row in set (0.00 sec)

·         This SQL statement finds out the storage engine used for a Cars table in mydb database. We could also use SELECT CREATE TABLE Cars SQL statement. The information_schema is a table which stores technical information about our tables.

      mysql> ALTER TABLE Cars ENGINE='MyISAM';

·         This SQL statement changes the storage engine to MyISAM.

         mysql> SELECT ENGINE FROM information_schema.TABLES

          -> WHERE TABLE_SCHEMA='mydb'

            -> AND TABLE_NAME='Cars';

         +--------+

        | ENGINE |

         +--------+

     | MyISAM |

       +--------+

         1 row in set (0.00 sec)

·         Now the storage engine is MyISAM.

SQL SERVER 2014 HEKATON USEFUL LINKS

SQL Server 2014 is nearly here. When it ships it will come with an “In-Memory OLTP” feature. This feature is generally known by its codename: Hekaton. Why that code name? Well, hekaton is Greek for a hundred, and that was the targeted performance improvement that Microsoft set out to achieve when building this new technology.

First up, here is a link to the MSDN entry that gets you started on the topic of In-Memory OLTP: http://technet.microsoft.com/en-us/library/dn133186(v=sql.120).aspx

Are you instead looking for code samples? They have that, too: http://technet.microsoft.com/en-us/library/dn296373(v=sql.120).aspx

HEKATON BLOG POSTS

Here is a list of Microsoft blog posts that will help you prepare for Hekaton:

·         Getting Started with SQL Server 2014 In-Memory OLTP:http://blogs.technet.com/b/dataplatforminsider/archive/2013/06/26/getting-started-with-sql-server-2014-in-memory-oltp.aspx

·         Sample AdventureWorks sample database specific for Hekaton functionality: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/11/new-in-memory-oltp-sample-for-sql-server-2014-ctp2.aspx

·         SQL Server 2014 In-Memory OLTP – bwin Migration and Production Experience:http://blogs.technet.com/b/dataplatforminsider/archive/2013/07/30/sql-server-2014-in-memory-oltp-bwin-migration-and-production-experience.aspx

·         SQL Server 2014 In-Memory OLTP: App Migration Scenario Leveraging the Integrated Approach: http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/13/sql-server-2014-in-memory-oltp-app-migration-scenario-leveraging-the-integrated-approach.aspx

·         SQL Server 2014 In-Memory OLTP: Nonclustered Indexes for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/12/sql-server-2014-in-memory-oltp-nonclustered-indexes-for-memory-optimized-tables.aspx

·         SQL Server 2014 In-Memory OLTP: High Availability for Databases with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/05/in-memory-oltp-high-availability-for-databases-with-memory-optimized-tables.aspx

·         SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/14/sql-server-2014-in-memory-oltp-memory-management-for-memory-optimized-tables.aspx

·         SQL Server 2014 In Memory OLTP: Memory-Optimized Table Types and Table Variables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/07/sql-server-2014-in-memory-oltp-memory-optimized-table-types-and-table-variables.aspx

·         In-Memory OLTP: How Durability is Achieved for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/11/in-memory-oltp-how-durability-is-achieved-for-memory-optimized-tables.aspx

·         In-Memory OLTP Programmability: Concurrency and Transaction Isolation for Memory-optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2013/10/01/in-memory-oltp-programmability-concurrency-and-transaction-isolation-for-memory-optimized-tables.aspx

·         In-Memory OLTP Index Troubleshooting, Part II: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/30/in-memory-oltp-index-troubleshooting-part-ii.aspx

·         Hardware Considerations for In-Memory OLTP in SQL Server 2014:http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/01/hardware-considerations-for-in-memory-oltp-in-sql-server-2014.aspx

·         How Memory-Optimized Database Technology is Integrated into SQL Server 2014:http://blogs.technet.com/b/dataplatforminsider/archive/2013/08/08/how-memory-optimized-database-technology-is-integrated-into-sql-server-2014.aspx

·         Boosting Transaction Performance in Windows Azure Virtual Machines with In-Memory OLTP: http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/25/boosting-transaction-performance-in-windows-azure-virtual-machines-with-in-memory-oltp.aspx

·         Storage Allocation and Management for Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/16/storage-allocation-and-management-for-memory-optimized-tables.aspx

·         Database Backup with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/05/database-backup-with-memory-optimized-tables.aspx

·         Differential Database Backup with Memory-Optimized Tables: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/07/differential-database-backup-with-memory-optimized-tables.aspx

·         SQL Server 2014: Inside Hekaton Natively Compiled Stored Procedures:http://blogs.msdn.com/b/igorpag/archive/2014/01/15/sql-server-2014-inside-hekaton-natively-compiled-stored-procedures.aspx

·         Using Natively Compiled Stored Procedures in SQL Server 2014: http://blogs.technet.com/b/dataplatforminsider/archive/2014/02/21/using-natively-compiled-stored-procedures-in-sql-server-2014.aspx

·         Merge Operation in Memory-Optimized Tables:http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/22/merge-operation-in-memory-optimized-tables.aspx

·         State Transition of Checkpoint Files in Databases with Memory-Optimized Tables:http://blogs.technet.com/b/dataplatforminsider/archive/2014/01/23/state-transition-of-checkpoint-files-in-databases-with-memory-optimized-tables.aspx

Posts from Tony Rogerson:

·         Hekaton In-Memory Tables: Hash Indexes: http://dataidol.com/tonyrogerson/2014/01/06/hekaton-in-memory-tables-hash-indexes/

·         Hekaton In-Memory tables: Understanding the Row Chains of Hash Indexes: http://dataidol.com/tonyrogerson/2014/01/16/sql-server-hekaton-in-memory-tables-understanding-the-row-chains-of-hash-indexes/

·         SQL Server Hekaton (XTP) In-Memory Tables: Range Indexes and Row Chains: http://dataidol.com/tonyrogerson/2014/01/21/sql-server-hekaton-xtp-in-memory-tables-range-indexes-and-row-chains/

·         SQL Server Hekaton (XTP) In-Memory tables: Choosing the correct BUCKET_COUNT for a Hash Index: http://dataidol.com/tonyrogerson/2014/01/25/sql-server-hekaton-xtp-in-memory-tables-choosing-the-correct-bucket_count-for-a-hash-index/

Posts from Bob Beauchemin:

·         Thinking about Hekaton? Then think about collations:http://www.sqlskills.com/blogs/bobb/thinking-about-hekaton-then-think-about-collations/

·         Tracking execution stats of SQL Server 2014 natively-compiled sprocs:http://www.sqlskills.com/blogs/bobb/tracking-execution-stats-of-sql-server-2014-natively-compiled-sprocs/

·         Hekaton data and code – where does that stuff actually live?:http://www.sqlskills.com/blogs/bobb/hekaton-data-and-code-where-does-that-stuff-actually-live/

·         SQL Server 2014 memory-optimized table variables – metadata and garbage collection: http://www.sqlskills.com/blogs/bobb/sql-server-2014-memory-optimized-table-variables-metadata-and-garbage-collection/

·         In-memory OLTP – read_set, write_set, and scan_set: http://www.sqlskills.com/blogs/bobb/in-memory-oltp-read_set-write_set-and-scan_set/

·         SQL Server 2014 In-Memory OLTP: What exactly is a “dusty corner”?:http://www.sqlskills.com/blogs/bobb/sql-server-2014-memory-oltp-exactly-dusty-corner/

HEKATON VIDEOS

Here is a video from Dr. David DeWitt at the PASS 2013 Summit in Charlotte:

·         Hekaton: Why, What, and How (fast forward to 27:00 mark so you don’t have to listen to me introduce David)

Here are some videos on Channel 9 from TechEd 2013, the first three are from Sunil Agarwal, a Principal Program Manager for the SQL Server product team at Microsoft:

·         Microsoft SQL Server In-Memory OLTP: Overview of Project “Hekaton”

·         Microsoft SQL Server In-Memory OLTP Project “Hekaton”: App Dev Deep Dive

·         Microsoft SQL Server In-Memory OLTP Project “Hekaton”: Management Deep Dive

These next three are from Jos de Bruijn, a Program Manager for the SQL Server product team and key member of the Hekaton team:

·         Microsoft SQL Server 2014 In-Memory OLTP: Overview

·         SQL Server In-Memory OLTP: DB Developer Deep Dive

·         SQL Server In-Memory OLTP: DBA Deep Dive

HEKATON WHITEPAPERS

Lastly, I’ll leave you with a link to the some whitepapers:

·         First up is the one published by Microsoft earlier this year: http://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdf

·         Here is one from Kalen Delany: http://download.microsoft.com/download/F/5/0/F5096A71-3C31-4E9F-864E-A6D097A64805/SQL_Server_Hekaton_CTP1_White_Paper.pdf 

Most of the information listed above is specific to SQL Server 2014 CTP1. As newer versions of SQL Server are released I intend to update this post over time with additional links that I find to be useful regarding Hekaton. So you may find it useful to bookmark this page for future reference.

Thanking you

Lakshmi Narayana

Top Ten: New Features in SQL Server 2014

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. 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).
2. 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.”
3. 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.
4. 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.
5.  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.
6.  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).
7. 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.
8. 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.
9. 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.
10. 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.

Important New Features in SQL Server 2014

In-Memory OLTP engine and other new features might justify an upgrade

Microsoft's new release of SQL Server 2014 comes pretty close on the heels of the last SQL Server 2012 release. For many organizations, this could be a hurdle to adoption, because upgrading core pieces of an IT infrastructure can be both costly and resource-intensive. However, SQL Server 2014 has several compelling new features that can definitely justify an upgrade. Without a doubt, the most notable new feature is the new In-Memory OLTP engine, which promises some big performance improvements for OLTP applications. The past couple of SQL Server releases have had a strong focus on business intelligence (BI), which makes the new In-Memory OLTP engine an especially welcome addition for relational database professionals. Let's dig in and have a closer look at the new In-Memory OLTP engine and the other new features in SQL Server 2014.

New In-Memory OLTP Engine

By far the most important new feature in SQL Server 2014 is the In-Memory OLTP engine (formerly code-named Hekaton). By moving select tables and stored procedures into memory, you can drastically reduce I/O and improve performance of your OLTP applications. Microsoft states that some applications can expect up to a 20x performance improvement. Edgenet, an early adopter, saw a 7x performance increase in its online and retail supply application.

The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. The In-Memory OLTP tables are copied into memory and made durable by transaction log writes to disk. An all-new lock-free engine processes the transactions for memory-resident tables. Stored procedure performance is improved by compiling the stored procedures into native code DLLs. Standard T-SQL stored procedures are interpreted, which adds overhead to the execution process. Compiling the stored procedures to native Win64 code makes them directly executable, thereby maximizing their performance and minimizing execution time.

To help you evaluate how the In-Memory OLTP engine will improve your database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool. Like its name suggests, the AMR tool analyzes your database and helps you identify the tables and stored procedures that would benefit from moving them into memory. It lists the expected performance improvements as well as any incompatibilities that need to be addressed. In addition, the AMR tool can help you perform the actual migration of tables to the new memory-optimized format. (For more information about the AMR tool, see "SQL Server 2014's Analysis, Migrate, and Report Tool.")

The In-Memory OLTP engine works with commodity server hardware, but it has a number of limitations. For instance, not all of the data types are supported. Some of the data types that aren't supported for memory-optimized tables include geography, hierarchyid, image, text, ntext, varchar(max), and xml. In addition, several database features can't be used with the new In-Memory OLTP capability. Database mirroring, snapshots, computed columns, triggers, clustered indexes, identity columns, FILESTREAM storage, and FOREIGN KEY, CHECK, and UNIQUE constraints aren't supported.

The In-Memory OLTP engine is supported on Windows Server 2012 R2, Windows Server 2012, and Windows Server 2008 R2 SP2. In addition, you need to be using the SQL Server 2014 Enterprise, Developer, or Evaluation edition. Notably, In-Memory OLTP won't be supported on the SQL Server 2014 Standard edition. For more information, check out "Rev Up Application Performance with the In-Memory OLTP Engine."

Enhanced Windows Server 2012 Integration

SQL Server 2014 provides improved integration with Windows Server 2012 R2 and Windows Server 2012. SQL Server 2014 will have the ability to scale up to 640 logical processors and 4TB of memory in a physical environment. It can scale up to 64 virtual processors and 1TB of memory when running on a virtual machine (VM).

SQL Server 2014 also provides a new solid state disk (SSD) integration capability that enables you to use SSD storage to expand SQL Server 2014's buffer pool. The new buffer pool enhancements can help increase performance in systems that have maxed out their memory capability by using high-speed nonvolatile RAM (NVRAM) in the SSD drives as an extension to SQL Server 2014's standard buffer pool. The new buffer pool extensions can provide the best performance gains for read-heavy OLTP workloads.

SQL Server 2014's Resource Governor provides a new capability to manage application storage I/O utilization. First introduced with SQL Server 2008, the Resource Governor originally enabled you to limit the amount of CPU and memory that a given workload can consume. SQL Server 2014 extends the reach of the Resources Governor so that you can now 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, allowing you to have more predictable application performance.

SQL Server 2014 also integrates with several new and improved features in Windows Server 2012 R2 and Windows Server 2012. For example, SQL Server 2014 supports the OSs' new Storage Spaces feature. With Storage Spaces, you can create pools of tiered storage to improve application availability and performance. In addition, SQL Server 2014 can take advantage of the OSs' Server Message Block (SMB) 3.0 enhancements to achieve high-performance database storage on Windows Server 2012 R2 and Windows Server 2012 file shares. Many enhancements were made to SMB 3.0, with the most notable being SMB Transparent Failover and SMB Direct. The new SMB Transparent Failover feature provides highly reliable SMB storage that's fully supported for applications like SQL Server and Hyper-V. With the new SMB Direct feature, you can leverage the NIC's Remote Direct Memory Access (RDMA) feature to provide access speeds for SMB file shares nearing the access speed for local resources.

 

Enhancements to AlwaysOn Availability Groups

SQL Server 2014's AlwaysOn Availability Groups has been enhanced with support for additional secondary replicas and Windows Azure integration. First introduced with SQL Server 2012, AlwaysOn Availability Groups boosted SQL Server availability by providing the ability to protect multiple databases with up to four secondary replicas. In SQL Server 2014, Microsoft has enhanced AlwaysOn integration by expanding the maximum number of secondary replicas from four to eight. Readable secondary replicas are now available for read-only workloads, even when the primary replica is unavailable. SQL Server 2014 also provides Windows Azure AlwaysOn integration. This new integration feature enables you to create asynchronous availability group replicas in Windows Azure for disaster recovery. In the event of a local database outage, you can run your SQL Server databases from Windows Azure VMs. The new Windows Azure AlwaysOn availability options are fully integrated into SQL Server Management Studio (SSMS).

Enhancements to Backups

Database backups in SQL Server now support built-in database encryption. Previous releases all required a third-party product to encrypt database backups. The backup encryption process uses either a certificate or an asymmetric key to encrypt the data. The supported backup encryption algorithms are Advanced Encryption Standard (AES) 128, AES 192, AES 256, and Triple DES (3DES).

SQL Server 2014 also provides new Windows Azure integration to SQL Server's backup capabilities. You can specify a Windows Azure URL as the target for your SQL Server 2014 database backups. This new Windows Azure backup feature is fully integrated into SSMS.

Updateable Columnstore Indexes

Columnstore indexes are another of Microsoft's high performance in-memory technologies. Microsoft introduced the columnstore index in SQL Server 2012 to provide significantly improved performance for data warehousing types of queries. Microsoft states that for some types of queries, columnstore indexes can provide up to 10x performance improvements. However, in the original implementation of the columnstore indexes, the underlying table had to be read-only. SQL Server 2014 eliminates this restriction. The new updateable columnstore index enables updates to be performed to the underlying table without first needing to drop the columnstore index. A SQL Server 2014 columnstore index must use all of the columns in the table, and it can't be combined with other indexes.

SQL Server Data Tools for Business Intelligence

Previously known as Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT), the new SQL Server Data Tools for BI (SSDT BI) is used to create SQL Server Analysis Services (SSAS) models, SQL Server Reporting Services (SSRS) reports, and SQL Server Integration Services (SSIS) packages. SSDT BI is based on Microsoft Visual Studio 2012 and 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 Community Technology Preview 2 (CTP2), SQL Server Setup doesn't install SSDT BI. Instead, you must download it separately from the Microsoft Download Center.

Power BI for Office 365 Integration

Power BI for Office 365 is Microsoft's cloud-based BI solution that leverages familiar Office 365 and Excel tools. Power BI for Office 365 provides business insights through data visualization and navigation capabilities. Power BI for Office 365 includes:

Power Pivot (formerly PowerPivot). This Excel 2010 and Excel 2013 ProPlus add-in enables Excel to perform data analysis on large volumes of data.

Power View. This Excel 2013 ProPlus add-in provides a Silverlight-based data visualization and navigation tool. Microsoft has extended Power View so that you can now use it with multidimensional models (OLAP cubes). Power View multidimensional models also support queries using Data Analysis Expressions (DAX). Power View's data visualization capabilities have also been enhanced. Power View now supports a number of data visualizations, including tables, matrixes, bubble charts, and geographical maps. To learn more about Power View's new multidimensional support, go to MSDN's Power View for Multidimensional Models web page.

Power Query (formerly code-named Data Explorer). This Excel 2013 add-in lets you discover and integrate data into Excel. It supports SQL Server data sources as well as external sources such as Windows Azure, text files, XML files, Open Data Protocol (OData) feeds, web pages, Hadoop data sets, and public data sets (e.g., Open Government data from data.gov).

Power Map (formerly code-named GeoFlow). This Excel 2013 ProPlus add-in provides 3D mapping visualizations.

As you can see, several of the Power BI for Office 365 components require Excel 2013, which must be acquired separately. You can learn more about Microsoft's Power BI for Office 365 tools at Microsoft's Power BI for Office 365 website.

Unchanged Subsystems and Discontinued Features

Like you might expect after such a short release cycle, not every subsystem has been updated. There are no major changes to SQL Server 2014's replication, SSIS, or SSRS.

There are several older and outdated capabilities that Microsoft is dropping from the SQL Server 2014 release. To see a list of the features and functionality that Microsoft is dropping from SQL Server 2014, check out MSDN's Deprecated Database Engine Features in SQL Server 2014 and Discontinued Database Engine Functionality in SQL Server 2014 web pages.

Time to Upgrade?

Upgrading to a new release is usually a difficult decision for most organizations. However, SQL Server 2014's new In-Memory OLTP engine, with its promise of significantly improved application performance, offers a very compelling reason to upgrade for customers using SQL Server to support OLTP applications. One great way to find out the type of performance improvement that you might get out of SQL Server 2014's In-Memory OLTP capability is to download and install the SQL Server 2014 Evolution Edition and use the AMR tool to analyze your production workload. The AMR tool supports collecting data on SQL Server 2008 and later instances. This will give you a good idea of the type of performance improvements that you might expect to get using the new In-Memory OLTP engine as well as the changes that you might need to make to implement it. SQL Server 2014's In-Memory OLTP support promises to boost your database application performance to the next level.

SQL Server 2014 Hits General Availability—No April Fools

Yesterday, April 1st 2014, Microsoft made the new SQL Server 2014 release generally available. The biggest news is the addition of the new high performance In-Memory OLTPwhich is available in the SQL Server 2014 Enterprise edition. In addition, the new release supports encrypted backup as well as backup and AlwaysOn integration with Azure.

The new SQL Server 2014 family consist of:

•             SQL Server 2014 Enterprise Edition

•             SQL Server 2014 Business Intelligence Edition

•             SQL Server 2014 Standard Edition

•             SQL Server 2014 Express Edition

 You can find a detailed description of the differences in the features supported in each edition at Features Supported by the Editions of SQL Server 2014.

Official SQL Server Blog Announcement: SQL Server 2014 now Generally Available

 Apart from these mainstream editions Microsoft also offers:

•             SQL Server 2014 Developer Edition

•             SQL Server 2014 Evaluation Edition

The SQL Server 2014 Developer Edition and Evaluation Edition both offer the same feature set as the SQL Server 2014 Enterprise Editions. The Developer edition is licensed per single developer while the Evaluation edition provides a 180-day trial.