Apostila sql server 2005, Notas de estudo de Informática

Baixe Apostila sql server 2005 e outras Notas de estudo em PDF para Informática, somente na Docsity!

Apostila:

Administração do Microsoft SQL Server 2005

Tutorial

por

Leonardo Grandinetti Chaves

dio (Utilizando a interface do SQL Server Management Studio)

dio (Utilizando a interface do SQL Server Management Studio)

Fig. 89: Configurando a fonte de dados destino no Import and Export Wizard -Oracle.

.................................................................................................................................... 53

 - Administração do Banco de Dados SQL Server 

• 1. Visão Geral do SQL Server INDICE DE FIGURAS
• Developer) 2. Planejando a Instalação do SQL Server (versões: Workgroup, Standard, Enterprise,
  • Fig. 1: Tela de inicio da instalação.
  • Fig. 2: Checagem dos Requisitos de instalação............................................................
  • Fig. 3: Definição da instância do banco de dados.
  • Fig. 4: Definição de contas de acordo com os serviços.
  • Fig. 5: Definição do modo de autenticação.
  • Fig. 6: Definição da classificação
  • Fig. 7: Definição sobre informações de erros...............................................................
  • Fig. 8: Início da instalação dos serviços e componentes.
• 3. Gerenciando Arquivos de Dados..................................................................................
  • Fig. 9: SQL Server Management Studio – Administração do SQL Server
  • Fig. 10: Estabelecimento da conexão ao banco de dados ou serviço.
  • Fig. 11: Hierarquia de Objetos
  • Fig. 12: Opção View do SQL Server Management Studio.
  • dados........................................................................................................................... Fig. 13: Utilização do Object Explorer para visualização das informações do banco de
  • Fig. 14: Propriedades do banco de dados utilizando o Transact SQL........................
  • Fig. 15: Visualização dos objetos de um banco de dados.
  • Fig. 16: Criando um Banco de Dados.........................................................................
  • Fig. 17: Definindo as regras de classificação, a recuperação e a compatibilidade.....
  • Fig. 18: Definindo os grupos de arquivos.
  • Fig. 19: Compactando e reduzindo os arquivos do banco de dados...........................
  • Fig. 20: Renomeando um banco de dados..................................................................
  • Fig. 21: Desanexando um banco de dados.
  • Fig. 22: Anexando um banco de dados.......................................................................
  • Fig. 23: Alterando o estado do banco de dados para off-line.
  • Fig. 24: Definido a origem dos dados a serem copiados............................................
  • Fig. 25: Copiando para o mesmo servidor ou entre servidores distintos....................
  • Fig. 26: Selecionando o método de cópia de bancos de dados...................................
  • Fig. 27. : Selecionando o banco de dados origem.
  • Fig. 28: Definindo o banco de dados destino, os arquivos de dados e o log.
  • Fig. 29: Criação do pacote para a cópia do banco de dados.......................................
• 4. Gerenciando a Segurança
  • Fig. 30: Verificando o tipo de autenticação................................................................
  • Fig. 31: Verificando os usuários no servidor de banco de dados.
  • Fig. 32: Verificando o status de um login.
  • Fig. 33: Atribuindo papéis a um login........................................................................
  • Fig. 34: Atribuindo múltiplos logins a papéis.
  • Fig. 35: Concedendo acesso ao login do usuário e atribuindo papéis.
  • Fig. 36: Atribuindo papéis a múltiplos logins.
  • Fig. 37: Criando papéis para bancos de dados.
  • Fig. 38: Criando papéis de aplicação..........................................................................
  • Fig. 39: Verificando as permissões no servidor.
    • Administração do Banco de Dados SQL Server
  • Fig. 40: Concedendo permissões aos usuários e papéis. dio (Utilizando a interface do SQL Server Management Studio)
  • Fig. 41: Concedendo permissões de objetos a um usuário.
  • Fig. 42: Concedendo permissões aos objetos.
  • Fig. 43: Concedendo permissões de objeto a múltiplos logins.
• 5. Executando Tarefas Administrativas..........................................................................
  • Fig. 44: Configurando o Database Mail.
  • Fig. 45: Utilizando o assistente para a configuração do Database Mail.....................
  • Fig. 46: Configurando uma conta de email do Database Mail.
  • Fig. 47: Visualizando alertas, tarefas e operadores no SQL Agent............................
  • Fig. 48: Utilizando o SQL Server Configuration Manager.
  • Fig. 49: Configurando um novo alerta.
  • Fig. 50: Configurando respostas aos alertas.
  • Fig. 51: Adicionando e configurando operadores no SQL Agent.
  • Fig. 52: Incluindo novos Jobs.....................................................................................
  • Fig. 53: Configurando os passos do Job.....................................................................
  • Fig. 54: Definindo o sequenciamento de passos de um trabalho................................
  • Fig. 55: Definindo o agendamento do trabalho.
  • Fig. 56: Configurando notificações para o trabalho.
  • Fig. 57: Utilizando o assistente de manutenção de bancos de dados.
  • Fig. 58:: Definindo as tarefas do Plano de Manutenção.............................................
  • Fig. 59: Definido a ordem das tarefas no Plano de Manutenção................................
  • Fig. 60: Fornecendo informações adicionais para a tarefa do Plano de Manutenção.
  • Fig. 61: Agendando o Plano de Manutenção do Banco de Dados.
  • Fig. 62: Configurando o relatório de execução do Plano de Manutenção..................
  • Fig. 63: Resumo do Plano de Manutenção do Banco de Dados.................................
  • Fig. 64: Definindo o servidor primário com o uso do Log Shipping.
  • Fig. 65: Definindo o servidor secundário com o uso do Log Shipping......................
• 6. Fazendo Backup de Bancos de Dados........................................................................
  • Fig. 66: Modo de Recuperação do banco de dados.
  • Fig. 67: Criando um novo dispositivo de backup.......................................................
  • Fig. 68: Configurando o dispositivo de backup..........................................................
  • Fig. 69: Iniciando a configuração do backup do banco de dados...............................
  • Fig. 70: Definindo o tipo de backup e o banco de dados............................................
  • Fig. 71: Definindo o conjunto de backup.
  • Fig. 72: Configurando as opções de backup...............................................................
• 7. Restaurando Bancos de dados
  • Fig. 73: Restaurando um Banco de Dados.
  • Fig. 74: Configurando a restauração do banco de dados............................................
  • Fig. 75: Configurando as opções de restauração de bancos de dados.
• 8. Monitorando o Desempenho do SQL Server
  • Fig. 76: Verificando o log de eventos do SQL Server no sistema operacional..........
  • Fig. 77: Verificando o Log do SQL Server.
  • Fig. 78: Visualizando o Log do SQL Agent...............................................................
  • Fig. 79: Configurando o número de arquivos de Log do SQL Server.
  • Fig. 80: Configurando o tamanho do Log no SQL Agent..........................................
    • Administração do Banco de Dados SQL Server
  • Monitor Fig. 81: Monitorando a performance do SQL Server utilizando o Performance
  • Fig. 82: Verificando os processos utilizando o Active Monitor
  • Fig. 83: Acessando o Profiler pelo SQL Server Management Studio
  • Fig. 84: Utilizando o Profiler para monitorar uma tabela.
  • Fig. 85: Utilizando classes de eventos para monitorar o desempenho.
  • Fig. 86: Janela do Profiler...........................................................................................
• 9. Transferindo Dados
  • Fig. 87: Iniciando o Assistente para a importação e exportação dos dados
  • Fig. 88: Configurando a origem dos dados no Import and Export Data Wizard.
  • Excel. Fig. 90: Configurando a fonte de dados destino no Import and Export Wizard –
  • Fig. 91: Opções de configuração do Export and Import Data.
  • Fig. 92: Selecionando as tabelas e visões para a transferência de dados....................
  • Fig. 93: Definindo o mapeamento dos dados no Export and Import Data.
  • Fig. 94: Salvando e executando o pacote do Export And Import Data.
  • Fig. 95: Salvando o pacote e autenticando no servidor.
• 10 Mantendo a Alta Disponibilidade............................................................................
  • Fig. 96: Configurando o espelhamento em conjunto com o Log Shipping................
• 11. Apresentando a Duplicação.
  • Fig. 97: Configurando Linked Servers.
  • Fig. 98: Configurando o provedor do Linked Server.
  • Fig. 99: Opções de Configuração do Linked Server.
  • Fig. 100: Configurações de segurança para Linked Servers.
  • Fig. 101: Configurando o Distribuidor.......................................................................
  • Fig. 102: Configurando o SQL Agent em conjunto com o Distribuidor....................
  • Fig. 103: Selecionado o Banco de Dados para publicação.........................................
  • Fig. 104: Definindo o tipo de publicação.
  • Fig. 105: Definindo os artigos para a publicação.
  • Fig. 106: Incluindo filtros para os artigos a serem publicados...................................
  • Fig. 107: Tornando a publicação disponível e fazendo o agendamento.....................
  • Fig. 108: Configurando a conta para o agente............................................................
  • Fig. 109: Definindo a conta para o agente e a conta a ser mapeada...........................
  • Fig. 110: Informando parâmetros adicionais para o final da criação da publicação.
  • Fig. 111: Informando o nome da publicação e conferindo os parâmetros.
  • Fig. 112: Criando a publicação e verificando o status................................................
  • Fig. 113: Iniciando a configuração de uma inscrição.................................................
  • Fig. 114: Selecionando o Editor e a publicação.

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

1. Visão Geral do SQL Server

Componentes do SQL Server:

• SQL Server Database Services:

o Banco de Dados;

o Replicação;

o Pesquisa de Dados Textual.

• Analysis Services:

o Online Analytical Processing (OLAP) – aplicações de BI;

• Data Integration Services:

o Transformação de Dados;

o Integracao de dados.

• Notification Services:

o Mecanismo de notificação para o envio de mensagens sincronizadas para

usuários e dispositivos.

• Reporting Services:

o Gerenciamento e distribuição de relatórios.

• Service Broker:

o Enfileiramento de tarefas e troca de mensagens.

Requisitos de Hardware:

• Memória: mínimo de 512 MB de RAM para a versão Standard, 1GB para a Enterprise Edition e 1GB para as versões de 64 bits.
• CPU: Versões de 32 bits funcionam em hardware Intel x86 ou compatível. Versões de 64 bits funcionam na família X64 da AMD e da Intel.
• SMP: Suporta multiprocessadores simétricos podendo executar consultas paralelas.
• Unidades de disco: Executa com RAID (0,1,5).

Edições do SQL Server:

• Workgroup: empresas de pequeno porte ou soluções departamentais;
• Standard: organizações de médio porte;
• Enterprise: organizações de grande porte;
• Developer: licenciada para uso em desenvolvimento;
• Express: é livre e suporta bancos de dados de até 4GB;
• Mobile: dispositivos móveis.

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

• Na próxima tela, digite o nome da empresa, a chave do produto e clique em

Next ;

• Selecione os componentes do SQL Server que devem ser instalados e clique em

Next ;

• Deve-se determinar o tipo de instância na instalação (Fig. 3). Para a instalação

da instância padrão, selecione Default , e então clique em Next. Caso contrário,

deve-se clicar em Named Instance , digitar o nome da instância e clicar em Next.

Fig. 3: Definição da instância do banco de dados.

• Na página Service Account ( Fig. 4), deve-se determinar as opções para cada

serviço:

• Se for selecionada a opção Customize For Each Service account , deve-se

configurar cada conta de serviço individualmente;

• Se for selecionada a opção Use the built-in System Account pretende-se

utilizar uma conta do servidor;

• Se for selecionada a opção Use a domain user account pretende-se

utilizar uma conta de usuário de domínio específica.

Fig. 4: Definição de contas de acordo com os serviços.

• Na página de autenticação ( Authentication Mode ), deve-se selecionar a

opção Microsoft Windows ou Mixed Mode (Fig. 5). A opção Microsoft

Windows permite apenas as contas Domain Users para a autenticação no

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

banco de dados. A opção Mixed Mode permite a autenticação de contas do

domínio ( Domain users ) ou usuários do próprio SQL Server.

Fig. 5: Definição do modo de autenticação.

• Na página Collation Settings (Fig. 6), deve-se definir a classificação para o

servidor (localidade padrão - Latin1_General). As opções Binary e case-

sensitive correspondem a ordens de classificação mais rápidas. Selecione

Customize for each service account para configurar as opções intercaladas

para o SQL server e o Analysis Services.

Fig. 6: Definição da classificação_._

• Na página Error and Usage Reporting Settings , deve-se informar se os erros

fatais e dados de utilização de recursos serão automaticamente informados

(Fig. 7).

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

3. Gerenciando Arquivos de Dados

Os arquivos de dados utilizados pelo SQL Server são os seguintes:

• Arquivos de Dados Primários: cada banco de dados possui o seu arquivo

(.mdf);

• Arquivos de Dados Secundários: dados adicionais para o banco de dados

(.ndf);

• Arquivos de log de transação: possuem as informações necessárias para

recuperação do banco de dados (.ldf).

Após a instalação, os seguintes bancos de dados e os respectivos arquivos de log serão

criados:

• master: controla os bancos de dados e a operação do SQL Server. Armazena as

informações de usuários, variáveis de ambiente e mensagens de erro (master.mdf

masterlog.log);

• model: fornece um modelo para novos bancos de dados (modeldev.mdf,

modellog.log);

• tempdb: Armazenamento de tabelas temporárias e outras necessidades

(tempdbdev.mdf, tempdblog.ldf);

• msdb: Área de armazenamento para as informações de agendamento e histórico

dos serviços (msdbdata.mdf, msdblog.ldf).

obs. : Para a instalação dos bancos de dados exemplo (inclui os arquivos com extensão

mdf e ldf) deve-se selecionar a opção Samples. Estes bancos de dados podem ser

instalados a posteriori bastando clicar em Microsoft SQL Server 2005 localizado no

item Adicionar e Remover Programas do Windows 2000 (Painel de Controle).

O SQL Server grava as informações em um arquivo de log de transações antes que

sejam gravadas no banco de dados para manter a consistência do banco de dados. Este

log também auxilia na recuperação do banco de dados. Se o processo de gravação do

banco de dados falhar, a recuperação automática gerenciada pelo próprio SGBD utiliza

as informações armazenadas no arquivo de log para reverter transações incompletas.

A Administração do Banco de Dados incluindo a visualização dos arquivos de dados

(mdf e log) é realizada através do SQL Server Management Studio (Fig. 9).

Fig. 9: SQL Server Management Studio – Administração do SQL Server

Ao clicar no SQL Server Management Studio, deverá ser informado o banco de dados ou serviço, o nome do servidor e o tipo de autenticação definidos durante a instalação (Fig. 10).

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

Fig. 10: Estabelecimento da conexão ao banco de dados ou serviço.

No SQL Server Management Studio, os objetos estão organizados de acordo com uma

hierarquia (Fig. 11), começando pelo nível dos grupos de servidores. Deve-se utilizar a visão Registered Servers para selecionar um tipo de servidor, como o banco de dados ( Database Engine ).

Fig. 11: Hierarquia de Objetos_._

Através da opção View do menu do SQL Server Management Studio , pode-se também selecionar os objetos, os servidores registrados e o sumário (Fig. 12). Através do Object Explorer , pode-se selecionar um banco de dados e verificar as suas propriedades clicando com o botão direito e selecionando Properties ( Fig. 13 ).

Fig. 12: Opção View do SQL Server Management Studio.

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

Fig. 15: Visualização dos objetos de um banco de dados.

Esta lista (Fig. 15) pode incluir os seguintes tipos de nós:

• Tables: tabelas de sistemas (correio do banco de dados, planos de manutenção, replicação, log shipping, backup, restauração) e de usuário;
• Views: visões de sistema e de usuário;
• Synonyms: contém nomes alternativos para objetos;
• Programmability: tipos e subtipos que podem ser programados incluindo procedimentos armazenados, gatilhos, funções, tipos de dados, regras, padrões e assemblies;
• Service Broker: objetos do service broker;
• Storage: objetos relacionados ao armazenamento;
• Security: objetos relacionados a segurança.

Para a criação do banco de dados pode-se utilizar o SQL Server Management Studio

clicando com o botão direito do mouse sobre Databases ( Object Explorer ) e

selecionando New Database. Será exibida uma Caixa de Diálogo em que devem ser informadas as propriedades do banco de dados a ser criado.

Fig. 16: Criando um Banco de Dados.

Na página General , pode-se definir o proprietário do banco de dados ( owner ), os nomes dos arquivos do banco de dados e log (l ogical name ), a indexação de texto ( full-text indexing ), os grupos de arquivos de dados permitindo dividir o banco de dados entre múltiplos discos ( filegroup, Fig. 18), o tamanho inicial do banco de dados ( initial size ), o tipo de crescimento do banco de dados ( autogrowth ) e o limite para o seu tamanho (Fig. 16). Na página Options, deve-se definir em Collations as regras para a classificação do dicionário e página de código (Fig. 17).

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

Fig. 17: Definindo as regras de classificação, a recuperação e a compatibilidade.

Na opção Recovery Model , deve-se definir a forma de recuperação do banco de dados (utilize a opção FULL para o registro de todas as transações no arquivo de log) e na opção Compability level pode-se estabelecer o nível de compatibilidade com outras versões do SQL Server (Fig. 17). Adicionalmente, existem recursos adicionais para gerenciamento automático do banco de dados, definição dos cursores e de compatibilidade. As opções automáticas de gerenciamento são as seguintes^1 :

• autoclose: o banco de dados é fechado após o encerramento da conexão do último usuário de torna-se disponível após a conexão do primeiro usuário;
• auto create statistics: criação de estatísticas à partir de cláusulas where ;
• auto shrink : o tamanho dos arquivos do banco de dados e log são reduzidos e compactados automaticamente;
• auto update statistics: as estatísticas são atualizadas automaticamente se os dados das tabelas sofrerem alterações;
• auto update statistics async: não ocorre uma atualização das estatísticas antes de uma compilação.

Fig. 18: Definindo os grupos de arquivos.

A opção de compactação e redução do banco de dados poder ser realizada manualmente ou agendada à partir da opção Shrink -> Database (Fig. 19).

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio) A tarefa de anexar um banco de dados deve ser realizada com todos os arquivos de dados disponíveis (primários, secundários e log se existirem) e permite especificar o banco de dados a ser criado ( Fig. 22 ). Adicionalmente, permite-se utilizar o Transact- SQL para a anexação de arquivos com o comando CREATE DATABASE ( for attach ) incluindo a reconstrução do arquivo de log ( attach_rebuilt_log ).

