Accelerating business with SQL Server 2016

Business users and decision makers demand:

• Retain cold data for many years (indefinitely)

• Cold data must be available and online

• Storage consumption increasing

• Faster report generation

DBA’s Concerns:

• Uncontrollable database growth in size and sprawl

• Operations overhead index management, backup/restore

• Frequent storage acquisition and provisioning

• Users can’t/won’t say what data can be deleted

Satya Nadella, CEO of Microsoft, announced SQL Server 2016, an intelligent platform for a mobile first, cloud first world. Top capabilities of this release are:

Always Encrypted - a new capability helps protect your data at rest and in motion, on-premises and in the cloud, with master keys sitting with the application, without application. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.

row-level security (RLS) and Dynamic Data Masking - have both been in preview in Azure SQL DB for several months now and are included in this CTP. So effectively, Azure SQL DB gets the new toys first where they get tested out, and then the on-premises products benefit by getting features more quickly (in service packs or cumulative updates).

Stretch on premise Database/table to SQL Azure - 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. One common idiom in recent years, is how cheap storage is. While it may be cheap to buy a 3TB drive from Amazon, if you are buying enterprise-class SAN storage or enterprise SSDs, you will know that storage is still very expensive. Microsoft is trying to help reduce your storage (and processing costs) with a hybrid feature called "Stretch Database." The basics of Stretch Database are that some part of your tables (configurable or automated) will be moved into an Azure SQL Database in the cloud in a secure fashion. When you query those tables, the query optimizer knows which rows are on your server and which rows are in Azure, and divides the workload accordingly. The query processing on the Azure rows takes place in Azure so the only latency is for the return of the rows over the network. As an additional enhancement, you are only charged for the SQL Database in Azure when it is used for queries. You do, however, pay for the Azure Blob storage, which, generally speaking, is much cheaper than enterprise storage.

In-memory technologies - Enhanced in-memory performance provides up to 30x faster transactions, A more than 100x faster queries than disk-based relational databases and real-time operational analytics. A very nice session by Sunil Argawal and Kevin Farlee. They delivered a session where the main new improvements of the In-Memory OLTP in SQL Server 2016 full Ignite session the full Ignite session.

Query Store - One common problem many organizations face when upgrading versions of SQL Server is changes in the query optimizer (which happen from version to version) negatively impacting performance. Without comprehensive testing, this has traditionally been a hard problem to identify and then resolve. The Query Store feature maintains a history of query execution plans with their performance data, and quickly identifies queries that have gotten slower recently, allowing administrators or developers to force the use of an older, better plan if needed. The Query Store is configured at the individual database level.

Built-in Advanced Analytics,

PolyBase - is a integration wof SQL PDW with SQL Azure blob storage and Hadoop. Build on the philosophy to manage any data, any size any where. MS is targetiting to bind this capability with the enterprise edition along with PDW. A good detailed was explained in PASS SUMMIT 2012