Review: Microsoft SQL Server 2014
Page Contents
Microsoft releases new versions of its main products on a relatively predictable schedule. SQL Server releases or major updates have appeared at roughly two-year intervals going back to SQL Server 2008. A bit of a naming anomaly occurred with SQL Server 2008 R2, but that name coincided with Windows Server 2008 R2 which was released around the same time.
For most enterprise organizations upgrades of existing production applications and the underlying database application is typically driven by cost. Unless there's a compelling reason such as discontinued support or a significant increase in performance, the impetus to upgrade just isn't there.
That brings us to Microsoft's current release, SQL Server 2014. This release of SQL Server includes the technology known as Hekaton, which was developed by Microsoft Research. Hekaton is essentially an in-memory database optimized for online transaction processing (OLTP). Specifically, it implements memory-optimized tables and natively compiled stored procedures to increase the overall performance of the database. Determining the merits of these new capabilities and the benefits they might bring to an enterprise application will be a task worth the effort to undertake.
SQL Server Performance Improvements
Making things go faster seems to be an important part of every new product release, whether it is an operating system or a database server. SQL Server 2014 addresses performance on a number of different fronts. In addition to the in-memory work already mentioned, a number of areas demonstrate improvement for both performance and high availability.
Clustering is Microsoft's primary mechanism for providing highly available resources from Hyper-V virtual machines to storage using Cluster Shared Volumes (CSV). Windows Server 2012 introduced CSV based on the SMB 3.0 protocol, bringing a significant boost in performance and resiliency to file-based storage. Prior to SQL Server 2014, you had to use an iSCSI LUN for each SQL instance in order to connect to a clustered storage device. Another previous cluster limitation was due to drive letter limitations, meaning you were restricted to a maximum of 24 instances per cluster. This limitation is also lifted when deploying SQL Server 2014 using CSV, as the number of cluster mount points is unlimited.
One of the most common things you do with a database is a query. Cardinality Estimation is an algorithm used to optimize queries against large OLTP or data warehousing workloads. SQL Server 2014 includes a re-designed and tunable algorithm to help improve query performance using this approach. Other query improvements include the ability to use clustered columnstore indices for both resilience and performance purposes.
Cloud Integration via Azure
Microsoft Azure provides a SQL Server offering which makes it possible to create a hybrid on/off-premises database solution. SQL Server 2014 contains new wizards in SQL Server Management Studio, including Deploy Database to a Windows Azure VM and Deploy Database to Windows Azure SQL, to make the integration part easier.
Speaking of SQL Server Management Studio (see Figure 1), it includes other new features and improvements to make the database administrator more productive including an integrated script editor with a new interface for viewing XML results and integrated source control for solution and script projects.
While backing up a database might not be the most glamorous feature, it is an important one. SQL Server 2014 provides a number of new enhancements tied to the Azure cloud to make protecting your data even easier. SQL Server 2014 now supports the native use of an Azure Blob storage service as a target for backup job. You can also use a URL from within SQL Server Management Studio whereas previously you had to use a command line utility.
Another new feature in the 2014 release is the ability to encrypt a backup using industry-standard algorithms such as AES 128, AES 192, AES256 and Triple DES.
Extending Business Intelligence Capabilities
Cloud integration extends to the Office suite in the form of Power BI for Office 365. This new solution extends the existing self-service business intelligence (BI) capabilities currently available in Excel to Office 365. Now users can share their work to the cloud using a secure connection to data located either in Azure or on-premises. This feature also utilizes SharePoint for use as a document repository for reports and graphs created with Power BI for Office 365.
Big Data and Business Intelligence Tools
Dealing with Big Data is a prescient issue on the minds of IT administrators and C-level executives alike. Making use of the data locked inside corporate enterprise resource planning (ERP) applications and, ultimately, the databases storing the information can provide strategic advantages to a wide range of industries. Microsoft looks to provide new capabilities for both the storage and the processing of Big Data using the Azure cloud platform and SQL Server 2014.
Microsoft has made significant strides in providing tools to unlock the data hidden in SQL databases and continues to deliver new features related to business intelligence. SQL Server 2014 includes features to interoperate with Big Data sources such as Microsoft's HDInsight, a Hadoop-based distributed processing implementation running on Microsoft Azure.
SQL Server Management Studio for Business Intelligence provides the interface for developing scripts using Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and XML for Analysis (XMLA). Individual scripts can be combined to build an analysis solution to ultimately produce results in the forms of graphical charts or reports. Results can also be processed using the new Power BI for Office 365.
The SQL Server Data Tools (SSDT) work from within Microsoft's Visual Studio integrated development environment (IDE) to support database development projects. Versions of Visual Studio prior to the 2013 release required an external installer, while the latest release has them built in. You may, however, need to run an update (see Figure 2) to get the most up-to-date version.
Final Analysis of SQL Server 2014
Deciding on an upgrade is not an easy decision and depends greatly on each situation. Benefits such as the cloud integration do not exist in previous versions, so any project with an eye on the cloud will have to use SQL Server 2014 to take full advantage of these features.
Most organizations will use pilot projects running in parallel with existing implementations to measure performance gains. Running multiple instances of SQL Server at different version levels is not the most optimum configuration from an administrative and management perspective, but it can be done. The final answer for most organizations will undoubtedly be “it depends.”
The best way to learn is to download a trial version and check it out.
Paul Ferrill has been writing in the IT trade press for over 25 years. He's written hundreds of articles for publications like Datamation, Federal Computer Week, InfoWorld, Network Computing, Network World and PC Magazine and is the author of two books. He is a regular contributor to ServerWatch.com and several other QuinStreet Enterprise properties.
Paul Ferrill has been writing for over 15 years about computers and network technology. He holds a BS in Electrical Engineering as well as a MS in Electrical Engineering. He is a regular contributor to the computer trade press. He has a specialization in complex data analysis and storage. He has written hundreds of articles and two books for various outlets over the years. His articles have appeared in Enterprise Apps Today and InfoWorld, Network World, PC Magazine, Forbes, and many other publications.
