10 New Features SQL Server 2016 Does Better than SQL Server 2014

1. Query Store

Microsoft comes up with a unique feature on SQL server 2016 as “Query Store”, which will store the history of query execution with their performances, statistics, and areas of improvement in terms of a query. If there is any change in the performances, the Query Plan will take a snapshot of it to alert the administrator or developer regarding the change in the query performances to review them completely. As a frequent task, any issue encountered in the query will be simultaneously recorded in the Query Plan. Doing so can help the admins to fix a newer query with regards to the performance resource problem. It is implemented at the database level in SQL Server 2016.

2. Row Level Security

Basically, when a user data access is given, in SQL Server 2014, the access is given for the entire table with either read or write permissions. An additional feature in SQL Server 2016 gives a profound level of security to filter and view the data for specific users. This can avoid falling the data and information in the wrong hands. Though there are many stored procedures used in the industry to filter the data for specific ID and user, a new feature in 2016 brings fail-proof row-level security.

3. Always Encrypted

Further moving on to security level in data, a new feature in SQL Server 2016 adds up encryption level at every mode of transaction. The encryption is carried out when the database alive, during the transit and even during the resting time. This can primarily help trespass the data present in the database. However, the entire database is not being encrypted but is with the column level. A new feature called “Client Library” is used to retrieve the data, rather than retrieving directly from the database.

4. In Memory OLTP Improvements 

In-Memory OLTP is a feature in SQL Server 2016, which can get the most of workloads from SQL Server. It helps with vast support for foreign keys, unique constraints, and parallelism. Additionally, providing tables up to 2 TB storage support. The primary improvements have been in transaction speed and scalability. Being doing OLTP improvements, the latest database architecture that needs more memory size and many-core performances could be compromised. The IT infrastructure costs can come low with the use of In-Memory OLTP.

5. Polybase 

Originally introduced by Microsoft as “SQL Server Parallel Data warehouse”, which was used in SQL Server 2008 R2. The recent update is put forward in SQL Server 2016 as “Polybase” that is used to communicate and interact with the Hadoop data-carrying which is broken and semi-structured. Used to query both relational and non-relational databases. Further, the data can be stored in Azure Blob Storage.

6. Dynamic Data Masking

A fantastic feature introduced in SQL Server 2016, which allows users from being restricted to a certain level of reading access to columns. Obscuring the data is the priority in the Dynamic Data Masking feature. Say, an account number is stored in an object and a restriction has to be put on the object by making sure just a few digits of the last number have to be shown for a group of people or a specific person. Turning on the Dynamic Data Masking, we can achieve the target of just showing the last few digits of the account number. Further, by deactivating the feature for the rest of the folks, the account number is made visible completely. This can add a lot more security by preventing malicious developers and admins.

7. Multiple TempDB Database Files

During the installation setup, multiple TempDB database files are created to improve the performance based on the physical configuration of a computer. Intelligently, it detects the number of processors present and based on the presence the TempDB database files are created. About 8 TempDB data files are created in case if the process is core i3 and above during the setup of SQL Server 2016.

8. Live Query Statistics

Live Query Statistics is another awesome feature fused in SQL Server 2016, which helps real-time monitoring of active query for instant debugging and fix. We can exactly specify the issue since the debugging can be done even before the query is executed completely. Properties such as time-frame, operator progress, and so on help in Adhoc risk management.

9. Enhanced AlwaysOn

By replacing the prior feature of Mirroring, Microsoft has come up with a new feature called “AlwaysOn Availability Groups”. It helps in providing two database instances to be exactly the same in terms of transactions and data with completely zero data-loss. A lot of leverage has been put forward to duplicate the database instances with the help of an enhanced AlwaysOn concept.

10. Stretch Database

Using SQL Server 2016 Community Technology Preview 2(CTP2), the entire database can be migrated to the Microsoft Azure cloud without archiving the data. A new option called “remote query” will help to retrieve the query execution without retrieving the data directly from the server that leverages the processing power.

Simple way to identify blocking in SQL Server

In layman’s point of view, blocking can be anything that restricts from being accessed. Say, for example, a person is logged on to a computer which is with a single user account, in case a new person comes up to login to the same account, then the scenario is considered to be blocking.

In SQL Server, similar scenarios come up wherein we experience locks and blocks. General consequences would be one session being blocked by another session. The idea behind it is simple, where a session would be working on a resource and a second session takes birth to access the same resource. Usually, the blocking behavior in SQL server is very normal. 

To check the SQL Server blocking, simply use the command as:

EXEC SP_WHO2 ACTIVE

