This post is not intended to cover all of them, just the ones I see as most useful to know about.
Think of it as cliff notes for SQL Server 2016 features for mortals.
Always Encrypted
Description:
This new feature is useful for storing SQL data in an encrypted format. This is done by generating a column master key and column encryption key. The SQL server has no ability to decrypt the data. Any application with access to the SQL Server can query the data, but only those applications with the encryption key will be able to make sense of it. No word yet on when Dexterity will take advantage of this, but .NET Addins may be able to use it now.
Pros:
- SQL Server never sees the unencrypted version of the data.
- The data is not transferred unencrypted.
- You can choose to encrypt only some of the columns in a table.
- If you encrypt a column using a deterministic encryption type, you can make it a key column or use it for compare operations such as joins and where clauses.
Cons:
- Only the .NET framework 4.6 and JDBC 6.0 drivers work with this so far.
- Simple ad-hoc tools like SSMS cannot be used to review or change data.
- Out the box, SSRS will not be able to decrypt the data for reporting
- LIKE and sort operations are not supported on encrypted columns
Row level security
Description:
This new feature allows you to control which rows of data a particular user has access to select, insert, update, or delete. This is done by adding a column for the user credentials and then creating inline table functions for the tables you wish to protect. You then create a security policy on the table to perform the filter. There does not appear to be any reason this will not work with Dexterity tables, but I have not yet tested it.
Pros:
- The database handles the data access, reducing repetitive coding across multiple applications.
- Eliminates security access code on the client side.
- Works out of the box with SSRS and even SSMS
- You can silently block data modification operations or return an error
Cons:
- Only intended for middle tier applications, since it is not immune to side-channel attacks.
Dynamic Data Masking
Description:
This feature allows you to mask all or part of a columns data. This is different from encryption, where the value can be decrypted to get the actual data. Masking replaces the data with zeros or X's.
This is done using the MASKED WITH option in the table definition. There does not appear to be any reason this will not work with Dexterity tables, but I have not yet tested it.
Pros:
- Only the returned data is changed. There is no change to the underlying data in the table.
- You can grant the UNMASK permission to a role to allow certain users to see the actual values.
- Removes repetitive code from applications.
- Helps keep sensitive data from being reproduced (such as on reports)
- More secure than encryption, since the masked value cannot be used to get the real value in any way.
Cons:
- Granting UNMASK only works for the entire database, not a single table.
- Does not work with encrypted columns
Autoconfigured data files for TempDB
Description:
This one is the best kind. It is actually a performance fix of sorts that requires you to do nothing!
Prior to this version, during install, the TempDB was created using a single data file. In 2011, Microsoft began recommending that in high-use scenarios there be one file per logical processor to prevent contention. Unfortunately, this word did not get disseminated very well, and white papers on the subject likely flew over most mortal's heads.
So Microsoft changed the install to automatically detect the number of logical processors and create the recommended number of data files for TempDB. This does not help if you later change hardware, but adding more data files is relatively simple.
Query Store
Description:
This is a new feature that helps find and fix performance issues. When turned on, it persists execution plans and makes several new dashboards available for query performance. This can be used in conjunction with FORCE PLAN to force SQL to use the most efficient execution plan all the time,
Stretch Database
Description:
This feature allows you to store an effectively unlimited amount of "cold" data in the SQL Azure Cloud while maintaining warm and hot data in an on-premise installation. This requires no changes to application code to implement, and data transmitted through he cloud can be encrypted. This feature is of limited use with Dynamics installations, however, due to the heavy use of check constraints in dexterity tables. Still, it is worth mentioning for disk hogs such as tables containing images and text columns.
Temporal Tables
Description:
This features allows you to create tables that allow you to see the state of the data at any point in the past. It is really two tables - current and history. It is not compatible with FILESTREAM or INSTEAD OF triggers. It is also not recommended for use with blobs due to storage space issues. Queries on this type of table are identical except for the addition of the FOR SYSTEM_TIME clause if you are getting historical records. You can combine this with Stretch Database to track history indefinitely. The jury is still out on whether we can use this with Dexterity tables, since we must add hidden period columns (thus changing the table structure). Sounds like it needs a test :)
JSON support
Description:
The new FOR JSON clause makes exporting data to JSON a breeze, and other new functions such as OPENJSON, ISJSON, JSON_QUERY, and JSON_MODIFY help "bake in" JSON support. Using computed columns, you can even create table indexes off JSON properties inside the column data.
Reporting Enhancements
Mobile Report Publisher: https://www.microsoft.com/en-us/download/confirmation.aspx?id=50400 This is a new tool that works with SSRS to allow you to design and publish reports for mobile devices. It has many dashboard style controls to help conserve screen space.
KPI Development: A new KPI screen lets you create your own KPI's directly from the SSRS Report Manager web portal page.
Export Directly to PowerPoint from SSRS.
No comments:
Post a Comment