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

Monday 12 December 2016

Data past, data present and data future

Database and data management practices are changing and new practices need to be adaptive and agile. Here are a few data thoughts for data past, data present and data future.

Data of Christmas Past
  •  Reports and dashboards have become standard place within the business. 
  •  Big data is just the new look of data.
  • DevOps has become mainstream.
Data of Christmas Present
  • There is an increasing plethora of database architecture designs to choose from which means selecting the right design and database engine for the right job is harder than before.
  • Business is driving the need for data. 
  • Best practice delivery is hard in a fast changing environment. 
  • Complexity is increasing in a diverse landscape.
Data of Christmas Yet to Come 
  • The face of database administration is changing with multiple of types of engines, tools, applications and cloud offerings. 
  •  It is necessary to have broad range of database knowledge to ensure best practice configurations are deployed for the plethora of tools.  
  • Predicative analytics are becoming critical for business. 
  • Deep learning utilizes machine learning to model data at high levels of abstraction which will transform how we live.
  • Research is starting to become embedded in industry with the need to drive the next innovation.

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.

Saturday 8 October 2016

Innovation and Knowledge Development Research Award

Friday 7 October was a very special day for me. I attended the Annual Association of Open University Graduates Research Awards Ceremony at The Open University in Milton Keynes with my family. I was amazingly privileged to be the recipient of the 2016 AOUG Will Swann Award for Innovation and Knowledge Development for dedication and outstanding achievement in postgraduate research.

This followed the submission of my thesis A Study into Best Practices and Procedures used in the Management of Database Systems for which I am awaiting my viva voce exam. 

Tuesday 4 October 2016

Microsoft Machine Learning and Data Science Summit 2016

The Machine Learning & Data Science Summit took place in Atlanta between 26 - 27 September 2016.

There are many Videos from the Microsoft Machine Learning & Data Science Summit in Atlanta to watch.

I have so far watched the great Day 2 Keynote Session by Dr. Edward Tufte on The Future of Data Analysis included 

"Data analysis seeks to learn from experience.  Better inferences require better thinking and better tools. Practical advice about how to   make more credible conclusions based on data. What we can expect in the future, and what we should aspire to in the future. "

The many other videos cover many topics including practical patterns to jump start your analytic solutions to data lake patterns and practices.

Thursday 29 September 2016

Microsoft Streamlines Technical Certifications

Microsoft has announced a streamlining of certifications.

The Born to Learn site contains the full details,

The five new expert certifications are:

  • MCSE: Cloud Platform and Infrastructure - focusing on skills validation for Windows Server and Microsoft Azure
  • MCSE: Mobility - focusing on skills validation for Windows Client and Enterprise Mobility Suite
  • MCSE: Data Management and Analysis - focusing on skills validation for both on-premises and cloud-based Microsoft data products and services
  • MCSE: Productivity - focusing on skills validation for Office 365, SharePoint, Exchange, and Skype for Business
  • MCSD: App Builder - focusing on skills validation for Web and Mobile app development

To earn each of these credentials, you must first earn a qualifying Microsoft Certified Solutions Associate (MCSA) certification and, then, pass a single additional exam from a list of electives associated with the corresponding Center of Excellence.   

Every year, you need to take an additional exam from the list of electives, demonstrating your investment in broadening or deepening your skills in a given Center of Excellence to retain the certification.  Each time you earn the certification, a new certification entry will be added to your transcript. 

Wednesday 28 September 2016

Thesis Submission

After 6 years working on my part time PhD entitled A Study into Best Practices and Procedures used in the Management of Database Systems, it is complete. I wrote about 95,400 words.

The final package was posted for examination.

Monday 15 August 2016

DocumentDB Tools

There is a useful cheat sheet to help with writing DocumentDB queries. This can be downloaded here

Estimate Request Units and Data Storage

Query Playground converts T-SQL Syntax to JSON

Friday 5 August 2016

OpenMinds 2016 Database Research

The 'Get Animated about Research' article shares details about my research with the kind help of my friend Andrew Fryer. The article on page 10-12 is in the annual OpenMinds 2016 magazine dubbed 'the journal for enquiring minds', The Open University alumni publication.

Thursday 4 August 2016

Document Database Comparisions

Azure DocumentDB is a NoSQL database that leverages a write-optimised, latch-free database engine service for highly available and globally distributed apps.

An interesting comparison of DocumentDB offerings.

Thursday 14 July 2016

Database Lifecycle Management

This survey report released discusses the expertise required for managing data complexity. The findings identify the need for database expertise to manage this challenging environment.

Tuesday 12 July 2016

Systems Thinking can help Database Management

A different way to think about managing database systems is using systems thinking. A fun animation about systems thinking and evaluation.

Wednesday 6 July 2016

DBaaS - Transforming Database Management

There have been various reports about Database as a Service.

