Bancos de Dados

1. Bancos de Dados SQL Server 2012 Databases

2. Criandobanco de dados Create database AlunoDB GO Select * from sys.databases where name = ‘AlunoDB’ GO sp_helpdbAlunoDB GO

3. Criandobanco de dados É necessáriodefinir: • O nome do database • O tamanho do database • Osarquivos em que o banco de dados seráarmazenado (no mínimo um de dados e um de log) CREATE DATABASE Equipamentos ON PRIMARY ( NAME=EquipamentosData, FILENAME='c:\Program Files\..\..\Data\Equipamentos_Data.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=EquipamentosLog, FILENAME= 'c:\Program Files\..\..\Data\Equipamentos_Log.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) COLLATE SQL_Latin1_General_Cp1_CI_AS

4. Criandobanco de dados • Criaumaduplicata do bancomodel • Armazenadefinição do novo database no bancomaster • Veja databases existentesatravés da view de sistemasys.databases SELECT * FROM sys.databases

5. Criandobanco de dados (sintaxe) CREATE DATABASE database_name [ON { [PRIMARY] (NAME = logical_file_name, FILENAME = 'os_file_name' [, SIZE = size] [, MAXSIZE = {max_sizeUNLIMITED}] [, FILEGROWTH = growth_increment] ) } [,...n] ] [LOG ON { (NAME = logical_file_name, FILENAME = 'os_file_name' [, SIZE = size] [, MAXSIZE = {max_sizeUNLIMITED}] [, FILEGROWTH = growth_increment] ) } [,...n] ] [COLLATE collation_name]

6. Criandobanco de dados (sintaxe) • PRIMARY: • Especifica os arquivos do filegroup primário, que contém todas as tabelas de sistema e todos os objetos de banco não associados à outros filegroups. • Todo banco de dados contém um arquivo de dados primário, que é o ponto de partida do database e que aponta para todos os outros arquivos do database. • A extensão recomendada é .MDF • Se você não especificar a palavra-chave PRIMARY, o primeiro arquivo listado no comando se tornará o arquivo primário.

7. Criandobanco de dados (sintaxe) • FILENAME: • Esse parâmetro especifica o nome e rota do arquivo no sistema operacional. • Deve ser um folder local ao servidor onde o SQL está instalado. • SIZE: • Especifica o tamanho do arquivo de dados ou de log em (MB) ou KB (default) • Mínimo de 512KB ou o tamanhomínimo do arquivo de dados primário do bancomodel • Valor padrão (default) é de 1MB

8. Criandobanco de dados (sintaxe) • MAXSIZE: • Especifica o tamanho máximo que o arquivo pode alcançar em MB ou KB. • Se não especificado, o arquivo poderá crescer até o disco ficar completamente cheio. • FILEGROWTH: • Especifica o incremento de crescimento. • O crescimento máximo não irá execederMaxsize. • Pode ser especificado em MB (default), KB ou %. • Se não especificado, será se 10% ou 64KB no mínimo.

9. Criandobanco de dados (sintaxe) • COLLATION: • Especifica a colação (collation) padrão do banco • A collation de um banco compreende regras de classificação, conjunto de caracteres suportados e sensibilidade a acentos. • Ex: • SQL_Latin1_General_Cp1_CI_AS (USA) • SQL_Latin1_General_Cp1_CI_AI (Brasil)

10. Criando Bancos de Dados Exercício (linha de comando): • Criar um banco de dados de nome TesteDB, com um arquivo primário de 10 MB, tamanho máximo de 20 MB e crescimento de 10% e um arquivo de log transacional de 5 MB, tamanho máximo de 10 MB e crescimento de 1MB. • Arquivo primário: C:\SQLDATA\TesteDB_Data.mdf • Arquivo de log: C:\SQLDATA\TesteDB_Log.ldf

11. Log transacional funcionamento

12. Log transacional funcionamento 1. Uma solicitação de alteração de dados é enviada pela aplicação. 2. Quando a alteração é executada, as páginas afetadas são carregadas do disco para o cache (buffer) se as mesmas já não estiverem em cache devido a uma consulta ou alteração anterior. 3. Cada alteração nos dados é gravada no log de transação ao mesmo tempo em que ocorrem (em tempo real). As alterações são sempre gravadas no log em disco antes que as mesmas alterações sejam feitas no banco de dados. Esse técnica é chamada de “write-ahead log”. 4. De tempos em tempos, um processo denominado checkpoint escreve todas as transações completadas no disco. Se ocorre uma falha no sistema um processo de recuperação automatizado usa o log transacional para refazer (rollforward) as transações que completaram (committed) e desfazer (rollback) as transações incompletas.

13. Database Options configuração • Configuráveis por: • SQL Server Management Studio • Linha de comando • Alter Database • Execsp_dboption Ex: Alter database TesteDB SET READ_ONLY

14. Database Options configuração • Modelos de recuperação do banco de dados: ALTER DATABASE <DBNAME> SET RECOVERY • FULL • BULK_LOGGED • SIMPLE • FULL proporciona total recuperabilidade em caso de falhas; • BULK_LOGGED geramenos log, mas é maisvulnerável. • SIMPLE recupera o bancosomenteaté o último backup full oudiferencial.

15. Database Options configuração • Controle do estado do banco de dados: ALTER DATABASE <DBNAME> SET • READ_ONLY • READ_WRITE ALTER DATABASE <DBNAME> SET • SINGLE_USER (somente um usuário) • RESTRICTED_USER (somente sysadmin ou db_owner) • MULTI_USER (normal )

16. Database Options – Exercício 1 1. No banco master, mudar TesteDB para single_user Use master Alter database TesteDB set single_user 2. Abrir nova query e selecionar TesteDB Use TesteDB 3. Abrir mais uma query e selecionar TesteDB Use TesteDB 4. No banco master, mudar TesteDB para multi_user Use master Alter database TesteDB set multi_userwithrollbackimmediate

