Doug Bernhardt Doug BernhardtDell com Deploying SQL Server

Doug Bernhardt Doug. Bernhardt@Dell. com Deploying SQL Server 2019 Big Data Clusters on VMware

• Visit the Sponsor Booths • Lots of Great Raffle Prizes! • Get your parking paid via Sponsor Bingo Platinum Sponsor: Gold Sponsors: Global Alliance Partners: Thank you Sponsors!

PASSMN – News/Info Thanks to all our sponsors of 2019! We need Speakers & Sponsors for 2020 PASSMN Meetings! • Sign up to present at one of the monthly meetings! Monthly Meetup: 3 rd Tuesday of Each Month (except Oct) at Microsoft MTC in Edina (food usually provided) Signup on Meetup: https: //www. meetup. com/MN-SQL-Server-User-Group-PASSMN/ Board Member Elections in November/December: • Your chance to help out the MN SQL community!

Join the brightest data professionals focused on the Microsoft Data Platform! November 3 th Through November 8 th • • Pre-Conference Sessions – Monday/Tuesday Conference – Wednesday through Friday

SQLSaturday #913 – After Party Location: 4 th Floor of Mall of America Time: 6: 30 PM – 10 PM There will be drinks and appetizers as well as free game cards and bowling! Hang out with some new friends you’ve made.

Deploying SQL Server 2019 Big Data Clusters on VMware

Intro • Doug Bernhardt • Senior Principal Engineer – Dell EMC Technical marketing • Dell EMC since 2012 • SQL Server since 1994 • Architecture, Performance, Tuning, Storage Integration

Agenda SQL Server 2019 BDC Overview Deployment overview Why deploy on VMware? VMware deployment steps Questions

SQL Server 2019 BDC Intro • • Connect all of your data • • • Relational no. SQL Hadoop • • SQL Apache Spark • Azure Data Studio Create intelligence from all your data Single pane of glass management Scalable cluster Simplified deployment Same but different https: //docs. microsoft. com/en-us/sql/big-data-cluster-overview

A Brave New World Get comfortable being uncomfortable • • • Lots of new tools and languages Landscape is changing fast Documentation is sparse at times

Big data clusters deployed on Kubernetes, Open. Shift, AKS Applications Controller Svc Proxy Configuration Store (SQL Server) Kibana Grafana Elastic Search Influx. DB Custom apps BI Analytics SQL Sever 2019 Cluster (BDC) SQL External data sources SQL Server master instance Compute pool SQL Compute Instance Application pool Application SQL Compute Instance Data pool SQL Datapool Instance SQL Compute Instance Storage Node … SQL Compute Instance Storage pool SQL Datapool Instance Spark SQL Server HDFS Datanode Io. T data SQL Compute Instance Directly read from HDFS Storage Spark SQL Server HDFS Datanode … Spark SQL Server HDFS Datanode Kubernetes pod Node Persistent storage Microsoft architecture diagram for SQL Server 2019 BDC Node

What did they (MS) do? • Data lake components containerized • • • SQL Server Spark HDFS Elasticsearch …and more • • Deployment Management • Kubernetes • Tools

SQL Server 2019 BDC Deployment (greatly oversimplified) 1. Deploy Kubernetes (K 8 s) 1. Cloud 1. AKS or something similiar 2. Fairly simple 2. On Prem 1. Some assembly required 2. OS 3. K 8 s 4. Storage 2. Deploy SQL 2019 BDC 1. Azdata 2. Fairly universal 3. Enjoy SQL 2019 BDC Goodness

K 8 s Deployment options • • • Many ways – this is just one (l. cncf. io) • • • 58 Certified distribution platforms 40 Certified hosted platforms Multiple Linux OS flavors • • • Minikube Cloud Appliance Software (PKS) Virtualized Bare metal Various experiences SQL Server & Windows OS – The good old days

Some background on Docker and K 8 s • Containers are designed to be ephemeral • Containerized applications were supposed to be stateless • No concept of “persistent storage” until 2018. • 2018 • We need to save data • Persistent storage is born K 8 s 1. 10 (1. 16 = current release) • Already on second iteration

Why on VMware • • • Benefits of virtualization • • Availability Snapshots Thin provisioning/oversubscription Dev. Ops flexibility • • Persistent Storage is REQUIRED Persistent Storage relatively NEW! Evolution and change Not all storage types are handled natively • • • Pivotal PKS Project Pacific Traditional (Terraform, Kubespray, etc) Storage Deployment tools

Setup Steps 1. 2. 3. 4. 5. 6. 7. Create VM Install OS (Ubuntu 18. 04 LTS) Customize OS and install K 8 s components Configure VMware storage provider Configure persistent storage Test storage Deploy BDC * Versions: Ubuntu 18. 04 LTS/Kubernetes 1. 15. 0/SQL Server 2019 RC 1 image: 2019 -RC 1 -ubuntu

