Rotinas de um Administrador de

Banco de Dados
Sumário
Índice de Figuras..................................................................................5
Infraestrutura recomendada para a instalação e configuração do SQL
Server...................................................................................................6
Arquivos gerados pelo SQL Server e suas finalidades..........................8
Arquivos do banco de dados..............................................................8
Nomes de arquivos lógico e físico.....................................................9
Gerenciamento de usuários dos Bancos de Dados no SQL Server.......9
Processos de Backup e Restore no SQL Server .................................16
Como realizar Backups manuais em disco com o SQL Server.........16
Onde eu devo guardar os meus Backups?....................................22
Como restaurar um banco de dados (RESTORE)..........................23
Segurança no SQL Server:..................................................................31
Níveis de permissões.......................................................................31
Funções de banco de dados fixas.................................................32
Funções especiais para o Banco de Dados SQL e o Azure Synapse
.....................................................................................................34
Funções msdb...............................................................................35
Trabalhando com funções de nível de banco de dados................36
Função de banco de dados pública...............................................37
Adicionar um usuário a uma função de nível de banco de dados.38
Auditoria..........................................................................................38
Componentes de auditoria do SQL Server....................................39
Auditoria do SQL Server................................................................39
Especificação da Auditoria do Servidor.........................................39
Especificação da Auditoria do Banco de Dados............................40
Destino.........................................................................................40
Visão geral do uso de auditoria do SQL Server.............................42
Considerações..............................................................................43
Anexando um banco de dados a uma auditoria definida..............43
Espelhamento de Banco de Dados e o SQL Server Audit.............43
Administradores de auditoria.......................................................44
Criando e gerenciando auditorias com o Transact-SQL................44
Instruções de linguagem de definição de dados...........................44
Exibições e funções dinâmicas.....................................................45
 Exibições do catálogo...................................................................46
Permissões....................................................................................46
Criptografia......................................................................................47
Hierarquia de criptografia.............................................................48
Mecanismos de criptografia..........................................................52
Funções Transact-SQL..................................................................52
Certificados...................................................................................52
Chaves assimétricas.....................................................................53
Chaves simétricas.........................................................................54
Criptografia de Dados Transparente.............................................54
Planos de Manutenção........................................................................54
Benefícios de planos de manutenção..............................................54
Funcionalidade de plano de manutenção........................................55
• Replicação de dados........................................................................56
TIPOS DE REPLICAÇÃO.....................................................................56
Transacional.................................................................................56
Snapshot.......................................................................................56
Merge............................................................................................56
ENTIDADES DE REPLICAÇÃO (ORIGEM)...........................................57
Publisher (Publicador)...................................................................57
Article(Artigo)...............................................................................57
Publication (Publicação)................................................................57
Distributor (Distribuidor)...............................................................57
ENTIDADES DE REPLICAÇÃO (DESTINO)..........................................57
Subscriber (Assinante)..................................................................57
Subscription (Inscrição)................................................................57
Push subscription..........................................................................58
Pull Subscription...........................................................................58
COMPARAÇÃO ENTRE OS TIPOS DE REPLICAÇÃO............................58
1) • Alta disponibilidade....................................................................58
2) • Performance..............................................................................58
Testes de stress...............................................................................58
1) Plano de execução....................................................................58
1) O que é um plano de execução..............................................58
3) Boas práticas para querys performáticas...............................60
 Índices.............................................................................................60
Recomendações na criação de um índice.....................................62
Especificação da Auditoria do Banco de Dados............................64
Índice composto ou com colunas incluídas...........................64
O que é e como evitar o Key Lookup e o RID Lookup...................65
Índices Únicos (Exclusivos)......................................................................67
Faça o Rebuild / Reorganize dos índices quando necessário............................69
DMV’s e views do catálogo de índices........................................................71
Analisando o histograma dos índices..........................................................74
5) Estatísticas..............................................................................76
6) Particionamentos....................................................................76
7) Monitoramento dos bancos de dados (recursos do SQL Server e
SSMS).................................................................................................77
Activity Monitor (Monitor de Atividade)..............................................77
8) → Extended Events (Eventos estendidos)..................................77
9) → Data Collection (Coleta de dados)..........................................77
10) Query Store (Repositório de Consultas)....................................77
11) DMV (Views de gerenciamento dinâmico)................................78
Índice de Figuras
Figura 1 - Camadas de criptografia......................................................8
Figura 2 - Camadas de criptografia....................................................10
Figura 3 – replicação de dados...........................................................15
Figura 4 - Tipos de replicação.............................................................17
Figura 5 - Plano de execução estimado..............................................18
Figura 6 - Plano de execução real......................................................19
Figura 7 - Páginas de índices..............................................................20
Figura 8..............................................................................................25
Figura 9..............................................................................................25
Figura 10 - RID Lookup.......................................................................26
Figura 11 - Particionamento de tabelas..............................................36
Infraestrutura recomendada para a instalação e
configuração do SQL Server.
Os seguintes requisitos de hardware se aplicam ao SQL Server 2016 e
ao SQL Server 2017:

Component Requisito
e

Disco rígido O SQL Server requer no mínimo 6 GB de espaço

disponível no disco rígido.

Os requisitos de espaço em disco variam de acordo com

os componentes do SQL Server instalados. Para obter
mais informações, consulte Requisitos de espaço em
disco rígido posteriormente neste artigo. Para obter mais
informações sobre tipos de armazenamento de arquivos
de dados com suporte, consulte Tipos de armazenamento
para arquivos de dados.
Recomendamos instalar o SQL Server em computadores
com os formatos de arquivo NTFS ou ReFS. O sistema de
arquivos FAT32 tem suporte, mas não é recomendado,
pois é menos seguro do que os sistemas de arquivos
NTFS ou ReFS.
Unidades somente leitura, mapeadas ou compactadas são
bloqueadas durante a instalação.

Unidade É necessária uma unidade de DVD, conforme apropriado,

para a instalação a partir de disco.

Monitoramen OSQL Server requer um monitor com resolução Super-

to VGA (800 x 600) ou superior.

Internet A funcionalidade de Internet requer acesso à Internet (a

cobrança de taxas poderá ser aplicável).

Memória * Mínimo:
Edições Express: 512 MB
Todas as outras edições: 1 GB
Recomendado:
Edições Express: 1 GB
Todas as outras edições: Pelo menos 4 GB e deve ser
aumentado à medida que o tamanho do banco de dados
Component Requisito
e

aumenta para garantir um ótimo desempenho.

Velocidade Mínimo: processador x64: 1,4 GHz

do
Recomendado: 2,0 GHz ou mais rápido
processador

Tipo de Processador x64: AMD Opteron, AMD Athlon 64, Intel

processador Xeon com suporte Intel EM64T, Intel Pentium IV com
suporte EM64T
 Arquivos gerados pelo SQL Server e suas
finalidades.
Todo o banco de dados SQL Server tem, no mínimo, dois arquivos de
sistema operacional: um arquivo de dados e um arquivo de log. Os
arquivos de dados contêm dados e objetos como tabelas, índices,
procedimentos armazenados e exibições. Os arquivos de log contêm
as informações necessárias para recuperar todas as transações no
banco de dados. Os arquivos de dados podem ser agrupados em
grupos de arquivos para propósitos de alocação e administração.

Arquivos do banco de dados

SQL Server possuem três tipos de arquivos, como mostrado na tabela

a seguir.

Arquivo Descrição

Primária Contém informações de inicialização do banco de dados e aponta

para os outros arquivos no banco de dados. Todo banco de dados
possui um arquivo de dados primário. A extensão de nome de
arquivo indicada para arquivos de dados primários é .mdf.

Secundário Arquivos de dados opcionais definidos pelo usuário. Os dados

podem ser distribuídos em vários discos, colocando cada arquivo
em uma unidade de disco diferente. A extensão de nome de
arquivo indicada para arquivos de dados secundários é .ndf.

Log de O log contém informações usadas para recuperar o banco de

Transações dados. Deve haver, no mínimo, um arquivo de log para cada
banco de dados. A extensão de nome de arquivo indicada para
arquivos de transação é .ldf.

Por exemplo, um simples banco de dados nomeado

como Vendas tem um arquivo primário que contém todos os dados e
objetos e um arquivo de log que contém as informações do log de
transações. Um banco de dados mais complexo nomeado
como Pedidos pode ser criado incluindo um arquivo primário e cinco
arquivos secundários. Os dados e objetos no banco de dados
distribuem-se pelos seis arquivos, e os quatro arquivos de log contêm
as informações do log de transação.

Por padrão, os dados e logs de transação são colocados na mesma

unidade e caminho para lidar com sistemas de disco único. Essa
opção não é o ideal para ambientes de produção. Recomendamos
que você coloque os dados e arquivos de log em discos separados.
Nomes de arquivos lógico e físico

Os arquivos do SQL Server têm dois tipos de nome de arquivo:

logical_file_name: o logical_file_name é o nome usado para se

referir ao arquivo físico em todas as instruções Transact-SQL. O nome
de arquivo lógico deve estar de acordo com as regras de
identificadores SQL Server e deve ser exclusivo entre os nomes de
arquivos lógicos no banco de dados.

os_file_name: o os_file_name é o nome do arquivo físico que inclui o

caminho de diretório. Ele deve seguir as regras dos nomes de arquivo
de sistema operacional.

Gerenciamento de usuários dos Bancos de Dados no

SQL Server
No SQL Server temos 2 tipos de logins: Windows Authentication, que
são os usuários do Windows ou Active Directory (AD) e login do tipo
SQL Server, que são aqueles que criamos somente no SQL Server.

Quando trabalhamos com Windows Authentication é possível criar o

acesso para grupos de usuários. O esquema abaixo nos mostra como
estão subdivididas as camadas de acesso ao SQL Server.
 Figura 1

Quando temos um servidor ingresso em um domino ou não, podemos

tanto criar logins tanto utilizando logins do domínio ou usuários locais,
quanto, grupos de usuários do AD ou locais.

No nível de instancia temos os temos os logins (AD, Local ou Grupos) e

as server roles, onde concedemos os acessos.

No nível de database temos os usuários com ou sem login e as database

role.
Até o SQL Server 2008 ou 2008 R2, sempre que precisássemos criar
um usuário, primeiro era necessário criar um login na instância e
depois associar a uma database. Porém, isso mudou no SQL Server
2012, quando começou a ser possível criar um usuário somente no
banco de dados, sem a necessidade de criar um login na instância.

Figura 2

Como podemos observar na imagem acima, estamos em um SQL

Server 2008 R2, onde vamos fazer um teste de criar um usuário
direto no banco Stackoverflow, sem criar um login.

CREATE USER [[Link]] WITH PASSWORD='123456', DEFAULT_SCHEMA=[dbo]

GO

Figura 3
Ao tentar criar o usuário, o SQL retorna um erro de sintaxe incorreta.

Agora, vamos fazer a mesma ação em um SQL Server 2017, porém,

vamos criar o usuário sem login na base Northwind.

Para criar um usuário sem login é necessário fazer algumas

alterações na base de dados e na instância.

sp_configure 'show advanced options',1

GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
ALTER DATABASE Northwind SET CONTAINMENT = PARTIAL

Para ver a documentação completa e as limitações basta acessar o link.

USE [Northwind]
GO
CREATE USER [[Link]] WITH PASSWORD=N'123456',
DEFAULT_SCHEMA=[dbo]
GO
USE [Northwind]
GO
ALTER ROLE [db_owner] ADD MEMBER [[Link]]
GO

Figura 4
Como podemos observar, o usuário foi criado com sucesso.
Para conectar no Management Studio é necessário informar qual é a
database default.

Na console de conexão, selecione a opção “Options”.

Figura 5

Nas propriedades de conexão, você deverá informar em qual

database você deseja conectar, no caso selecionei “Northwind”
 Figura 6

