New Features in SQL Server 2016 - Part 2

Good Morning Guys,

As we have seen the top 3 new features in SQL Server 2016, here is the continuation of that post. We will see few other enhancements in it.

Following are the list extending to these top 3 features:

4. Enhancements to AlwaysOn:

As everyone knows the concept of AlwaysON was introduced by Microsoft in SQL Server 2012. This was introduced due to many limitations in the previous versions of SQL Server on High Availability and Disaster Recovery AlwaysON was introduced for mission critical Enterprise Application.

It was released for only Enterprise Edition but in SQL Server 2016 it will be released in Standard Edition of course with limited functionality. AlwaysON has also seen enhancement in SQL Server 2014 as well.

There is 1 Primary replica, 8 Secondary replicas and upto 3 replicas can be synchronized compared to 2 secondary replica can be synchronized in 2014.

Distributed transaction or MSDTC was not supported between Databases on AG in 2012 and 2014; which is now supported in SQL Server 2016.

We can now perform Full, File, File group or Log backups on the secondary replica Databases, but can't perform Differential Backup.

For more detail please refer to this blog on AlwaysON.

5. Native JSON Support:

JSON stands for JavaScript Object Notation. This is mainly provided for the developers, who will use this function for organizing the data in more logical manner. It is much more similar to XML already available by SQL Server.

It format the SQL result set as JSON by adding FOR JSON clause in the From statement. It is very much similar to XML in terms of types as well:

• RAW
• AUTO
• Explicit

Following is the syntax for using the JSON:

SELECT column, expression, column as alias
 FROM table1, table2, table3
 FOR JSON [AUTO | PATH]

Figure 1 shows the demo how the JSON script be written from a SQL Table:

Figure 1

Please refer the msdn blog here for more detail on JSON.


6. Enhancement In-Memory OLTP:

The feature of In-memory OLTP was introduced in SQL Server 2014 with the name "Hekaton" which is derived from a Greek which means 'one hundred' i.e. the goal of the project was to see 100x performance appraisal.

It has a separate query processor engine which is lock free design but at the same time it also maintain the data transnational integrity. Due to which it is different from Pin Table in SQL Server 6.5 release or putting Databases in SSD's (Solid State Disks). Refer this link to get an idea about the architecture of In-memory OLTP.

When it has done the debut it came with lots of drawback. And now from these demerits many has been converted to merit. lets see some of them below:

    i.  The max table size 256 GB which has changed to 2 TB.

    ii. TDE feature which is the greatest security feature in SQL Server was not supported in 2014 version, but now in 2016 it is supported.

    iii. Another major drawback clause such as IN, EXISTS, DISTINCT,OR,NOT,OUTER JOIN was not supported for query. Now these clauses are supported in SQL Server 2016.

    iv. LOB data type such as Varbinary(MAX) or Varchar(MAX) were not supported by the released version but from SQL Server 2016 it is supported.

    v. Similar way Foreign Key was not supported in older version which is not supported in SQL Server 2016.

There are many other new properties which has been enhanced in the new product of SQL Server 2016 for In-Memory OLTP check this msdn link for more detail.

These were the few new and some enhancement done in SQL Serve 2016. Check out this link which has some series of SQL unplugged videos for more details. So guys get ready to work with these exciting new features of SQL Server 2016 for Administrators.

Also along with features for DBA many more are waiting for BI and Developers guys as well.

New Features in SQL Server 2016 - Part 1

Hello Friends,

I've given a short introduction to you on SQL Server 2016 here now, it's time to learn some of the much awaited new features for DBA's. You can consider this a qualified interview question as well.
I'll try to explain these features one by one here:

1. Stretch Database:  

This is the most interesting feature introduced by Microsoft in SQL 2016. The word "Stretch" means expand, which means you can expand your local (on-premise) Database to Azure (Cloud). So here we will see the combination of on-premise + Azure in one Database.

Stretch Database

By default, this feature is disabled. We need to enable through SP_Configure 'Remote Data Archive'. 

Once it is enabled, you can configure this option under; 

Right Click on Database; Next Tasks; Next 'Enable Database for Stretch...' 

You need to supply the Azure Credential and select the table which you want to move.  

