Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Thursday 17 November 2016

SQL Server vNext

Microsoft announced the next version SQL Server for Windows and Linux http://tcrn.ch/2f4oUND

You can now download the SQL Server vNext community technology preview. The preview doesn’t include the business intelligence stack yet but will include improved support for R Services and a number of new machine learning and deep neural networking features.

Wednesday 16 November 2016

SQL Server 2016 Service Pack 1

SQL Server 2016 SP1 is released with key innovations accessible across all SQL Server editions. Microsoft want to make it easier for developers and partners to build and upgrade applications that take advantage of advanced performance, security, and data mart capabilities. Full details are here http://bit.ly/2eH8VGJ  

Features now available in all versions

The Future of Database Management

There is a change coming to the database administration role. Change brings uncertainty but it also brings opportunity.  The database administration role has not really changed for a decade and although change is now a foot it will be a several years before the full force of the cloud is fully embedded in the database world.  These are exciting times for database administrators.  

The new offerings from Microsoft span the entire breath from Physical to Platform as a Service.

Some offerings will always stay on physical machines while I suspect the majority will move to Platform as a Service just in the same way as physical server offerings moved to virtualization platforms a few years ago.

In my opinion, the role of the administrator will not be lost. It is after all an administration role and just because some of the database services move to Platform as a Service, administrative tasks still need to be undertaken. The complexity of database management will just transition to a new level.

Moving to the Microsoft cloud offerings there are two to consider. Infrastructure as a Service, SQL Server in a VM and Azure SQL Database. SQL Server in a VM is currently a VM that is still fully managed by individual businesses. There is the opportunity to select additional options to help lighten the load as an administrator. By using the SQL Server Iaas Agent extension, it is possible to delegate the automatic backup and patching to Microsoft.  These, although a critical part of the service, can be advantageous allowing DBAs to spend more time working on performance tuning, creating and testing those run books and data. These offerings are very likely to be the option of choice for many years due to the historic nature of applications and businesses needing to stay on versions of SQL Server that are supported by the applications that use them. Businesses need to be able to use support contracts with providers when product issues occur and that requires being on their supported configurations.

Azure SQL Database is an entirely different option. It is a Platform as a Service. Microsoft takes care of patching, backups, monitoring, high availability and security. The SQL database advisor provides help with performance tuning. This is an inclusive database service which will work well for new applications. There always seem to be a lot of smaller or less active databases that just take up valuable time and would suit this approach well. The administration cost of databases is very high for businesses, particularly as data is a key part of every business, and this service will enable business to better manage their services without having the dedicated need of an administrator.

There is another option which is now appearing which may affect development environments and that is the use of Docker images for SQL Server. Windows containers are isolated resource controlled environments and an application can run without affecting the rest of the system. This solution is likely to benefit the continuous deployment process and rapid test scenarios.  

I believe the future of administration is architecting the most suitable database solution and recommending the tools to use. Also, through DevOps, creating deployment scripts which will need to be continually written and updated, working on performance tuning of database code and data security administration. The other key change I see is the diversification of knowledge and gaining of skills through all the peripheral data tools which now need managing.  

Enhancing Business Intelligence with Data Science

The heterogeneous nature of data has resulted in an evolution of the business intelligence platform. The traditional data warehouse architectures are now a part of a greater diverse set of products and tools available for use, to gain insight. This new architecture is in Microsoft Azure, which consists of information management, big data stores, machine learning and analytics and intelligence.

This huge number of tools are known as the Cortana Intelligence Suite. Cortana Intelligence is a platform and a process to perform advanced analytics from start to finish. It is a fully managed business intelligence, big data and advanced analytics offerings. Microsoft have been helping people learn the 14 new tools to explain and show how these fit together by using a mnemonic.

Say it Cortana, Cognitive Services, Bot Framework – intelligent assistant for speech and vision
See it Power BI – interactive report and visualization
Stream it Azure Stream Analytics – real time stream processing
Big it HD Insight – implementation of apache Hadoop
Learn it Azure Machine Learning and MRS – machine learning and R Server engine
Relate it Azure SQL DB, Data Warehouse, DocumentDB -  SQL and NoSQL engines
Store it Azure Data Lake –data storage and distributed processing
Bring it Azure Event Hubs – ingest data for web, IoT and apps
Move it Azure Data Factory – pipeline to move data in and out
Doc it Azure Data Catalog – documentation
Host it Microsoft Azure - IaaS, PaaS or SaaS

These tools are supplemented by a modified process model based on the CRISP-DM (Cross Industry Standard Process for Data Mining). CRISP-DM is a data mining process model that describes commonly used approaches that data mining experts use to tackle problems. CRISP-DM has six major phases. 

The Microsoft team science process is:

There are many tools to get started learning about data science and these a just a few.

A collection of data science tools

Code samples

Free eBooks from Microsoft Press - Microsoft Virtual Academy
  • Data Science with Microsoft SQL Server 2016
  • Microsoft Azure Essentials: Fundamentals of Azure, Second Edition

Data science track in the Microsoft Professional Program

Tuesday 1 November 2016

Data Intelligence

I had the amazing opportunity to attend PASS Summit 2016, the largest Microsoft SQL Server event I the world. The event provided the opportunity to meet many international experts and engage with Microsoft engineers in every field.

As a first time attendee there was a lot of logistics to understand to get the most from the event.  I was amazed by the number of Europeans who attended the conference, many of whom I know as a helper for many years at SQLBits. PASS Summit is the pinnacle of the year and I can say I gained much from this event which otherwise would not have been possible.

The first summit keynote delivered by Joseph Sirosh who presented types of A.C.I.D. intelligence with various patterns, intelligent DB, intelligent lake and deep intelligence. A.C.I.D. intelligence being Algorithms, Cloud, IoT and Data. Intelligence is now in every piece of software with applications that continually learn from the data and subsequent information.  This pushes intelligence to where the data lives.

The intelligent database incorporates the new functionality of R Services, provides an operating system of choice (Windows or Linux) for any data deployed anywhere.  The SQL Server 2016 functionality is extended with the hybrid transaction and analytical processing (HTAP) solution which the In-Memory OLTP, In-Memory Analytics, In-Memory Azure SQL Database (launched 15 November) combined with Polybase enable fast querying of structured and unstructured data. Polybase can connect to all data sources such as MongoDB, Hadoop, Teradata, Oracle.  Adding machine learning to the suite of tools add benefits such as real time fraud detection.  DocumentDB properties were also discussed highlighting the blazing fast performance and global replication.

The intelligence lake enables the handling of petabytes of data through algorithms and the extensible data lake. Azure analysis services is available at public preview and Azure SQL Data Warehouse with its parallel processing and scale out was offered as an exclusive one month free trial. There was a great demo by Julie Koesmarno on Azure cognitive services with U-SQL which provided sentiment analysis of War and Peace.

The final part of the key note presented deep learning which looked at many real life examples of learning everywhere from collecting data reviewing whether power lines looked in a good state of repair to face detection to medical research detecting cancer cells.

The keynote was truly inspirational. There were many other amazing sessions with a vast amount of information on diverse topics which I will share in separate posts.