Como podemos ver, conseguimos conectar ao banco de dados e

temos acesso somente à database “Northwind”.
 Figura 7

Bom, então vimos que a partir do SQL Server 2012 é possível criar um
usuário somente no banco de dados sem a necessidade de criar o
usuário na instância. Uma grande vantagem de criar os usuários
direto no banco de dados é quando nós utilizamos ambiente com o
Always On, pois, em caso de failover os usuários já estão na base de
dados.
Processos de Backup e Restore no SQL Server .
BACKUP: Backup é basicamente uma cópia de segurança. A razão do
procedimento é assegurar que o usuário ou organização se resguardem de uma
possível perda de arquivos e dados originais, que pode ocorrer por diversos
motivos conforme descrito anteriormente.
Backups realizados periodicamente, com o procedimento correto e
armazenados de forma inteligente minimizam e até neutralizam os impactos a
serem sofridos na perda de dados e arquivos.
RESTORE: Também conhecido como Restauração, trata-se do procedimento
utilizado para recuperar a condição do seu banco de dados ou repositório de
arquivos, utilizando o Backup realizado. Neste procedimento, os dados estarão
na condição exata do momento em que o backup foi realizado.
Como realizar Backups manuais em disco com o SQL Server
1. Acesse o SQL Server Management Studio

2. Expanda a opção Banco de dados:

Figura 8

3. Clique com o botão direito do mouse no Banco de dados que deseja realizar o
Backup e selecione a opção Tarefas > Fazer backup:
 Figura 9

4. A seguinte janela será exibida, e nela podemos escolher o tipo de Backup

que será efetuado, havendo duas opções:

 Completo – Obtém o estado do banco de dados no momento em que o

backup é iniciado; é feita uma cópia dos dados e do esquema de todas
as tabelas do banco de dados e as estruturas de arquivo
correspondentes;
 Diferencial – Nesta opção apenas as partes do banco de dados
alteradas desde o último backup completo do banco de dados são lidas e
em seguida gravadas para a cópia.
 Figura 10

5. Além disso, nesta tela o SSMS já sugere um diretório para salvar o arquivo de
Backup, porém o usuário tem a liberdade de selecionar um local diferente para
isto, caso deseje salvar o seu Backup em uma localidade diferente, clique no
botão “Adicionar” e selecione o diretório em que deseja salvar o arquivo de
Backup e o nome do mesmo.
 Figura 11

Selecione o diretório em que deseja salvar o arquivo (destino do backup) e seu

nome:

Figura 12

Selecionamos o diretório C:\Boson em nosso exemplo. Clique em “Ok” para

salvar a configuração:
 Figura 13

6. Novamente na Tela principal de Backup, caso tenha definido um outro

diretório para salvar o seu Backup, selecione este caminho no quadro em
destaque:
 Figura 14

7. Antes de clicar em “Ok” na tela principal de backup, vá até “Opções de

mídia” e marque a opção “Substituir todos os conjuntos de backups existentes”,
depois volte na opção geral, selecione o local definido para o Backup e clique
em “Ok”. Desta forma será carregado o processo de Backup e será
demonstrado ao fim a mensagem que o backup foi realizado com sucesso.
 Figura 15

Todo este processo que foi descrito também pode ser realizado através do
seguinte comando (T-SQL):

BACKUP DATABASE Vendas TO DISK = 'C:\Boson\

DBVENDAS_20190115.BAK'

Onde eu devo guardar os meus Backups?

Um dos maiores erros que vejo nas empresas é o Backup do banco de dados
ser guardado apenas no próprio servidor, onde está alocado o Banco de dados,
algo que é totalmente inseguro, pois nesta situação, por exemplo em uma
infecção por RANSOMWARE além de perder o banco de dados em produção, o
Backup também será totalmente comprometido.

Portanto, uma boa prática para armazenar seus Backups é a aquisição de

algum serviço de Cloud Computing (Nuvem) , como o Google Drive, Dropbox,
OneDrive, etc., pois neste caso o Backup estará fora de sua infraestrutura de
rede, diminuindo de forma grandiosa os efeitos e prejuízos causados por um
vírus desse tipo, possibilitando a restauração dos dados de forma rápida
durante a reestabilização do sistema de uma empresa.

Ainda há mais segurança se o próprio Servidor for hospedado em um serviço de

Cloud Computing, porém este tem um custo ainda maior.

Como profissionais de TI, sabemos que muitas vezes sofremos com situações
de Budget escasso e às vezes alguns diretores de empresas que vêem alguns
procedimentos e serviços de TI como apenas um custo, fatores estes que
podem iniviabilizar a aquisição de um serviço de Cloud. Para estas situações
recomenda-se como plano B salvar o seu Backup em HD externo e retirá-lo do
Computador em seguida.

Obs. esta solução, além de ser mais trabalhosa é menos segura, pois este
dispositivo está em contato direto com a sua infraestrutura interna de TI,
havendo ainda o risco de ser infectado por vírus também.

Como restaurar um banco de dados (RESTORE)

Como explicamos no início do artigo RESTORE (ou restauração), trata-se do

procedimento utilizado para recuperar a condição do seu banco de dados
utilizando o Backup realizado.

ANTES de executar o RESTORE, é importante que se realizem alguns

procedimentos, dependendo da situação em que a Restauração está sendo
realizada:

Situação 1: Restauração quando há um Banco de dados em vigência.

Utilizado geralmente em situações onde a intenção seja restaurar os dados para

uma condição anterior, geralmente utilizadas em Desenvolvimento e testes de
softwares e até mesmo em situações de imputação de dados de forma
inconsistente em sistemas.

Antes do RESTORE é necessário se certificar que o Banco de dados não esteja

sendo utilizado, ou seja, se o banco de dados estiver com a s suas opções
expandidas, ou caso uma aba de consulta esteja aberta, ou se algum sistema
que utiliza os dados deste banco de dados estiver aberto, o RESTORE não é
completado com sucesso, onde o SSMS demonstrará uma mensagem de erro ao
tentar realizar o procedimento de RESTORE.

Situação 2: Restauração por meio de criação de Banco de dados

Este outro método é utilizado quando houve a real perda total dos dados do
servidor, de forma que se exige uma nova instalação do SQL Server, desta
forma exigindo também a configuração como se fosse um novo banco de dados
no SSMS.

Neste caso, antes do RESTORE deve ser criado um novo banco de dados:

– Clique com o Botão direito em ‘Banco de dados’ e selecione a opção ‘Novo

banco de dados’:
 Figura 16

– Insira o ‘Nome do banco de dados’ e clique em OK (caso utilize as opções

padrões do SQL):

Figura 17
Após detectar qual é a situação em que o RESTORE será realizado e executar os
procedimentos adequados conforme a situação descrita , realize a Restauração
através deste passo à passo:

1 – Clique com o botão direito do mouse sobre o banco de dados a ser

restaurado, nas opções Tarefas > Restaurar > Banco de dados:

2- A janela “Restaurar banco de dados” será exibida. Nela, marque a opção

“Dispositivo” e clique no botão “…” para fazer a busca do arquivo de Backup
Banco de dados que será utilizado para restaurar:
 Figura 18

3 – Nesta janela selecione se o banco de dados a ser restaurado é um arquivo

de Backup ou proveniente de uma URL, em seguida, clique em “Adicionar” e
selecione o arquivo ou informe as credenciais do Azure (caso tenha este serviço
contratado), de acordo com a opção selecionada:
 Figura 19

Selecione o arquivo de Backup e clique em “Ok”:

Figura 20
Ao término deste procedimento, o caminho do seu Backup estará destacado
nos campos em vermelho:

Figura 21

4 – Clique em Opções e marque a opção “Substituir o banco de dados

existente” e por fim, clique em Ok:
 Figura 22

Obs. Existem outras opções nesta tela, mas usadas para outras finalidades de
restaurações diferentes do contexto abordado neste artigo, que poderão ser
abordados em outros artigos.

5 – Após a realização do procedimento, será exibida uma mensagem

comunicando que o banco de dados foi restaurado com sucesso.

Figura 23

Todo este processo que foi descrito também pode ser feito através do seguinte
comando (T-SQL):

RESTORE DATABASE Vendas FROM DISK = 'C:\Boson\

DBVENDAS_20190115.BAK' WITH REPLACE
Segurança no SQL Server:
Níveis de permissões.

Para gerenciar facilmente as permissões em seus bancos de dados, o

SQL Server fornece várias *funções, que são entidades de segurança
que agrupam outras entidades. Eles são como grupos no sistema
operacional microsoft Windows. As funções de nível de banco de
dados são permitidas em todo banco de dados em seus escopos de
permissões.

Para adicionar e remover usuários de uma função de banco de dados,

use as opções ADD MEMBER e DROP MEMBER da instrução ALTER ROLE . O
PDW (Analytics Platform System) e o Azure Synapse não dão suporte
ao uso de ALTER ROLE. Em vez disso, use os antigos
procedimentos sp_addrolemember e sp_droprolemember .

Há dois tipos de funções no nível do banco de dados: funções de banco de

dados fixas , que são predefinidas no banco de dados, e funções de banco
de dados definidas por usuário , que você pode criar.

As funções de banco de dados fixas são definidas no nível de banco

de dados e existem em cada banco de dados. Os membros da função
de banco de dados db_owner podem gerenciar a associação a
funções de banco de dados fixas. Também há algumas funções de
banco de dados com finalidade especial no banco de dados msdb.

Você pode adicionar qualquer conta de banco de dados e outras

funções do SQL Server nas funções de nível de banco de dados.

Dica

Não adicione funções de banco de dados definidas pelo usuário como

membros de funções fixas. Isso poderia habilitar o escalonamento
não intencional de privilégios.

As permissões de funções de banco de dados definidas pelo usuário

podem ser personalizadas com instruções GRANT, DENY e REVOKE.
Para obter mais informações, consulte Permissões (Mecanismo de
Banco de Dados).

Para obter uma lista de todas as permissões, consulte o

cartaz Permissões do Mecanismo de Banco de Dados . As permissões
em nível de servidor não podem ser concedidas às funções de banco
de dados. Logons e outras entidades em nível de servidor – como
funções de servidor – não podem ser adicionados às funções de
banco de dados. Para a segurança em nível de servidor no SQL
Server, use funções de servidor . As permissões em nível de servidor
não podem ser concedidas por meio das funções no Banco de Dados
SQL e no Azure Synapse.

Funções de banco de dados fixas

A tabela a seguir mostra as funções de banco de dados fixas e suas

funcionalidades. Essas funções existem em todos os bancos de dados.
Exceto para a função de banco de dados pública, as permissões
atribuídas às funções de banco de dados fixas não podem ser
alteradas.

Nome da função Descrição

de banco de
dados fixa

db_owner Os membros da função de banco de dados

fixa db_owner podem executar todas as atividades de
configuração e manutenção no banco de dados, bem
como drop o banco de dados no SQL Server. (No Banco de
Dados SQL e no Azure Synapse, algumas atividades de
manutenção exigem permissões em nível de servidor e
não podem ser executadas por db_owners.)

db_securityadmin Os membros da função de banco de dados

fixa db_securityadmin podem modificar a associação
de função somente para funções personalizadas e
gerenciar permissões. Membros dessa função têm o
potencial de elevar seus privilégios e suas ações devem
ser monitoradas.

db_accessadmin Os membros da função de banco de dados

fixa db_accessadmin podem adicionar ou remover o
acesso ao banco de dados para logons do Windows,
grupos do Windows e logons do SQL Server .

db_backupoperat Os membros da função de banco de dados

or fixa db_backupoperator podem fazer backup do banco
de dados.

db_ddladmin Os membros da função de banco de dados

fixa db_ddladmin podem executar qualquer comando
Data Definition Language (DDL) em um banco de dados.

db_datawriter Os membros da função de banco de dados

