SQL 2016 - Módulo III

680 Pages·2017·12.9 MB·Portuguese

SQL 2016 - Módulo III
SQL 2016 - Módulo III
COD.: 1809_1

SQL 2016 - Módulo III
Créditos Sumário
Copyright © Monte Everest Participações e Empreendimentos Ltda.
Todos os direitos autorais reservados. Este manual não pode ser copiado, fotocopiado, reproduzido,
traduzido ou convertido em qualquer forma eletrônica, ou legível por qualquer meio, em parte ou
no todo, sem a aprovação prévia, por escrito, da Monte Everest Participações e Empreendimentos
Ltda., estando o contrafator sujeito a responder por crime de Violação de Direito Autoral, conforme
o art.184 do Código Penal Brasileiro, além de responder por Perdas e Danos. Todos os logotipos
e marcas utilizados neste material pertencem às suas respectivas empresas.
"As marcas registradas e os nomes comerciais citados nesta obra, mesmo que não sejam assim identificados, pertencem
aos seus respectivos proprietários nos termos das leis, convenções e diretrizes nacionais e internacionais."
SQL 2016 - Módulo III
Coordenação Geral
Marcia M. Rosa
Coordenação Editorial
Henrique Thomaz Bruscagin
Autoria
Daniel Paulo Tamarosi Salvador
Revisão Ortográfica e Gramatical
Marcos Cesar dos Santos Silva
Diagramação
Carla Cristina de Souza
Edição nº 1 | 1809_1
Setembro/ 2016
Este material constitui uma nova obra e é uma derivação da seguinte obra original, produzida por TechnoEdition
Editora Ltda., em Nov/2014: SQL 2014 – Módulo III
Autoria: Daniel Paulo Tamarosi Salvador
4
Sumário
Capítulo 1 - Instalando o SQL Server .................................................................................11
1.1. Conceitos de banco de dados ............................................................12
1.2. Planejando a instalação ......................................................................12
1.2.1. Edições ..............................................................................................13
1.2.2. Licenciamento ...................................................................................15
1.2.3. Recursos ............................................................................................16
1.2.4. Requisitos mínimos para instalação ...................................................17
1.2.5. Antes de iniciar a instalação ..............................................................17
1.3. Instalação do SQL Server ....................................................................18
Pontos principais ................................................................................................50
Teste seus conhecimentos......................................................................................................51
Mãos à obra! ..................................................................................................................................53
Capítulo 2 - Instância e banco de dados ..........................................................................69
2.1. Instância ............................................................................................70
2.1.1. Visualizando e alterando a instância ..................................................70
2.2. Página de dados e extensão ...............................................................79
2.3. Arquivos e grupos de arquivos ...........................................................86
2.3.1. Arquivo de dados primários ...............................................................86
2.3.2. Arquivo de dados secundários ...........................................................86
2.3.3. Arquivos de log .................................................................................86
2.3.4. Recomendações para a divisão de arquivos ........................................86
2.3.5. Grupo de arquivos (filegroup) ............................................................88
2.3.5.1. Tipos de grupos de arquivos ..............................................................91
2.4. Banco de dados .................................................................................91
2.4.1. Configurações ...................................................................................92
2.4.2. Banco de dados de sistema ................................................................95
2.4.3. Banco de dados SNAPSHOT ................................................................98
2.4.4. Banco de dados de usuário ................................................................101
2.4.5. Obtendo informações sobre o banco .................................................109
2.4.6. Alterando um banco de dados ...........................................................111
2.4.7. Exemplos ...........................................................................................113
Pontos principais ................................................................................................122
Teste seus conhecimentos......................................................................................................123
Mãos à obra! ..................................................................................................................................125
Capítulo 3 - Gerenciando tabelas ........................................................................................141
3.1. Criando tabelas .................................................................................142
3.1.1. Constraints ........................................................................................142
3.1.2. Tipos de dados SQL Server (DATATYPES) ............................................144
3.1.3. Tabelas regulares ..............................................................................146
3.1.4. Tabelas temporárias locais ................................................................155
3.1.5. Tabelas temporárias globais ..............................................................156
3.1.6. Tabelas baseadas em consultas .........................................................156
3.1.7. Tabelas particionadas ........................................................................156
3.1.8. Tabelas com compressão ...................................................................161
3.1.9. Tabelas baseadas em arquivos (FILETABLE) ........................................163
3.1.10. Tabelas em memória (In-Memory) ......................................................171
3.2. Objetos para acesso a dados .............................................................173
3.2.1. View ..................................................................................................173
3.2.2. Funções .............................................................................................177
3.2.3. Procedures ........................................................................................180
5
SQL 2016 - Módulo III Sumário
3.2.3.1. Parâmetros ........................................................................................181
3.3. Sequências ........................................................................................187
3.4. Sinônimos ..........................................................................................189
Pontos principais ...............................................................................................190
Teste seus conhecimentos......................................................................................................191
Mãos à obra! ..................................................................................................................................193
Capítulo 4 - Bloqueios e índices ..........................................................................................197
4.1. Multiprogramação e concorrência ......................................................198
4.1.1. Bloqueios ...........................................................................................198
4.1.1.1. Shared (S) .........................................................................................199
4.1.1.2. Exclusive (X) ......................................................................................200
4.1.1.3. Update (U) .........................................................................................200
4.1.1.4. Intent (I).............................................................................................201
4.1.1.5. Schema (Sch) .....................................................................................201
4.1.1.6. Bulk Update (BU) ................................................................................201
4.1.1.7. Key-range ..........................................................................................202
4.1.2. Granularidade ....................................................................................202
4.1.3. Problemas evitados pelos bloqueios ..................................................203
4.1.4. Compatibilidade entre bloqueios .......................................................204
4.1.5. Customizando bloqueios ...................................................................204
4.1.6. Customizando bloqueios na seção .....................................................205
4.1.7. Lock dinâmico ...................................................................................207
4.1.8. Monitorando os bloqueios .................................................................207
4.1.8.1. Activity Monitor .................................................................................208
4.1.8.2. sys.dm_tran_locks .............................................................................209
4.1.8.3. SQL Server Profiler .............................................................................209
4.1.8.4. Reliability and Performance Monitor ...................................................210
4.1.9. Deadlocks ..........................................................................................210
4.1.10. Timeout .............................................................................................211
4.2. Índices ...............................................................................................212
4.2.1. Estruturas de índices do SQL Server ...................................................212
4.2.2. Índice Clustered .................................................................................213
4.2.3. Índice NonClustered ..........................................................................215
4.2.4. Índice Unique ....................................................................................216
4.2.5. Índice composto ................................................................................217
4.2.6. Índices comprimidos ..........................................................................218
4.2.7. Índices particionados .........................................................................218
4.2.8. INCLUDE ............................................................................................219
4.2.9. Pilhas .................................................................................................219
4.3. Determinando a criação de um índice ................................................220
4.3.1. Criando índices através do comando ..................................................221
4.3.2. Criando índices graficamente .............................................................226
4.4. Manutenção de índices ......................................................................228
4.4.1. Obtendo informações sobre os índices ..............................................228
4.4.2. Obtendo informações sobre estatísticas .............................................229
4.5. O otimizador e o plano de execução ..................................................235
4.5.1. Exemplo de saída de um plano de execução .....................................237
4.5.2. Saídas do plano de execução .............................................................238
4.5.3. Operadores lógicos e físicos ..............................................................239
4.5.4. Sobrepondo o otimizador ..................................................................244
4.6. Índices Full-Text ................................................................................244
6
Sumário
4.6.1. Full Population ...................................................................................245
4.6.2. Change Tracking Based Population ....................................................245
4.6.3. Incremental Timestamp-Based Population ..........................................246
4.6.4. Criando um catálogo FULL-TEXT ........................................................247
4.6.5. Criando um índice FULL-TEXT ............................................................248
4.6.6. Pesquisando em colunas FULL-TEXT ...................................................254
Pontos principais ................................................................................................257
Teste seus conhecimentos......................................................................................................259
Mãos à obra! ..................................................................................................................................261
Capítulo 5 - Gerenciando a recuperação de dados .....................................................273
5.1. Planejando o Backup/Restore .............................................................274
5.1.1. Roles para execução de backup .........................................................275
5.1.2. Mídia para armazenar backups ..........................................................275
5.1.3. Devices de backup .............................................................................276
5.1.4. Backup set, media set, media family, initial media, continuation media .....277
5.1.5. Usando múltiplos backup devices ......................................................278
5.1.6. Atividades que não podem ser executadas durante o processo de backup ..278
5.1.7. Modo de recuperação ........................................................................279
5.1.8. Modelo de recuperação completo (FULL) ............................................279
5.1.9. Modelo de recuperação BULKED-LOGGED ...........................................280
5.1.10. Modelo de recuperação simples (SIMPLE) ...........................................281
5.1.11. Modalidades de backups ....................................................................281
5.1.12. Backup físico frio ...............................................................................282
5.1.13. Backup físico quente ..........................................................................283
5.2. Realizando backups ...........................................................................283
5.2.1. Backup completo ...............................................................................284
5.2.2. Backup de log ....................................................................................289
5.2.3. Backup parcial de arquivo e de grupo de arquivo ...............................289
5.2.4. Backup utilizando ambiente gráfico ...................................................290
5.3. Restauração de um backup ................................................................291
5.3.1. Restauração completa de banco de dados ..........................................292
5.3.2. Restauração de grupo de arquivos ou de arquivos de banco de dados ...296
5.3.3. Restauração de log de banco de dados ..............................................297
5.3.4. Restauração de página de banco de dados .........................................297
5.3.5. Restauração de bancos de dados de sistema (MSDB) ..........................298
5.3.6. Restauração utilizando ambiente gráfico ............................................299
5.4. Anexando e desanexando um banco de dados ...................................302
Pontos principais ...............................................................................................306
Teste seus conhecimentos......................................................................................................307
Mãos à obra! ..................................................................................................................................309
Capítulo 6 - Transferência e manipulação de dados ..................................................329
6.1. Exportando e importando dados ........................................................330
6.2. Ferramentas de integração de dados..................................................340
6.2.1. Bulk Copy Program (BCP) ...................................................................341
6.2.2. BULK INSERT ......................................................................................344
6.2.3. LINKED SERVER ..................................................................................348
6.2.4. SSIS - SQL Server Integration Services .................................................349
Pontos principais ................................................................................................352
Teste seus conhecimentos......................................................................................................353
Mãos à obra! ..................................................................................................................................355
7
SQL 2016 - Módulo III Sumário
Capítulo 7 - Segurança de dados ........................................................................................365
7.1. Login e usuário ..................................................................................366
7.2. Principals e securables .......................................................................367
7.2.1. Server Securables – Endpoints ............................................................368
7.2.2. Server Securables – LOGINS ................................................................372
7.2.3. Criando LOGINS .................................................................................374
7.2.4. Criando usuários ...............................................................................375
7.2.5. LOGINS DEFAULT ...............................................................................377
7.3. Gerenciando acesso à instância..........................................................377
7.4. Gerenciando acesso aos bancos de dados ..........................................378
7.5. Grupos de permissões criados pelo usuário .......................................380
7.6. Grupos de permissões criados para aplicações ..................................383
7.7. Permissionamento .............................................................................385
7.7.1. GRANT ...............................................................................................386
7.7.2. DENY .................................................................................................387
7.7.3. REVOKE .............................................................................................387
7.8. Schema ..............................................................................................388
7.9. Credenciais ........................................................................................390
7.10. Row-Level Security .............................................................................392
7.11. Dynamic Data Masking ......................................................................395
Pontos principais ................................................................................................398
Teste seus conhecimentos......................................................................................................399
Mãos à obra! ..................................................................................................................................401
Capítulo 8 - Automação de tarefas, alertas e operadores ........................................413
8.1. Automação de tarefas ........................................................................414
8.1.1. Conta do SQL Server Agent ................................................................415
8.2. Configurando o envio de e-mails ........................................................416
8.3. Jobs ...................................................................................................427
8.4. Operadores ........................................................................................433
8.5. Alertas ...............................................................................................436
8.6. Centralizando múltiplos agentes ........................................................444
8.7. Cópia de tarefas ................................................................................448
8.8. Solução de problemas (Troubleshooting) ...........................................450
8.9. Catálogo do SSIS (SQL Server Integration Services) ............................453
Pontos principais ................................................................................................455
Teste seus conhecimentos......................................................................................................457
Mãos à obra! ..................................................................................................................................459
Capítulo 9 - Replicação e distribuição de dados ..........................................................487
9.1. Introdução .........................................................................................488
9.2. Transação distribuída ........................................................................488
9.3. Replicação .........................................................................................489
9.3.1. Síncrona unidirecional .......................................................................489
9.3.2. Síncrona bidirecional .........................................................................490
9.3.3. Assíncrona unidirecional ....................................................................490
9.3.4. Assíncrona bidirecional ......................................................................491
9.4. Escolhendo a estratégia para deposição de dados ..............................492
9.5. Replicação de dados no SQL Server ....................................................493
9.5.1. Metáfora da replicação .......................................................................493
9.5.2. Publicações e artigos .........................................................................494
9.5.2.1. Filtrando dados..................................................................................494
8
Sumário
9.6. Tipos de assinaturas ..........................................................................495
9.7. Agentes de replicação ........................................................................495
9.8. Tipos de publicação ...........................................................................496
9.8.1. Snapshot Publication .........................................................................497
9.8.2. Transactional Publication ...................................................................498
9.8.3. Merge Publication ..............................................................................499
9.8.4. Resolução de conflitos .......................................................................499
9.9. Cenário de replicação ........................................................................500
9.9.1. Cenário de replicação cliente/servidor ...............................................500
9.9.2. Cenário de replicação entre servidores ...............................................500
9.10. Restrições de replicação ....................................................................501
Pontos principais ................................................................................................502
Teste seus conhecimentos......................................................................................................503
Mãos à obra! ..................................................................................................................................505
Capítulo 10 - Gerenciando um banco de dados ............................................................526
10.1. Introdução .........................................................................................526
10.2. Auditoria ...........................................................................................526
10.2.1. Auditoria de objetos ..........................................................................526
10.2.1.1. Gatilhos .............................................................................................527
10.2.1.2. AUDIT DATABASE ...............................................................................531
10.2.2. Auditoria de segurança ......................................................................531
10.2.3. Auditoria do SQL Server .....................................................................532
10.3. Checklist de atividades de um DBA ....................................................534
10.3.1. Atividades diárias ..............................................................................535
10.3.2. Atividades semanais ..........................................................................545
10.3.3. Atividades mensais ............................................................................545
10.4. Revisão da conectividade do ambiente ...............................................546
10.5. Monitoração do ambiente ..................................................................548
Pontos principais ................................................................................................572
Teste seus conhecimentos......................................................................................................573
Mãos à obra! ..................................................................................................................................575
Capítulo 11 - Monitorando e ajustando a performance do SQL Server ..............581
11.1. Introdução .........................................................................................582
11.2. Considerações para uma boa performance.........................................582
11.3. Fatores que afetam o tempo de resposta ...........................................582
11.3.1. O que fazer para diminuir o tempo de resposta .................................583
11.4. Planejando o ajuste de performance ..................................................584
11.4.1. Situação atual do sistema e objetivos a serem alcançados ..................584
11.5. Ajustando a performance de uma aplicação .......................................585
11.6. Ferramentas de monitoramento .........................................................586
11.6.1. Windows System Monitor ...................................................................587
11.6.1.1. Contadores mais relevantes ...............................................................587
11.6.1.2. Pontos de atenção .............................................................................591
11.6.2. SQL Profiler ........................................................................................591
11.6.2.1. Broker ...............................................................................................594
11.6.2.2. Cursor ...............................................................................................595
11.6.2.3. CLR ....................................................................................................596
11.6.2.4. Database ...........................................................................................596
11.6.2.5. Deprecation .......................................................................................596
11.6.2.6. Errors and Warnings ..........................................................................597
9
SQL 2016 - Módulo III Sumário
11.6.2.7. Full Text ............................................................................................598
11.6.2.8. Locks .................................................................................................598
11.6.2.9. Objects ..............................................................................................599
11.6.2.10. OLE DB ..............................................................................................599
11.6.2.11. Performance ......................................................................................600
11.6.2.12. Security Audit ....................................................................................601
11.6.2.13. Stored Procedures ..............................................................................604
11.6.2.14. Transactions ......................................................................................605
11.6.2.15. TSQL ..................................................................................................606
11.6.3. Transact SQL .....................................................................................607
11.6.3.1. Procedimentos (Stored Procedures) ....................................................607
11.6.3.2. DBCC .................................................................................................607
11.6.3.3. DMV – Dynamic Management Views ...................................................608
11.6.3.4. Activity Monitor .................................................................................609
11.6.3.5. Relatórios ..........................................................................................610
11.6.4. SQL Server Distributed Replay ...........................................................612
Pontos principais ...............................................................................................613
Teste seus conhecimentos......................................................................................................615
Mãos à obra! ..................................................................................................................................617
Capítulo 12 - Alta disponibilidade ......................................................................................637
12.1. Introdução .........................................................................................638
12.2. Log Shipping .....................................................................................638
12.2.1. Configurando o Log Shipping ............................................................640
12.3. Database Mirroring ............................................................................654
12.4. Always ON – SQL 2016 .......................................................................660
12.4.1. Arquitetura Windows Server Failover Cluster e Always ON SQL Server 2016 ....661
Pontos principais ................................................................................................663
Teste seus conhecimentos......................................................................................................665
Mãos à obra! ..................................................................................................................................667
10