SQL-Server 2014 New Features of Interest : Newest Microsoft Database / RDMS

Microsoft SQL-Server 2014 New Features

SQL-SERVER 2014 UPGRADES AND ENHANCEMENTS OF NOTE

It seems like the pace of SQL-Server releases is picking up! SQL-Server 2014 CTP1 is just now available for download and it does not seem like that long since I wrote my blog about the previous version and SQL Server 2012 New Features. In general, I think I was more excited about SQL 2012 than I am about SQL-Server 2014, since there were more Transact SQL enhancements in that version, and I tend to focus quite a bit on programming stored procedures and user-defined functions.

But, SQL-Server 2014 definitely has some nice new features that deserve looking at, especially when it comes to "cloud" deployment on Microsoft Azure.

SQL Server 2014 New and Improved Features / Enhancements:

• Microsoft's codename "Hekaton" in-memory OLTP engine.
• Column store (columnstore in-memory indexes from SQL 2012) become update-able in SQL2014 by way of updateable clustered columnstore indexes — there are some limitations and restrictions (compared to non-updateable, non-clustered version), but these new indexes should lead to faster query speeds and greater data compression for certain data-warehousing and real-time analytics needs.
• Buffer-pool-extension support for solid-state drives — enabling faster paging by extending the SQL Server in-memory buffer pool to SSDs.
• Windows Azure — automatic or manual back-ups to Azure will allow you to keep your on-site data backed up to "the cloud" at a DB-instance-level (for disaster-recovery); restore to an Azure VM if needed too. Also, a new SSMS Migration Wizard for Windows Azure Infrastructure Services makes migrating your on-site SQL-Server to "the cloud" much simpler.
• SQL 2012 "AlwaysOn" technology extended — SQL Server 2014 gets more "mission critical" availability with up to 8 readable secondaries and no downtime during online indexing operations; in addition, a new SSMS Wizard helps you deploy AlwaysOn secondaries to a Window Azure VM.
• When used with Windows Server 2012 R2, Greater scalability of compute, networking and storage — including: Scaling up to 640 logical processors and 4TB of memory in a physical environment and up to 64 virtual processors and 1TB of memory per VM; Network Virtualization that abstracts networking layer allowing easy migration of SQL Server from one data-center to another; Storage Virtualization with Storage Spaces where you create pools of storage and storage tiers to prioritize "hot" data to access premium storage (e.g., SSDs) and "cold" data to access standard storage.
• New security features — these enhancements should simplify administration, auditing, and some application requirements: new CONNECT ANY DATABASE Permission, IMPERSONATE ANY LOGIN Permission, SELECT ALL USER SECURABLES Permission, and ALTER ANY DATABASE EVENT SESSION Permissions;

Transact-SQL (T-SQL) improvements / enhancements:

• The SELECT … INTO statement is improved and can now operate in parallel;
• Compiled Transact SQL (compiled T-SQL) Stored Procedures using "WITH NATIVE_COMPILATION" directive — Expensive T-SQL stored procedures (SPs) that reference only In-memory OLTP ("Hekaton") tables can be natively compiled into machine code for further performance improvements. There are some additional requirements for compiling T-SQL that include:
  • Native compiled stored procedures must be schema-bound (use SCHEMABINDING directive);
  • Execution context is required (e.g., "EXECUTE AS OWNER");
  • Must be in an atomic block context — BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') ... statements here ... END;
  • NOTE: While these "native compiled" stored procedures are written using Transact-SQL, they do not support the full Transact-SQL surface area.

SQL-SERVER 2014 : Initial Thoughts

When I first read about "compiled T-SQL procedures", I was excited, but... that quickly faded as I learned about all the limitations.  If I am correct (I think I am), these new compiled stored procedures are only available for the "Hekaton" related data, and even then it sure sounds like further limitations will abound.  I need more time to play with this feature.

Next, I think the simplified deployment to Azure makes sense (certainly for Microsoft, as they can sell you yet another product / service), but it seems a bit much for me to pay for another software upgrade to get feature(s) that enable me to spend more money on their other products.  

Bottom line: I think the reason SQL-Server 2014 showed up on the scene so quickly (relative to the release-cycle windows between SQL2005, 2008, and 2012) has a lot to do with Microsoft freaking out about Amazon and other "cloud" providers landing hosting gigs for SQL-Server databases on a platform they do not own or collect subscriber-cash from.  So, Microsoft had to throw together whatever it could for a new "SQL 2014" release and get it out there to encourage users to adopt Azure as their preferred "cloud" deployment option for SQL-Server.

The new features that accompany SQL2014 pretty much all fit with this analysis.  Sure, the "compiled stored procedures" may not be 100% to do with Azure, but I cannot help wondering if the reason they only work with Hekaton stuff is that they are really "half baked" (and were perhaps intended to apply to all procedures, but there was no "time" to make that happen while rushing to shore-up Azure sales).  Just my thoughts.

Continue to read this Software Development and Technology Blog for computer programming articles (including useful free / OSS source-code and algorithms), software development insights, and technology Techniques, How-To's, Fixes, Reviews, and News — focused on Dart Language, SQL Server, Delphi, Nvidia CUDA, VMware, TypeScript, SVG, other technology tips and how-to's, plus my varied political and economic opinions.