fixa db_datawriter podem adicionar, excluir ou alterar
dados em todas as tabelas de usuário.

db_datareader Os membros da função de banco de dados

fixa db_datareader podem ler todos os dados de todas
Nome da função Descrição
de banco de
dados fixa

as tabelas de usuário e exibições. Os objetos de usuário

podem existir em qualquer esquema,
exceto sys e INFORMATION_SCHEMA.

db_denydatawrite Os membros da função de banco de dados

r fixa db_denydatawriter não podem adicionar, modificar
ou excluir nenhum dado nas tabelas de usuário de um
banco de dados.

db_denydataread Os membros da função de banco de dados

er fixa db_denydatareader não podem ler nenhum dado
das tabelas de usuário e exibições de um banco de
dados.

As permissões atribuídas às funções de banco de dados fixas não

podem ser alteradas. A seguinte figura mostra as permissões
atribuídas às funções de banco de dados fixas:
 Figura 24

Funções especiais para o Banco de Dados SQL e o Azure Synapse

Essas funções de banco de dados existem somente no banco de

dados mestre virtual. As permissões são restritas às ações
executadas no mestre. Somente os usuários de banco de dados no
mestre podem ser adicionados a essas funções. Logons não podem
ser adicionados a essas funções, mas é possível criar usuários com
base nos logons e esses usuários podem ser adicionados às funções.
Os usuários de banco de dados contidos no mestre também podem
ser adicionados a essas funções. No entanto, os usuários de banco de
dados adicionados contidos na função dbmanager no mestre não
podem ser usados para criar novos bancos de dados.

Nome da Descrição
função

dbmanager Pode criar e excluir bancos de dados. Um membro da função

dbmanager que cria um banco de dados se torna o proprietário
desse banco de dados, o que permite ao usuário se conectar ao
banco de dados como o usuário dbo. O usuário dbo tem todas
as permissões de banco de dados no banco de dados. Os
Nome da Descrição
função

membros da função dbmanager não têm necessariamente

permissão para acessar bancos de dados que eles não possuem.

db_exporte Aplica-se apenas aos pools de SQL dedicados do Azure Synapse

r Analytics (antes conhecido como SQL DW).
Os membros da função de banco de dados
fixa db_exporter podem realizar todas as atividades de
exportação de dados. As permissões concedidas por meio dessa
função são CREATE TABLE, ALTER ANY SCHEMA, ALTER ANY
EXTERNAL DATA SOURCE, ALTER ANY EXTERNAL FILE FORMAT.

loginmanag Pode criar e excluir logons no banco de dados mestre virtual.

er

Observação

A entidade de segurança no nível do servidor e o Administrador do

Azure Active Directory (se estiver configurado) têm todas as
permissões no Banco de Dados SQL e no Azure Synapse sem precisar
ser membros das funções. Saiba mais em Autenticação e
autorização do Banco de Dados SQL: concessão de acesso.

Algumas funções de banco de dados não são aplicáveis ao SQL do

Azure ou Azure Synapse:

 db_backupoperator não é aplicável no banco de dados

SQL do Azure (instância não gerenciada) e no pool sem
servidor do Azure Synapse porque os comandos do T-SQL
de backup e restauração não estão disponíveis.
 db_datawriter e db_denydatawriter não são aplicáveis
ao Azure Synapse sem servidor porque ele apenas lê dados
externos.

Funções msdb

O banco de dados msdb contém as funções com finalidade especial

que são mostradas na tabela a seguir.

Nome da função msdb Descrição

db_ssisadmin Os membros dessas funções de banco de dados

podem administrar e usar o SSIS. As instâncias
db_ssisoperator do SQL Server que são atualizadas de uma
versão anterior podem conter uma versão mais
db_ssisltduser antiga da função que foi nomeada com o DTS
Nome da função msdb Descrição

(Data Transformation Services), e não com o

SSIS. Para obter mais informações, consulte As
Funções do Integration Services (Serviço SSIS).

dc_admin Os membros dessas funções de banco de dados

podem administrar e usar o coletor de dados.
dc_operator Para obter mais informações, consulte Data
Collection.
dc_proxy

PolicyAdministratorRole Os membros da função de banco de dados db_

PolicyAdministratorRole podem executar
todas as atividades de configuração e
manutenção nas políticas condições do
Gerenciamento Baseado em Políticas. Para obter
mais informações, veja Administrar servidores
usando o gerenciamento baseado em políticas.

ServerGroupAdministrato Os membros dessas funções de banco de dados

rRole podem administrar e usar grupos de servidores
registrados.
ServerGroupReaderRole

dbm_monitor Criada no banco de dados msdb quando o

primeiro banco de dados é registrado no Monitor
de Espelhamento de Banco de Dados. A
função dbm_monitor não tem nenhum membro
até que um administrador do sistema atribua
usuários à função.

Importante

Os membros das funções db_ssisadmin e dc_admin podem elevar

seus privilégios para sysadmin. Essa elevação de privilégios pode
ocorrer porque essas funções podem modificar os pacotes do
Integration Services e os pacotes do Integration Services podem ser
executados pelo SQL Server usando o contexto de segurança
sysadmin do SQL Server Agent. Para se proteger contra essa elevação
de privilégio ao executar planos de manutenção, conjuntos de coletas
de dados e outros pacotes do Integration Services , configure os
trabalhos do SQL Server Agent que executam pacotes para usar uma
conta proxy com privilégios limitados ou apenas adicione
membros sysadmin às funções db_ssisadmin e dc_admin .
Trabalhando com funções de nível de banco de dados

A tabela a seguir explica os comandos, exibições e funções para

trabalhar com funções de nível de banco de dados.

Recurso Type Descrição

sp_helpdbfixedrole Metadad Retorna uma lista das funções de banco

(Transact-SQL) os de dados fixas.

sp_dbfixedrolepermission Metadad Exibe as permissões de uma função de

(Transact-SQL) os banco de dados fixa.

sp_helprole (Transact- Metadad Retorna informações sobre as funções no

SQL) os banco de dados atual.

sp_helprolemember Metadad Retorna informações sobre os membros

(Transact-SQL) os de uma função no banco de dados atual.

sys.database_role_memb Metadad Retorna uma linha para cada membro de

ers (Transact-SQL) os cada função de banco de dados.

IS_MEMBER (Transact- Metadad Indica se o usuário atual é um membro do

SQL) os grupo Microsoft Windows especificado ou
da função de banco de dados do Microsoft
SQL Server.

CREATE ROLE (Transact- Comando Cria uma nova função de banco de dados
SQL) no banco de dados atual.

ALTER ROLE (Transact- Comando Altera o nome ou a associação de uma

SQL) função de banco de dados.

DROP ROLE (Transact- Comando Remove uma função do banco de dados.

SQL)

sp_addrole (Transact- Comando Cria uma nova função de banco de dados

SQL) no banco de dados atual.

sp_droprole (Transact- Comando Remove uma função de banco de dados

SQL) do banco de dados atual.

sp_addrolemember Comando Adiciona um usuário de banco de dados,

(Transact-SQL) uma função de banco de dados, o logon
do Windows ou um grupo do Windows em
uma função de banco de dados no banco
de dados atual. Todas as plataformas,
exceto o PDW (Analytics Platform System)
e o Azure Synapse, devem usar o ALTER
ROLE em vez disso.
Recurso Type Descrição

sp_droprolemember Comando Remove uma conta de segurança de uma

(Transact-SQL) função do SQL Server no banco de dados
atual. Todas as plataformas, exceto o
PDW (Analytics Platform System) e o
Azure Synapse, devem usar o ALTER
ROLE em vez disso.

GRANT Permissõ Adiciona a permissão a uma função.

es

DENY Permissõ Nega uma permissão a uma função.

es

REVOKE Permissõ Remove as permissões concedidas ou

es negadas anteriormente.

Função de banco de dados pública

Cada usuário do banco de dados pertence à função de banco de

dados pública . Quando permissões específicas não são concedidas
ou são negadas a um usuário em um objeto seguro, o usuário herda
as permissões concedidas como públicas naquele objeto. Os
usuários do banco de dados não podem ser removidos da
função pública .

Exemplos

Os exemplos nesta seção mostram como trabalhar com funções de

nível de banco de dados.

Adicionar um usuário a uma função de nível de banco de dados

O exemplo a seguir adiciona o usuário 'Ben' à função fixa de nível de

banco de dados db_datareader.

ALTER ROLE db_datareader

ADD MEMBER Ben;
GO

Listando todas as entidades de segurança de banco de dados que são

membros de uma função de nível de banco de dados

A instrução a seguir retorna todos os membros de qualquer função de

banco de dados.

SELECT r.principal_id AS RolePrincipalID

, [Link] AS RolePrincipalName
, drm AS MemberPrincipalID
 , [Link] AS
MemberPrincipalName
FROM sys.database_role_members AS drm
JOIN sys.database_principals AS r
ON drmrole_principal_id = r.principal_id
JOIN sys.database_principals AS m
ON drm.member_principal_id = m.principal_id;
GO

Auditoria.

A auditoria de uma instância do Mecanismo de Banco de Dados do SQL

Server ou de um banco de dados individual envolve o controle e o
registro em log dos eventos que ocorrem no Mecanismo de Banco de
Dados. A auditoria doSQL Server permite criar auditorias de servidor,
que podem conter especificações de auditoria de servidor para
eventos no nível de servidor, além de especificações de auditoria de
banco de dados para eventos no nível de banco de dados. Os eventos
auditados podem ser gravados nos logs de eventos ou nos arquivos
de auditoria.

Dependendo dos requisitos ou padrões governamentais de sua

instalação, há vários níveis de auditoria no SQL Server. A auditoria do
SQL Server fornece as ferramentas e os processos necessários para
habilitar, armazenar e exibir auditorias em vários objetos de servidor
e de banco de dados.

É possível gravar grupos de ação de auditoria de servidor por

instância, e grupos de ação de auditoria de banco de dados ou ações
de auditoria de banco de dados por banco de dados. O evento de
auditoria ocorrerá sempre que a ação auditável for encontrada.

Todas as edições do SQL Server oferecem suporte a auditorias no

nível do servidor. Todas as edições oferecem suporte a auditorias no
nível do banco de dados a partir do SQL Server 2016 (13.x) SP1.
Antes disso, a auditoria no nível de banco de dados se limitava às
edições Enterprise, Developer e Evaluation. Para obter mais
informações,

Componentes de auditoria do SQL Server

Auditoria é a combinação de vários elementos em um único pacote de

um grupo específico de ações de servidor ou de banco de dados. Os
componentes de auditoria do SQL Server são combinados para
produzir uma saída conhecida como auditoria, da mesma forma como
uma definição de relatório combinada com elementos gráficos e de
dados produz um relatório.
A auditoria doSQL Server usa Eventos Estendidos para ajudar a criar uma
auditoria. Para obter mais informações sobre Eventos Estendidos,
consulte Eventos Estendidos.

Auditoria do SQL Server

O objeto Auditoria do SQL Server coleta uma instância única de ações no

nível do servidor e/ou do banco de dados e grupos de ações a serem
monitoradas. A auditoria está no nível de instância do SQL Server .
Você pode ter várias auditorias por instância do SQL Server .

Ao definir uma auditoria, especifique o local de saída dos resultados.

Esse é o destino da auditoria. A auditoria é criada em um
estado desabilitado e não audita automaticamente nenhuma ação. Após
a habilitação da auditoria, o destino da auditoria recebe dados da
auditoria.

Especificação da Auditoria do Servidor

O objeto Especificação da Auditoria do Servidor pertence a uma auditoria. É

possível criar uma especificação de auditoria de servidor por
auditoria, já que ambas são criadas no escopo da instância do SQL
Server .

A especificação da auditoria do servidor coleta muitos grupos de ação

no nível do servidor gerados pelo recurso Eventos Estendidos. É
possível incluir grupos de ação de auditoria em uma especificação da
auditoria do servidor. Os grupos de ação de auditoria são grupos de
ações predefinidos, que são eventos atômicos que ocorrem no
Mecanismo de Banco de Dados. Essas ações são enviadas à auditoria,
que por sua vez os registra no destino.

Grupos de ação de auditoria no nível do servidor são descritos no

tópico Ações e grupos de ações de auditoria do SQL Server.

Especificação da Auditoria do Banco de Dados

O objeto Especificação da Auditoria do Banco de Dados também pertence à

auditoria do SQL Server . É possível criar uma especificação da
auditoria do banco de Dados por banco de dados do SQL Server por
auditoria.

Uma especificação da auditoria do banco de dados coleciona ações

de auditoria no nível do banco de dados geradas pelo recurso Eventos
Estendidos. É possível adicionar grupos de ações de auditoria ou de
eventos de auditoria a uma especificação da auditoria do banco de
dados. Eventos de auditoria são ações atômicas que podem ser
examinadas pelo mecanismo do SQL Server . Grupos de ação de
auditoria são grupos de ações predefinidos. Ambos estão no escopo do
banco de dados do SQL Server . Essas ações são enviadas à auditoria,
que por sua vez os registra no destino. Não inclua objetos do escopo
de servidor, como as exibições do sistema, em uma especificação de
auditoria de banco de dados do usuário.

Grupos de ação de auditoria no nível do banco de dados e ações de

auditoria são descritos no tópico Ações e grupos de ações de
auditoria do SQL Server.

Destino

Os resultados de uma auditoria são enviados a um destino que pode

ser um arquivo, o log de eventos de Segurança do Windows ou o log
de eventos de Aplicativo do Windows. É necessário verificar e
arquivar os logs periodicamente para certificar-se de que o destino
tenha espaço suficiente para gravar mais registros.

Importante

Qualquer usuário autenticado pode fazer a leitura ou gravação no log

de eventos de Aplicativo do Windows. O log de eventos de Aplicativo
requer menos permissões que o log de eventos de Segurança do
Windows e é menos seguro.

Gravar no log de Segurança do Windows exige que a conta de serviço

do SQL Server seja adicionada à política Gerar auditorias de
segurança . Por padrão, Sistema Local, Serviço Local e Serviço de
Rede fazem parte dessa política. Esta configuração pode ser definida
com o uso do snap-in de política de segurança ([Link]). Além
disso, é necessário habilitar a política de segurança Auditar acesso
ao objeto para Êxito e Falha. Esta configuração pode ser definida
com o uso do snap-in de política de segurança ([Link]). No
Windows Vista ou no Windows Server 2008 (e posterior), é possível
definir a política mais detalhada de aplicativo gerado na linha de
comando usando o programa de política de auditoria
([Link]) . Para obter mais informações sobre as etapas para
habilitar a gravação no log de Segurança do Windows,
consulte Gravar eventos de auditoria do SQL Server no log de
segurança. Para obter mais informações sobre o programa
[Link], consulte o artigo 921469 How to use Group Policy to
configure detailed security auditingda Base de Dados de
Conhecimento. Os logs de eventos do Windows são globais ao
sistema operacional Windows. Para obter mais informações sobre os
logs de eventos do Windows, consulte Event Viewer Overview. Se
você precisar de permissões mais exatas na auditoria, use o destino
de arquivo binário.
Quando você está salvando informações de auditoria em um arquivo,
para ajudar a impedir falsificação, você pode restringir o acesso ao
local do arquivo das seguintes maneiras:

 A Conta de Serviço SQL Server deve ter permissão de

Leitura e Gravação.

 Os Administradores de Auditoria geralmente requerem

permissão de Leitura e Gravação. Isso pressupõe que os
Administradores de Auditoria sejam contas do Windows
para a administração de arquivos de auditoria, por
exemplo, copiando-os em compartilhamentos diferentes,
armazenando-os em backup e assim por diante.

 Os Leitores de Auditoria que são autorizados a ler os

arquivos de auditoria precisam ter permissão de Leitura.

Mesmo quando o Mecanismo de Banco de Dados estiver gravando em

um arquivo, outros usuários do Windows poderão ler o arquivo de
auditoria se tiverem permissão. O Mecanismo de Banco de Dados não
possui um bloqueio exclusivo que impeça operações de leitura.

Como o Mecanismo de Banco de Dados pode acessar o arquivo, os

logons do SQL Server que tiverem a permissão do CONTROL SERVER
poderão usar o Mecanismo de Banco de Dados para acessar os
arquivos de auditoria. Para registrar qualquer usuário que esteja
lendo o arquivo de auditoria, defina uma auditoria em
[Link].fn_get_audit_file. Isso registra os logons com permissão
CONTROL SERVER que acessaram o arquivo de auditoria por meio do
SQL Server.

Se um Administrador de Auditoria copiar o arquivo em um local

diferente (para fins de arquivamento, entre outros), as ACLs no novo
local deverão ter apenas as seguintes permissões:

 Administrador de Auditoria – Leitura/Gravação

 Leitor de Auditoria – Leitura

É recomendável gerar relatórios de auditoria de uma instância

separada do SQL Server, como uma instância do SQL Server Express,
a qual apenas Administradores de Auditoria ou Leitores de Auditoria
tenham acesso. Ao usar uma instância separada do Mecanismo de
Banco de Dados para relatório, você pode ajudar a impedir que
usuários não autorizados obtenham acesso ao registro de auditoria.

Você pode oferecer proteção adicional contra acesso não autorizado

criptografando a pasta na qual o arquivo de auditoria é armazenado
usando Criptografia de Unidade de Windows BitLocker ou Sistema de
Arquivo do Windows Encrypting.
Visão geral do uso de auditoria do SQL Server

É possível usar o SQL Server Management Studio ou o Transact-SQL

para definir uma auditoria. Após a criação e habilitação da auditoria, o
destino receberá entradas.

É possível ler os logs de eventos do Windows usando o

utilitário Visualizador de Eventos do Windows. Para destinos de
arquivo, é possível usar o Visualizador do Arquivo de Log no SQL
Server Management Studio ou a função fn_get_audit_file para ler o
arquivo de destino.

O processo geral para criar e usar uma auditoria do é o seguinte.

1. Crie uma auditoria e defina o destino.

2. Crie uma especificação da auditoria do servidor ou

especificação da auditoria do banco de dados que mapeie
para a auditoria. Habilite a especificação de auditoria.

3. Habilite a auditoria.

4. Leia os eventos de auditoria usando o

recurso Visualizador de Eventos do Windows,
o Visualizador do Arquivo de Log ou a função
fn_get_audit_file.

Considerações

No caso de falha durante o início da auditoria, o servidor não será

iniciado. Nesse caso, é possível iniciar o servidor usando a opção -f na
linha de comando.

Quando uma falha na auditoria faz com que o servidor seja desligado
ou não seja iniciado devido à especificação de
ON_FAILURE=SHUTDOWN para a auditoria, o evento
MSG_AUDIT_FORCED_SHUTDOWN é gravado no log. Como o
desligamento ocorrerá quando configuração for encontrada pela
primeira vez, o evento será gravado uma vez. Esse evento será
gravado após a mensagem de falha da auditoria provocar o
desligamento. Um administrador pode ignorar os desligamentos
induzidos por auditoria iniciando o SQL Server no modo de usuário
único usando o sinalizador -m. Se você iniciar no modo de Usuário
Único, desatualizará qualquer auditoria em que
ON_FAILURE=SHUTDOWN estiver especificado para execução naquela
sessão como ON_FAILURE=CONTINUE. Quando o SQL Server for
iniciado pelo sinalizador -m, a mensagem
MSG_AUDIT_SHUTDOWN_BYPASSED será gravada no log de erros.
Para obter mais informações sobre as opções de inicialização do
serviço, consulte Opções de inicialização do serviço Mecanismo de
Banco de Dados.

Anexando um banco de dados a uma auditoria definida

Anexar um banco de dados que tenha uma especificação de auditoria

e especifica um GUID inexistente ao servidor gerará uma
especificação de auditoria órfã . Como não existe uma auditoria com
um GUID correspondente na instância do servidor, nenhum evento de
auditoria será gravado. Para corrigir isso, use o comando ALTER
DATABASE AUDIT SPECIFICATION para conectar a especificação de
auditoria órfã a uma auditoria de servidor existente. Ou use o
comando CREATE SERVER AUDIT para criar uma nova auditoria de
servidor com o GUID especificado.

É possível anexar um banco de dados que tenha uma especificação

de auditoria definida a outra edição do SQL Server que não dê
suporte à auditoria do SQL Server , como o SQL Server Express , mas
os eventos de auditoria não serão gravados.

Espelhamento de Banco de Dados e o SQL Server Audit

Um banco de dados com uma especificação de auditoria de banco de

dados definida e que usa espelhamento de banco de dados incluirá a
especificação de auditoria de banco de dados. Para funcionar
corretamente na instância de SQL espelhada, é necessário configurar
os seguintes itens:

 É necessário que o servidor espelho tenha uma auditoria

com o mesmo GUID para habilitar a especificação de
auditoria de banco de dados para gravar registros de
auditoria. Isso pode ser configurado usando o comando
CREATE AUDIT WITH GUID = <GUID from source Server Audit>.

 Para destinos de arquivos binários, é necessário que a

conta do serviço de servidor espelho tenha as permissões
apropriadas onde a trilha de auditoria começou a ser
gravada.

 Para destinos de log de eventos do Windows, a política de

segurança no computador em que se encontra o servidor
espelho deve permitir o acesso de conta de serviço ao log
de eventos de aplicativo ou de segurança.
Administradores de auditoria

Os membros da função de servidor fixa sysadmin são identificados

como o usuário dbo em cada banco de dados. Para auditar as ações
dos administradores, audite as ações do usuário dbo .

Criando e gerenciando auditorias com o Transact-SQL

É possível usar instruções DDL, funções e exibições de gerenciamento

dinâmico e exibições do catálogo para implementar todos os aspectos
da Auditoria do SQL Server .

Instruções de linguagem de definição de dados

É possível usar as seguintes instruções DDL para criar, alterar e

remover especificações de auditoria:

Instruções DDL Descrição

ALTER AUTHORIZATION Altera a propriedade de um protegível.

ALTER DATABASE AUDIT Altera o objeto de especificação de

SPECIFICATION auditoria do banco de dados usando o
recurso Auditoria do SQL Server.

ALTERAR AUDITORIA DE SERVIDOR Altera um objeto de auditoria do

servidor usando o recurso Auditoria do
SQL Server.

ALTER SERVER AUDIT SPECIFICATION Altera o objeto de especificação de

auditoria do servidor usando o recurso
Auditoria do SQL Server.

CREATE DATABASE AUDIT Cria um objeto de especificação de

SPECIFICATION auditoria do banco de dados usando o
recurso de auditoria do SQL Server.

CRIAR AUDITORIA DE SERVIDOR Cria um objeto de auditoria do servidor

usando a Auditoria do SQL Server.

CREATE SERVER AUDIT Cria um objeto de especificação de

SPECIFICATION auditoria do servidor usando o recurso
Auditoria do SQL Server.

DROP DATABASE AUDIT Descarta o objeto de especificação de

SPECIFICATION auditoria do banco de dados usando o
recurso Auditoria do SQL Server.

DROP SERVER AUDIT Descarta um objeto de auditoria de

servidor usando o recurso SQL Server
Instruções DDL Descrição

Audit.

DROP SERVER AUDIT SPECIFICATION Descarta um objeto de especificação

de auditoria do servidor usando o
recurso Auditoria do SQL Server.

Exibições e funções dinâmicas

A tabela a seguir lista as exibições e funções dinâmicas que podem

