SQL Server 2016 Reporting Services

SSRS  have changed significantly since their strongly outdated predecessors. Report manager is no longer present and has been replaced by visually attractive web portal thanks to which users are able to define KPI quantities. Mobile report publisher enables user to create reports compatible with mobile phones and tablets and it is also possible to manage them through the web portal once they are deployed.

Visualization in reports also looks much better now. It is obvious they are taken over from Power BI. Reports of reporting services can be integrated within Power BI and some parts of the reports can even be pinned to PBI dashboard. Here is an article on news in reporting services 2016. It is one of the biggest changes in SQL Server 2016.

Hybrid Cloud (Stretch Database)

One of the most interesting functions stretch database makes it possible to allocate part of the data to the cloud service MS Azure and part of it to on-premise. This service also enables system and monetary funds to be allocated between on-premise and cloud in any ratio.

If you need to free up some space on the physical server you may allocate part of the data to the cloud and make use of freed space. In the analogically opposite way, having free capacity on the server disc, we can save money for storage in Azure by downloading part of the data to on-premise.

Data Masking

MS SQL Server 2016 offers data to be presented to some of the users in secured form. Data masking is the ideal way between data encrypting and unsecured storage. If we have sensitive data in the database (account numbers of our clients etc.) data masking can hide part of the data and show only the last four digits to the user.

Update 24.9.2019 – I described data masking in-depth and with an example in the article Data Masking in SQL Server – How to hide data in given column

JSON Native Support

Native support JSON will be useful mostly for app developers. SQL Server is able to parse JSON data and work with them. The other way around it can transfer data from database format into JSON using functions JSON_VALUE(), JSON_QUERY(), ISJSON() and OPENJSON. Exchange of the data between apps and SQL Server is therefore much easier.

Update 24.9.2019 – I wrote an article on OPENJSON SQL OPENJSON functions – How to parse JSON files?

Encrypting and row level security

SQL Server 2016 now has Always encrypted function. It is possible to enable access to sensitive data only through the client app. The app contains encrypting key which is invisible to the system admins. Regular data are not encrypted, minimizing negative effect on client app performance. This version will be usable only for HR databases or db where are stored client data with high risk of misuse. This technique dramatically lowers the number of people operating with sensible data.

Another new feature is native row level security police. It can be set in a way that only some users can see rows in a given table (based on predicate function). Interesting option is to promote security management to a higher level by combining row level security and data masking.

SQL Server R Services

SQL server 2016 supports advanced analysis of data using R language which is currently the most favorite programming language for these tasks. BI developers or data analytics  can use system procedure sp_execute_external_script of SQL Server to process R    queries/visualizations and then process report in Reporting Sevices. It is also possible to pin the report on the Power BI dashboard afterwards.