SQL Server 2014 is now generally available, with support for in-memory OLTP, and integration with Azure.
There are several editions of SQL Server. Alongside Enterprise are Business Intelligence, Standard, Web, Developer and Express. Developer and Express are both free.
Microsoft is referring to SQL Server as “the foundation of our cloud-first data platform”.
In practical terms, this means hybrid features that work both on a customer’s premises, and in the cloud, so you can choose how to deploy a database.
For example, you can back up and recover an on-site database using Azure. You can also use Azure for disaster recovery; full SQL Server can run in an Azure VM, and is simple to set up. The new release includes a Windows Azure virtual machine image as part of general availability, and the AlwaysOn Availability Groups feature has been improved with support for additional secondary replicas and Windows Azure integration. This lets you create asynchronous availability group replicas in Windows Azure for disaster recovery.
For many companies the in-memory OLTP is more important. This offers high performance, although only in the Enterprise Edition. It is the release version of Hekaton, and lets you move specific tables and stored procedures into memory to minimize I/O overheads and improve the performance of your OLTP apps. Microsoft says some applications can expect to run up to twenty times faster.
A new Analysis Migrate and Report (AMR) tool is designed to help you make use of the in-memory OLTP engine. The tool analyzes your database and suggests tables and stored procedures that might be able to make use of the new operating model. It also points out any problems you need to address first, and will help you migrate the tables to the new memory-optimized format.
Another change SQL Server 2014 is support for up to 640 logical processors and 4TB of memory in a physical environment, and up to 64 virtual processors and 1TB of memory on a virtual machine. You can also now use solid state disks to expand the buffer pool.
There’s also support for Windows Server 2012 Storage Spaces, which let you create pools of tiered storage to improve application availability and performance. The changes to Windows Server 2012’s Server Message Block (SMB) 3.0 are also supported, including the SMB Transparent Failover and SMB Direct.
The BI aspects of SQL Server have also been improved. Data Tools for Business Intelligence are 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. The database also supports Power BI for Office 365, with Power Pivot and Power View for data analysis and visualization.
Power View has been extended to provide support for multidimensional models, and for queries written using Data Analysis Expressions (DAX). A new Excel add-in, Power Query, lets you integrate data into Excel, and Power Map provides 3D mapping.
It is a little known fact that GPS can provide much more accurate positioning, although only if you provide a big enough antenna. This isn't a reasonable proposition for most applications, but now we [ ... ]