SQL Server on the Edge Time for Pi

SQL Server on the Edge Time for Pi Patrick Flynn, Link Group

Patrick Flynn SQL Server DBA Link Group patrick@sqllensman. com @sqllensman Patrick Flynn MCM – SQL Server 2008 MCSM – Data Platform Production DBA for 10+ years. https: //github. com/sqllensman/Presentations

Own your career with interactive learning built by community and guided by data experts. Get involved. Get ahead. Explore your PASS community Free online webinar events Local user groups around the world Connect with the global data community . org Online special interest user groups Learning on-demand delivered to you Get involved

Missed PASS Summit 2019? Get the Recordings Download all PASS Summit sessions on Data Management, Analytics, or Architecture for only $399 USD More options available at PASSstuff. com

We are thrilled to say the first ever PASS Virtual Summit will be taking place this November! We will be launching PASS Summit into a whole new world, and with that comes exciting opportunities with what we can offer you. We will go beyond simply moving sessions online to fully embracing everything a virtual environment can offer. Find out more at PASS. org/summit

Thank you to our Global Sponsors and Supporters

This event was sponsored by Microsoft Learn more about SQL Server 2019 today: -Get free training: aka. ms/sqlworkshops -Download the SQL 19 e. Book: aka. ms/sql 19_ebook

Edge

The Basics of Edge … … … Other useful terms to know - • Fog Computing decentralizes computing infra in which data, compute, storage, and applications are distributed in the most logical, efficient place between the data source and the cloud. • Io. T = uniquely identifiable, trackable and internet -connected objects • Edge Computing pushes intelligence and computing power closer to the source of the data, i. e. machines such as pumps, turbines, sensors, and more. • Edge Device also referred as Edge Computing Device, such as sensors or other connected devices (gateways) that processes or analyzes data, instead of sending it back to the cloud or remote data center. It has also been referred to as an apparatus that acts as an entry point to the core networks of an enterprise or a service provider Connected Semi-connected Offline / Disconnected Read more: https: //www. networkworld. com/article/3224893/what-is-edge-computing-and-how-it-s-changing-the-network. html

Data Landscape is Changing Top trends driving the change • Latency, security, bandwidth/connectivity continue to gravitate data compute needs to edge • New sensors and chip innovation with analytics and lower cost enable more Edge compute • 5 G and Io. T Mesh architecture accelerate distributed computing • Cost Inefficiency to store everything in Cloud By 2025, 75% of enterprise-generated data will be created and processed outside the datacenter or cloud, up from less than 20% today Source: Gartner” “Edge Computing Solutions for Industrial Io. T, July 2018, Gartner” “Top Strategic Io. T Trends and Technologies Through 2023. September 2018, Gartner”

Edge data Volume of Captured Io. T Data (in Exabytes, 1018 bytes) 2, 500 CAGR 2, 000 Less than 50% of structured data is actively used in decision making • Less than 1% of unstructured data is analyzed or used at all Challenges for data engines running on Edge 1, 500 1, 297 35. 7% 1, 064 1, 000 824 500 358 0 • 112 482 165 633 242 2016 2017 2018 Sensor and Machine Data 361 524 749 2019 2020 2021 Video and Image Data Source: ABI Research, Harvard Business Review, Cisco, Constat Edge Data Research 21. 9% • Limitations on size and processing power • Limited database choices • Security and compliance concern • Complexity of management • Data siloed on the edge • Learning curve for developers and operators • Cost of licensing

Azure SQL Edge

Microsoft - Intelligent cloud and intelligent edge Microsoft Azure a Edge MCU b c Interactive devices d Edge Gateways/Servers Azure Io. T Edge Source: Julia White Blogpost outlining Microsoft’s Intelligent Cloud, Intelligent Edge POV e

Consistent Data & Analytics Engine running natively across Edge, Data Center and Cloud SQL Server Azure SQL Database small-footprint, Industry-leading. Adata engine with turn-key, full AI/MLML-capable data engine running workloads connected or offline for Edge capability for mission-critical

Small footprint, AI built-in, optimized for Edge Turn-key management Time. Series Built-In Time-Series Streaming and Analytics Machine Learning over all data with SQL In-Database ML and Spark Local streaming, storage, analytics all-in-one Edge Single management plane for deployment & updates L AN A H SA P 2 e. S Q DB gr st cle Q Po IB M ra O y. S M Se rv er L 200 180 160 140 120 100 80 60 40 20 0 SQL Light-weight SQL Engine <500 MB tuned to run on ARM and Intel devices Unparalleled performance and security L x 64 AI built-in SQ Arm 64 Time-series built-in Vulnerabilities (2010 -2018) Choice of platform Most secure with industry leading performance benchmarks Develop once, deploy anywhere for your apps Competitive TCO Data Center/Cloud