Fig. 22: Anexando um banco de dados.

O SQL Server permite mudar o estado do banco de dados que pode ser on-line, off- line ou estado de emergência. A mudança de estado é extremamente útil para a realização de tarefas de mudança dos arquivos do banco de dados. No estado on-line, o banco de dados permanece acessível aos usuários e disponível para a sua utilização. No estado off-line, pode-se movê-lo ou desmontá-lo. No estado de emergência, os registros de log são desativados e apenas os usuários do grupo sysadmin podem utilizá-lo para tarefas de manutenção do banco de dados. Para alterar o estado do banco de dados para off-line, clique com o botão direito do mouse no banco de dados, selecione tasks e clique em Take offline (Fig. 23).

Fig. 23: Alterando o estado do banco de dados para off-line.

As tarefas de cópia de um banco de dados são realizadas com a ajuda de um assistente no SQL Server Management Studio. Clique com o botão direito do mouse sobre o banco de dados, selecione Tasks e Copy Database. Uma tela de apresentação do assistente será exibida. Clique em Next e selecione o servidor de origem dos dados (Fig. 24). Adicionalmente, informe a forma de autenticação e caso

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio) ela seja realizada pelo SQL Server, digite as informações de usuário e senha ( user name e password ) e clique em Next.

Fig. 24: Definido a origem dos dados a serem copiados.

