SQL Server 2017 becomes the first commercial RDMS with built-in AI support

AI Into SQL Server 2017

Today (4/19/2017), Microsoft announced the release of SQL Server 2017 CTP 2.0 (formerly known as SQL Server vNext). It has pretty exciting features including deep learning AI capabilities. Deep learning model demonstrated today at Microsoft Data Amp is used some medical data to figure out the probability of getting a lung cancer for a particular patient. Faster early detection of such potential disease helps to reduce the healthcare cost and also to have a healthier life. All these happens under one roof meaning, in one single data platform which is SQL Server. All the analytics capabilities of the deep learning system are now integrated into SQL Server which makes super easy for data scientists to work and analyze data. The application has to call just a single stored procedure to do the deep learning analytics of a patient, it's that much simple.

The data, the model, and the intelligence are all built into SQL Server which will eliminate so many challenges of building today’s AI application by using so many different types of tools.

Python Integration

SQL Server 2017 has a built-in support for Python code inside T-SQL queries which gives so much powerful programming capabilities. SQL Server 2016 supported R integration as well. You can use sp_execute_external_script system stored procedure with T-SQL to call R or Python language which external to T-SQL.

Adaptive Query Processing

Adaptive Query Processing is one of the extremely powerful database engine enhancements in SQL Server 2017. With this new feature SQL Server has the intelligent to learn itself about query processing and improve itself and generate better execution plan based on past query execution with no human intervention. This is an amazing feature that I was dreamed for years.

SQL-Graph

Graph data represents many-to-many relationships and typically relational databases need to store that kind of relationships as one-to-many relationships. SQL Server now supports graph data and processing of graph data. You can create graph objects which create nodes and edges in SQL Server 2017. This essentially extends the SQL Server query processing capabilities. This query capability is fully integrated into SQL engine in SQL Server 2017. Previously, you had to use a special type of databases called graph databases to store this kind of data.

One platform which supports both Windows and Linux

All the features of SQL Server 2017 now support both Windows and Linux platforms.

In a nutshell, SQL Server is not just another database server, it is an extremely powerful data platform eco-system because it combines AI and analytics into one product. Everything is built in. So much easier than using hundreds of different tools. I can’t wait to get some hands-on of these new features.

Cheers!

Powershell supports Linux

Microsoft this week announced that PowerShell is now open sourced and available on Linux, the start of a development process that aims to enable users to manage any platform from anywhere, on any device.

More details

Microsoft releases CU #1 for SQL Server 2016

Microsoft released their latest flagship database product, Microsoft SQL Server 2016 on June 1st 2016. After 2 months, now Microsoft announced the release of CU #1 which contains fixes for 146 issues across different categories of the product.

The CU1 has 146 hotfixes in 10 different categories as stated above. The highest no.of hotfixes are for the SQL Service.

You can download the CU1 by using the below link.

Cumulative Update 1 for SQL Server 2016

Cheers.

SQL Server 2014 changes ACID behavior

Any database professional know what it means by ACID. They are some golden properties of relational systems.

A – Atomicity

C – Consistency

I  - Isolation

D – Durability

SQL Server supports ACID properties fully until SQL Server 2012.  In SQL Server 2014 (still in CTP level) DBA has the option to control the D of ACID. Till SQL Server 2012, the log records need to be flushed to the disk before a transaction can be declared committed. However in SQL Server 2014, this statement is not 100% valid with the introduction of new feature called “delayed durability”.

With this new option, SQL Server transactions can be either fully durable or delayed durable. BOL states;

“Fully durable transaction commits are synchronous and report a COMMIT as successful and return control to the client only after the log records for the transaction are written to disk. Delayed durable transaction commits are asynchronous and report a COMMIT as successful before the log records for the transaction are written to disk. Hardening transaction log entries to disk is required for a transaction to be durable. Delayed durable transactions become durable when the transaction log entries are flushed to disk.”

As it obvious thing, the delayed durability comes with a cost that, there could be a data loss in the event like fail over. The advantages of this option are, reduce contention and increase throughput.

Use of delayed durability option is not suitable for all the cases. This can not be used in situations where you cant tolerate a data loss.

Delayed durability option can be applied at Database level, Atomic code block level and Commit level.

It is worth to be considered why Microsoft changed the ACID behavior after 22 years of SQL Server history. I believe this is an response to the highly volatile database technology market which we are seeing today, specially with the widely use of NoSQL technologies. NoSQL technologies are using “BASE” (Basically Available, Soft state, Eventual Consistency) against the ACID in relation systems. With the introduction of BASE the NoSQL vendors claim that the support for big data and huge performance gain in OLTP as opposed to the relational databases.

Finally, I believe it is a great option and DBAs will like the new feature. However, SQL Server 2014 is still in CTP which means it is subjected to change in future releases, sometimes this could be completely removed.

For more details, How to: Control Transaction Durability

Replication framework between relational database and document oriented (NOSQL) database

I’m working on a research project of building a replication framework from relational database to document oriented (non-relational/nosql) database system. This is to fulfill the research requirement of the MSc degree. The POC would be developed to demonstrate the solution by using MS SQL Server (relational) and mongoDB (document oriented).  I would like to hear any comments/thoughts about this from the community.


I started a discussion on this in LinkedIn mongoDB user group and received couple of valuable comments. Many thanks for those who given the comments. 


You can see them here.

VLDBs backup in few seconds

VLDB - Very Large Databases, like several Gigs of databases. (50GB, 100GB, etc) How about managing backups? You may need to spend 20-30 hours to take a full backup of such DBs. Of course SQL Server 2008 has improved backup system than its earlier versions. Another alternative method would be to use third party tools like, LiteSpeed or RedGate backups. But stills you can't make it in few seconds. How about a system to take such a large DB backup in a few seconds? ANSWER is to use SAN snapshot backup.

Pearson Lanka DBA team at SS SLUG

Pearson Lanka DBA team presented six demo's at last meetup of SS SLUG (http://sqlserveruniverse.com/v2/ssslug.aspx). The series of demo's named as WirlWind DBA session. It is really a mind blowing session which each session limited to ten minutes including three minutes Q&A. 

Why so special about these presentations? Yes; it is based on our day to day DBA activities, which we encountered and gathered the knowledge after doing many research. So thought of sharing the valuable information to the community who loves databases. 

There I presented on how to read a deadlock graph which I'm going to do a small write up and publish in future. 

I've the videos of all the presentations and hoping to upload them to youtube.