SQL Server 2019 for Developers

Outline

SQL Server Planning

• SQL 2019 New Features
• SQL 2019 New Features (contd.)
• Upgrade Considerations
• SQL Server 2019 Supported Upgrades
• Pre-upgrade Checklist
• Migration Tools
• Pre-migration Stages
• Discover Stage
• Assess and Convert
• A/B Testing (optional)
• Migration Overview
• Side-by-side Migration DMA
• In-place Upgrade
• Memory and Performance Consideration
• Memory Guidance
• Lock Pages in Memory (LPIM)
• Lock Pages in Memory (contd.)
• Instant File Initialization
• Instant File Initialization (contd.)
• Perform Pre-checks of I/O
• Using SQLIOSIM
• Summary

Performance Improvements

• Introduction to the Buffer Pool Extension
• Considerations for Using the Buffer Pool Extension
• Configuring the Buffer Pool Extension
• What Is Resource Governor?
• What Are Resource Pools and Workload Groups?
• Managing Resource Governor
• Assigning a Workload to a Workload Group
• Indexes in SQL Server
• The INCLUDE Clause Considerations
• What are Columnstore Indexes
• What are Columnstore Indexes (contd.)
• Query Plan with Columnstore Index
• Columnstore Index Scenarios
• Nonclustered Columnstore Indexes
• Clustered Columnstore Indexes
• Creating a Nonclustered Columnstore Index
• Creating a Clustered Columnstore Index
• Creating a Clustered Columnstore Table with Primary and Foreign Keys
• Managing Columnstore Indexes
• Index Fragmentation
• Columnstore Indexes and Memory Optimized Tables
• Online Index Create and Rebuild
• Online Index Caveats
• Resumable Online Index
• Working with Resumable Online Index
• Pausing and Resuming Resumable Online Index
• View Resumable Online Index Status
• Intelligent Performance with SQL Server 2019
• Intelligent Query Processing Features
• Intelligent Query Processing Features (contd.)
• What Are Memory-Optimized Tables?
• Scenarios for Memory-Optimized Tables
• Creating a Filegroup for Memory-Optimized Data
• Creating Memory-Optimized Tables
• Creating Memory-Optimized Tables (contd.)
• Indexes in Memory-Optimized Tables
• Memory-Optimized Table Indexes Example
• Converting Tables with Memory Optimization Advisor
• Querying Memory-Optimized Tables
• Querying Memory-Optimized Tables (contd.)
• What Are Natively Compiled Stored Procedures?
• When to use Natively Compiled Stored Procedures
• Creating Natively Compiled Stored Procedures
• Execution Statistics
• Planning Memory-Optimized Tables
• Summary

Advanced Indexing for SQL Server

• Index Strategies
• Covering Indexes
• Using the INCLUDE Clause
• Heap vs. Clustered Index
• Filtered Index
• What Is Fill Factor?
• What is Pad Index
• Implementing Fill Factor and Padding
• Managing Statistics
• Using DMOs to Improve Index Usage
• Consolidating Indexes
• Using Query Hints
• What is an Execution Plan
• Actual vs. Estimated Execution Plans
• Common Execution Plan Elements
• Methods for Capturing Plans
• Execution Plan Related DMVs
• Live Query Statistics
• A Slide Header [Style: Slide Header]
• A Slide with Vertical Split
• Summary

Temporary Data Implications

• TempDB
• TempDB (contd.)
• tempdb Facts
• tempdb Configuration
• Memory and tempdb
• Default tempdb Configuration
• tempdb Performance Optimization
• tempdb Performance Optimization (contd.)
• tempdb File Placement Recommendations
• Moving the tempdb Database
• tempdb Secondary File Recommendations
• Multiple TempDB Database Files
• tempdb Restrictions
• tempdb Restrictions (contd.)
• tempdb Permissions
• Memory-optimized tempdb metadata
• Memory-optimized tempdb limitations
• Configuring and using memory-optimized tempdb metadata
• Enabling Sort In TempDB
• Delayed Durability
• Clear tempdb in SQL Server
• Configuring tempdb Storage
• Configuring tempdb Storage
• Summary

Docker Introduction

• What is Docker
• Docker Containers vs Traditional Virtualization
• Where Can I Run Docker?
• Docker Containers vs Traditional Virtualization
• Docker as Platform-as-a-Service
• Docker Integration
• Docker Services
• Docker Hub Container Registry
• Alternative Container Registries
• Competing Containerization Systems
• Docker Command-line
• Starting, Inspecting, and Stopping Docker Containers
• Summary

Building Docker Images

