Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 4: Reach insights faster by running analytics at the point of creation
This is the fourth post in a five-part blog series. Keep an eye out for upcoming posts and catch up on the first, second, and third in the series.
In addition, join us for Microsoft Data Amp on April 19 at 8 AM PT. The online event will showcase how data is the nexus between application innovation and artificial intelligence. You’ll learn how data and analytics powered by the most trusted and intelligent cloud can help companies differentiate and out-innovate their competition. Microsoft Data Amp—where data gets to work.
“Data! Data! Data! … I can’t make bricks without clay!” – Sherlock Holmes (Sir Arthur Conan Doyle)
If he lived today, Sherlock Holmes might be a data scientist, working to solve cases faster by using advanced analytics to augment his legendary deductive powers. And Sherlock would insist on the fastest means possible for reaching insights. So what’s the best way to process massive amounts of data quickly to get faster time to insight?
It’s elementary: Sherlock would deduce that SQL Server 2016 and Windows Server 2016 are an exceptional platform for delivering built-in fast analytics by running queries at the point of creation.
At the OS level, Windows Server 2016 delivers new levels of performance with capabilities such as Persistent Memory (or Storage Class Memory), which improves latency by 3x, and Storage Spaces Direct, which gives you highly available, scalable storage area network functionality on inexpensive industry-standard servers and produces read speeds that can exceed 25 GB per second.
These features are built into the OS, so no additional licenses are required. For full details on Windows Server 2016 price/performance benefits, read the blog post “Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost.”
In addition, at the data platform level, SQL Server 2016 delivers innovative analytics tools such as SQL Server R Services, real-time operational analytics, and new R-models.
Combined, SQL Server 2016 and Windows Server 2016 provide multithreading and massively parallel processing for high-performance data analysis.
SQL Server R Services built into T-SQL
R is a respected data-mining tool for uncovering insights and making predictions. SQL Server R Services is built into T-SQL and brings advanced predictive analytics to the data.
As a SQL Server 2016 data professional, you probably use T-SQL daily. Now, you can take advantage of R through the T-SQL interface. With R Services support for in-database analytics, you can work with data in SQL Server 2016, and applications can use T-SQL system stored procedures to call R scripts. If you’re an application developer, you don’t need to deep dive into R. You can rely on the T-SQL API for such tasks as creating SQL Server Reporting Services reports or Power BI dashboards with scores, predictions, and visuals from R.
You also get SQL Server built-in functions and mechanisms to accelerate performance and integration. For example, you can use columnstore indexes with R for faster queries. Built-in resource governance can control the resources allocated to the R runtime. The stored procedure interface gives you smooth integration with SQL Server Integration Services for integration with common extract, transform, and load and job scheduling. Learn more about SQL Server 2016 R Services and read about a real-world implementation.
Real-time operational analytics
With SQL Server 2016, you can do real-time operational analytics in two ways: on disk-based and memory-optimized tables. This means you don’t have to make changes to your applications when you perform real-time analytics.
SQL Server 2016 Real-Time Operational Analytics lets you use columnstore indexes to run analytics queries directly on your operational workload. Figure 1 shows a possible configuration, which uses Analysis Server in Direct Query mode, but if you have other analytics tools or a custom solution, you can use those, too. When you use both memory-optimized and columnstore, you get the best of online transaction processing performance and analytics query performance. Learn more about real-time operational analytics using in-memory technology.
Figure 1: A real-time operational analytics example
Multithreading and massive parallel processing
The cloud is built into SQL Server 2016, making it possible for you to take advantage of multithreading and massive parallel processing (MPP) to achieve high-performance data analysis. Azure SQL Data Warehouse uses an elastic MPP architecture built on the SQL Server 2016 database engine. This means you can continue using the SQL Server-based tools and BI applications that you use today when you want to interactively query and analyze data. Azure SQL Data Warehouse has built-in performance analytics and storage compression, the aggregation capabilities of SQL Server, and cutting-edge query optimization capabilities. In addition, with Polybase built in, you can query Hadoop systems directly, so that you have a single SQL-based query surface for all your data.
New R-models
SQL Server and Windows Server come with built-in functionality to make your job easier. In addition, Azure data services provides access to the work data scientists have already done by creating R-models that you can use. You can find a model you need in the Azure marketplace.
The Cortana Intelligence Solutions Gallery is the Microsoft Data Science VM that comes loaded with all the tools a data scientist needs. You can also find the code on GitHub, so you can run it locally on your own machine.
To learn more, see “Cortana Intelligence and Machine Learning Blog: Using SQL Server 2016 with R Services for Campaign Optimization”.
SQL Server 2016 and Windows Server 2016: It’s all built in
The role of data in business decision-making is taking on ever greater importance, and the difference between success and failure can hinge on how fast you’re able to analyze data. As a result, business intelligence and advanced analytics are changing the very nature of business. By examining all the built-in capabilities, surely Sherlock Holmes would deduce that with the combination of SQL Server 2016, Windows Server 2016, and Azure, you can make sure your business is equipped for success. For an overview of SQL Server 2016 advanced analytics and business intelligence, see “Decisions @ the speed of thought with SQL Server 2016.” For details on price/performance, see “Five reasons to run SQL Server 2016 on Windows Server 2016 – No. 2: Performance and cost.”
