19 January 2016

SQL Server 2016 for real time operational analytics

SQL Server, an industry leader, now packs an even bigger punch

With the upcoming release of SQL Server 2016, our best SQL Server release in history, and the recent availability of the Cortana Analytics Suite, Microsoft is offering unmatched innovation across on-premises and the cloud to help you turn data into intelligent action.

What's New in SQL Server 2016 with Greg Burns - Alaska SQL User Group

In the recent Gartner Magic Quadrant for Operational Database Management Systems Microsoft is positioned as a leader, highest in execution and furthest in vision. SQL Server 2016 builds on this leadership, and will come packed with powerful built-in features. As the least vulnerable database for six years in a row, SQL Server 2016 offers security that no other database can match. It also has the data warehouse with the highest price-performance, and offers end-to-end mobile BI solutions on any device at a fraction of the cost of other vendors. It provides tools to go beyond BI with in-database Advanced Analytics, integrating the R language and scalable analytics functions from our recent acquisition of Revolution Analytics.

Microsoft’s cloud-first product development model means that new features get hardened at scale in the cloud, delivering proven on-premises experience. In addition, we offer a consistent experience across on-premises and cloud with common development and management tools and common T-SQL.

Security with Always Encrypted

The Always Encrypted feature in SQL Server 2016 CTP 3.0, an industry-first, is based on technology from Microsoft Research and helps protects data at rest and in motion. Using Always Encrypted, SQL Server can perform operations on encrypted data and – best of all – the encryption key resides with the application in the customers’ trusted environment. It offers unparalleled security.

One example of a customer that’s already benefitting from this new feature is Financial Fabric, an ISV that offers a service called DataHub to hedge funds. The service enables a hedge fund to collect data ranging from transactions to accounting and portfolio positions from multiple parties such as prime brokers and fund administrators, store it all in one central location, and make it available via reports and dashboards.

“Data protection is fundamental to the financial services industry and our stakeholders, but it can cause challenges with data driven business models,” said Subhra Bose, CEO, Financial Fabric. “Always Encrypted enables the storage and processing of sensitive data within and outside of business boundaries, without compromising data privacy in both on-premises and cloud databases. At Financial Fabric we are providing DataHub services with “Privacy by Design” for our client’s data, thanks to Always Encrypted in SQL Server 2016. We see this as a huge competitive advantage because this technology enables data science in Financial Services and gives us the tools to ensure we are compliant with jurisdictional regulations.”

Always Encrypted updates in CTP3 include the following; please see the SSMS team blog for additional detail.

  • Encrypting columns and key management made easy with new UI in SSMS
  • Encrypt Columns Wizard
  • Key management/rotation workflows
  • Azure Key Vault support
  • Integration with hardware security modules (.NET 4.6.1) and Azure Key Vault
  • Mission Critical Performance

With an expanded surface area, you can use the high performance In-Memory OLTP technology in SQL Server with a significantly greater number of applications. We are excited to introduce the unique capabilities of combine in-memory analytics (columnstore) with in-memory OLTP and traditional relational store in the same database to achieve real-time operational analytics. We have also made significant performance and scale improvements across all components in the SQL Server core engine.

Mission Critical features in SQL Server 2016 

Insights on All Your Data

You’ll find significant improvements in both SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS) that help deliver business insights faster and improve productivity for BI developers and analysts. The enhanced DirectQuery enables high-performing access to external data sources like SQL Server Columnstore. This capability enhances the use of SSAS as a semantic model over your data for consistency across reporting and analysis without storing the data in Analysis Services.

SQL Server 2016

SQL Server Reporting Services 2016 offers a modernized experience for paginated reports and updated tools as well as new capabilities to more easily design stunning documents. To get more from your investments in SSRS and to provide easy access to on-premises reports to everyone in your organization, you can now pin paginated reports items to the Power BI dashboard. In coming months, we will add new Mobile BI capabilities to Reporting Services, allowing you to create responsive, interactive BI reports optimized for mobile devices.

PolyBase, available today with the Analytic Platform System, is now built into SQL Server, expanding the power to extract value from unstructured and structured data using your existing T-SQL skills. PolyBase CTP 3.0 improvements including better performance and scale out PolyBase nodes to use other SQL Server instances. see also: PolyBase in APS - Yet another SQL over Hadoop solution

