Objetivo

Após realizar este Curso SQL Server 2019 for Developers, você será capaz de:

• Aprenda como implementar as melhores práticas ao instalar o SQL Server 2019
• Entenda as novas melhorias de desempenho no SQL Server 2019
• Melhore o desempenho usando processamento inteligente de consultas, índices de coluna e tabelas/bancos de dados com otimização de memória.
• Implemente estratégia de dados distribuídos usando Polybase.
• Noções básicas sobre os recursos de aprendizado de máquina do SQL Server 2019.
• Conteinerização do SQL Server usando contêineres Docker.
• Entenda os conceitos básicos de CI/CD usando Jenkins.
• Entenda os novos recursos de segurança.

Publico Alvo

• Administradores de banco de dados, desenvolvedores e arquitetos que precisam entender os recursos mais recentes introduzidos no SQL Server 2012 - 2019. 

Pre-Requisitos

• Noções básicas de SQL Server ou qualquer outro sistema DBMS são altamente recomendados.

Materiais

Conteúdo Programatico

SQL Server Planning

1. SQL 2019 New Features
2. Upgrade Considerations
3. SQL Server 2019 Supported Upgrades
4. Pre-upgrade Checklist
5. Migration Tools
6. Pre-migration Stages
7. Discover Stage
8. Assess and Convert
9. A/B Testing (optional)
10. Migration Overview
11. Side-by-side Migration DMA
12. In-place Upgrade
13. Memory and Performance Consideration
14. Memory Guidance
15. Lock Pages in Memory (LPIM)
16. Instant File Initialization
17. Perform Pre-checks of I/O
18. Using SQLIOSIM

Performance Improvements

1. Introduction to the Buffer Pool Extension
2. Considerations for Using the Buffer Pool Extension
3. Configuring the Buffer Pool Extension
4. What Is Resource Governor?
5. What Are Resource Pools and Workload Groups?
6. Managing Resource Governor
7. Assigning a Workload to a Workload Group
8. Indexes in SQL Server
9. The INCLUDE Clause Considerations
10. What are Columnstore Indexes
11. Query Plan with Columnstore Index
12. Columnstore Index Scenarios
13. Nonclustered Columnstore Indexes
14. Clustered Columnstore Indexes
15. Creating a Nonclustered Columnstore Index
16. Creating a Clustered Columnstore Index
17. Creating a Clustered Columnstore Table with Primary and Foreign Keys
18. Managing Columnstore Indexes
19. Index Fragmentation
20. Columnstore Indexes and Memory Optimized Tables
21. Online Index Create and Rebuild
22. Online Index Caveats
23. Resumable Online Index
24. Working with Resumable Online Index
25. Pausing and Resuming Resumable Online Index
26. View Resumable Online Index Status
27. Intelligent Performance with SQL Server 2019
28. Intelligent Query Processing Features
29. What Are Memory-Optimized Tables?
30. Scenarios for Memory-Optimized Tables
31. Creating a Filegroup for Memory-Optimized Data
32. Creating Memory-Optimized Tables
33. Indexes in Memory-Optimized Tables
34. Converting Tables with Memory Optimization Advisor
35. Querying Memory-Optimized Tables
36. What Are Natively Compiled Stored Procedures?
37. When to use Natively Compiled Stored Procedures
38. Creating Natively Compiled Stored Procedures
39. Execution Statistics
40. Planning Memory-Optimized Tables

Advanced Indexing for SQL Server

1. Index Strategies
2. Covering Indexes
3. Using the INCLUDE Clause
4. Heap vs. Clustered Index
5. Filtered Index
6. What Is Fill Factor?
7. What is Pad Index
8. Implementing Fill Factor and Padding
9. Managing Statistics
10. Using DMOs to Improve Index Usage
11. Consolidating Indexes
12. Using Query Hints
13. What is an Execution Plan
14. Actual vs. Estimated Execution Plans
15. Common Execution Plan Elements
16. Methods for Capturing Plans
17. Execution Plan Related DMVs
18. Live Query Statistics
19. A Slide Header [Style: Slide Header]
20. A Slide with Vertical Split

emporary Data Implications

1. TempDB
2. tempdb Facts
3. tempdb Configuration
4. Memory and tempdb
5. Default tempdb Configuration
6. tempdb Performance Optimization
7. tempdb File Placement Recommendations
8. Moving the tempdb Database
9. tempdb Secondary File Recommendations
10. Multiple TempDB Database Files
11. tempdb Restrictions
12. tempdb Permissions
13. Memory-optimized tempdb metadata
14. Memory-optimized tempdb limitations
15. Configuring and using memory-optimized tempdb metadata
16. Enabling Sort In TempDB
17. Delayed Durability
18. Clear tempdb in SQL Server
19. Configuring tempdb Storage
20. Configuring tempdb Storage

Docker Introduction

1. What is Docker
2. Docker Containers vs Traditional Virtualization
3. Where Can I Run Docker?
4. Docker Containers vs Traditional Virtualization
5. Docker as Platform-as-a-Service
6. Docker Integration
7. Docker Services
8. Docker Hub Container Registry
9. Alternative Container Registries
10. Competing Containerization Systems
11. Docker Command-line
12. Starting, Inspecting, and Stopping Docker Containers

Building Docker Images

