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:
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.
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.
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 |
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.
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.
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 |
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?
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!!!
