Microsoft SQL Server 2016

Better security

SQL Server 2016 introduces three new principal security features—Always Encrypted, Row-Level Security, and dynamic data masking. While all these features are security related, each provides a different level of data protection within this latest version of the database platform.


Always Encrypted

Always Encrypted is a client-side encryption technology in which data is automatically encrypted not only when it is written but also when it is read by an approved application. Unlike Transparent Data Encryption, which encrypts the data on disk but allows the data to be read by any application that queries the data, Always Encrypted requires your client application to use an Always Encrypted–enabled driver to communicate with the database. By using this driver, the application securely transfers encrypted data to the database that can then be decrypted later only by an application that has access to the encryption key. Any other application querying the data can also retrieve the encrypted values, but that application cannot use the data without the encryption key, thereby rendering the data useless. Because of this encryption architecture, the SQL Server instance never sees the unencrypted version of the data.


Row-Level Security

Row-Level Security (RLS) allows you to configure tables such that users see only the rows within the table to which you grant them access. This feature limits which rows are returned to the user, regardless of which application they are using, by automatically applying a predicate to the query. You can use a filter predicate to silently filter the rows that are accessible by the user when using INSERT, UPDATE, or DELETE statements. In addition, you can use the following block predicates to block the user from writing data: AFTER INSERT, AFTER UPDATE, BEFORE UPDATE and BEFORE DELETE. These block predicates return an error to the application indicating that the user is attempting to modify rows to which the user does not have access.

You implement RLS by creating an inline table function that identifies the rows accessible to users. The function you create can be as simple or complex as you need. Then you create a security policy to bind the inline table function to one or more tables.

RLS is designed to simplify your application code by centralizing access logic within the database. It should be noted that, as with any RLS solution and workarounds, it is possible for users with the ability to execute arbitrary T-SQL commands to infer the existence of data that should be filtered, via side-channel attacks. Therefore, RLS is intended for scenarios where the queries thatusers can execute are controlled, such as through a middle-tier application.
Be aware that RLS impacts all users of a database, including members of the db_owner fixed database role. Members of this role have the ability to remove the RLS configuration from tables in the database. However, by doing so, all other users again have access to all rows in the table.


Dynamic data masking

When you have a database that contains sensitive data, you can use dynamic data masking to obfuscate a portion of the data unless you specifically authorize a user to view the unmasked data. To mask data, you can use one of the following four masking functions to control how users see the data returned by a query:

• Default Use this function to fully mask values by returning a value of XXXX (or fewer Xs if a column length is less than 4 characters) for string data types, 0 for numeric and binary data types, and 01.01.2000 00:00:00.0000000 for date and time data types.
• Email Use this function to partially mask email addresses like this: aXXX@XXXX.com. This pattern masks not only the email address but also the length of the email address.
• Partial Use this function to partially mask values by using a custom definition requiring three parameters as described in the following table:
  Prefix - Number of starting characters to display, starting from the first character in the value.
  Padding - Value to be displayed between the prefix and suffix characters.
  Suffix - Number of ending characters to display, starting from the last character in the value.
• Random Use this function to fully mask numeric values by using a random value between a lower and upper boundary that you specify.

Technical Preview high-availability enhancements

Nearly every version of Windows Server since Windows Server 2008 R2 has had major enhancements to the operating system’s failover clustering stack as a result of development investments in related technologies. First, Hyper-V, the virtualization platform in the operating system, uses the clustering stack for its high-availability and disaster-recovery scenarios. Microsoft Azure also uses this same functionality. Because SQL Server has failover clustering at the center of its high-availability/disaster-recovery technologies, it also takes advantage of the clustering features in the operating system. Sometimes these features are visible from the database tier, allowing you to make configuration changes, but other features from the operating system, such as dynamic quorum, enhance SQL Server’s uptime without requiring configuration. Windows Server 2016 Server Technical Preview includes the following features that enhance SQL Server’s uptime:

• Workgroup clusters
• Cloud witness
• Storage Spaces Direct
• Site-awareness
• Troubleshooting enhancements to Windows Server Failover Clusters (WSFC)
• Cluster operating system rolling upgrade

Improved database engine