ser usadas na auditoria do SQL Server .

Exibições e funções Descrição

dinâmicas

sys.dm_audit_actions Retorna uma linha para cada ação de auditoria que

pode ser reportada no log de auditoria e para cada
grupo de ação de auditoria que pode ser
configurado como parte do SQL Server Audit.

sys.dm_server_audit_stat Fornece informações sobre o estado atual da

us auditoria.

sys.dm_audit_class_type_ Retorna uma tabela que mapeia o campo

map class_type do log de auditoria para o campo
class_desc em sys.dm_audit_actions.

fn_get_audit_file Retorna informações de um arquivo de auditoria

criado por uma auditoria de servidor.

Exibições do catálogo

A tabela a seguir lista as exibições do catálogo que podem ser usadas

para auditoria do SQL Server .

Exibições do catálogo Descrição

sys.database_audit_specifications Contém informações sobre as

especificações de auditoria do banco de
dados de uma auditoria do SQL Server em
uma instância de servidor.

sys.database_audit_specification_ Contém informações sobre as

details especificações de auditoria de banco de
dados em uma auditoria do SQL Server em
uma instância de servidor para todos os
bancos de dados.

sys.server_audits Contém uma linha para cada auditoria do

Exibições do catálogo Descrição

SQL Server em uma instância de servidor.

sys.server_audit_specifications Contém informações sobre as

especificações de auditoria do servidor em
uma auditoria do SQL Server em uma
instância do servidor.

sys.server_audit_specifications_de Contém informações sobre os detalhes

tails (ações) de especificação de auditoria de
servidor em uma auditoria do SQL Server
em uma instância de servidor.

sys.server_file_audits Contém informações estendidas de

repositórios sobre o tipo de auditoria de
arquivo em uma auditoria do SQL Server ,
em uma instância do servidor.

Permissões

Cada recurso e comando de Auditoria do SQL Server tem requisitos

de permissão individuais.

Para criar, alterar ou descartar uma Auditoria de Servidor ou uma

Especificação de Auditoria de Servidor, as entidades de segurança
exigem a permissão ALTER ANY SERVER AUDIT ou a permissão
CONTROL SERVER. Para criar, alterar ou descartar uma Especificação
de Auditoria de Banco de Dados, as entidades de segurança de banco
de dados exigem a permissão DATABASE AUDIT ou ALTER ou
CONTROL no banco de dados. Além disso, as entidades de segurança
devem ter permissão para se conectar ao banco de dados ou à
permissão ALTER ANY SERVER AUDIT ou CONTROL SERVER.

A permissão VIEW ANY DEFINITION fornece acesso para exibir os

modos de exibição de auditoria no nível de servidor e VIEW
DEFINITION fornece acesso para exibir os modos de exibição de
auditoria no nível de banco de dados. A negação dessas permissões
substitui a capacidade de exibir os modos de exibição de catálogo,
mesmo que a entidade de segurança tenha as permissões ALTER ANY
SERVER AUDIT ou ALTER ANY DATABASE AUDIT.

Para obter mais informações sobre como conceder direitos e

permissões, consulte GRANT (Transact-SQL).

Cuidado
Entidades na função sysadmin podem violar qualquer componente de
auditoria e aqueles na função db_owner podem violar quaisquer
especificações em um banco de dados. A auditoria doSQL Server
valida esse logon que cria ou altera uma especificação de auditoria
que tenha pelo menos a permissão ALTER ANY DATABASE AUDIT. No
entanto, ela não faz nenhuma validação quando você anexa um
banco de dados. Você deve considerar que todas as Especificações de
Auditoria de Banco de Dados são seguras somente para as entidades
nas funções sysadmin ou db_owner.

Criptografia.
Criptografia é o processo de confundir dados pelo uso de uma chave
ou senha. Isso pode tornar os dados inúteis sem a chave de
descriptrografia correspondente ou senha. A criptografia não resolve
problemas de controle de acesso. Porém, aumenta a segurança,
limitando perda de dados mesmo se os controles de acesso forem
ignorados. Por exemplo, se o computador host do banco de dados for
malconfigurado e um hacker obtiver dados confidenciais, as
informações roubadas poderão ser inúteis se estiverem
criptografadas.
Importante
Embora a criptografia seja uma ferramenta valiosa para ajudar a
garantir a segurança, não deve ser considerada em todos os dados ou
conexões. Quando você estiver decidindo se a criptografia deve ser
implementada, considere como os usuários acessarão os dados. Se os
usuários acessarem dados por uma rede pública, a criptografia de
dados poderá ser necessária para aumentar a segurança. No entanto,
se todos os acessos envolverem uma configuração de intranet
segura, a criptografia poderá não ser necessária. Qualquer uso de
criptografia deve também incluir uma estratégia de manutenção de
senhas, chaves e certificados.
Observação
Você pode usar a criptografia em SQL Server para conexões, dados e
procedimentos armazenados. Os tópicos a seguir contêm mais
informações sobre criptografia em SQL Server.
Hierarquia de criptografia
SQL Server criptografa dados com uma criptografia hierárquica e
infraestrutura de gerenciamento de chaves. Cada camada criptografa
a camada abaixo dela usando uma combinação de certificados,
chaves assimétricas e chaves simétricas. Chaves assimétricas e
chaves simétricas podem ser armazenadas fora do SQL Server em um
módulo EKM (Gerenciamento Extensível de Chaves).
A ilustração a seguir mostra que cada camada da hierarquia de
criptografia criptografa a camada abaixo dela e exibe as
configurações de criptografia mais comuns. O acesso ao início da
hierarquia normalmente é protegido por uma senha.

Figura 25 - Camadas de criptografia

Lembre-se dos seguintes conceitos:

 Para obter melhor desempenho, criptografe os dados

usando chaves simétricas em vez de certificados ou chaves
assimétricas.

 As chaves-mestre do banco de dados são protegidas pela

Chave Mestra do Serviço. A Chave Mestra do Serviço é
criada pela configuração do SQL Server e é criptografada
com a DPAPI (API de proteção de dados) do Windows.
  Outras hierarquias de criptografia que empilham camadas
adicionais são possíveis.

 Um módulo de Gerenciamento Extensível de Chaves (EKM)

mantém chaves simétricas ou assimétricas fora do SQL
Server.

 A TDE (Criptografia Transparente de Dados) deve usar uma

chave simétrica chamada de chave de criptografia de
banco de dados que é protegida por um certificado
protegido pela chave mestra de banco de dados do banco
de dados mestre ou por uma chave assimétrica
armazenada em um EKM.

 A Chave Mestra do Serviço e todas as Chaves Mestras do

Banco de Dados são chaves simétricas.

A ilustração a seguir mostra as mesmas informações de uma maneira

alternativa.
 Figura 26 - Camadas de criptografia

Este diagrama ilustra os seguintes conceitos adicionais:

 Nesta ilustração, as setas indicam hierarquias de

criptografia comuns.
  As chaves simétricas e assimétricas no EKM podem
proteger o acesso às chaves simétricas e assimétricas
armazenadas no SQL Server. A linha pontilhada associada
ao EKM indica que as chaves no EKM podem substituir as
chaves simétricas e assimétricas armazenadas no SQL
Server.

Mecanismos de criptografia

SQL Server fornece os seguintes mecanismos para criptografia:

 Transact-SQL funções

 Chaves assimétricas

 Chaves simétricas

 Certificados

 Criptografia de Dados Transparente

Funções Transact-SQL

Itens individuais podem ser criptografados quando são inseridos ou

atualizados usando funções Transact-SQL .

Certificados

Um certificado de chave pública, geralmente chamado apenas de

certificado, é uma instrução assinada digitalmente que associa o valor
de uma chave pública à identidade da pessoa, dispositivo ou serviço
que mantém a chave privada correspondente. Os certificados são
emitidos e assinados por uma autoridade de certificação (CA). A
entidade que recebe um certificado de uma CA é o assunto desse
certificado. Normalmente os certificados contêm as seguintes
informações.

 A chave pública do assunto.

 A informações de identificador do assunto, como o nome e

endereço de email.

 O período de validade. Este é o período no qual o

certificado é considerado válido.

Um certificado só é válido pelo período de tempo

especificado nele; cada certificado contém as datas Válido
a partir de e Válido até . Estas datas definem os limites
do período de validade. Depois que o período de validade
 de um certificado chega ao fim, um novo certificado deve
ser solicitado pelo assunto do certificado agora expirado.

 Informações de identificador de emissor.

 A assinatura digital do emissor.

Esta assinatura atesta a validade da associação entre a

chave pública e as informações de identificador do
assunto. (O processo de assinar digitalmente as
informações força a transformação de informações, bem
como de algumas informações secretas mantidas pelo
remetente, em uma marca chamada assinatura).

O principal benefício dos certificados é que o host não precisa manter

um conjunto de senhas para assuntos individuais. Ao contrário, o host
só precisa estabelecer confiança em um emissor de certificado, que
pode então assinar um número ilimitado de certificados.

Quando um host,como um servidor Web seguro, designa um emissor

como uma autoridade raiz confiável, ele confia implicitamente nas
políticas que o emissor usou para estabelecer as associações dos
certificados que ele emitiu. Consequentemente, o host confia que o
emissor verificou a identidade do assunto do certificado. Um host
designa um emissor como uma autoridade raiz confiável colocando o
certificado autoassinado do emissor, que contém a chave pública
dele, no repositório de certificados da autoridade de certificação raiz
confiável do computador host. As autoridades de certificação
intermediárias ou subordinadas são confiáveis apenas se tiverem um
caminho de certificação válido de uma autoridade de certificação raiz
confiável.

O emissor pode revogar um certificado antes de sua expiração. A

revogação cancela a associação de uma chave pública para uma
identidade que é declarada no certificado. Cada emissor mantém uma
lista de revogação de certificados que pode ser usada pelos
programas quando eles estiverem verificando a validade de qualquer
certificado especificado.

Os certificados autoassinados criados pelo SQL Server seguem o

padrão X.509 e oferecem suporte aos campos X.509 v1.

Chaves assimétricas

Uma chave assimétrica é composta de uma chave privada e uma

chave pública correspondente. Cada chave pode descriptografar
dados criptografados por outra chave. A criptografia assimétrica e a
descriptografia usam muitos recursos, mas eles fornecem um nível
mais alto de segurança que a criptografia simétrica. Uma chave
assimétrica pode ser usada ao criptografar uma chave simétrica para
armazenamento em um banco de dados.

Chaves simétricas

Uma chave simétrica é uma chave usada para criptografia e

descriptografia. A criptografia e a descriptografia que usam uma
chave simétrica são rápidas e adequadas ao uso de rotina com dados
confidenciais no banco de dados.

Criptografia de Dados Transparente

A TDE (Criptografia Transparente de Dados) é um caso especial de

criptografia que usa uma chave simétrica. Ela criptografa um banco
de dados inteiro usando a chave simétrica chamada de chave de
criptografia de banco de dados. A chave de criptografia de banco de
dados é protegida por outras chaves ou certificados que são
protegidos pela chave mestra de banco de dados ou por uma chave
assimétrica armazenada em um módulo EKM.

Planos de Manutenção.
Os planos de manutenção criam um fluxo de trabalho das tarefas
necessárias para garantir que o banco de dados seja otimizado,
armazenado regularmente em backup e livre de inconsistências. O
Assistente de Plano de Manutenção também cria os planos principais
de manutenção, mas criar planos de forma manual pode oferecer
uma flexibilidade bem maior.

Benefícios de planos de manutenção

No Mecanismo de Banco de Dados do SQL Server 2019, os planos de

manutenção criam um pacote Integration Services que é executado
por um trabalho do SQL Server Agent. Os planos de manutenção
podem ser executados de forma manual ou automática em intervalos
agendados.

Os planos de manutenção do SQL Server fornecem os seguintes

recursos:

 Criação de fluxo de trabalho usando uma série de tarefas

básicas de manutenção. É igualmente possível criar scripts
Transact-SQL próprios e personalizados.

 Hierarquias conceituais. Todos os planos permitem que se

criem e editem fluxos de trabalho de tarefas. As tarefas de
 todos os planos podem ser agrupadas em subplanos, que
podem ser agendados para execução em horas diferentes.

 O suporte para planos multisservidor pode ser usado em

ambientes de servidor mestre/servidor de destino.

 Suporte a histórico de plano de registro em servidores

remotos.

 Suporte à Autenticação do Windows e à Autenticação do

SQL Server. Quando possível, use a autenticação do
Windows.

Funcionalidade de plano de manutenção

É possível criar planos de manutenção para executar as seguintes

tarefas:

 Reorganizar os dados de páginas de índice e de dados

através da recompilação de índices com um novo fator de
preenchimento. A recompilação de índices com um novo
fator de preenchimento garante que essas páginas de
bancos de dados tenham espaços em branco e dados
distribuídos igualmente. Também possibilita o crescimento
mais rápido no futuro. Para obter mais informações,
veja Especificar fator de preenchimento para um índice.

 Compactar arquivos de dados removendo páginas vazias

do banco de dados.

 Atualizar estatísticas de índices para garantir que o

otimizador de consulta possua as informações atuais sobre
a distribuição de valores nas tabelas, o que permite que o
otimizador de consultas avalie melhor a forma de acessar
os dados, já que possui mais informações sobre os dados
armazenados no banco de dados. Embora as estatísticas
de índice sejam atualizadas periódica e automaticamente
através do SQL Server , esta opção pode forçar a
atualização imediata das estatísticas.

 Executar verificações internas de consistência de páginas

de dados e dados dentro do banco de dados para garantir
que um problema no sistema ou software não danificou os
dados.

 Fazer backup do banco de dados e dos arquivos de log de

transações. É possível reter backups de bancos de dados e
de logs por um período especificado, o que permite criar
um histórico dos backups a serem usados caso haja
 necessidade de restaurar o banco de dados em uma
determinada data anterior ao último backup executado.
Também é possível executar backups diferenciais.

 Executar tarefas do SQL Server Agent, o que pode ser

usado para criar trabalhos que efetuem várias ações e os
planos de manutenção que executem esses trabalhos.

Os resultados gerados pelas tarefas de manutenção podem ser

gravados como relatórios em arquivos de texto ou como tabelas de
plano de manutenção
(sysmaintplan_log e sysmaintplan_logdetail) em msdb. Para
exibir os resultados no visualizador de arquivos de log, clique com o
botão direito do mouse em Planos de Manutenção e clique
em Exibir Histórico.

• Replicação de dados
A replicação é um conjunto de tecnologias do SQL Server que permite
duplicar e distribuir todos ou parte dos seus dados e objetos em
diferentes bancos de dados.
O processo de replicação funciona em segundo plano com a ajuda de
jobs.
Esses jobs também são chamados de agentes.
Usam internamente os [Link] presentes na pasta
%SQL\%COM
 Figura 27 – replicação de dados

TIPOS DE REPLICAÇÃO
Transacional
Copia dados unidirecionalmente do banco de dados de origem para o
banco de dados de destino. Usa os arquivos de log do banco de dados
de origem para manter os dados em sincronia com o assinante.
Snapshot
Cria uma cópia idêntica de todos os objetos replicados de Publicações
que você deseja replicar toda vez que é executada.
Merge
Permite que dois ou mais bancos de dados sejam mantidos em
sincronia. Sempre que as alterações ocorrem em um banco de dados
(se configurado para isso), elas são automaticamente aplicadas aos
outros bancos de dados. Se as alterações ocorrerem no Publicador,
elas serão aplicadas ao Assinante e vice-versa.

ENTIDADES DE REPLICAÇÃO (ORIGEM)

Publisher (Publicador)
É um banco de dados de origem no qual a replicação é iniciada.
Disponibiliza dados para replicação.
Os editores definem o que publicam por meio de uma publicação.
Article(Artigo)
Os artigos são os objetos de banco de dados reais incluídos na
replicação, como tabelas, views, índices, etc.
Um artigo pode ser filtrado quando enviado ao assinante.
Publication (Publicação)
Um grupo de artigos é chamado de publicação.
Um artigo não pode ser distribuído individualmente. Portanto, a
publicação é necessária.
Distributor (Distribuidor)
É intermediário entre editor e assinante.
Ele recebe transações ou snapshots publicados e, em seguida,
armazena e encaminha essas publicações para o assinante.
¬

ENTIDADES DE REPLICAÇÃO (DESTINO)

Subscriber (Assinante)
É o banco de dados de destino onde a replicação termina.
Pode inscrever-se em várias publicações de vários publicadores.
Ele pode enviar dados de volta ao editor ou publicar dados para
outros assinantes.
¬
Subscription (Inscrição)
¬É uma solicitação de um assinante para receber uma publicação.
Nós temos dois tipos de assinaturas – push e pull.
Push subscription
¬Com essa assinatura, o publicador é responsável por atualizar todas
as alterações no assinante sem que o assinante faça essas
alterações.
As assinaturas push são criadas no servidor Publicador.
Pull Subscription
¬Com essa assinatura, o assinante inicia a replicação em vez do
editor.
As assinaturas são criadas no servidor Assinante.
COMPARAÇÃO ENTRE OS TIPOS DE REPLICAÇÃO

Figura 28 - Tipos de replicação

1) • Alta disponibilidade.

2) • Performance
Testes de stress.
1) Plano de execução.
1) O que é um plano de execução
Um plano de execução exibe de maneira gráfica e bem fofa os
métodos de recuperação de dados que o Otimizador de Consulta do
SQL Server escolheu. Além disso, mostra também o custo (em termos
de uso de recursos) da execução das instruções.
2 – Para que serve um plano de execução
Com esse recurso fofinho você consegue ver as etapas de execução
da sua query e assim verificar o que pode ser melhorado.
Uma outra utilidade é comparar duas queries. Imagine que você fez
uma melhoria no seu sistema e alterou uma consulta. É uma boa
prática verificar se o plano de consulta da nova query é melhor que o
plano de consulta da query anterior.
3- Quando usar um plano de execução
Conselho da Dani: use o plano de execução sempre que escrever uma
consulta! Assim você sempre entregará a melhor query (naquele
momento).
4- Tipos de plano de execução
Existem dois tipos de plano de execução:
• Plano de execução estimado: este é o plano produzido
pelo Otimizador de Consulta em tempo de compilação.
• Plano de execução real: é gerado depois que as consultas
são executadas. Por isso, além das etapas para a execução da
query, um plano de execução real contém também as
 informações de tempo de execução (métricas, avisos e tempo
de execução).
5- Como visualizar o plano de execução
Essa é a etapa mais fácil. Se você não quer executar a query, mas
quer ver o plano de execução, então você deve optar pelo plano
estimado. Neste caso, clique no botão selecionado na imagem 1.

Figura 29 - Plano de execução estimado

Se você quer verificar o plano de execução real, é simples também,

basta clicar no botão destacado na figura 2 e depois executar a
consulta.

Figura 30 - Plano de execução real.

3) Boas práticas para querys performáticas.
Índices.
Um índice é uma estrutura em disco associada a uma tabela ou view,
que agiliza a recuperação das linhas. Um índice contém chaves
criadas de uma ou mais colunas e essas chaves são armazenadas em
uma estrutura (árvore B) que habilita o SQL Server a localizar a linha
ou as linhas associadas aos valores de chave de forma rápida e
eficaz.
Com a criação do índice, o banco de dados irá criar uma estrutura de
árvore ordenada para facilitar as buscas, onde o primeiro nível é a
raiz, os níveis intermediários contém as árvores de índices e o último
nível contém os dados e uma lista duplamente encadeada ligando as
páginas de dados, contendo um ponteiro de página anterior e
próxima página, conforme imagem abaixo:

Figura 31 - Páginas de índices

Nem sempre o uso de índice trará um bom desempenho, pois a

escolha incorreta de um índice pode causar um desempenho
insatisfatório. Portanto, a tarefa do otimizador de consulta é
selecionar um índice ou uma combinação de índices apenas quando
isso gerar melhoria de desempenho e evitar a recuperação indexada
quando isso atrapalhar o desempenho.
A página de dado contém um cabeçalho com índice, com uma lista
duplamente encadeada contendo um ponteiro para a página anterior
e a próxima, um bloco com os registros (dados) e no rodapé existem
os array slots que contém os endereços de memória das páginas com
os dados.

Recomendações na criação de um índice

As seguintes tarefas compõem a estratégia recomendada para
criação de índices:
 Entenda as características das consultas mais usadas. Por
exemplo, saber que uma consulta usada frequentemente
associa duas ou mais tabelas o ajudará a determinar o melhor
tipo de índice a ser usado.
 Entenda as características das colunas usadas nas consultas.
Por exemplo, um índice é ideal para colunas que tenham um
tipo de dados de inteiro e, também, colunas exclusivas ou não
nulas. Para colunas que têm subconjuntos bem definido de
dados, é possível usar um índice filtrado no SQL Server 2008 e
versões posteriores.
 Determine quais opções de índice poderiam aumentar o
desempenho na criação ou manutenção do índice. Por exemplo,
a criação de um índice clusterizado em uma tabela grande
existente se beneficiaria da opção de índice ONLINE. A opção
ONLINE permite que atividade simultânea nos dados
subjacentes continue enquanto o índice está sendo criado ou
reconstruído.
 Determine o melhor local de armazenamento para o índice. Um
índice não clusterizado pode ser armazenado no mesmo grupo
de arquivos que a tabela subjacente ou em um grupo de
arquivos diferente. O local de armazenamento de índices pode
melhorar o desempenho de consulta aumentando desempenho
de I/O do disco. Por exemplo, o armazenamento de um índice
não clusterizado em um grupo de arquivos que está em um
disco diferente do grupo de arquivos de tabela pode melhorar o
desempenho porque vários discos podem ser lidos ao mesmo
tempo.
 Crie índices não clusterizados nas colunas frequentemente usadas em
predicados e condições de JOINS em consultas. No entanto, evite
adicionar colunas desnecessárias. Acrescentar muitas colunas de índice
vai aumentar o espaço em disco e o desempenho de manutenção de
índice.
 Cobrindo índices pode melhorar desempenho de consulta porque todos
os dados precisaram satisfazer os requisitos da consulta existe dentro
do próprio índice. Ou seja, apenas as páginas de índice, e não as
páginas de dados da tabela ou do índice clusterizado, são necessárias
para recuperar os dados solicitados, portanto reduzindo as operações de
E/S gerais do disco. Por exemplo, uma consulta de colunas a e b em
uma tabela que tem um índice composto criado em colunas a, b e c
pode recuperar os dados especificados somente do índice.

 Escreva consultas que insiram ou modifiquem o máximo de filas possível

em uma única instrução, em vez de usar consultas múltiplas para
atualizar essas mesmas filas. Ao usar apenas uma instrução, pode-se
explorar uma manutenção otimizada do índice.

 Avalie o tipo da consulta e como as colunas são usadas na consulta. Por

exemplo, uma coluna usada em uma consulta de correspondência exata
seria uma boa candidata para um índice clusterizado ou não
clusterizado.
 Mantenha o comprimento da chave de índice curto para os índices
clusterizados. Além disso, os índices clusterizados se beneficiam de
serem criados em colunas exclusivas ou não nulas.

 Examine a singularidade da coluna. Um índice exclusivo em vez de um

índice não exclusivo na mesma combinação de colunas, provê
informações adicional para o otimizador de consulta, o que torna o
índice mais útil.

 Examine a distribuição de dados na coluna. Frequentemente, uma

