Microsoft released the latest version of SQL Server – SQL Server 2014 – to the world in April, 2014. As usual, it comes with a lot of new features that are designed to give developers and administrators better performance, security, and manageability. Here, we’re going to focus on just five of the major areas of improvement: in-memory OLTP, improvements to Always On, new Hybrid Cloud features, the new Buffer Pool, and better security through separation of duties between administrators and developers.
Hekaton – In-Memory OLTP and Native Compiled Stored Procedures
Let’s start with the most impressive new feature in SQL Server 2014, taken from a Microsoft Research project called Hekaton: in-memory OLTP. With in-memory OLTP, a database administrator can identify tables that are to run entirely in-memory, removing the traditional code paths used for paging data into and out of disk storage. With Hekaton, all of the indexes and data for a table is kept in memory, giving up to a 30x – not 30%, 30 times – improvement in performance. As long as your SQL Server has enough RAM, all of the processing for important tables – or even an entire database – can take place in memory, with lazy writes back to disk for persistence.
As a SQL Server DBA for almost twenty years, I have to say that I haven’t been this excited about a new feature – ever. It’s jaw-droppingly fast. Even if your server had enough RAM to hold your entire database in cache before, Hekaton still runs much faster with its new locking algorithms and lack of disk/memory paging.
In addition to the in-memory OLTP features, Hekaton also brings native-compiled stored procedures to SQL Server 2014 when running against memory-optimized tables. This means that your stored procedures are read and compiled using parts of the Visual C++ compiler, generating native .DLL’s that are invoked as if they had been written in C. These new native compiled stored procedures can give a 2-3x improvement in performance over interpreted SQL.
Always On Improvements
Always On is a marketing name for a set of features introduced in SQL Server 2012 that allow SQL Server to keep running across multiple servers in the event one fails or needs to be taken out of service for maintenance. SQL Server 2014 improves on Always On Availability Groups in a number of ways.
First, the old limit of four read-only replicas has been increased to eight. By having more read-only replicas of your database, you can improve availability while shifting read-intensive workloads off to those replicas. For instance, you might have your master database running a standard OLTP workload, with read-only replicas handling tasks like reporting and backup.
Next, SQL Server 2014 allows read-only processing to continue using the replicas in an Availability Group, even if the quorum managing the read/write version of the database fails. By decomposing the functionality of your application into read-write and read-only features, you can enable a degree of functioning for your users even while the main database is offline.
Hybrid Cloud Features
Continuing the high-availability theme, SQL Server allows your on-premise databases to create secondary replicas on SQL Server 2014 instances running in Azure Virtual Machines. This gives you the ability to do a manual fail-over to Azure in the event that your main database and replicas are completely unavailable in a disaster scenario.
Additionally, if you run your main database in an Azure Virtual Machine, you can create your primary replicas in another Azure VM and have them automatically fail over to each other.
One other exciting new feature in SQL Server 2014 is the built-in ability to do database backups directly to Azure Blob Storage. With this feature, the days of running on-premise backups and shipping tapes offsite are over. For older versions of SQL Server, Microsoft is shipping a file monitor service that watches for your backups to be run and then automatically uploads them to Azure Blob Storage. With your backups already in Blob Storage, SQL Server disaster recovery in Azure is as simple as standing up a new Virtual Machine and running a database restore.
Buffer Pool Extensions
SQL Server has always used memory as a cache for data and stored procedures to improve performance; technically, this is known as the buffer pool. With larger data sets, the buffer pool can run out, and SQL Server will evict memory pages from the pool as it needs to for more-recently-touched data. In SQL Server 2014, we can use a fast SSD drive as a buffer pool extension (BPE). This extension is used only when main server memory starts to run out, and provides faster-than-spinning-disk-I/O access to data that’s already been read from disk. This provides improved random I/O throughput, reduced latency, and a new caching architecture that enables us to take advantage of low-cost SSD drives to expand available cache, instead of just adding RAM.
Separation of Duties
SQL Server 2014 comes with a new ability to create roles and sub-roles for administration and operations staff that provides granular control over what a DBA has access to do. In the past, most operational functions required sysadmin-level rights, meaning that a DBA could not only perform administrative tasks like installing servers, creating databases and availability groups, and running backups, but also had rights to see all of the data on those servers. With new Separation of Duties features, roles can be created for operations staff that enable those server-level and database-level tasks while providing no rights to read or modify any of the data contained in them. Although security logs were available already to audit this kind of data access, these new features can guarantee that staff only has access to data when they need it.
There’s Much More
Although I’ve given an overview of what I think are the most significant improvements, this is hardly a comprehensive look at what’s new in SQL Server 2014. There are improvements to the overall data story, including data mining, big data, and Polybase – SQL Server / Hadoop integration. Microsoft offers a 180-day free trial when you’re ready to try it for yourselves.