Polybase in CTP3 includes the following new capabilities:

  • Improved PolyBase query performance with scale-out computation on external data (PolyBase scale-out groups)
  • Improved PolyBase query performance with faster data movement from HDFS to SQL Server and between PolyBase Engine and SQL Server
  • Support for exporting data to external data source via INSERT INTO EXTERNAL TABLE SELECT FROM TABLE
  • Support for push-down computation to Hadoop for string operations (compare, LIKE)
  • Support for ALTER EXTERNAL DATA SOURCE statement

SQL Server 2016 Everything built-in FULL deck

PolyBase with Freedom of Choice


One important key differentiator of PolyBase compared to all of the existing competitive approaches is ‘openness’. We do not force users to decide on a single solution, like some Hadoop providers are pursuing. With PolyBase, you have the freedom to use an HDInsight region as a part of your APS appliance, to query an external Hadoop cluster connected to APS, or to leverage Azure services from your APS appliance (such as HDInsight on Azure).

To achieve this openness, PolyBase offers these three building blocks.

Advanced Analytics

Advanced Analytics (RRE integration)

With this release, we are very excited to announce the public availability SQL Server R Services in SQL Server 2016, an Advanced Analytics capability which supports enterprise-scale data science, significantly reducing the friction for adopting machine learning in your business. SQL Server R Services is all about helping customers embrace the highly popular open source R language in their business. R is the most popular programming language for Advanced Analytics.

What's New in SQL Server 2016 for BI

You can use it to analyze data, uncover patterns and trends and build predictive models. It offers an incredibly rich set of packages and a vibrant and fast-growing developer community. At the same time, embracing R in an enterprise setting presents certain challenges, especially as the volume of data rises and with the switch from modeling to production environments. Microsoft SQL Server R Services with in-database analytics helps customers embrace this technology by supporting several scenarios. Two of the key scenarios are:

One: Data Exploration and Predictive Modeling with R over SQL Server data

The data scientist can choose to analyze the data in-database or to pull data from SQL Server and analyze it on the client machine (or a separate server). Analyzing data in-database has the advantage of performance and speed by removing the need to move data around and leverage the strong compute resources on the SQL Server. RevoScaleR package and APIs contains a set of common functions and algorithms that were designed for performance and scale, overcoming R limitations of single-threaded execution and memory bound datasets.

Two: Operationalizing your R code using T-SQL

For SQL Server 2016 CTP3, Microsoft supports ad-hoc execution of R scripts via a new system stored procedure. This stored procedure will support pushing data from a single SELECT statement and multiple input parameters to the R side and return a single data frame as output from the R side.

Taking R Analytics to SQL and the Cloud

R at Microsoft 

PASS 2015 Keynote: Accelerating your Business with a Modern Data Strategy

Transactional replicate from SQL Server to Azure SQL DB in new in CTP3. 

Now you can setup Azure SQL DB as a subscriber of transaction replication, allowing you to migrate data from SQL Server instance on-premises or in IaaS to Azure SQL database without downtime. The replication is one way in this release, and works with SQL Server 2016, SQL Server 2014 and SQL Server 2012. This is the same Transactional Replication technology you have been using for many years on premise. As you configure a subscriber (from SSMS or by script), instead of entering an instance name, you enter the name of your Azure SQL DB subscription along with the associated login and password. A snapshot (as in a Replication Snapshot) will used to initialize the subscription and subsequent data changes will be replicated to you Azure SQL DB in the same transactional consistent way you are used to. A transactional publication can deliver changes to subscribers both in Azure SQL DB and/or on premise/Azure VM. There is no Replication service hosted in Azure for this. Everything is driven from on-premise distribution agents. To use this feature, you just need to set it up the way you do to replicate on-premises: Install the Replication components, configure the Distributor, the Publisher and create the Publication, the Articles and you the Subscriptions. In this case, one of the subscriptions will be your Azure SQL DB.

In-Memory improvements in this release:

  • In-Memory OLTP
  • FOREIGN KEY constraints
  • CHECK constraints
  • UNIQUE constraints
  • DML triggers (AFTER only)
  • Inline table-values functions
  • Security built-ins and increased math function support
  • Real-time Operational Analytics
  • Support for in-memory tables
  • Existing nonclustered columnstore index (NCCI) are updateable without requiring index rebuild
  • Parallel Index build of nonclustered columnstore index (NCCI)
  • Performance improvements (INSERT, String pushdown, bypassing delete buffer when processing deleted rows)
  • In-Memory Analytics
  • You can upgrade databases with nonclustered columnstore index and have it updateable without requiring rebuild of the index
  • General performance improvements for analytics queries with columnstore index especially involving aggregates and string predicates
  • Improved supportability with DMVs and XEvents

