·
8 min read

SQL Server 2016 is generally available today

This post was authored by Joseph Sirosh, Corporate Vice President, Data Group, Microsoft.

Today we announced the general availability of SQL Server 2016, the world’s fastest and most price-performant database for HTAP (Hybrid Transactional and Analytical Processing) with updateable, in-memory columnstores and advanced analytics through deep integration with R Services. Software applications can now deploy sophisticated analytics and machine learning models in the database resulting in 100x or more speedup in time to insight, compared to deployments of such models outside of the database.

A new platform for intelligent applications

The integration of advanced analytics into a transactional database is revolutionary. Today a majority of advanced analytic applications use a primitive approach of moving data from databases into the application tier to derive intelligence. This approach incurs high latency because of data movement, doesn’t scale as data volumes grow and burdens the application tier with the task of managing and maintaining analytical models. And deep analytics on real-time transactions are next to impossible without a lot of heavy lifting.

SQL Server 2016 simplifies analytics in the way databases simplified enterprise data management, by moving analytics close to where the data is managed instead of the other way around. It introduces a new paradigm where all joins, aggregations and machine learning are performed securely within the database itself without moving the data out, thereby enabling analytics on real-time transactions with great speed and parallelism. As a result, analytical applications can now be far simpler and need only query the database for analytic results. Updating machine learning models, deploying new models, and monitoring their performance can now be done in the database without recompiling and redeploying applications. Furthermore, the database can serve as a central server for the enterprise’s analytical models and multiple intelligent applications can leverage the same models. It is a profound simplification in how mission critical intelligent applications can be built and managed in the enterprise.

A good example of how our customers are benefiting from the new model comes from PROS Holdings, Inc., a revenue and profit realization company that helps B2B and B2C customers achieve their business goals through data science. PROS Holdings uses SQL Server 2016’s superior performance and built-in R Service to deliver advanced analytics more than 100x faster than before, resulting in higher profits for their customers. Royce Kallesen, senior director of science and research at PROS says, “Microsoft R’s parallelization and enhanced memory management on the server integrated with SQL Server provides dramatically faster results on a common platform with built-in security.”

Eliminating the need to move data out of the database for analytics dramatically reduces the latency for insights. For example, Microsoft Dynamics AX, a cloud-scale online ERP offering, gains real-time insights by using a non-clustered columnstore index on their transactional tables to reduce aggregation latency from hours to seconds.

SQL Server 2016 comes with several features and tools to support cross-platform analytics. Polybase allows you to run queries on external data in Hadoop or Azure blob storage. It can push computation to Hadoop where appropriate, so that your analytical application can join and integrate data from big data stores with the data in the relational store. Microsoft R Services, which is integrated with SQL Server also runs on multiple Hadoop distributions and is also integrated with Azure HDInsight + Spark, enabling both choice and standardization in developing analytics code. And finally, R Tools for Visual Studio allows the ease of use of the modern Visual Studio IDE for developing analytical code in R.

Benchmark leadership through efficient use of modern hardware

SQL Server 2016 has ground-breaking performance optimizations and efficiencies, leading to new levels of performance and scale. Modern servers can support a large number of cores with sophisticated vector instructions, can hold terabytes of memory, and provide very high I/O bandwidth with local flash storage. Optimizing for the concurrency and parallelism inherent within such servers can provide dramatic speedups at scale, and often outperform large distributed databases.

For example, Microsoft recently collaborated with Intel to demonstrate stunning performance on a massive 100TB data warehouse using just a single server with four Intel Xeon E7 processors and SQL Server 2016. The system was able to load a complex schema derived from TPC-H at 1.6TB/hour, and it took just 5.3 seconds to run a complex query (the minimum cost supplier query) on the entire 100TB database. The system also demonstrated incredible concurrent query performance, where running all queries concurrently took less time than running them back-to-back, as illustrated in the chart below. This is a feat of scale, performance and efficiency that no other database has achieved to date.

Intel100TB_Concurrency

Industry standard database benchmarks further support SQL Server’s performance leadership. It is the only database that is simultaneously the leader in the industry standard transaction processing benchmark TPC-E, and the data warehouse benchmark TPC-H (non-clustered TPC-H 30TB, 10TB, 3TB, 1TB scale factors). For customers, this means incredible value compared to other databases, and the opportunity to consolidate both OLTP and Data Warehouse workloads into a simpler single system, with significant savings in total cost of ownership.

Large performance improvements from prior versions

Our focus for SQL Server 2016 was to deliver great value for our customers with dramatic improvements while keeping the price the same. Simply by upgrading to SQL 2016, many customer workloads experienced large performance gains. For example, the 3TB TPC-H benchmark on the same hardware demonstrated a forty-eight percent performance gain1.

Our customers and partners have realized great benefits by upgrading. Some examples:

RealtyTrac, a provider of housing data and analytics, used an Always-On availability group spanning on-premises and Microsoft Azure to provide high availability and disaster recovery for their data warehousing. By offloading queries to the secondary replicas, RealtyTrac achieved huge performance improvements: “We saw up to ten times speed improvement on some of the extraction processes. SQL Server 2016 in a hybrid-cloud environment with Azure really cuts through all the logistics of managing large data sets,” said Richard Sawicky, chief data officer, RealtyTrac.