1. Docker Images
2. Containerizing an Application
3. Building The Image
4. Building a Docker Images using Dockerfile
5. Sample Dockerfile
6. Environment Variables
7. Environment Variables - Example
8. Arguments
9. Multi-stage Builds
10. Stop at a Specific Build Stage
11. RUN
12. EXPOSE
13. EXPOSE (Continued)
14. COPY
15. ADD
16. CMD
17. ENTRYPOINT
18. CMD vs. ENTRYPOINT
19. VOLUME
20. Build the Image
21. .dockerignore
22. Dockerfile – Best Practices
23. Published Ports
24. Docker Documentation Link
25. Docker Registry
26. Hosting a Local Registry
27. Deploying Docker Images to a Kubernetes Cluster
28. Running Commands in a Container
29. Multi-Container Pod

Azure Container Registry and Azure Container Instances

1. Azure Container Registry (ACR)
2. ACR Typical Workflow
3. Container Registry SKUs
4. Creating ACR
5. Pushing Existing Docker Images into ACR
6. Build Images in ACR
7. Obtaining ACR Credentials
8. Azure Container Instances (ACI)
9. Azure Container Instance Workflow
10. Working with ACI
11. Deleting Container Instances and Container Registry

Kubernetes Core Concepts

1. Kubernetes Basics
2. What is Kubernetes?
3. Container Orchestration
4. Kubernetes Architecture
5. Kubernetes Concepts
6. Cluster and Namespace
7. Nodes
8. Master
9. Pod
10. Using Pods to Group Containers
11. Label
12. Label Syntax
13. Annotation
14. Label Selector
15. Replication Controller and Replica Set
16. Service
17. Storage Volume
18. Secret
19. Resource Quota
20. Authentication and Authorization
21. Routing
22. Docker Registry
23. Azure Kubernetes Service (AKS)
24. AKS Diagram
25. Deploying an AKS Cluster
26. Application Deployment on AKS

Deploying and Exposing Applications

1. Configuring AKS for Deployment
2. Deploying to Kubernetes
3. Kubernetes Services
4. Service Resources
5. Service Type
6. ClusterIP
7. NodePort
8. NodePort from Service Spec
9. LoadBalancer
10. LoadBalancer from Service Spec
11. ExternalName
12. Accessing Applications
13. Service Without a Selector
14. Ingress
15. Ingress Resource Example
16. Ingress Controller
17. Service Mesh

Kubernetes Workload

1. Kubernetes Workload
2. Managing Workloads
3. Imperative commands
4. Imperative Object Configuration
5. Declarative Object Configuration
6. Configuration File Schema
7. Understanding API Version
8. Understanding API Version
9. Obtaining API Versions
10. Stateless Applications
11. Sample Deployment Manifest File
12. Working with Deployments
13. Stateful Applications
14. Sample Stateful Manifest File
15. Working with StatefulSet
16. Jobs
17. Sample Job Manifest File
18. Working with Batch Job
19. DaemonSets
20. Sample Daemon Manifest File
21. Rolling Updates
22. Summary
23. Chapter 11 - Using PolyBase in SQL Server
24. Understanding PolyBase
25. PolyBase Example
26. Data Virtualization with PolyBase
27. PolyBase Use-cases
28. PolyBase and Massive Parallel Processing
29. Supported SQL Products and Services
30. PolyBase Connectors
31. PolyBase vs. Linked Server
32. PolyBase Limitations
33. Using PolyBase in SQL Server Instance
34. PolyBase Installation
35. Enable PolyBase
36. Create external data source to reference a SQL Server Instance
37. Create external data source in SQL Server 2019 to reference Oracle
38. Create external data source to access data in Azure Storage
39. Create external data source to reference Hadoop
40. Create an external table for SQL Server
41. Create an External Table for Azure Blob Storage
42. Importing Data with CTAS
43. Exporting Data with CETAS
44. View PolyBase Objects in SSMS

Security Features

1. Always Encrypted
2. Security Features
3. Always Encrypted using SSMS
4. Selecting Columns to Encrypt
5. Creating the Always Encrypted Master Key
6. Creating the Column Encryption Key
7. PowerShell Script

Lab Exercises

• Lab 1 - SQL Server Installation and Configuration
• Lab 2 - Pre-installation Stress Testing
• Lab 3 - Configure Memory
• Lab 4 - Working with Buffer Pool Extension
• Lab 5 - Working with the Resource Governor
• Lab 6 - Using Columnstore Indexes
• Lab 7 - Working with Memory Optimization Advisor
• Lab 8 - Using In-Memory Tables
• Lab 9 - Intelligent Query Processing: Approximate Query Processing
• Lab 10 - Optimizing Indexes
• Lab 11 - Working with tempdb
• Lab 12 - Working with PolyBase
• Lab 13 - Creating a Docker Account and Obtain an Access Token
• Lab 14 - Managing Containers
• Lab 15 - Building Images
• Lab 16 - Dockerfiles
• Lab 17 - Docker Volumes
• Lab 18 - Containerizing SQL Server
• Lab 19 - Configuring Minikube/Kubernetes to Use a Custom Docker Account
• Lab 20 - Accessing Applications
• Lab 21 - Working with Machine Learning Services
• Lab 22 - Working with Dynamic Data Masking
• Lab 23 - Working with Row-level and Always Encrypted Security Features
• Lab 24 - Working with Monitoring Baselining