MICROSOFT SQL SERVER 2016:
WHAT YOU NEED TO KNOW

One of the most significant upgrades of Microsoft SQL Server 2016 is security. Microsoft added a new feature known as “Always Encrypted” technology, which allows the database server to perform operations using encrypted data. The encryption key stays in a secure and trusted environment within the application.

It’s only possible to read encrypted data using a special driver and the encryption key. This helps to avoid changes that could possibly affect existing applications in your database. Also, it doesn’t allow DBAs to access the plaintext data.

It also comes with row-level security, which allows you to offer users access to rows that are relevant to their function. You can enable this feature by creating inline table functions.

Users love it when they are able to get quick or instant results to their SQL queries. This is something that SQL Server 2016 offers with the upgrades to the In-memory OLTP and analytics platform system.

SQL Server supports memory-optimized tables and now feature massive object type columns, triggers, constraints, and foreign keys. You can also set and change the type of collation you want for your tables. Your tables can also use Nullable index key columns, columns with collation, and unique indexes.

In-memory enhancements have also allowed users to perform real-time analytics on their OLTP database without experiencing resource contention between two different workloads. Additionally, you can use filtered column store indexes to reduce the overhead caused by the maintenance of clustered column store indexes.

The engine comes with a new Stretch Database, which allows you to securely archive data from a local database to an Azure database in the cloud. In this case, this will enable you to reduce dependence on your on-site hardware. You will still have data access through your on-site database.

TempDB of the server is also improved for higher performance. In SQL Server 2016, you don’t need to determine how many TempDB files you have to add.

The server gives the default number by basing it on the number of logical processors it has. It also comes with a new Query Store that helps to troubleshoot issues related to the query plan. This stores queries execution plans, texts, and performance metrics within the database. You have access to a database that highlights queries that consumed the most memory, CPU resources, and time.

Management of relational and non-relational data is now possible with SQL Server 2016 thanks to Polybase technology. This technology works by incorporating Hadoop into the regular on-site database. In turn, this helps in connecting Big Data and Hadoop.

If your company is dealing with a large number of text files for data storage, you can easily store them in Azure Blob Storage. Polybase also allows you to query data in Azure Blob storage or Hadoop.

With temporal tables, you can view all data changes together with the time and date they occurred. These tables consist of two tables: the current (temporal) table and the historical table. The historical table utilizes page compression to save on space.

As such, when changes are made to the current table, a copy of the changes is stored in the historical table in real-time. This means you can never lose track of any changes or past versions of your data.

In the older version, Microsoft SQL 2005, the Azure SQL database only offered a subset of the features that are available in the typical SQL Server database engine. With the 2016 edition, several enhancements have been made to boost the performance of Azure.

For example, Azure security has been improved to ensure that only authorized users have access to Data. You can now audit different events in Azure, including logins, stored procedures, schema modifications, parametrized and plain T-SQL, and transactions.

It also offers elastic databases that help to avoid the provisioning of resources. In this case, you don’t need to estimate resources on a per-database basis. Instead, you can group databases in a pool and delegate resources for that particular database pool.

With SQL Server 2016 Analysis Services (SSAS), filtering, authoring, processing and managing your tubular model databases is now made easier. The server now has R services (code), which allow scientists, analysts, and statisticians, to execute R code directly on their database. If you have the SQL Server enterprise edition, you can enjoy high-speed computation by employing multithreading, multiprocessors, and multicores.

It also has a new Database Consistency Checker (DBCC) that detects data corruption issues. You can also query live external data without importing it thanks to Direct Query. This feature also supports additional data sources, including Teradata, Oracle, and Azure SQL.

Check out our article SQL 2016 requirements for system requirements you will need to run SQL 2016.