SQL Server 2016 Security using Encryption
- In SQL Server 2016, to improve security, data will always be in an encrypted form and the key for its decryption will reside somewhere. So only those users authorized to use your application can use your encrypted database using that key.
![]()
Figure 1
- So, if you are using a cloud for your database then now you can feel more relaxed, since the key is in your hand for your encrypted data and no one else can harm your database.
![]()
Figure 2
- Sometimes we want to provide access to some sensitive column to only those users authorized for it. This can be done using Dynamic DataMasking, in which we can mask the column depending on user roles if needed.
![]()
Figure 3
- In SQL Server 2016 new policies are added for Row-level security in which a user can access rows depending on their roles only. For example, a manager can check a data row depending on his role and employees can access data rows depending on their role.
![]()
Figure 4
Always On Availability Groups
The following are SQL Server 2016 improvement on Always On Availability Groups:
- SQL Server 2016 has also improved the always on availability group.
- Always on was introduced in SQL Server 2012 for disaster recovery and high availability for databases.
- For example, you can create your availability group (like UK and US) of your existing database that is in one server with other servers using SQL Server 2012 or above by creating a replica of your database.
![]()
Figure 5
- To provide a connection point for these replica database servers you need to create an Availability group listener. Using this listener a user can connect to a replica database. For example, for the UK group you can create a UK_Listener connection.
![]()
Figure 6
- Another server will also restore your database and if any conflicts occur in the future then these server databases will be highly available for you to recover.
- With SQL Server 2016 you will get support for the Microsoft Distributed Transaction Coordinator (DTC) that allows a client application to include several sources of data.
![]()
Figure 7
- SQL Server 2016 has also improved the log of records of all the transactions and database modifications made by each transaction.
![]()
Figure 8
- SQL Server 2016 provides better policies for failover. In other words, if one node crashes then another node will be restarted without human intervention.
![]()
Figure 9
Updates for ColumnStore Indexes
The following shows SQL Server 2016 updates for ColumnStore indexes:
Row-store
![]()
Figure 10
Column Store
![]()
Figure 11
- With SQL Server 2016, you can run T-SQL constructs in batch mode. In other words, you can send a collection of one or more SQL statements as one unit .
![]()
Figure 12
- In SQL Server 2016 more predicates (used in a search condition) and aggregates (like avg, count and so on) are pushed down to the storage engine.
The Storage engine controls database concurrency, manage transactions, locking and logging.
![]()
Figure 13
- SQL 2016 has:
- New DMVs that return server-state information.
- New XEvents for handling server systems.
- New performance counters that allow you to measure the current performance.
![]()
Figure 14
- SQL Server 2016 provides support for RCSI and snapshot isolation. They are used for improving performance and concurrency in the database.
SQL Server 2016 for In-Memory OLTP
-
In-Memory OLTP (that uses your RAM instead of database disk) will support foreign keys between in-memory tables.
![]()
Figure 15
- SQL Server 2016 has increased the size limit of in-memory OLTP from 256 GB to 2TB.
![]()
Figure 16
- SQL Server 2016 can also encrypt in-memory tables.
- SQL Server 2016 has added Check constraints, Unique constraints and triggers support for in-memory tables.
SQL Server 2016 for DBA Features
- DBA can use a new feature of QueryStore that allows a DBA to track changes and compare execution plans and then determine which plan to use.
![]()
Figure 17
- A DBA can keep your archive in a cloud and data on-premise.
![]()
Figure 18
SQL Server 2016 for DEV features
- SQL Server 2016 provides Native JSON Support as in the following:
![]()
Figure 19
- With SQL Server 2016 you can track historical changes in the database.
- SQL Server 2016 has combined Visual Studio 2015 and BI database projects.
![]()
Figure 20