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:
1. Microsoft's codename "Hekaton" in-memory OLTP engine.
2. 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.
3. Buffer-pool-extension support for solid-state drives -- enabling faster paging by extending the SQL Server in-memory buffer pool to SSDs.
4. 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.
5. 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.
6. 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.
7. 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:
1. Native compiled stored procedures must be schema-bound (use SCHEMABINDING directive);
2. Execution context is required (e.g., "EXECUTE AS OWNER");
3. Must be in an atomic block context -- BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') ... statements here ... END;
4. NOTE: While these "native compiled" stored procedures are written using Transact-SQL, they do not support the full Transact-SQL surface area.
There are currently no comments on this post. Be the first one!