The whole purpose of Stretch Database is, we can move the old historic data to Azure by remote query processing. 

No changes is required from Application side. If a query is executed from an Application it will hit the on-premise Database and this on-premise Database will fetch the data from Azure and return the result set. (Only if that particular table data is moved to Azure)

Backup and Restore procedure of these Database would be little different from regular one. If you follow the regular 'Full' backup it will only consider the on-premise Database.

This would be consider as a good option to your environment because you can save the Hardware cost, Backup time would be less, Since historic data are moved out query processing for the current data will be improved. Also most important it's not a pre-requisite to have knowledge on SQL Azure  so any DBA who din't had luck to worked on SQL Azure should not worry.

There would be little Performance impact because it will fetch the data from on-premise as well as Azure. This will be the major drawback of this feature.

This feature must be only available in Enterprise Edition (Not sure yet). 

This was just a brief note about this Database. More information about Stretch Database are available here on msdn. You can also refer this site, it provides each and every details with GUI about Stretch Database.

2. Always Encrypted: 

Moving ahead with this interesting feature of Stretch Database, you might be definitely having question about data security, when there is any data movement from on-premise to Azure or vice-versa.

With this let me introduce to the next exciting feature of SQL 2016 which is Always Encrypted.
You might be well aware of TDE (Transparent Data Encryption) which was introduced by SQL Server 2008 in Enterprise Edition. It's use is to encrypt the data at rest with the help of certain keys (Master Key and Certificate). But as the data in Database is encrypted at the rest, the data is very much transparent; when the data is on fly (i.e. when data is moved from Application to Database or vice-versa). So man in middle attack is easily possible.This was the main drawback for TDE.

To overcome this drawback Microsoft has introduced new or can say extended security feature of TDE i.e "Always Encrypted" feature in SQL Server 2016.

Always Encrypted

Basically there is an enhancement in ADO.NET library which will protect the data in rest as well as motion.

So basically, the keys and certificate which we have create in SQL Server has to be deployed during application creation. This deployment of the Keys and Certificated will be handled by the ADO.NET library.

The figure on right will give an fair idea about the same. The data during fly will be encrypted and decryption will take place on the client side with the help of keys present in ADO.NET library.

This was just an overview on Always Encrypted, you can find more details here in msdn blog which is explained in depth with GUI. This is a very important feature for any organisation which handles critical data in there Database w.r.t to Auditing and Compliance. 

3. PloyBase: 

This one is the most important and exciting feature Microsoft has introduced ever and also the unexpected. Before this version any RDBMS product has the ability to interact with any other RDBMS (Through Linked Server).
But for the first time in SQL Server history it will interact with Non-RDBMS product as well. If you have gone through my previous post here, I already introduced to you about this Non-RDBMS product i.e. Hadoop.  

PloyBase

Looking into the future of Database world, Microsoft has also considered to introduce this feature in there upcoming product.
Earlier Apache Sqoop (It is a tool for data transfer between Hadoop Cluster and Relational Database) has the ability to interact with other RDBMS application but it has few limitations.
These limitations can be overcome with this new feature in SQL Server 2016.

If you have both SQL Server and Hadoop Cluster in your environment now it will not be necessary to learn Hadoop query to fetch data it can be easily just like Linked Server in SQL Server 2016.

How it will work?

• Microsoft has introduced two new SQL Services which will be installed during installation of SQL Server. (Uncheck the box if there is no need of this Service)
• We need to start by configuring SP_Configure parameter 'Hadoop Connectivity',0; by passing an appropriate value.
• You can use the following configuration values:

0	no Hadoop connectivity (default)
3	Enable connectivity to Cloudera CDH for Linux
4	Enable connectivity to Hortonworks Data Platform for Windows Server (HDP), HDInsight on Analytics Platform System, or HDInsight’s Microsoft Azure blob storage
5	Enable connectivity to Hortonworks Data Platform (HDP)for Linux

Kindly refer to this link you will get a complete details about this feature.

These are just an overview of three new feature which is introduced by Microsoft in there new version of SQL Server 2016. We will see the another few features in our next post.

Till then stay tuned for more updates and feel free to share your comments here. Also do like and share if you liked the post.

Check this link for Part 2 of this post.

Keep Learning and Enjoy Learning!!!