The 451 Research October 2015 report on the state of Database as a Service 

The Report

IBM State of DBaaS 2016 report - Database-as-a-Service

The Report

Sunday 19 June 2016

The State of DevOps

Puppet labs have been reporting on the current state of DevOps over the last 4 years.  They promote the need to get on board with DevOps or be left behind. The 2015 report can be found here.

Modern IT: DevOps to ITIL, Creating a Complete Lifecycle for Service Management free training.

The training looks at merging the DevOps movement of DevOps into existing ITIL service management practices. DevOps practices and techniques can be used with established ITIL service management framework, to get the best of both worlds from development and operating environments.

Wednesday 1 June 2016

The Wait is Over: SQL Server 2016 General Availability and Tools

SQL Server 2016 is generally available today. The release blog provides more details. The full featured Developer Edition is free. This can be downloaded
The generally available release of SQL Server Management Studio (SSMS) is annouced today. It provides a means for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editor. It features improved compatibility with previous versions of SQL Server, a stand-alone web installer, and toast notifications within SSMS when new releases become available.

The SQL Server 2016 RTM build is 13.0.1601.5. SSMS 2016 has a build number of 13.0.15000.23.

SQL Server Data Tools (SSDT) for Visual Studio 2015 is now generally available.

SSDT can be  downloaded for free to build SQL Server relational databases, Azure SQL databases, Integration Services packages, Analysis Services data models, and Reporting Services reports.

The SQL Server 2016 free e-book can be downloaded. It  covers

  • Mission-Critical Performance: Chapters cover faster queries, better security, higher availability, and the improved database engine.
  • Deeper Insights Across Data: Chapters cover the broader data access, increased analytics, and better reporting in SQL Server 2016.
  • Hyperscale Cloud: Chapters cover the improvements in Azure SQL Database and how to expand your options with SQL Data Warehouse.

Tuesday 31 May 2016

New Sample Database: Wide World Importers

Microsoft have replaced the the Adventure Works Sample Databases. There was Pubs, then Adventures Works and now Wide World Importers. Wide World Importers is a sample database that both illustrates database design, and how SQL Server and Azure SQL Database features can be leveraged in an application.

The sample database represents a typical database. The Wide World Importers database can be used for  transaction processing (OLTP - Online Transaction Processing) and operational analytics (HTAP - Hybrid Transaction and Analytics Processing). There are sample queries, processes for ETL (Extract, Transform, Load) that migrates data from the transactional database WideWorldImporters to the data warehouse WideWorldImportersDW and descriptions that show how to leverage SQL Server features for analytics processing.

Applies to: SQL Server 2016 (or higher), Azure SQL Database
Features including: Core database features, PolyBase, nonclustered columnstore index, Row-Level Security
Workload types: OLTP, OLAP, IoT, Analytics, Operational Analytics
Programming Language: T-SQL, C#

Sunday 8 May 2016

SQLBits in Space

SQLBits XV was held between 4 -6 May 2016 at the Exhibition Centre in Liverpool. It was the official UK launch event of SQL Server 2016 which will RTM 1st June. There were lots of amazing sessions held for the first time in domes.

The keynote was delivered by Joseph Sirosh, the corporate vice president of the Microsoft Data Group. His keynote entitled the unreasonable effectiveness of data. A paper was written by Alon Halevy, Peter Norvig, and Fernando Pereira of the same titleJoseph Sirosh mentioned the future effectiveness of data and the Sloan Digital Sky Survey, the 1st astronomy datascope. A take away was that there are many new data services and R should be the language to learn. 

There were many sessions covering the new features of SQL Server 2016 on both the BI and administration side. A highlight for me was the training day on data science by Buck Woody and using the Cortana Intelligence suite. 

The Cortana Analytics Suite big data and advances analytics process

The Azure IaaS and PaaS Services are embedded into these services.


U-SQL is another new language that allows you to query unstructured data. Michale Rys delivered a very informative session on Azure Data Lake and U-SQL. The traditional data warehousing approach is

  The new Data Lake approach

 The slides are http://www.slideshare.net/MichaelRys

 There are many new features in SQL Server 2016 and many new data features in Azure.

Monday 2 May 2016

SQL Server 2016 General Availability

SQL Server 2016 will be generally available on 1st June 2016. The SQL Server 2016 editions will include Enterprise, Standard, Express, and Developer.  SQL Server 2016 Developer edition will be free to download.

The SQL Server 2016 preview details .

Friday 22 April 2016

Database Research and Data's Journey

The first TechNet UK blog post was entitled Management of Database Systems Research and was a prĂ©cis of the database research I am undertaking. A comic strip was included telling the story of Data and the path that lead to starting the database research.  

My research journey has progressed and my new blog post crosses the boundary between academia and the industrial world of database systems. Data reprises his role taking the reader through the research journey and methods used.