This above command lists the SPIDs along with the status of the process and blocked sessions as “BlkBy” by default using the master system database. The screenshot for the same is mentioned below:

From the screenshot, we find that the 83 SPID is using some resource to block the SPID 2. Here the option to stop the blocking bleeding is to use the command as shown below:

KILL 83

However, doing so will stop the blocking bleed, but there are high changes more problems may creep in case very important sessions are killed in the production server. Henceforth, always keep in mind that killing session should be considered very critical and should be carried by a person much familiar in the field. 

Creating a COPY_ONLY Backups in SQL server

COPY_ONLY backups are used to create backups in the SQL server to keep the Log Sequence Number intact. The Log Sequence number is stored in the backup files, which gets used during the restoration. When we create a backup with COPY_ONLY, the sequence number in the log does not get shuffled, rather it is kept in a proper sequence. These sequence numbers are used during the restoration of backups. The feature of COPY_ONLY option was implemented starting SQL Server 2005 edition.

 In case we try to restore a backup file that was without COPY_ONLY out of the sequence then the sequence number will be overwritten by an update and we may encounter the error as shown below:

ERROR: Restore failed

System.Data.SqlClient.SqlError: The log in this backup set beings at LSN 5400000054410000001, which is too recent to apply to the database. An earlier log backup that includes LSN 5400000054410000001 can be restored. (Microsoft. SqlServer.Smo)

The database name and the Log Sequence Number can be fetched using the below script:

SELECT DB_NAME(database_id) AS [DATABASE_NAME], differential_base_lsn AS [LOG SEQUENCE

NUMBER]

FROM sys.master_files

WHERE database_id =DB_ID('SQLARENA_TEST')

Result:

To overcome the above error, we take the full backups with COPY_ONLY to restore the hassle-free restoration process.

T-SQL script for creating the full backups with COPY_ONLY is shown below:

BACKUP DATABASE SQLARENA_TEST

TO DISK = N'C:\SQLARENA\SQLARENA_TEST_Backup.trn'

WITH  COPY_ONLY,NOFORMAT, STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:

1.  Right-click on the “database” name.

2.  Click the option “Tasks”.

3.  A new window pops with a drop-down to select either “Full” or “Log”.

4.  Select the “Full” or “Log” option with a specific disk under the option “Back up to”.

5.  Check the option “COPY_ONLY” to create backups with COPY_ONLY.

6.  Click “OK” to take the full backups or log backups with COPY_ONLY.

The above for the same is shown below:

In the production server, mostly the COPY_ONLY backups are created when the business sites are offline. This is made since creating the backups offline will not affect the Log Sequence Number of the backups created and also the respective database business. 

How to create a Differential Backup in SQL Server

Differential backups in SQL Server is a database backup that happens after the latest full backup. Full backups create the backups of the entire database, but there is a gap that happens after the latest full backups and this gap is covered by the differential database backups.

Further, differential backups are taken to restore a database with the changes made since the last full backups. A unique feature is turned on when there is any change in the transaction in the database since the last full backups and thereafter a differential backup is created. Multiple differential backups can be created, and every now and then when the differential backups are created, they are from the last full backups.

If a full backup is very old, then there are high chances that the differential database backups sizes to be very big.

Here is the T-SQL command to create a differential backup in SQL Server:

BACKUP DATABASE [SQL_TEST] TO DISK = N'C:\SQLArena\LOG\SQL_TEST.DIF' WITH DIFFERENTIAL

STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:

1.  Right-click on the “database” name

2.  Click the option “Tasks”

3.  A new window pops with a drop-down to select “Differential”

4.  Select the “Differential” option with a specific disk under the option “Back up to”

5.  Click “OK” to take the full backup

The screenshot for the same is mentioned below:

How to create a Full Backup in SQL server

The full backups in SQL server are the complete backup of a database. Generally, in the SQL server, if backups are into consideration, then it comes to the point of full backups. Since it is a full backup for the entire database, there is a large amount of space required if the database consists of a lot more data and transactions. The three major types of backups are full, differential, and transaction log. While taking full backups, even some portion of the transaction logs is created. 

T-SQL Command to take transaction log backup:

BACKUP DATABASE [SQL_TEST] TO DISK = N'C:\SQLArena\LOG\SQL_TEST.bak'

STATS = 10

Steps to follow in SQL Server Management Studio graphic user interface:

1.  Right-click on the “database” name

2.  Click the option “Tasks”

3.  A new window pops with a drop-down to select “Full”

4.  Select the “Full” option with a specific disk under the option “Back up to”

5.  Click “OK” to take the full backup

The screenshot for the same is mentioned below: