Sidney Cirqueira. Reestruturando um Ambiente de Banco de Dados SQL Server na Prática

Reestruturando um Ambiente de Banco

de Dados SQL Server na Prática

Apresentação pessoal

✓Atuando no mercado de TI a 7 anos

✓Formado em Redes de Computadores (Estácio de Sá)

✓Pós-Graduado em Redes com Ênfase em Segurança da Informação (Uniceub) ✓Concluindo Especialização em Banco de dados (UFG)

Melhores práticas de

Administração de Banco de

Agenda

1. Instalação e configuração

2. Gerenciamento de acessos

3. Estratégia de Backup e Restore

4. Automatização de tarefas

5. Monitoramento / Tuning

6. Criação de alertas

Planejamento

1.

Conhecer o ambiente (Inventário)

• Analisar os recursos do Servidor

o Memória o Disco o CPU

2.

Conferir a versão do SQL, as Maiores Bases e Tabelas

3.

Escolha da localização default para os arquivos do SQL Server

• Binários

• Bases de Dados

o Dados, Log e TempDB

Planejamento

4.

Definir dos recursos e serviços que serão instalados

Exemplo: Reporting Services, Analysis Services e Integration Services

5.

Definir a Collation da Instância do Banco de dados

6.

Definir as Contas de Serviço do SQL Server

• Um conta para cada serviço

SQL Server instalado com sucesso!!!!

Configuração pós instalação

•

Instalação do último Service Pack disponível

• Instalar primeiro em servidor de homologação

• Sempre realizar backup das bases antes da instalação

Configurações de energia do SQL Server

Configuração de memória do SQL Server

https://www.brentozar.com/blitz/max-memory/

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options

IFI – Instant File Initialization

• Melhora o tempo de criação

de novas bases de dados, do

crescimento automático e da

realização de backup e

restore.

secpol.msc -> Local Policies Folder -> User Rights Assignment -> Perform Volume

Database Mail

• Habilita o SQL Server a enviar mensagens de e-mails.

• Utiliza o protocolo de Rede SMTP

Configurando Database Mail

• SQL Server: Logins - Roles – Users

• Instancia e Base de dados

1. Permissão de SYSADMIN ACESSO RESTRITO ao DBA!

2. Mapeamento de Logins e permissões de acesso ao Banco de Dados. 3. Criação de Usuários definidos para desenvolvedores e consultores

externos.

4. Criação de usuários de sistema para as aplicações corporativas.

Gerenciamento de acessos

Estratégia de Backup e Restore

Quanto tempo de informação podemos perder?

✓

Verificar as páginas do disco

✓

Definir o tipo de Recovery Model

✓

Definir os tipos de Backups

✓

Definir o local de armazenamento dos arquivos de Backup

✓

Criar a estratégia de Backup adequada pro negócio

Page Verify Option

• NONE: Não faz nenhuma

validação

• TOR_PAGE_DETECTION:

Verifica alguns bits das

páginas de dados.

• CHECKSUM: Faz uma

validação completa das

páginas de dados

Recovery Model

• SIMPLE

• Menor esforço administrativo, pois não tem backup do log

• Não é possivel restaurar até um determinado momento no tempo • Não é indicado para bases críticas

• FULL

• Guarda todas as operações de escrita no log • Obrigatório para bases criticas

• Atenção com o tamanho do arquivo de log

• Consegue restaurar até um determinado segundo do dia

• BULK-LOGGED

• Permite que algumas operações pesadas não sejam gravadas no log Ex: BULK INSERT, CREATE INDEX, SELECT INTO e etc.

Tipos de Backup

• FULL

• Gera uma cópia integral de todo o banco • Salva o log das transações ativas durante o

backup

• DIFERENCIAL

• Salva as páginas de dados alteradas desde o ultimo Backup FULL

• É cumulativo. • LOG

• Gera uma cópia do log de transações, apenas das alterações realizadas.

• Não armazena todas as informações dos backup de log anteriores.

• Deve ser criado um alerta para monitorar o tamanho do Log das bases.

Compressão de dados

Disponível a partir do SQL Server 2008

BACKUP DATABASE Exemplo

TO DISK = N’D:\SQL\Backup\Exemplo_full.bak’

WITH INIT, COMPRESSION, STATS = 10, CHECKSUM

Local de Armazenamento

• Armazenamento em discos diferentes dos arquivos de dados

do SQL Server

• Storage

• Drive ou fita externa

• Servidor remoto

• Backup to url - Azure

SQL Agent

• Jobs

• Schedules

• Alerts

Tuning

Tem como objetivo principal minimizar o tempo de

resposta e recuperação dos dados das aplicações.

O tuning é divido em 3 tipos:

1. Planejamento de performance 2. Tuning de instância e BD

3. Tuning de Consultas SQL

Ferramentas:

SQLNexus, etc…

• SQL Server Profile – Trace

• (Muito cuidado com a utilização) • Funcionalidade será descontinuada

• Disponível para SQL Server 2016 mas será removida na próxima versão

• Guarda o log de tudo que demora mais de 3 segundos no BD

• WhoisActive -

http://whoisactive.com/

• "Hey DBA! Why is the application so slow?"

• "Hey DBA! Why is my query taking, like, forever to return the results?" • "Hey DBA! Something is broken! Fix it, quick!"

Log Contadores SQL Server

• BacthRequests: Transações realizadas a cada segundo

• User Conection: Quantidade de conexões no banco de dados

• CPU: Consumo de CPU no servidor

• Page Life Expectancy: Expectativa de vida em segundos de

uma página na memória do SQL Server

Estatísticas

• São objetos do SQL Server que contém métricas de valores de colunas que são utilizadas pelo

Query Optimizer para ajudar a

montar o melhor plano de execução para uma query.

• Rotina de Update Statistics

• Melhores Planos de Execução • Realizada todo madrugada • Atualização do tipo FULL

Índices

• Estruturas em disco que proporcionam um acesso eficiente aos dados quando a pesquisa realizada pode ser atendida por esse índice.

• Após a criação de um indice, uma query que demorava 10 minutos pode ser executada em 1 minuto.

Criação de Índices

• Encontrar queries com mais READS

• Encontrar queries com maior consume de CPU • Encontrar queires mais executadas

Comandos:

• SET STATISTICS IO ON; • SET STATISTICS TIME ON; • CTRL + M : Execution Plan

• sys.dm_db_missing_index_* (Analisar)

• Sugerem alguns indices que poderiam ser criados

• Tomar cuidado com os índices sugeridos

• sys.dm_db_index_stats

• Informa quantidade de vezes que o indice foi utilizado • Guardar essa informação de forma diária

Page Split

• Quando os dados são inseridos nas paginas dos índices, quando uma página atinge 100% de utilização é necessário inserir mas linhas nessa pagina, o SQL divide esse preenchimento em duas páginas ficando 50% dos dados para cada página.

Fragmentação

• Como identificar a fragmentação de índices? • DMVs

• sys.dm_db_index_physical_stats

• Como resolver a fragmentação de índices? • ALTER INDEX REORGANIZE

• > 5% e < = 30%

• ALTER INDEX REBUILD

• > 30%

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/index-related-dynamic-management-views-and-functions-transact-sql

Criação de Alertas

• Indispensáveis para identificação de problemas

antes que eles aconteçam ou caso já tenha acontecido. Evitando uma grande perda de dados ou parada

do seu ambiente.

1. Processos Bloqueados 2. Base não Online

3. Log Full 4. Consumo CPU 5. Espaço em Disco 6. Alertas de Severidade 7. Arquivos MDF e LDF 8. Queries Lentas 9. Bases Corrompidas 10. Páginas Corrompidas 11. Jobs que Falharam

Checklist diário por E-mail

• Evita desperdício de tempo com a conferencia manual das informações

• Pode ser acessado de qualquer dispositivo

Benefícios

• Inventário do banco de dados

• Melhores práticas de administração • Estratégia de backup definida

• Tarefas automatizadas

• Monitoramento do ambiente • Tuning

• Alertas

Próximos eventos

•

SÃO PAULO #676 | SET 30

•

RIO DE JANEIRO #663 | OUT 21

•

SALVADOR #677 | NOV 18

sidneycirqueira.wordpress.com sidneyocirqueira@gmail.com

www.linkedin.com/in/sidney-oliveira-60b41128/ @sidneycirqueira