Microsoft DataAmp – SQL Server 2017

The DataAmp webcast was packed full of announcements. The Webcast was delivered by Scott Guthrie and Joseph Sirosh. The SQL Server product delivering intelligence, trust and flexibility.

Microsoft confirmed that the next version of SQL Server is SQL Server 2017 and will be available simultaneously on Windows, Linux and Docker. Download the SQL Server 2017 datasheet.  It will be the first RDBMS to deliver AI with data. There is a convergence of cloud, data and intelligence. Delivering AI with data: the next generation of Microsoft’s data platform blog shares more information.  SQL Server 2017 Community Technology Preview 2.0 now available.

There were so many new features announced only a few are mentioned below.  There are adaptive query processing improvements which will enhance the performance of workloads. There is a You Tube video SQL Server 2017: Adaptive Query Processing discussing this. Threat detection is now in Azure SQL Database and is straight forward to configure. 

Hybrid Cloud just got easier to adopt with the new Azure migration resources and tools. To help with SQL Server migrations to the cloud features such as Service Broker, SQLAgent, Profiler etc. are now available in Azure. There is a new data migration service for automatic migration for SQL Server, Oracle and MySQL in Azure .

SQL Graph

Storing and analyzing graph data relationships. This includes full CRUD support to create nodes and edges and T-SQL query language extensions to provide multi-hop navigation using join-free pattern matching.  The SQL Server engine integration enables querying across SQL tables and graph data.

SQL Server on Linux

 

The official Microsoft repository for SQL Server in Docker containers is here.

Here are a few videos to help get you started with Linux:

• SQL Server 2017: Security on Linux
• Get started developing apps with SQL Server 2017 running on Linux
• SQL Server 2017: HA and DR on Linux

Analytics 

SQL Server is the first commercial database to include Deep Learning algorithms, with the announcement of the Microsoft Cognitive Services general availability of the FACE API and Computer Vision API.

Azure Data Lake Services now have petabyte scale.

Azure Analysis Services became generally available. You Tube video: SQL Server 2017: BI enhancements.  

You can use Python for advanced analytics, You Tube video: SQL Server 2017: Advanced Analytics with Python

Azure DocumentDB

Azure DocumentDB is globally distrubuted and offer limitless scale of throughput and storage. It can be used for things such as IoT applications that need low response times and need to handle massive amounts of reads and writes.

Cortana Intelligence Solution Templates

You can now quickly build Cortana Intelligence Solutions from preconfigured solutions, reference architectures and design patterns. Some are released with more to follow.

The really important thing that I am excited about is the flexibility of choice within the SQL Server product.

Joseph Sirosh concluded comparing the industrial revolution with the intelligence revolution of today.

 

Event: Microsoft Data Amp

I am looking forward to watching the Microsoft Data Amp event tomorrow. The event covers putting data, analytics and artificial intelligence into the heart of your solutions. It states it will share the latest on big data and machine learning innovations. The event is online April 19, 2017 at 8AM PT .  There will be two keynote speakers Scott Guthrie and Joseph Sirosh.

 

SQLBits and Community Volunteering

SQLBits has always been the place I feel at home with my #sqlfamily. I have been to every SQLBits.

There have been 15 SQLBits conferences to date. The locations have been around the country each with a different theme.

• SQLBits 1 - October 2007, Microsoft UK, Reading
• SQLBits 2 - March 2008, Lakeside Conference Centre, Birmingham
• SQLBits 3 - October 2008, University of Hatfield, Hatfield
• SQLBits 4 - March 2009, Manchester Metropolitan University, Manchester
• SQLBits 5 - November 2009, Celtic Manor, Newport
• SQLBits 6 - April 2010, Church House Conference Centre, Westminster
• SQLBits 7 - September 2010, University of York, York
• SQLBits 8 - April 2011, Brighton Grand Hotel, Brighton
• SQLBits 9 - September 2011, Britannia Adelphi Hotel, Liverpool
• SQLBits 10 - March 2012, Novotel Hotel, Hammersmith, London
• SQLBits 11 - May 2013, East Midlands Conference Centre, Nottingham
• SQLBits 12 - July 2014, International Centre, Telford
• SQLBits 14 - March 2015, ExCeL London
• SQLBits 15 - May 2016, Liverpool Exhibition Centre
• SQLBits 16 - April 2017, International Centre, Telford