consulta longa é causada ao se indexar uma coluna com poucos valores
exclusivos, ou ao executar uma junção em tal coluna. Por exemplo, uma
lista telefônica física ordenada alfabeticamente pelo último nome não
será rápida em localizar uma pessoa, se todas as pessoas na cidade
tiverem nomes de Smith ou Jones.

 Considere a ordem das colunas se o índice contiver colunas múltiplas. A

coluna que é usada na cláusula WHERE em um critério de consulta
igual a (=), maior que (>), menor que (>) ou BETWEEN, ou que participa
em uma junção, deve ser posicionada primeiro. Colunas adicionais
devem ser ordenadas com base em seu nível de distinção, ou seja, do
mais distinto ao menos distinto.

 Por exemplo, se o índice for definido como LastName, FirstName o

índice será útil quando o critério de consulta for WHERE LastName =
‘Smith’ ou WHERE LastName = Smith AND FirstName LIKE ‘J%’.
Porém, o otimizador de consulta não usaria o índice para uma consulta
 que tivesse pesquisado apenas em FirstName (WHERE FirstName =
‘Jane’).

 Especifique o fator de preenchimento (Fill Factor) do índice. Quando um

índice é criado ou recriado, o valor de fator de preenchimento determina
a porcentagem de espaço em cada página de nível folha a ser
preenchida com dados, reservando o restante em cada página como
espaço livre para futuro [Link] exemplo, a especificação de
um valor de fator de preenchimento de 80 significa que 20 por cento de
cada página de nível folha ficará vazio, fornecendo espaço para a
expansão do índice à medida que dados forem adicionados à tabela
subjacente. Um valor de fator de preenchimento corretamente escolhido
pode reduzir divisões potenciais de páginas fornecendo espaço
suficiente para expansão do índice à medida que são adicionados dados
à tabela subjacente.

 Considere indexar as colunas computadas.

Especificação da Auditoria do Banco de Dados

Índice Clustered
O índice CLUSTERED é montado na própria tabela, criando a estrutura
ORDENADA de árvore para facilitar as buscas. Por este motivo, apenas 1
índice desse tipo pode ser criado por tabela e não se pode utilizar INCLUDE de
colunas neste tipo de índice. Cada folha do índice cluster possui todas as
informações do registro.
Recomendações para a coluna que irá compor o índice clustered:

 Campos numéricos (smallint, tinyint, int, bigint)

 Dados crescentes
 Valores únicos
 Valores que não sofrem updates
 Dados que são utilizados com frequência em buscas, joins, etc
 Geralmente será criado na Primary Key
Exemplo clássico: Dicionário (Você localiza a palavra e junto com ela já tem a
definição).

Índice Nonclustered
O índice NONCLUSTERED é uma estrutura ORDENADA à parte, que contém
apenas a coluna indexada (e as colunas do INCLUDE, caso exista) e uma
tabela pode ter N índices deste tipo. Se for necessário consultar alguma
informação que não está no índice NONCLUSTERED, a informação é
localizada utilizando o índice clustered da tabela (Key Lookup). Caso a tabela
não tenha índice CLUSTERED, será utilizado o IAM (Index Allocation Map)
para localizar a informação via RID (RowID – na operação RID Lookup).

Exemplo clássico: Índice de um livro (Você localiza no índice a página onde

está o capítulo e depois vai até a página para ver as informações).
Índice composto ou com colunas incluídas
Dúvida muito frequente na criação do índice, essas 2 formas de criação de
índice possuem um funcionamento bem diferente:
 Índice composto: É um índice que é formado por mais de uma coluna.
Nesse caso, a estrutura de árvore do índice irá ter as informações das
colunas que fazem parte do índice em todos os níveis e buscas
utilizando esse colunas serão filtradas mais rapidamente.

 Índice com coluna incluída: É um índice formado por uma ou mais

colunas e que incluí outras colunas. Nesse caso, a estrutura de árvore
do índice será montada apenas com as colunas que fazem parte do
índice, e apenas no último nível da árvore (folha), a informação da
coluna incluída estará disponível (ocupando menos espaço em disco
que o índice composto). Esse tipo de índice é recomendável para evitar
operadores de Key Lookup, incluindo as colunas que não fazem parte do
índice e que sempre são buscadas, fazendo com que seja utilizado esse
índice e também o índice clustered para retornar outras informações.
 Quando usar um índice com colunas incluídas
Quando existem colunas utilizadas no SELECT apenas para
exibição dos dados, mas que não são utilizadas para filtros, é
interessante adicionar essas colunas no INCLUDE, pois as
informações ficarão gravadas apenas na nível de folha do índice
(último índice), apenas para exibição (por causa isso, esse tipo
de índice geralmente é menor que o índice composto).
Caso essas colunas não estejam presentes no índice, será
necessário utilizar outro índice (sempre no índice clustered ou no
ROWID, caso a tabela não tenha clustered) ou ter que varrer toda
a tabela para localizar essas informações. Veja mais detalhes
nesse link
Exemplo de criação de índice com coluna incluída
1CREATE NONCLUSTERED INDEX SK01 ON [Link](CPF) INCLUDE(RG, Nome)

Quando usar o índice composto

Quando uma consulta recorrente utiliza mais de uma coluna na

cláusula WHERE, pode ser utilizado índice composto (com mais
de uma coluna). Nessa situação, o query plan utiliza o conjunto
de colunas para filtrar, de forma que os índices mais restritivos
(índices de igualdade) devem vir primeiros na definição, antes
dos índices menos restritivos (inigualdades). As outras colunas
que compõem o índice são copiadas para todas as folhas da
árvore do índice (consumindo mais espaço).
Exemplo de criação de índice composto
1CREATE NONCLUSTERED INDEX SK01 ON [Link](CPF, RG)

O que é e como evitar o Key Lookup e o RID Lookup

Quando é realizada uma consulta em uma tabela, o otimizador de
consultas do SQL Server irá determinar qual o melhor método de
acesso ao dados de acordo com as estatísticas coletada e escolher o
que tiver o menor custo.

Como o índice clustered é a própria tabela, gerando um grande

volume de dados, geralmente é utilizado o índice não clustered de
menor custo para a consulta. Isso pode gerar um problema, pois
muitas vezes a query está selecionando colunas onde nem todas
estão indexadas, fazendo com que seja utilizado um índice não
clustered para a busca das informações indexadas (Index Seek
NonClustered) e também seja utilizado o índice clustered para
retornar as informações restantes, onde o índice não cluster possui
um ponteiro para a posição exata da informação no índice cluster (ou
o ROWID, caso a tabela não tenha índice cluster).

Figura 32

Essa operação é chamada de Key Lookup, no caso de tabelas

com índice clustered ou RID Lookup (RID = Row ID) para
tabelas que não possuem índice clustered (chamadas tabelas
HEAP) e por gerar 2 operações de leituras para uma única
consulta, deve ser evitada sempre que possível.
Key Lookup

Figura 33

RID Lookup

Figura 34 - RID Lookup

Para evitar o KeyLookup basta utilizar a técnica de cobrir o índice

(Covering index), que consiste em adicionar ao índice
NonClustered (INCLUDE) as principais colunas que são utilizadas
nas consultas à tabela. Isso faz com que o otimizador de consulta
consiga obter todas as informações lendo apenas o índice
escolhido, sem precisar ler também o índice clustered.
Entretanto, deve-se tomar muita atenção na modelagem dos
índices. Não é recomendável adicionar todas as colunas da
tabela no índice não cluster, uma vez que ele ficará tão grande
que ele não será mais efetivo e o otimizador de consulta poderá
até mesmo decidir em não utilizá-lo e preferir o operador Index
Scan, que faz a leitura sequencial de todo o índice, prejudicando
a performance das consultas.
Para evitar o RID Lookup, basta criar o índice clustered na tabela
e prestar atenção aos eventos de Key Lookup que possam vir a
surgir.
Índices Únicos (Exclusivos)
Um índice exclusivo garante que a chave de índice não contém
nenhum valor duplicado, e então, cada linha na tabela é exclusiva
de algum modo.
Índices exclusivos de multicolunas garantem que cada
combinação de valores na chave de índice é exclusivo. Por
exemplo, se um índice exclusivo for criado em uma combinação
de colunas LastName, FirstName e MiddleName, duas linhas na
tabela não poderão ter a mesma combinação de valores que
essas colunas.
Você não poderá criar um índice exclusivo em uma única coluna
se ela tiver NULL em mais de uma [Link] mesma forma, você
não poderá criar um índice exclusivo em várias colunas se a
combinação de colunas tiver NULL em mais de uma linha, pois
isso é tratado como valores duplicados para fins de indexação.
Internamente, quando se cria uma restrição PRIMARY KEY, é
criado automaticamente um índice clusterizado exclusivo na
coluna. A grande diferença entre PRIMARY KEY e índice único, é
que uma tabela pode possui apenas 1 PRIMARY KEY, mas
vários índices únicos.
Exemplo de criação de índice único:
1CREATE UNIQUE NONCLUSTERED INDEX SK01 ON [Link](CPF)

Ao tentar inserir um CPF na tabela de clientes que já exista, será

exibido um erro na tela do SQL Server e a execução será
abortada:

Utilizando o parâmetro IGNORE_DUP_KEY = ON, pode-se

permitir que o banco apenas ignore o registro duplicado e
apresente apenas um aviso na tela:
1CREATE UNIQUE NONCLUSTERED INDEX SK01 ON [Link](CPF)

2WITH(IGNORE_DUP_KEY = ON)

Faça o Rebuild / Reorganize dos

índices quando necessário
O Mecanismo de Banco de Dados do SQL Server mantém os
índices automaticamente sempre que são realizadas operações
de entrada, atualização ou exclusão nos dados subjacentes. No
decorrer do tempo, essas modificações podem fazer com que as
informações do índice sejam dispersadas pelo banco de dados
(fragmentadas). A fragmentação ocorre quando os índices têm
páginas nas quais a ordem lógica, com base no valor de chave,
não corresponde à ordem física do arquivo de dados. Índices com
fragmentação pesada podem degradar o desempenho da
consulta e causar lentidão de resposta do aplicativo.

Identificando a fragmentação dos índices

A primeira etapa para optar pelo método de fragmentação a ser

usado é analisar o índice para determinar o grau de
fragmentação. Usando a função de sistema
sys.dm_db_index_physical_stats, você pode detectar a
fragmentação em um índice específico, em todos os índices de
uma tabela ou exibição indexada, em todos os índices de um
banco de dados ou em todos os índices de todos os bancos de
dados. Para índices particionados,
sys.dm_db_index_physical_stats também fornece informações de
fragmentação por partição.
1 SELECT

2 OBJECT_NAME(B.object_id) AS TableName,

3 [Link] AS IndexName,

4 A.index_type_desc AS IndexType,

5 A.avg_fragmentation_in_percent

6 FROM

7 sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') A

8 INNER JOIN [Link] B WITH(NOLOCK) ON B.object_id = A.object_id

AND B.index_id = A.index_id
9
WHERE
1
0 A.avg_fragmentation_in_percent > 30

1 AND OBJECT_NAME(B.object_id) NOT LIKE '[_]%'

1
AND A.index_type_desc != 'HEAP'
1
2 ORDER BY

1 A.avg_fragmentation_in_percent DESC
3

1
4

Uma vez identificado o nível de fragmentação do índice, pode-se

escolher qual o método que será utilizado para desfragmentá-lo:
– REORGANIZE: Utilizado quando o nível de fragmentação está
entre 5% e 30%. Esse método não causa indisponibilidade do
índice, pois o índice não chega a ser apagado, apenas
reorganizado.
– REBUILD: Utilizado quando o nível de fragmentação é superior
a 30%. Esse método por padrão causa indisponibilidade do
índice, pois apaga e recria o índice novamente. Para que não
seja gerada indisponibilidade, pode-se utilizar o parâmetro
ONLINE na execução do REBUILD.

