Top 10 New Features in SQL Server 2016
Microsoft is releasing new and exciting features with new versions over the time. SQL Server 2016 also has lots of new features for database users and it has been reformed and made database users life easier. Real time operational analytics is amazing which is upto 30x fatser transactions with in-memory OLTP which performs operations from minutes to seconds. Here I am covering 10 new features in SQL Server 2016 which I think is great for database users though there are tons of new features which we will see in future articles in detail.
1. Stretch Databases
2. Always Encrypted
3. Dynamic Data Masking
4. Row Level Security
5. Multiple TempDB
6. JSON Support
7. PolyBase
8. Temporal Tables
9. Query Store
10. Advanced Analytics
1. Stretch Databases
With this new and exciting feature you can stretch or extend the storage of on-premise database to Microsoft Azure and can retain data for longer time without any table break as normally we create historical tables for huge tables. It minimises storage cost as you can control it over Azure. Once data is stretched to cloud, data movement will be handled in background which totally query-able without any changes to applications. The major benefit is Azure storage is 80% less expensive and reduces on-premises maintenance.
2. Always Encrypted
SQL Server 2016 includes enhanced security layer as ‘Always Encrypted’ feature which encrypts the data in motion, in memory and at rest without impacting performance. Microsoft has tried to protect sensitive data like bank details, credit card details and other critical data among data owners, data managers, on-premise DBAs, Cloud Operators and unauthorised users etc. Always Encrypted driver automatically encrypts and decrypts sensitive data in client applications. Sensitive columns data are encrypted before passing to database SQL Server engine and maintains semantics.
3. Dynamic Data Masking
This feature in SQL Server 2016 helps to prevent unauthorised access to confidential and sensitive data where client can control over limits of data to mask without impacting application layer. The masking is applied on query output without modifying existing queries and protects data which clients don’t want to reveal. This feature is just an added advantage with existing available features in previous versions of SQL Server like Auditing, Encryption etc.
4. Row Level Security (RLS)
SQL Server 2016 has introduced new feature to uniquely control and protect data at row level called Row Level Security (RLS). You can restrict data access from particular users with total transparency with applications. This is again one more added advantage to control access at more granular level than GRANT, REVOKE and DENY permissions.
5. Multiple TempDB
As we know tempdb is re-created every time SQL Server is restarted and temporary objects are dropped automatically and backup or restore activity is not allowed on tempdb. In SQL Server 2016 you can setup the tempdb data files at Database Engine Configuration step during installation which automatically creates default number of files with respect to number of logical processors on the server up to a maximum of 8 tempdb files on servers having 8 or more cores. Performance is also taken care and temporary tables and variables caching allows drop and create activity very fast with reduced page allocation contention. Trace flag 1117 and 1118 is not required now and all tempdb files will auto grow at the same time.
6. JSON Support
Java Script Object Notation also called JSON in short, is one of the most commonly used data exchange format. Normally web browsers and other recent services use JSON text format. So there was a huge request for SQL Server to handle and process JSON text. Microsoft added this in-built feature to parse and process JSON formatted data and to query JSON data stored and to exchange relational data in SQL Server.
7. PolyBase
This feature allows you to use T-SQL statements to query Hadoop clusters or Azure Blob Storage and can create queries that joins semi-structured data with SQL Server relational data sets. PolyBase is a inbuilt feature of SQL Server 2016 and you do not need understanding of Java or other Hadoop related concepts or any extra tools. PolyBase concept is not new to us as this concept is already introduced in Microsoft’s massive Parallel Processing (MPP) appliance called Parallel Data Warehouse (PDW).
8. Temporal Tables
Temporal table is a new feature in SQL Server 2016, it is also called System Versioned tables which automatically keeps history of data in the table. The temporal table is physically a different table then the base table and you can use it as row versioning solution also. Temporal table allows you to query records which have been updated or deleted and regular table will return current data only. This feature is useful for auditing, versioning, monitoring and recovering modified data from history.
9. Query Store
Query Store as the name suggests, it captures history of queries, execution plans and run time statistics which we can review for performance. You can easily find performance variations caused by query plan changes and improve the same. Prior to SQL Server 2016 you must have seen execution plan by using DMVs which are available in plan cache but now historical execution plan will also be available for review.
10. Advanced Analytics
SQL Server 2016 comes with enhanced analysis with R language. Revolution Analytics is statistical open source company which Microsoft purchased recently. Now users don’t need to export data to run analysis in R as it is incorporated with SQL Server. R is built-in to your T-SQL including end-to-end mobile BI, high performance DW, mission critical OLTP, multi-threading and massive parallel processing with in-memory.
Reference: Manzoor Siddiqui [www.SQLServerLog.com]
You may also like