I have helped now for 7 years and it has been an amazing opportunity to help the community, network and learn loads from the high quality training. It is the largest data platform conference in Europe and we are privileged to see some of the world’s best speakers. I have seen the conference grow and change as the product has changed. I always enjoy greeting attendees as they arrive for SQLBits and hope they will have as much fun as I do at the conference. It is a time when you can fully immerse yourself in data and the product and find out what exciting new features there are and what other members of the community have found to share. I would recommend volunteering to anyone as the experience you gain from this is very rewarding and it is good to give back to the SQL Community.

To add to my volunteering experience I joined the SQL Relay team last year, so looking forward to helping bring this years event to the community between 9 – 13 October 2017.

  

State of the SQL Nation and the Microsoft Engineering Model

SQLBits 2017 was different this year and instead of a keynote launching the event they had a 15 mins Q & A session with Conor Cunningham and Simon Sabin. They discussed the rapid change of new engineering features in the product. There are now new features shipped monthly. The new model requires running test 24/7 and the interesting change is that all builds have the same level of sign off, increasing the quality of the build now. This is why the quality of the 2 monthly cumulative updates has increased. They run over 700,000 functional automated tests and stress test as well. They aim to ship with the minimum viable product and release updates every 3 to 6 months on them. They don’t deprecate features specifically unless there is a security risk and they use telemetry to help measure feature adoption.

SQL Server / Azure Engineering Model

A second session I attended was about the completely new engineering model that Microsoft now operate. There were some very interesting changes that I think all businesses need to think about when designing and structuring new teams to adapt to the changing model.

Microsoft DevOps Interesting Facts

• Microsoft have around 1.7 million production databases.
• They have no Database Administrators (DBAs).
• They have no Operations team.
• Engineers automate everything.
• SQL Server Azure runs 100% DevOps.
• SQL Server is managed by telemetry. 600TB of logs are collected every day and machine learning algorithms used to automatically fix, migrate and manage the systems.  They have a large big data pipeline to process the logs in various ways.

 Privacy Statement

The SQL Server Privacy Statement has been updated. It explains the data collection and use practices for Microsoft SQL Server 2016 (SQL Server) and included or referenced products which can be installed separately (such as SQL Server Management Studio). This is a huge change with Microsoft stating explicitly what data they store.  It explicitly provides a data classification matrix for access control, customer content, end-user identifiable information (EUII) , internet based services data and system metadata and explains permitted usage scenarios, access restrictions and retention requirements. This will massively help with regulatory and compliance requirements for the up and coming GDPR. You can now OPT IN and OPT OUT.

Compliance Boundary Around the Service

The way for security is using more automated ways to protect systems. Microsoft use

• Two factor authentication to cross boundary to administrator / troubleshoot  
•  All access is audited
• Customer data is protected in the boundary
• Telemetry is used to protect data
• Telemetry is scrubbed to protect customer data
•  The disks are encrypted

SQL Server vNext

SQL Server vNext is to be shipped later this year.  We can expect to see the SQL Server products to have more frequent releases than the previous every 2 to 3 years.

Another big change in Microsoft development is for all SQL product features is that they need to have customers asking for them. This sounds like a more relevant model.

The vNext version of SQL Server will have important new features such as being able to

• SQL Server on Linux
• Linux based Docker containers
• SQLGraph
• Cluster-less Availability Groups
• Availability Groups can now work across Windows-Linux to enable cross-OS migrations
• Online non-clustered columnstore index build and rebuild
• Temporal Tables Retention Policy support

 SQL Azure Cloud Service is similar but not quite the same as the SQL Server product.