Create VMs (1 of 7) • • Min 3 8 CPU 64 GB Ram 250 GB Hard disk thin provisioned (100 GB min) • OS, K 8 s, and Docker images • Data specified later • VMware virtual machine settings disk. Enable. UUID=TRUE https: //docs. vmware. com/en/VMware-v. Sphere/6. 7/Cloud-Native. Storage/GUID-3501 C 3 F 2 -7 D 7 C-45 E 9 -B 20 A-F 3 F 70 D 1 E 4679. html

Install OS (2 of 7) 1. Ubuntu 18. 04 LTS - select SSH server during install 2. Allow password authentication thru SSH 1. Edit /etc/sshd_config 2. Change Password. Authentication to yes 3. sudo systemctl restart ssh 3. Apply upgrades 1. sudo apt upgrade 4. VM snapshots recommended

Customize OS (3 of 7) 1. 2. 3. 4. 5. 6. Add current machine to hosts file echo $(hostname -i) $(hostname) | sudo tee -a /etc/hosts Disable swapping sudo sed -i "/ swap / s/^/#/" /etc/fstab sudo swapoff –a Install Kubernetes components KUBE_DPKG_VERSION=1. 15. 0 -00 sudo apt-get update && sudo apt-get install -y ebtables ethtool && sudo apt-get install -y docker. io && sudo apt-get install -y apt-transport-https && sudo apt-get install -y kubelet=$KUBE_DPKG_VERSION kubeadm=$KUBE_DPKG_VERSION kubectl=$KUBE_DPKG_VERSION && curl https: //raw. githubusercontent. com/kubernetes/helm/master/scripts/get | bash Set IP table behavior . /etc/os-release if [ "$VERSION_CODENAME" == "bionic" ]; then sudo modprobe br_netfilter; fi sudo sysctl net. bridge-nf-call-iptables=1 https: //docs. microsoft. com/en-us/sql/big-data-cluster/deploy-with-kubeadm VM snapshots recommended

Configure VMware Storage Provider (4 of 7) • • v. Center required (no standalone ESX) Provision storage on VMware Create/obtain v. Center resources • • Credentials Name/IP & port Datacenters Network • • Enable disk. Enable. UUID parm VM Options -> Advanced -> Edit Configuration disk. Enable. UUID = TRUE Restart For Each VM

K 8 s Storage Provider Deployment (old) v. Sphere Cloud Provider (in-tree) • • • v. Sphere 6. 0. x + v. SAN, VMFS and NFS supported Kubernetes v 1. 6. 5+ Will be deprecated at some point Recommended only for older v. Sphere versions https: //vmware. github. io/vsphere-storage-forkubernetes/documentation/prerequisites. html

K 8 s Storage Provider Deployment (new) v. Sphere CSI Driver(out-of-tree) • • • Kubernetes v 1. 14+ v. Sphere 6. 5+ (with limitations) v. Sphere 6. 7 Update 3 Improved documentation docs. vmware. com -> Getting Started with VMware Cloud Native Storage

K 8 s install w/v. Sphere Cloud Provider • No storage driver install • Configuration only • Create config file with v. Sphere settings • Credentials and path to v. Center • Datastore • Create K 8 s cluster (master and worker nodes) • Configuration passed to setup: cloud-provider: vsphere • Patching into existing cluster can be messy • https: //blah. cloud/kubernetes/setting-up-k 8 s-and-thevsphere-cloud-provider-using-kubeadm/ (Myles Gray – VMware)

K 8 s install w/v. Sphere CSI Provider • Documentation is fairly good • cloud-provider: external • At this point, follow VMware instructions to: 1. Initialize master and worker nodes using config parameters 2. Install v. Sphere Cloud Controller Manager 3. Install v. Sphere Container Storage Interface Driver 4. Watch Kubernetes version references • See Getting Started with VMware Cloud Native Storage in Vmware v. Sphere Product Documentation

Configure Persistent Storage (5 of 7) • • • K 8 s allows for dynamic storage provisioning SQL 2019 BDC takes advantage of this Storage Class (SC) • • Storage provider Provisioning defaults (thin vs thick, datastore, format, etc) • • • Creates the volume Size Access Mode File System Location Persistent Volume Claim (PVC) Provide SC(s) and size to BDC install, it will create the PVCs Need to understand for testing