Key Capabilities Native data movement Data Center/Cloud Storage Device Sync Gateway Data Sync Interactive Dev. Data Streaming Complete analytics platform Data Center/Cloud ML Services Trained Models Ease of management Management Plane (e. g. Azure Io. T) Create, deploy, update Predictions Results Gateway Runtime, Messages Gateway Interactive Dev. Native-scoring / Predictions from ML models Edge Devices Optimize network bandwidth for native data sync with cloud/enterprise portal Process time-series data directly in Azure SQL Database Edge Single management plane for deployment, remote updates/re-initialize for feature and security Stream Io. T data from Edge with a high performant engine Use SQL in-database machine learning and Time. Series analytics for predictions and anomaly detection etc. in low-latency on Edge Consistent security; flexible HA/DR built-in Device and data relationship mapping

Edge compute autonomy with bi-directional to cloud 1. Data streaming Azure Io. T Hub SQL Server Cosmos DB Azure SQL DW anomaly detection, classification Azure ML Analytic s 5 Cloud / On-premises 9 7 4 6 2. Data compute + storage 3. In-DB parallel ML processing data sync Custom Apps Edge device sync local ML results back 7. Push down ML model 8 BI Relational data Sensor 1 2 Sensor 2 … 3 Time-series data 1 Small-footprint container on Arm, x 64 devices w/ Linux, Windows OS 8. Create, deploy, update 9. BI at both edge and cloud

Slide Title Here Heading Lorem ipsum dolor sit amet, consectetur adipiscing elit. Quisque convallis in enim non consectetur.

Azure SQL Edge Use Cases

Business Challenges: • Able to monitor soil, humidity data detect anomaly for real-time actions • Optimize for network bandwidth by pre-process massive raw data locally before sync (aggregates) to cloud • • • Cosmos. DB Azure SQL Data Warehouse Predictions/ results Aggregated Data Trained ML A credible vendor that can address multiple edge scenarios • Aggregates • Filtering • Anomaly detection on raw data • … Azure SQL Database Edge • Streaming, storage, ML all-in-one • Native Edge/Cloud movement for data and analytics • Credibility of Microsoft and SQL brand franchise it brings Data Streaming Data Processing Azure SQL Database Edge Local BI

Business Challenges: • Able to monitor pump pressure, detect anomaly for real-time actions • A solution that could be bi-directional b/t Edge & Cloud • A credible vendor that can address multiple edge scenarios Server SQL • Azure SQL Database / Cosmos. DB Predictions/ Other data Sensitive Data Trained ML Maintenance Azure SQL Database Edge • Streaming, storage, ML all-in-one Shut-off • Native Edge/Cloud movement for data and analytics • Credibility of Microsoft and SQL brand franchise it brings Data Streaming Anomaly Detection Real-time actions Azure SQL Database Edge

Connected Edge-Cloud Architecture • Connected/Semi-Connected Architecture

Semi-Connected Edge-Cloud-Datacenter Architecture

Disconnected Edge Architecture

Azure SQL Edge Deployment

Ra

� A z u r e S Q • • • Download Ubuntu 18. 04 LTS Pi Image Flash Ubuntu onto your micro. SD card Boot Ubuntu Server Login to your Pi Create an Azure Io. T Hub. Register an Io. T Edge Device from the Azure portal. Prepare the Io. T Edge device to deploy Io. T Edge module from the Azure portal. Deploy SQL Edge through Azure portal Connect

Create an IOT Hub

Deploy to Pi Deploy SQL Edge through Azure portal

Recap 1. Data landscape gravitating towards Edge 2. Azure SQL DB Edge brings a smallfootprint, turn-key, ML-capable data engine running connected or offline for Edge 3. Consistent data and analytics engine running natively across Edge, Data Center and Cloud

R e s o u r c e s • • Azure SQL Edge http: //aka. ms/sqledge • Azure Io. T Edge documentation • https: //docs. microsoft. com/en-us/azure/iot-edge/ • Andrew Pruski • https: //dbafromthecold. com/2020/04/22/running-azure-sql-edge-on-a-raspberry-pi/ • Videos • https: //azure. microsoft. com/en-us/resources/videos/sql-database-edge-streaming-machinelearning/ https: //www. youtube. com/watch? v=c. LY 3 qs 32 HJY • • Rasberry Pi 4 https: //dev. to/azure-iot-edge-on-raspberry-pi-buster-plus-tips-for-raspberry-pi-4 -22 nn •