SQL Unplugged Episode 12

New Hybrid Scenario using Stretch Database

Stretch Database enables stretching a single database between on-premises and Azure. This will enable our customers to take advantage of the cloud economics of lower cost compute and storage without being forced into an all-or-nothing database move. Stretch Database is transparent to your application, and the trickle of data to Azure can be paused and restarted without downtime. You can use Always Encrypted with Stretch Database to extend data in a more secure manner for greater peace of mind.

StretchDB - Stretch tables to Azure SQL DB with SQL Server 2016

Stretch Database updates in this release:

Engine Update

  • Create/Drop index support
  • AlwaysEncrypted support
  • Improved credential management for remote Stretch database stores
  • Improved performance for joins between stretched tables
  • New external data source integration

Expert summit SQL Server 2016

SSMS Wizard updates

  • Database and Table level fly out menu options were updated to reflect new Stretch functionality
  • Stretch monitor functionality added to allow users to monitor current migration status, including the ability to pause the migration at the table level
  • XEVENT support for diagnostics session support in monitor
  • Updated and simplified stretch wizard flow to reduce the amount of steps required to enable or reconfigure Stretch
  • Help icon links Updated to point to new MSDN content focusing specifically on wizard topic
  • Added functionality that allows users to pause or disable migration at the table level
  • Added ability to Stretch individual tables
  • Added database scoped credential support - for AlwaysOn
  • Ability to enabling stretch on the server using the wizard
  • Updated table level validation error/warning messaging
  • The ability to Stretch to new SQL Azure or existing SQL Azure server
  • Updated SSMS Object Explorer Stretch Databases icons
  • SMO model for Stretch status query and updates
SQL Server 2016 novelties

Built-in JSON support improvements in this release include:

OPENJSON - Table value function that parses JSON text and returns rowset view of JSON. By default, OPENJSON returns properties of object or elements of array that is parsed. Advanced version of OPENJSON function with defined schema allows user to define schema of resulting rowset, and mapping rules that define where can be found values in the parsed JSON text that will be returned in the resulting rowset. It enables developers to easily parse JSON text and import it into relational tables.

JSON_VALUE - Scalar function that returns a value from JSON on the specified path. It can be used in any query, view, computed column. It can be also used to define indexes on properties of JSON text stored in table columns.

ISJSON - function that validates that JSON is properly formatted. It can be used to define check constraints on the columns that contain JSON text. It is not supported in check constraints defined on in-memory tables.

JSON_QUERY - Scalar function that returns a fragment from the JSON text. Unlike JSON_VALUE that returns scalar values, JSON_QUERY returns complex object (i.e. JSON arrays and objects).

Azure Data Lake Store and Analytics Service available in preview today

Last month we announced a new and expanded Azure Data Lake that makes big data processing and analytics simpler and more accessible. Azure Data Lake includes the Azure Data Lake Store, a single repository where you can easily capture data of any size, type and speed, Azure Data Lake Analytics, a new service built on Apache YARN that dynamically scales so you can focus on your business goals, not on distributed infrastructure, and Azure HDInsight, our fully managed Apache Hadoop cluster service. Azure Data Lake is an important part of the Cortana Analytics Suite and a key component of Microsoft’s big data and advanced analytics portfolio.

The Azure Data Lake service includes U-SQL, a language that unifies the benefits of SQL with the expressive power of user code. U-SQL’s scalable distributed query capability enables you to efficiently analyze data in the store and across SQL Servers in Azure, Azure SQL Database and Azure SQL Data Warehouse. Customers can use Azure Data Lake tools for Visual Studio, which simplifies authoring, debugging and optimization and provides an integrated development environment for analytics.