Saxo Bank is one of the leading players in global online trading. SQL Server 2016 will enable the bank to use enhanced columnstore on a wider array of tables for even faster analysis. “Using clustered columnstore indexes created huge savings in storage, allowing us to use up to eighty percent less disk space for tables. With SQL Server 2016, we have seen queries running up to two times faster on top of the 10x query performance gains from adopting columnstore” said Francesco L’Erario, Data Warehouse Architect, Saxo Bank.

M-Files, a popular SaaS EIM vendor, has its most valuable data in the SQL Server database. “Real-time operational analytics in SQL Server 2016 lets us run the analytical queries from the same data source. We experienced an immediate 10x boost in our most critical queries, with virtually no impact on the operational workload. Now we have our ‘analytics database’ in the single data source, with ground-breaking performance, automatically updated by SQL Server within the same transaction boundaries as our operational workload. It’s an unbelievable advantage to us,” said Antti Nivala, CTO and founder of M-Files.

SQL Server 2016 features were first released in Microsoft Azure and stress-tested in real-world through over 1.7 million Azure SQL DB databases. It is battle-tested and dozens of our customers have already been running on-premises production workloads on SQL Server 2016 prior to its general availability, a strong testament to its maturity as a product.

Unmatched security record

Solid security is an essential requirement for any business application. Databases contain a significant portion of sensitive corporate data and intellectual property. If database security is compromised, the entire business is potentially at risk.

For six years running2, SQL Server has had the least vulnerabilities of any of the major database platforms, according to the statistics compiled independently by the National Institute of Standards and Technology (NIST), the government agency that monitors security vulnerabilities by technology, vendor, and product. Now SQL Server 2016  takes security capabilities even further: Always Encrypted helps protect data at rest and in memory, Transparent Data Encryption encrypts all user data now with low performance overhead, and Dynamic Data Masking (DDM) and Row Level Security (RLS) allow developers to build applications that restrict access and protect data.

For example, DocuSign helps organizations build entire approval workflows without a single sheet of paper or filing cabinet in sight, so security and reliability are critical, as they are with every business today. Using SQL Server 2016 for internal telemetry and monitoring, DocuSign monitors customer experiences across the globe to identify and address issues quickly. DocuSign customers rely on them for mission critical workflows and DocuSign relies on SQL Server for their entire business. “We’ve picked SQL Server for our platform, because of its performance and ability to manage all types of data. With the largest banks, insurers and other financial institutions counting on us, we need to deliver secure software for our environment. New features in SQL Server 2016 like Always Encrypted allow us to deliver on this promise,” says Eric Fleischman, chief architect and vice president of platforms.

Unmatched value proposition

SQL Server 2016 ships as a comprehensive data platform with everything built-in. The powerful database engine with in-memory capabilities also includes unique features such as JSON support for building modern apps, Stretch Database for reducing storage costs while keeping data available for querying in Azure, significantly faster Geospatial query support, Temporal Tables for traveling back in time and a Query Store for ensuring performance consistency. We bundle in powerful data integration capabilities with SQL Server Integration Services (SSIS), Data Quality Services (DQS) and Master Data Services (MDS). We provide world-leading data warehousing capabilities, closely integrated with advanced analytics and machine learning through R. We provide a world-leading Business Intelligence engine with Tabular and Multidimensional models. We deliver end-to-end mobile BI solution on any device and you can add on self-service Power BI for a fraction of the total cost. The table below shows a simple competitive comparison that highlights the value of the SQL Server package.

TCO

Today customers can deliver intelligent applications that are faster performing, more secure than Oracle, and at less than one-tenth of the total cost of using Oracle to run the same transactional, data warehouse, data integration, business intelligence and advanced analytics workloads3. With our ongoing promotion to help more customers adopt SQL Server 2016, customers currently running applications or workloads on a non-Microsoft paid commercial RDBMS platform can migrate their existing applications with free SQL Server licenses.

The quickest way to experience the magic of SQL Server 2016 is to create a test environment using an Azure SQL VM. You can also experience the full features through the free developer edition (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2016 Developer Edition). Visit the SQL Server 2016 webpage to gain insights about new features and download the SQL Server 2016 e-book.

With new capabilities for analytics, record-breaking performance, in-memory processing, unmatched security and an unbeatable value proposition, SQL Server 2016 is the best data platform available to gather and manage data, apply analytics that generate deep insights, and translate those insights into mission critical production. Adopt it today to transform your business with data driven intelligence.

–Joseph

1Comparison of TPC-H 3TB benchmark created using SQL Server 2016 (http://www.tpc.org/3322) to TPC-H 3 TB benchmark created using SQL Server 2014 (http://www.tpc.org/3315) on the same Cisco UCS C460 M4 Server

2National Institute of Standards and Technology, National Vulnerability Database statistics as of 2/1/2016, https://nvd.nist.gov/

3http://www.oracle.com/us/corporate/pricing/technology-price-list-070617.pdf