8115  Reviews star_rate star_rate star_rate star_rate star_half

SQL Server 2019 for Developers

You will learn about Performance Improvements, Intelligent Query Processing, Memory Improvements, Advanced Indexing, Machine Learning, Polybase, security features, Continuous Integration and...

Read More
$3,140 USD
Course Code WA3190
Duration 5 days
Available Formats Classroom

You will learn about Performance Improvements, Intelligent Query Processing, Memory Improvements, Advanced Indexing, Machine Learning, Polybase, security features, Continuous Integration and Continuous Delivery (CI/CD), Monitoring and Management Improvements, and more.

Skills Gained

  • Learn how to implemented best practices when installing SQL Server 2019
  • Understand the new performance improvements in SQL Server 2019
  • Enhance performance using Intelligent Query Processing, Column indexes, and Memory Optimized Tables/Databases.
  • Implement distributed data strategy using Polybase.
  • Understanding Machine Learning capabilities of SQL Server 2019.
  • Containerizing SQL Server using Docker containers.
  • Understand the basics of CI/CD using Jenkins.
  • Understand the new security features.

Who Can Benefit

This course is intended for database administrators, developers, and architects that need to understand the newer features introduced in SQL Server 2012 - 2019.

Prerequisites

Basics of SQL Server or any other DBMS system is highly recommended.

Course Details

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