Storage Class v. Sphere Cloud Provider (sc. yaml) kind: Storage. Class api. Version: storage. k 8 s. io/v 1 metadata: name: fast annotations: storageclass. kubernetes. io/isdefault-class: "false" provisioner: kubernetes. io/vsphere-volume parameters: datastore: K 8 SDatastore 3 fstype: ext 4 v. Sphere CSI Provider (sc. yaml) kind: Storage. Class api. Version: storage. k 8 s. io/v 1 metadata: name: fast annotations: storageclass. kubernetes. io/isdefault-class: "false" provisioner: csi. vsphere. vmware. com parameters: storagepolicyname: "Standard" fstype: ext 4

Persistent Volume Claim (pvc. yaml) api. Version: v 1 kind: Persistent. Volume. Claim metadata: name: testpvc 3 spec: storage. Class. Name: fast access. Modes: - Read. Write. Once resources: requests: storage: 2 Gi

Test it out (6 of 7) 1. 2. Create the storage class kubectl create –f sc. yaml Create a test volume (persistent volume claim) kubectl create –f pvc. yaml 3. Check the status 4. Check v. Sphere 5. VM snapshot recommended kubectl get pvc (should return status = bound) v. Sphere Cloud Provider – kubevols folder on specified datastore v. Sphere CSI Provider – 6. 7 U 3 – displayed under Monitor->Cloud Native Storage-> Container Volumes

Deploy SQL 2019 BDC (7 of 7) • Follow MS instructions for Azdata 1. 2. 3. https: //docs. microsoft. com/en-us/sql/big-data-cluster/deployment-guidance Install Azdata Use Azdata to create deployment template (recommended) azdata bdc config init --source kubeadm-dev-test --target custom 1. Edit control. json 2. Use Azdata to deploy BDC 1. 2. storage class names storage sizes azdata bdc create --config-profile custom --accept-eula yes • • Azdata will download all binaries during deploy VM snapshot recommended

control. json { "api. Version": "v 1", "metadata": { "kind": "Cluster", "name": "mssql-cluster" }, "spec": { "docker": { "registry": "mcr. microsoft. com", "repository": "mssql/bdc", "image. Tag": "2019 -RC 1 -ubuntu", "image. Pull. Policy": "Always" }, "storage": { "data": { "class. Name": "fast", "access. Mode": "Read. Write. Once", "size": "150 Gi" }, "logs": { "class. Name": "fast", "access. Mode": "Read. Write. Once", "size": "100 Gi" } }, "endpoints": [ { "name": "Controller", "service. Type": "Node. Port", "port": 30080 }, { "name": "Service. Proxy", "service. Type": "Node. Port", "port": 30777 } ] } }

Data Protection • Challenges • • • Multiple formats Multiple sources Size • • • Rebuild & rehydrate Backups Snapshots • Possible solutions • • • VM Storage Kubernetes support

K 8 S/VMware Issues and Workarounds • VM Provider. ID/UUID • • • Attempt to verify cluster: • • kubectl describe nodes | grep "Provider. ID“ Returns blank provider id • • Need to get Provider. ID from command line or v. Sphere flash client, not available in HTML 5 client. Run this for each node in the cluster K 8 s bug – need to manually patch nodes kubectl patch node <Node name> -p '{"spec": {"provider. ID": "vsphere: //<vm uuid>"}}’ https: //github. com/kubernetes/issues/65933 • Taint does not clear • • node. cloudprovider. kubernetes. io/uninitialized kubectl patch node , <node name> -p '{"spec": {"taints": []}}'

Tips • Docker pull all binaries locally before deploy • • • List of containers: https: //docs. microsoft. com/en-us/sql/big-datacluster/deploy-offline Build VMs from scratch rather than OVA • Might be old hardware compatibility level – upgrade might not work • https: //github. com/kubernetes/issues/65933 • https: //github. com/microsoft/sqlworkshops/tree/master/sqlserver 201 9 bigdataclusters Snapshot VMs at various stages of deployment v. Sphere UUID does not populate correctly after K 8 s 1. 11. 0 SQL 2019 BDC workshop

Suggested learning • SQL Server 2019 BDC architecture • VMware • Linux • Docker • Kubernetes • Helm • • Tiller Putty PSCP/SCP YAML JSON Spark Python

Thank You! Stop by Dell EMC Booth and say hello Doug. Bernhardt@Dell. com

Spare slides

Why an entire session on deployment? Don’t you just run setup. exe? Unfortunately……. no I’ll just RTM Well…. there isn’t one

Create K 8 s Cluster • Configure storage provider settings • Create the cluster • • Depends on storage provider May be possible to change, easiest to just do it at creation

SQL 2019 BDC introduces K 8 s SQL Server 2019 BDC deploys on Kubernetes (K 8 s) • • Deployment method is “your choice” Implementation Flexibility Dozens of ways to deploy K 8 s Same functionality

Title Content

Title Only

Content Only

Title Content 1 Content 2