Desfragmentando o índice

Desfragmentando um índice fragmentado

(REORGANIZE)
1ALTER INDEX SK01 ON [Link]
2REORGANIZE

Desfragmentando todos os índices da tabela

(REORGANIZE)
1ALTER INDEX ALL ON [Link]

2REORGANIZE

Desfragmentando todos os índices da tabela

(REBUILD)
1ALTER INDEX ALL ON [Link]

2REBUILD

Desfragmentando todos os índices da tabela

(REBUILD – COMPLETO)
1ALTER INDEX ALL ON [Link]

2REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE =

ON, ONLINE = ON)

Para saber mais sobre Rebuild e Reorganize, acesse este link.

DMV’s e views do catálogo de índices

Segue a lista de DMV’s e views de catálogo de índices que
podem ser utilizadas para obter informações de uso e estatísticas
dos índices:
DMV’s
 sys.dm_db_column_store_row_group_physical_stats
 sys.dm_db_missing_index_groups
 sys.dm_db_index_operational_stats
 sys.dm_db_index_physical_stats
 sys.dm_db_index_usage_stats
 sys.dm_db_missing_index_columns
 sys.dm_db_missing_index_details
 sys.dm_db_missing_index_group_stats
Views de catálogo
 sys.index_columns
 [Link]
 [Link]
 [Link]
 sys.xml_indexes
Verificar a utilização dos índices

Com a query abaixo, é possível identificar a utilização dos índices

no banco, exibindo leituras com Index Seek (índice bem
utilizado), Index Scan (Possível problema na modelagem do
índice), Lookups e Updates (número de vezes que o índice foi
atualizado com novos registros)
1 SELECT

2 ObjectName = OBJECT_SCHEMA_NAME(idx.object_id) + '.' +

OBJECT_NAME(idx.object_id),
3
IndexName = [Link],
4
IndexType = CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + idx.type_desc,
5
User_Seeks = us.user_seeks,
6
User_Scans = us.user_scans,
7
User_Lookups = us.user_lookups,
8
User_Updates = us.user_updates
9
FROM
1
0 [Link] idx

1 LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND

1 idx.index_id = us.index_id AND us.database_id = DB_ID()

1 WHERE
2
OBJECT_SCHEMA_NAME(idx.object_id) != 'sys'
1
3 ORDER BY

1 us.user_seeks + us.user_scans + us.user_lookups DESC

4

1
5
Ajudando a identificar o melhor candidato a índice
clustered

Com a query abaixo, é possível deixar o SQL nos ajudar a definir qual
o melhor índice candidato a ser o clustered da tabela. Analisando a
DMV dm_db_index_usage_stats, a query identifica o índice non-
clustered que tenha mais leituras Seek que o clustered (na query,
defini um percentual de 150%) e a quantidade de Seeks seja menor
que os Lookups do índice clustered.

Não é recomendável utilizar apenas o resultado dessa query para

essa definição. Ela deve servir para indicar uma possível
melhoria na troca dos índices e o DBA deve fazer a análise
detalhada para confirmar essa indicação.
1 SELECT

2 TableName = OBJECT_NAME(idx.object_id),

3 NonUsefulClusteredIndex = [Link],

4 ShouldBeClustered = [Link],

5 Clustered_User_Seeks = c.user_seeks,

6 NonClustered_User_Seeks = nc.user_seeks,

7 Clustered_User_Lookups = c.user_lookups,

8 DatabaseName = DB_NAME(c.database_id)

9 FROM

1 [Link] idx
0
LEFT JOIN sys.dm_db_index_usage_stats c ON idx.object_id = c.object_id AND
1 idx.index_id = c.index_id
1
JOIN (
1
2 SELECT

1 idx.object_id,
3
nonclusteredname = [Link],
1
ius.user_seeks
4
FROM
1
5 [Link] idx
1 JOIN sys.dm_db_index_usage_stats ius ON idx.object_id = ius.object_id AND
6 idx.index_id = ius.index_id

1 WHERE
7
idx.type_desc = 'nonclustered' AND ius.user_seeks = (
1
8 SELECT

1
9 MAX(user_seeks)

2 FROM
0
sys.dm_db_index_usage_stats
2
1 WHERE

2 object_id = ius.object_id AND type_desc =

2 'nonclustered'

2 )
3
GROUP BY
2
idx.object_id,
4
[Link],
2
5 ius.user_seeks
2 ) nc ON nc.object_id = idx.object_id
6
WHERE
2
7 idx.type_desc IN ( 'clustered', 'heap' )

2 AND nc.user_seeks > ( c.user_seeks * 1.50 ) -- 150%

8
AND nc.user_seeks >= ( c.user_lookups * 0.75 ) -- 75%
2
ORDER BY
9
nc.user_seeks DESC
3
0

3
1

3
2

3
3

3
4

3
5

3
6

3
7

3
8

3
9
Identificando índices ausentes (missing index)

Uma das tarefas do dia a dia de um DBA, é identificar índices

ausentes no banco de dados, que podem sugerir um ganho de
performance de consultas que são frequentemente executadas.
Com a query abaixo, podemos tornar essa tarefa um pouco mais
fácil, pois consultando as DMV’s de missing index, podemos
identificar esses dados rapidamente.
1 SELECT

2 [Link],

3 migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 ) * ( migs.user_seeks +

migs.user_scans ) AS improvement_measure,
4
OBJECT_NAME(mid.object_id),
5
'CREATE INDEX [missing_index_' + CONVERT (VARCHAR, mig.index_group_handle) + '_'
6 + CONVERT (VARCHAR, mid.index_handle) + '_' + LEFT(PARSENAME([Link], 1), 32)
+ ']' + ' ON ' + [Link] + ' (' + ISNULL(mid.equality_columns, '') + CASE WHEN
7
mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE
8 '' END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' +
mid.included_columns + ')', '') AS create_index_statement,
9
migs.*,
1
0 mid.database_id,

1 mid.[object_id]
1
FROM
1
sys.dm_db_missing_index_groups mig
2
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle =
1
mig.index_group_handle
3
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle =
1
mid.index_handle
4
WHERE
1
5 migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 ) * ( migs.user_seeks +
migs.user_scans ) > 10
1
6 ORDER BY

migs.avg_total_user_cost * migs.avg_user_impact * ( migs.user_seeks + migs.user_scans

) DESC
Analisando o histograma dos índices
Um ponto muito importante a se considerar após o índice ser
criado, é analisar o seu histograma. Utilizando esse recurso, é
possível identificar o quão granulado são os dados da tabela e o
quão seletivo é o nosso índice, de modo que quanto mais
seletivo, melhor ele será utilizado.
Um exemplo disso, é criar um índice na Coluna Sexo em uma
tabela de clientes e a distribuição dos dados ficar em 50% para
cada um dos valores. Nessa situação, o índice não está sendo
muito seletivo e o banco terá que fazer muitas leituras para
retornar as informações. Para observar a quantidade de registros
para cada valor, observe a coluna EQ_ROWS.
Para visualizar o histograma do índice, pode-se utilizar a
procedure DBCC:
1-- DBCC SHOW_STATISTICS(Nome_da_Tabela, Nome_do_Indice)

2DBCC SHOW_STATISTICS(Logins, SK01)

No exemplo abaixo, podemos observar um caso de índice bem

seletivo, que apresenta uma densidade de 0,4% e possui várias
informações distintas. Quando esse índice for utilizado, irá
retornar um volume de dados bem pequeno.

Já no exemplo abaixo, podemos identificar um índice pouco

seletivo, com densidade de 50% (apenas 2 valores distintos),
sendo que um desses valores (‘DBA’) representa 98% (232
registros de um total de 236) dos dados coletados. Ou seja, caso
esse índice seja utilizado e a consulta seja realizada procurando
pela palavra ‘DBA’, o índice precisaria retornar 98% dos dados do
índice e o otimizador do SQL iria preferir realizar uma operação
de Index Scan ao invés do Index Seek.
5) Estatísticas.
6) Particionamentos.

O particionamento horizontal de tabelas é uma técnica que utilizamos na

arquitetura da estrutura de dados, para melhorar a
performance/desempenho/gerenciamento do RDBMS. Esta técnica consiste em
dividir os dados, baseado em um parametro, em tabelas e File Groups
separados.

Para exemplificar, veja na imagem abaixo os dados que estavam na tabela

Visitas foram divididos em 12 tabelas, cada uma armazenando os dados de um
mês específico. A tabela azul armazena visitas de Janeiro, a
tabela verde armazena de Fevereiro e a tabela laranja armazena visitas de
Dezembro. Porém, a tabela preta representa a consulta de todas as visitas do
ano inteiro…

Figura 35 - Particionamento de tabelas

7) Monitoramento dos bancos de dados (recursos
do SQL Server e SSMS).

Activity Monitor (Monitor de Atividade).

8) → Extended Events (Eventos estendidos).
9) → Data Collection (Coleta de dados).

10) Query Store (Repositório de Consultas).

Query Store é uma funcionalidade que surgiu no SQL Server 2016,
que nos ajuda na análise de problemas de performance e está
disponível desde a versão Express até a versão Enterprise. Ou seja,
você vai poder usar de qualquer forma quando migrar para o SQL
Server 2016 ou superior.
O Query Store armazena um histórico de execução de queries e
planos de execução, incluindo estatísticas de consumo das queries
após sua execução. Além de armazenar, ele também disponibiliza
gráficos e DMVs para que possamos utilizar essas informações
valiosas.
• Ele nos ajuda a identificar quando uma procedure que
normalmente é executada de forma rápida, de uma hora para a
outra, passa a rodar lenta. Com o Query Store, conseguimos ver
os dois planos de execução dessa procedure (rápido e o lento),
comparar, identificar o motivo e até forçar de forma muito
simples o uso do plano que é o mais rápido
• Quando migramos o SQL Server de versão, com as novas
regras de otimização do Query Optimizer dos níveis de
compatibilidades mais recentes, a maioria das queries tendem
a rodar igual ou mais rápidas. Contudo, uma query ou outra
pode rodar de forma mais lenta (isso acontece de mais no meu
dia a dia). Com o Query Store conseguiremos identificar esse
problema de forma muito mais simples nas migrações para o
SQL Server 2016
• Temos essas informações mesmo após um boot,
diferentemente das DMVs, que são reiniciadas após um Restart
do SQL Server. O cliente reinicia o SQL Server e depois chama o
consultor para descobrir o que era. Agora nós, consultores,
teremos mais informações com o Query Store para analisar um
problema
11) DMV (Views de gerenciamento dinâmico).
Views de gerenciamento dinâmico (também chamado de DMV) são o
conjunto de informações que retratam o comportamento do ambiente
do banco de dados, mostrando ao DBA informações sobre diversas
situações como índices ausentes, espaço armazenado pelos objetos,
consultas que estão consumindo mais recursos, entre outras
informações.
As exibições e funções de gerenciamento dinâmico retornam
informações do estado do servidor que podem ser usadas para
monitorar a saúde da instância do servidor, diagnosticar problemas e
ajustar o desempenho.
Importante
Elas retornam dados de estado internos específicos de
implementação. Os esquemas e os dados retornados podem mudar
em versões futuras do SQL Server. Por isso, as exibições e funções de
gerenciamento dinâmico, em versões futuras, podem não ser
compatíveis com as exibições e funções de gerenciamento dinâmico
nessa versão. Por exemplo, em versões futuras do SQL Server, a
Microsoft poderá aumentar a definição de qualquer exibição do
gerenciamento dinâmico adicionando colunas ao final da lista de
colunas. Não é recomendável o uso da sintaxe SELECT * FROM
dynamic_management_view_name no código de produção, pois o
número de colunas retornado pode mudar e quebrar seu aplicativo.
•