ASOS.com, the UK's largest independent online fashion and beauty retailer, has been using Azure Data Lake to improve customer experience on their website. “At ASOS we are committed to putting the customer first. As a global fashion destination for 20-somethings we need to stay abreast of customer behaviour on our site, enabling us to optimize their shopping experience across all platforms of ASOS.com and wherever they are in the world. Microsoft Azure Data Lake Analytics assists in processing large amounts of unstructured clickstream data to track and optimize their experience. We have been able to get productive immediately using U-SQL because it was easy to use, extend and view and monitor the jobs all within Visual Studio” said Rob Henwood, Enterprise Architect at ASOS.com.

Azure SQL Database In-Memory OLTP and Operational Analytics

Today, we are releasing our next generation in-memory technologies to Azure with the public preview of In-Memory OLTP and real-time Operational Analytics in Azure SQL Database. In-Memory OLTP in the Azure SQL Database preview includes the expanded surface area available in SQL Server 2016, enabling more applications to benefit from higher performance. By bringing this technology to the cloud, customers will be able to take advantage of in-memory OLTP and Operational Analytics in a fully managed database-as-a-service with 99.99% SLA.

Foundation Session: Microsoft Business Intelligence

Temporal support improvements in this release include:

  • Support for using temporal system-versioning with In-Memory OLTP
  • Combining disk-based table for cost-effective storing of history data with memory-optimized tables for storing latest (actual) data
  • Super-fast DML and current data querying supported from natively compiled code
  • Temporal querying supported from interop mode
  • Internal in-memory table created to minimally impact performance of DML operations
  • Background process that flushes the data from internal in-memory to permanent disk-based history table
  • Direct ALTER for system-versioned temporal tables enables modifying table schema without introducing maintenance window
  • Support for adding/altering/dropping columns while SYSTEM_VERSIONING is ON
  • Support for ADD/DROP HIDDEN for period columns while SYSTEM_VERSIONING is ON
  • Support for temporal querying clause FOR SYSTEM_TIME ALL that enables users to query entire data history easily without specifying period boundaries
  • Optimized CONTAINED IN implementation with minimized locking on current table. If your main case is analysis on historical data only, use CONTAINED IN.

Combined with the releases earlier this month of Always Encrypted, Transparent Data Encryption, support for Azure Active Directory, Row-Level security, Dynamic Data Masking and Threat Detection, Azure SQL Database provides unparalleled data security in the cloud with fast performance. As part of our intelligent capabilities, SQL Database also has built-in advisors to help customers get started quickly with in-memory OLTP to optimize performance.

SQL Server 2016 New Security Features

It’s never been easier to capture, transform, mash-up, analyze and visualize any data, of any size, at any scale, in its native format using familiar tools, languages and frameworks in a trusted environment, both on-premises and in the cloud.

In Summary:

SQL Server 2016 has many new features.  Some of features are enhancements to existing features, while others are entirely new features.   In this article I only explored some of the new functionality in SQL Server 2016.   When moving to SQL Server 2016 you should exploit those new features that provide value to your SQL Server environment.


- Enhanced in-memory performance provides up to 30x faster transactions, more than 100x faster queries than disk-based relational databases and real-time operational analytics

- New Always Encrypted technology helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application changes

- Stretch Database technology keeps more of your customer’s historical data at your fingertips by transparently stretching your warm and cold OLTP data to Microsoft Azure in a secure manner without application changes

- Built-in advanced analytics provide the scalability and performance benefits of building and running your advanced analytics algorithms directly in the core SQL Server transactional database

- Business insights through rich visualizations on mobile devices with native apps for Windows, iOS and Android

- Simplify management of relational and non-relational data by querying both with T-SQL using PolyBase

- Faster hybrid backups, high availability and disaster recovery scenarios to back up and restore your on-premises databases to Microsoft Azure and place your SQL Server AlwaysOn secondaries in Azure

Where You Can Get Additional Information

Below are some additional resources that you can use to find out more information about SQL Server 2016.

SQL Server 2016 Early Access Web Site: https://www.microsoft.com/en/server-cloud/products/sql-server-2016/

SQL Server 2016 data sheet: http://download.microsoft.com/download/F/D/3/FD33C34D-3B65-4DA9-8A9F-0B456656DE3B/SQL_Server_2016_datasheet.pdf


SQL Server 2016 release notes: https://msdn.microsoft.com/en-US/library/dn876712.aspx

What’s new in SQL Server, September Update: https://msdn.microsoft.com/en-US/library/bb500435.aspx











0 reacties:

Post a Comment