• Docker Images
• Containerizing an Application
• Building The Image
• Building a Docker Images using Dockerfile
• Sample Dockerfile
• Environment Variables
• Environment Variables - Example
• Arguments
• Multi-stage Builds
• Multi-stage Builds (Continued)
• Stop at a Specific Build Stage
• RUN
• EXPOSE
• EXPOSE (Continued)
• COPY
• ADD
• CMD
• ENTRYPOINT
• CMD vs. ENTRYPOINT
• VOLUME
• Build the Image
• Build the Image (continued)
• .dockerignore
• Dockerfile – Best Practices
• Dockerfile - Best Practices (contd.)
• Published Ports
• Docker Documentation Link
• Docker Registry
• Hosting a Local Registry
• Hosting a Local Registry (continued)
• Deploying Docker Images to a Kubernetes Cluster
• Deploying to Kubernetes (continued)
• Deploying to Kubernetes (contd.)
• Running Commands in a Container
• Multi-Container Pod
• Multi-Container Pod (continued)
• Summary

Azure Container Registry and Azure Container Instances

• Azure Container Registry (ACR)
• ACR Typical Workflow
• Container Registry SKUs
• Creating ACR
• Pushing Existing Docker Images into ACR
• Build Images in ACR
• Obtaining ACR Credentials
• Azure Container Instances (ACI)
• Azure Container Instance Workflow
• Working with ACI
• Working with ACI (contd.)
• Deleting Container Instances and Container Registry
• Summary

Kubernetes Core Concepts

• Kubernetes Basics
• What is Kubernetes?
• Container Orchestration
• Kubernetes Architecture
• Kubernetes Concepts
• Cluster and Namespace
• Nodes
• Master
• Pod
• Using Pods to Group Containers
• Label
• Label Syntax
• Annotation
• Label Selector
• Replication Controller and Replica Set
• Service
• Storage Volume
• Secret
• Resource Quota
• Authentication and Authorization
• Routing
• Docker Registry
• Azure Kubernetes Service (AKS)
• AKS Diagram
• Deploying an AKS Cluster
• Application Deployment on AKS
• Summary

Deploying and Exposing Applications

• Configuring AKS for Deployment
• Deploying to Kubernetes (contd.)
• Deploying to Kubernetes (contd.)
• Deploying to Kubernetes (contd.)
• Kubernetes Services
• Service Resources
• Service Type
• ClusterIP
• NodePort
• NodePort from Service Spec
• LoadBalancer
• LoadBalancer from Service Spec
• ExternalName
• Accessing Applications
• Service Without a Selector
• Ingress
• Ingress Resource Example
• Ingress Controller
• Service Mesh
• Summary

Kubernetes Workload

• Kubernetes Workload
• Kubernetes Workload (contd.)
• Managing Workloads
• Imperative commands
• Imperative Object Configuration
• Declarative Object Configuration
• Configuration File Schema
• Understanding API Version
• Understanding API Version
• Obtaining API Versions
• Obtaining API Versions (contd.)
• Stateless Applications
• Sample Deployment Manifest File
• Working with Deployments
• Stateful Applications
• Sample Stateful Manifest File
• Sample Stateful Manifest File (Contd.)
• Working with StatefulSet
• Jobs
• Sample Job Manifest File
• Sample Job Manifest File (Contd.)
• Working with Batch Job
• DaemonSets
• DaemonSets (contd.)
• Sample Daemon Manifest File
• Rolling Updates
• Rolling Updates (Contd.)
• Rolling Updates (Contd.)
• Summary

Using PolyBase in SQL Server

• Understanding PolyBase
• PolyBase Example
• Data Virtualization with PolyBase
• PolyBase Use-cases
• PolyBase and Massive Parallel Processing
• Supported SQL Products and Services
• PolyBase Connectors
• PolyBase vs. Linked Server
• PolyBase Limitations
• Using PolyBase in SQL Server Instance
• PolyBase Installation
• Enable PolyBase
• Create external data source to reference a SQL Server Instance
• Create external data source in SQL Server 2019 to reference Oracle
• Create external data source to access data in Azure Storage
• Create external data source to reference Hadoop
• Create an external table for SQL Server
• Create an External Table for Azure Blob Storage
• Importing Data with CTAS
• Exporting Data with CETAS
• View PolyBase Objects in SSMS
• Summary

Security Features

• Always Encrypted
• Security Features
• Always Encrypted using SSMS
• Selecting Columns to Encrypt
• Creating the Always Encrypted Master Key
• Creating the Column Encryption Key
• PowerShell Script
• Summary

Lab Exercises

• SQL Server Installation and Configuration
• Pre-installation Stress Testing
• Configure Memory
• Working with Buffer Pool Extension
• Working with the Resource Governor
• Using Columnstore Indexes
• Working with Memory Optimization Advisor
• Using In-Memory Tables
• Intelligent Query Processing: Approximate Query Processing
• Optimizing Indexes
• Working with tempdb
• Working with PolyBase
• Creating a Docker Account and Obtain an Access Token
• Managing Containers
• Building Images
• Dockerfiles
• Docker Volumes
• Containerizing SQL Server
• Configuring Minikube/Kubernetes to Use a Custom Docker Account
• Accessing Applications
• Working with Machine Learning Services
• Working with Dynamic Data Masking
• Working with Row-level and Always Encrypted Security Features
• Working with Monitoring Baselining