Permite-se copiar bancos de dados entre servidores distintos. A página permite selecionar qual é o servidor e a forma de autenticação (Fig. 25).

Fig. 25: Copiando para o mesmo servidor ou entre servidores distintos.

Os métodos de cópia (Fig. 26) incluem a seleção de anexação dos arquivos e incluir os arquivos se ocorrer alguma falha. A opção Use the SQL Management Object Method permite selecionar o banco de dados.

Fig. 26: Selecionando o método de cópia de bancos de dados.

O assistente também verifica o status dos bancos de dados do servidor origem e servidor destino (Fig. 27). Adicionalmente, permite-se fazer a cópia de um banco de

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

Fig. 29: Criação do pacote para a cópia do banco de dados.

Para mover arquivos de dados utilizando o TRANSACT-SQL pode-se utilizar o comando ALTER DATABASE. Consulte a documentação on-line para verificar a sintaxe do comando.

Administração do Banco de Dados SQL Server 2005

dio (Utilizando a interface do SQL Server Management Studio)

4. Gerenciando a Segurança

Para uma autenticação do tipo Windows ( Windows A utentication ), o usuário repassa as credenciais para o SQL Server que verifica na tabela de sistema se a conta existe, não sendo necessária uma nova autenticação. Para uma autenticação no servidor de banco de dados, o SQL Server checa na tabela do sistema se o usuário existe e a senha corresponde a senha gravada anteriormente. A vantagem na utilização da autenticação no servidor é permitir que usuários que não pertencem a grupos do Windows possam estabelecer conexões no banco de dados, como usuários de Internet, por exemplo. Para verificar o tipo de autenticação do SQL Server, clique com o botão direito do mouse no servidor, selecione Properties e depois a página Security (Fig. 30).

Fig. 30: Verificando o tipo de autenticação.

Para visualizar os logins existentes no SQL Server, expanda a pasta Security do servidor. Para verificar as propriedades e permissões de acesso, clique com o botão direito em um login de usuário e selecione a opção Properties.

Fig. 31: Verificando os usuários no servidor de banco de dados.

As opções do usuário na guia General (Fig. 31) são as seguintes:

• Enforce password policy: impõe a diretiva de senha do Windows;
• Enforce password expiration: impõe a expiração da senha;
• Certificate name: permite a utilização de credenciais;
• Default database name: especifica o banco de dados padrão;