Objetivo

O SQL Server é a maior plataforma de banco de dados utilizada no mercado atualmente e devido a isto cada vez mais são necessários profissionais especializados para planejar, implementar a gerenciar uma infraestrutura de alta performance e missão crítica nas empresas.

 

Com o intuito de formar um profissinal diferenciado, a Vita Database Solutions desenvolveu um treinamento focado em analisar e discutir detalhadamente como funcionam todos os componentes existentes no SQL Server e como garantir que seus recursos de hardware (CPU, Memória e Disco) sejam utilizados da melhor maneira possível, visando garantir que todas as aplicações possam acessar de forma rápida e escalável os dados armazenados em seu ambiente de banco de dados SQL Server.

 

Com os tópicos discutidos neste treinamento o profissional estará apto a planejar e implementar uma solução de banco de dados utilizando o SQL Server além de também poder executar tarefas mais complexas, tais como resolução de problemas (troubleshooting) e otimização de desempenho em um ambiente OnPremisse.
​​

Metodologia

Aulas teóricas e práticas em laboratório

 

Conteúdo Programático

Módulo 1 - SQL SERVER ARCHITECTURE

- Database Transactions

- ACID Properties

- SQL Server Transactions

- The Life Cycle of a Query

- The Relational and Storage Engines 

- The Buffer Pool

- A Basic SELECT Query

- A Simple Update Query

- Recovery

- SQL Server’s Execution Model and the SQLOS

- Execution Model

- The SQLOS
 

Módulo 2 - UNDERSTANDING MEMORY

- Physical and Virtual Memory

- Physical Memory

- Maximum Supported Physical Memory

- Virtual Memory

- NUMA

- SQL Server Memory

- Memory Nodes

- Clerks, Caches, and the Buffer Pool

- Optimizing SQL Server Memory Configuration

- Min and Max Server Memory

- Lock Pages in Memory

- Optimize for Ad-Hoc Workloads
 

Módulo 3 - STORAGE SYSTEMS

- SQL Server I/O

- Storage Technology

- SQL Server and the Windows I/O Subsystem

- Choosing the Right Storage Networks

- Shared Storage Arrays

- Capacity Optimization

- Storage Tier

- Data Replication

- Remote Data Replication

- Risk Mitigation Planning

- Measuring Performance

- Storage Performance Counters

- Disk Drive Performance

- Sequential Disk Access

- Server Queues

- File Layout

- Partition Alignment

- NTFS Allocation Unit Size

- Flash Storage

- Storage Performance Testing

- Understand solid-state storage impact on SQL Server

 

Módulo 4 - KNOWING TEMPDB

- Internal Temporary Objects

- The Version Store

- Troubleshooting Common Issues

- Monitoring Tempdb I/O Performance

- Troubleshooting Space Issues

- Configuration Best Practices
 

Módulo 5 - TROUBLESHOOTING METHODOLOGY AND PRACTICES

- Approaching Problems

- Ten Steps to Successful Troubleshooting

- Behavior and Attitude

- Success Criteria

- Working with Stakeholders

- Service-Level Agreements

- Engaging External Help

- Defining the Problem

- Guidelines for Identifying the Problem

- Isolating the Problem

- Performance Bottlenecks

- Data Collection

- Focused Data Collection

- Understanding Data Gathering

- Tools and Utilities

- Data Analysis

- Validating and Implementing Resolution

- Validating Changes

- Testing Changes in Isolation

- Implementing Resolution
 

Módulo 6 - VIEWING SERVER PERFORMANCE WITH PERFMON AND THE PAL TOOL

- Performance Monitor Overview

- Reliability and Performance Monitor

- New PerfMon Counters for SQL Server

- Getting More from Performance Monitor

- Bottlenecks and SQL Server

- Prescriptive Guidance

- Wait Stats Analysis

- Getting a Performance Baseline

- Performance Analysis of Logs

- Getting Started with PAL

- Other PerfMon Log Analysis Tools

- Using SQL Server to Analyze PerfMon Logs

- Combining PerfMon Logs and SQL Profiler Traces

- Using Relog

- Using LogMan

- Using LogParser

 

Módulo 7 - CONSOLIDATING DATA CAPTURE WITH SQLDIAG

- The Data Collection Dilemma

- An Approach to Data Collection

- Getting Friendly with SQLdiag

- Using SQLdiag in Snapshot Mode

- Using SQLdiag as a Command-line Application

- Using SQLdiag as a Service

- Using SQLdiag Configuration Manager

- Configuring SQLdiag Data Collection Using Diag Manager

- Adding Trace Filters to an SQLdiag Configuration

- Employing Best Practices

- Gearing Up for Long-Term Data Collection

- Filtering Out the Noise

- Alert-Driven Data Collection with SQLdiag
 

Módulo 8 - BRINGING IT ALL TOGETHER WITH SQL NEXUS

- Introducing SQL Nexus

- Getting Familiar with SQL Nexus

- Prerequisites

- Loading Data into a Nexus Database

- Analyzing the Aggregated Data

- Customizing SQL Nexus

- Using ReadTrace.exe

- Building Custom Reports for SQL Nexus

- Running SQL Nexus Using the Command Prompt

- Creating Your Tables in the SQL Nexus Database

- Writing Your Queries

- The OSTRESS Executable

- Resolving Common Issues
 

Módulo 9 - DIAGNOSING SQL SERVER USING EXTENDED EVENTS

- Introduction to Extended Events

- Getting Familiar with Extended Events

- Why You Should Be Using Extended Events

- SQL Server Roadmap

- Graphical Tools

- Low Impact

- When You Might Use Extended Events

- What Are Extended Events?

- Where the Name Extended Events Comes From 

- Extended Events Terminology

- Creating Extended Events Sessions in SQL Server

- Introduction to the New Session Form 

- Creating Sessions Using T-SQL

- Viewing Data Captured by Extended Events 

- Viewing Event File Data
 

Módulo 10  - DELIVERING A SQL SERVER HEALTH CHECK

- The Importance of a SQL Server Health Check

- Running DMV and DMF Queries 

- SQL Server Builds 

- Database-Level Queries

 

Módulo 11  - DELIVERING MANAGEABILITY AND PERFORMANCE

- Improve Efficiency with SQL Server Manageability Features

- Manageability Enhancements in SQL Server

- Policy-Based Management

- Other Microsoft Tools for Managing SQL Server 

- Create a baseline using Data Collector

 

Módulo 12 - VIRTUALIZATION

- Understand virtualization and how it works with SQL Server

 

Módulo 13 - PLANING AND IMPLEMENTING HIGH AVAILABILITY

- Which is the best high-availability solution?

- Choosing quorum for Windows Failover clusters

- Using Log Shipping as a cheap solution

- When Database Mirroring is the right choice

- Working with Availability Groups

 

Carga Horária

40 horas

​
 

Público Alvo
Arquitetos de Infraestrutura, desenvolvedores e DBAs.

​

 

Investimento

- R$ 2500,00 à vista

- R$ 2700,00 através do PagSeguro

​

 

Referências

- SQL Server 2019 Revealed

- SQL Server 2019 Administration

- Query Store for SQL Server 2019

- SQL Server 2012 Internals and Troubleshooting (WROX)

- Microsoft SQL Server 2012 Internals (MSPRESS)

- Documentação técnica do SQL Server (https://docs.microsoft.com/en-us/sql/sql-server/?view=sql-server-ver15)

 

Próxima Turma - Janeiro/2024

- 20/01

- 27/01

- 17/02

- 24/02

- 02/03