In past releases of SQL Server, Microsoft has targeted specific areas for improvement. In SQL Server 2005, the storage engine was new. In SQL Server 2008, the emphasis was on server consolidation. Now, in SQL Server 2016, you can find enhanced functionality across the entire database engine. With Microsoft now managing more than one million SQL Server databases through its Database as a Service (DBaaS) offering—Microsoft Azure SQL Database—it is able to respond more quickly to opportunities to enhance the product and validate those enhancements comprehensively before adding features to the on-premises version of SQL Server. SQL Server 2016 is a beneficiary of this new development paradigm and includes many features that are already available in SQL Database. Few of the key new features, which enable you to better manage growing data volumes and changing data systems, manage query performance, and reduce barriers to entry for hybrid cloud architectures.


Stretch Database

One of the more common refrains in IT infrastructure organizations in recent years has been the high costs of storage. A combination of regulatory and business requirements for long-term data retention, as well as the presence of more data sources, means enterprises are managing ever-increasing volumes of data. While the price of storage has dropped, as anyone who owns enterprise storage knows, the total cost of ownership (TCO) for enterprise storage commonly used for databases is still very high. Redundant arrays of independent disks (RAID), support contracts, management software, geographical redundancy, and storage administrators all add to the high total cost of enterprise storage.

Another factor in the cost of storage is the lack of support for online data archiving in many third-party applications. To address this problem, a common approach is to use file groups and partitioning to move older data to slower disks. Although this approach can be effective, it also comes with high managerial overhead because it involves storage administrators in provisioning the storage and requires active management of partitions.

Perhaps more important than the TCO of enterprise storage is the impact of large databases and tables on overall administration and availability of the systems. As tables grow to millions and even billions of rows, index maintenance and performance tuning become significantly more complex. These large databases also affect availability service-level agreements as restore times can often exceed service-level agreements required by the business.

SQL Server 2016 introduces a new hybrid feature called Stretch Database that combines the power of Azure SQL Database with an on-premises SQL Server instance to provide nearly bottomless storage at a significantly lower cost, plus enterprise-class security and near-zero management overhead. With Stretch Database, you can store cold, infrequently accessed data in Azure, usually with no changes to application code. All administration and security policies are still managed from the same local SQL Server database as before.


More analytics

Better and faster analytics capabilities have been built into SQL Server 2016. Enhancements to tabular models provide greater flexibility for the design of models, and an array of new tools helps you develop solutions more quickly and easily. As an option in SQL Server 2016, you can now use SQL Server R Services to build secure, advanced-analytics solutions at enterprise scale. By using R Services, you can explore data and build predictive models by using R functions in-database. You can then deploy these models for production use in applications and reporting tools.


Better reporting

For report developers, Reporting Services in SQL Server 2016 has a more modern development environment, two new data visualizations, and improved parameter layout options. Users also benefit from a new web portal that supports modern web browsers and mobile access to reports.


Report content types

This release of Reporting Services includes both enhanced and new report content types:

• Paginated reports Paginated reports are the traditional content type for which Reporting Services is especially well suited. You use this content type when you need precise control over the layout, appearance, and behavior of each element in your report. Users can view a paginated report online, export it to another format, or receive it on a scheduled basis by subscribing to the report. A paginated report can consist of a single page or hundreds of pages, based on the dataset associated with the report. The need for this type of report continues to persist in most organizations, as well as the other report content types that are now available in the Microsoft reporting platform.
• Mobile reports In early 2015, Microsoft acquired Datazen Software to make it easier to deploy reports to mobile devices, regardless of operating system and form factor. This content type is best when you need touch-responsive and easy-to-read reports that are displayed on smaller screens, communicate key metrics effectively at a glance, and support drill-through to view supporting details. In SQL Server 2016, users can view both paginated and mobile reports through the web portal interface of the on-premises report server.
• Key performance indicators (KPIs) A KPI is a simple type of report content that you can add to the report server to display metrics and trends at a glance. This content type uses colors to indicate progress toward a goal and an optional visualization to show how values trend over time.

Source: Introducing Microsoft SQL Server 2016: Mission-Critical Applications, Deeper Insights, Hyperscale Cloud - Preview 2 - Stacia Varga, Denny Cherry, Joseph D’Antoni