17. Database Options – Exercício 2 1. Criar tabela de nome “Cliente” em TesteDB Use TesteDB; Createtable Cliente (cod_clienteintnotnull, nome_clientevarchar (30) notnull); 2. Inserir linha na tabela Cliente Insertinto Cliente values (1,’João Silva’); Select * from Cliente; 3. No banco master, mudar TesteDB para read_only Use master; Alter database TesteDB set read_onlywithrollbackimmediate; 2. Tentar inserir nova linha na tabela Cliente Use TesteDB; Insertinto Cliente values(2,’Maria Santos’); 3. No banco master, mudar TesteDB para read_write Use master; Alter database TesteDB set read_writewithrollbackimmediate;

18. Obtendoinformaçõessobre databases • sp_helpdb • sp_helpdbdatabase_name • sp_spaceused[objname] • sp_helpfile Ex: sp_helpdb sp_helpdbTesteDB Use TesteDB exec sp_spaceused@updateusage=true Use TesteDB exec sp_helpfile

19. Obtendoinformaçõespelas views de sistema • Select * from sys.databases • Select * from sys.database_files • Select * from sys.allocation_units

20. Gerenciandoarquivos de dados e log Sintaxe: ALTER DATABASE database { ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name] | ADD LOG FILE < filespec > [ ,...n ] | REMOVE FILE logical_file_name[ WITH DELETE ] | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE < filespec > | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name {filegroup_property| NAME = new_filegroup_name} | SET < optionspec > [ ,...n ] [ WITH < termination > ] | COLLATE < collation_name> }

21. Gerenciandoarquivos (cont.) Usandocrescimentoautomático de arquivos • Boas práticas: • Aloque um tamanhoinicialsuficiente para evitaroperações de expansãofrequentes. • Determine um tamanhomáximo para osarquivos. • Configure osincrementosgrandes o suficientepara evitaroperações de expansãofrequentes. Porexemplo, se o log cresce 40 MB pordia, configure o autogrow para 50 MB e não 1MB!

22. Gerenciandoarquivos (cont.) • Adicionando arquivos secundários de dados ALTER DATABASE TesteDB ADD FILE (NAME = TesteDB_Data2, FILENAME='c:\Program Files\..\..\ Data\TesteDB_Data2.ndf', SIZE=15MB, MAXSIZE=20MB) GO

23. Gerenciandoarquivos (cont.) • Expandindo arquivos manualmente ALTER DATABASE TesteDB MODIFY FILE ( NAME = ‘TesteLog', SIZE = 30MB) GO

24. Gerenciandoarquivos (cont.) Exercícios: • Adicionar 2 arquivos de dados aobancoTesteDB • Expandir o arquivo de log de TesteDB para 30MB • Mudar o autoincremento do arquivo de log de TesteDB para 10 % e o tamanhomáximo para 100MB.

25. Deletando um Database DROP DATABASE Ex: Drop database TesteDB Não é possívelexcluir um database se: • O mesmoestá em uso • É um database de sistema • Está no meio de um Restore • Deletatodososarquivos de dados (operaçãoirreversível)

26. ManutençãoBásica • DBCC Shrinkdatabase: compacta o Banco Inteiro EX: DBCC Shrinkdatabase (TesteDB, 15) • DBCC Shrinkfile: compacta um arquivo do Banco Use TesteDB DBCC Shrinkfile (TesteDB_Data, 5) • Auto_shrink : compacta o Banco automaticamente ALTER database TesteDB SET auto_shrink ON

27. ManutençãoBásica Exercícios: • Compactararquivo de dados do banco AdventureWorks2012 para 100 MB. Verificar o tamanho final do mesmo. • Compactar o banco AdventureWorks2012 deixando 15% de espaçolivre. Verificar o tamanho final de todososarquivos (dados e log).

28. ManutençãoBásica Exercícios: • Gerarregistros de log com o script abaixo: Use AdventureWorks2012 dbccdbreindex ('Person.Person') dbccdbreindex ('Sales.SalesOrderDetail') • Verificartamanho do arquivo de log • Compactararquivo de log do banco AdventureWorks2012 para 1 MB. • Verificaro tamanho final do mesmo. O queocorreu?

29. ManutençãoBásica Exercícios: • Mudar o modelo de recuperação do banco AdventureWorks2012 para FULL • Fazer backup de dados e log do banco AdventureWorks2012 backup database AdventureWorks2012 to disk = 'C:\SQLBACKUP\AdventureWorks2012_20131203.BAK' with init backup log AdventureWorks2012 to disk = 'C:\SQLBACKUP\AdventureWorks2012_201312030800.TRN' with init • Compactarnovamente o arquivode log do banco AdventureWorks2012 para 1 MB. • Verificar o tamanho final do mesmo. O queocorreu?

30. ArmazenamentoInterno

31. ArmazenamentoInterno • O SQL Server armazena, lê e escreve dados em blocos de 8KB, chamadospáginas. • Linhasde umatabela, de um modogeral, nãopodemsermaioresqueumapágina, ou 8060 (8192 Bytes – overhead). • Todas as páginassãoarmazenadas em extents. Um extent sãooitopáginascontíguas, ou 64KB. • Páginaspodemconter dados de tabelas, índicesoucontroles de alocação.

32. Links Úteis • Create Database sintaxe: http://technet.microsoft.com/pt-br/library/ms176061(v=sql.120).aspx • Alter Database sintaxe: http://technet.microsoft.com/pt-br/library/ms174269(v=sql.120).aspx • DBCC shrinkdatabase: http://technet.microsoft.com/pt-br/library/ms190488.aspx