SQL Server 2016 – Features and Functional Capabilities (Part 2 of 2)

MAY 26, 2016 // By Jared Zagelbaum

This is Part 2 of a 2-part series. To read Part 1, click here.

In Part 1 we took an in-depth look at Analytics. This post will observe Hybrid Architecture, Security, Administration & Performance, and Reporting.

Hybrid Architecture

There are quite a few PaaS offerings in the data platform space available in Azure, however, MS has designed a few specific Azure services to be quickly and easily extended from an on prem SQL instance through both T-SQL and wizards available in client tools. The requirements for these hybrid solutions generally involve an acceptable amount of latency / DR scenarios.

Features of note:

  • Stretch Database is a new feature that automatically archives “cold” data to Azure storage. The data remains available to actively query, and reduces storage cost and eliminates technical implementation for on prem partition management solutions
  • Managed backup directly to Azure blob storage. This can be scripted directly using simple T-SQL. In practice, network bandwidth limitations often require disk replication of shared local backup instead. File-Snapshot and Striped backups are new additional Azure backup types.
  • AlwaysOn failover groups support azure sql instances running as IaaS as remote secondary replicas. Replicas can be actively queried and serve both DR and workload balancing requirements.


SQL Server 2016 incorporates new security features inherited from Azure SQL Database (in congruence with MS’ cloud first strategy). The features themselves do not offer new functionality so much as incorporate common security implementations into the engine itself, making implementation and management somewhat easier. The easier implementation should be taken with a grain of salt, however, as lack of experience in this area on the part of most enterprise DBAs will continue to allow for poor practices in key management and holes in security design. Data security is a strong opportunity for seasoned consultants in the data platform space.

Features of note:

  • Dynamic data masking (DDM) masks data at select time based on user or database roles. This centralizes and standardizes masking that previously was maintainable only in the application layer.
  • Row Level Security (RLS) creates schema bound objects and functions for implementing row level filtering based on user level permissions. Comparable implementations in prior versions were implemented in user views. A strong value proposition for RLS is in the ability to retroactively implement user security without requiring application code changes apart from connection string properties.
  • Always Encrypted is the latest offering in RDBMS encryption for SQL Server. It encrypts data at the column level both at rest and in transit (hence, Always Encrypted). It enforces secure database calls in the application layer as well as requiring specific libraries and methods for database access. It also requires master keys to be maintained in a windows, azure, or HSM module. Understanding of the need for these practices is still imperative, and the ease of implementation for Always Encrypted masks the comprehensive understanding required to implement and maintain enterprise security. Expect to see master keys continue to be saved in the backup directory as long as the DBAs are in charge.

Administration & Performance

MS has made some foundational improvements to the SQL Server engine, marketing performance gains of 34x faster query response, along with other multiples in throughput and reporting simply from upgrading. Initial feedback from customers testing upgrades on various RCs validates these statements. Along with OOTB performance improvements, MS offers admins additional tools for maintaining on prem installations. The installation process has also been somewhat simplified, however, knowledge of proper SQL configuration is still required to optimize enterprise installations and avoid common issues.


There has not been any revolutionary investment in integration services for on prem installations. The new features of SSIS incrementally formalize more common tasks and data connections into the platform as native components rather than requiring custom objects. Microsoft has invested heavily in Azure integration offerings (primarily Azure Data Factory), as well as Power Query, the declarative ETL tool integrated into Power BI. There have been statements as to the intention of supporting Power Query and the declarative M language as part of future releases of SSIS.


Microsoft invested heavily in Reporting Services for 2016 and incorporated a lot of features previously available only in competing BI platforms or 3rd party solutions, along with completely modernizing the user experience. MS has also stated intentions for SSRS 2016 to allow for on prem hosting of Power BI. There will be / should be a lot of buzz for SSRS 2016 for replacing expensive licensing for on prem BI toolsets that no longer offer more then what is available OOTB with SQL Server licensing. As an about face, a lot of the functionality (especially mobile reporting) is available only in the stand alone installation, making SharePoint integration a much less attractive option then just a couple of years ago.

Feature of note:

  • SSRS engine completely overhauled to render in HTML 5, supporting consistent experience in all popular web browsers
  • Report portal redesigned as a self-service dashboard, supporting KPIs and at a glance viewing
  • New mobile report publisher (previously Datazen acquisition) supports formatting for mobile devices and rendering through native Power BI mobile app on iOS, Android, and Windows Mobile. Does not require Power BI subscription!
  • MS has promised Power BI on prem hosting in the near future through SSRS, although it will not be available at RTM

If you’d like to contact Magenic directly, email us or call us at 877-277-1044.

Categories // Business Intelligence
Tags // SQL, SQL Server 2016, Microsoft, MSDN, SSAS

Get Started

Contact Us