Microsoft SQL Server Instruções para administração do MS SQL Server e dos bancos de dados instalados Sumário Criação de Rotinas de Segurança (backup) Backup de banco de dados Backup de log de transações Criação de rotinas de segurança (backup) Cada banco de dados instalado no SQL Server pode trabalhar em três estados ou modos de recuperação (recovery models) de dados por motivo de danos: sem log (registro) de alterações nos dados (modo Simple), com log parcial para dados em caracteres (modo Bulk Logged) ou com log completo para todos os tipos de dados (modo Full). Criação de rotinas de segurança (backup) Os backups dos bancos de dados são feitos de forma independente dos backups dos seus logs (apenas bancos que estejam configurados com modo de recuperação que o gere). Backup de banco de dados Para o agendamento do backup do banco do Informa poderão ser executados 2 scripts: um de criação do dispositivo lógico (device) de backup e outro da tarefa (job), que ficarão respectivamente nas pastas Management/Backup e Management/SQL Server Agent/Jobs, e posteriormente executados no SQL Query Analizer*. Backup de banco de dados * Para executar o SQL Query Analizer, clique em Tools no menu superior do SQL Enterprise Manager e selecione a opção com o seu nome. Os scripts de comandos SQL (arquivos com extensão .sql) serão sempre executados através desse aplicativo. Para abri-los, clicar em File no menu superior e escolher a opção Open. Backup de banco de dados Necessário que o SQL Server Agent esteja ativado (seta verde sobre o ícone). Para mantê-lo sempre neste estado, clicar com o botão direito do mouse sobre o nome do servidor localizado na árvore de console e escolher Propriedades e, em seguida, na aba General, marcar o check box ‘Autostart SQL Server Agent’ para que este seja iniciado automaticamente quando o SQL Server também for. Backup de banco de dados A Modo Novo disponibilizará para download modelos dos dois scripts de backup. Antes de executar os comandos contidos nos scripts, é necessário analisá-los para efetuar possíveis alterações, como a colocação do nome correto do banco de dados em todas as suas citações, o caminho (path) de criação dos dispositivos de gravação dos backups (pasta padrão ‘D:\MSSQL\BACKUP\’, linha 18) e o horário de agendamento da execução (hora padrão ‘22:30:00’, linha 77). Backup do log de transações Caso um banco de dados instalado no SQL Server opcionalmente esteja em modo de recuperação (recovery model) com geração do log de transações (Bulk Logged ou Full), o arquivo físico de gravação irá crescer indefinidamente até o limite máximo do disco rígido onde está instalado ou travará o banco, exigindo backup imediato, caso atinja o limite definido na propriedade ‘Maximun File Size’ do seu arquivo de log. Backup do log de transações Porém, com a criação de uma rotina de backups freqüentes do log juntamente com backups diários completos do banco, o arquivo físico de log será naturalmente limitado, ao longo do tempo, pelo maior tamanho atingido em algum dia de trabalho, já que após cada backup diário do banco, o SQL Server limpará internamente o arquivo e reutilizará o espaço (mas nunca diminuirá automaticamente o tamanho de um arquivo, exigindo comando explícito para tal). Backup do log de transações Não precisamos, portanto, nos preocupar em limitar o tamanho do arquivo de log caso as rotinas de backup estejam funcionando perfeitamente. Backup do log de transações Temos que considerar que o intuito da geração de logs é, única e exclusivamente, recuperar o estado mais recente de um banco de dados desde seu último backup completo. Desta forma, as cópias de segurança dos logs de transação devem, por definição, ser mais freqüentes do que as do banco de dados. Backup do log de transações Dependendo do tipo de aplicação, ou seja, da quantidade de alterações nos dados num determinado espaço de tempo e do tamanho total do banco, caso fosse programado um backup total do banco de dados em períodos curtos (p.ex. a cada hora), não seria necessário trabalhar com geração de log de transações, tampouco fazer cópia de segurança dele. Backup do log de transações Pelas características da utilização do Informa no SESC e considerando que são feitos backups diários completos dos bancos de dados, sugerimos: que os backups dos logs sejam feitos a cada hora cheia, a partir do início do expediente normal até a última hora cheia antes da execução da cópia do banco (que automaticamente também apagará e recriará o dispositivo lógico de backup do log para que este não cresça indefinidamente – será acrescentada uma segunda etapa (step) na tarefa (job) de execução do backup). Backup do log de transações O SQL Server possui uma pasta padrão para gravação dos backups dos bancos de dados, pasta essa que deverá estar contida na configuração de pastas origem na gravação diária do backup do servidor em meio externo (fita DAT, Zip Drive, CD-RW, etc.). Backup do log de transações Porém, esta pasta não deverá ser usada para gravação dos backups do log, pois desta forma teríamos os logs e os dados no mesmo computador e disco rígido. Backup do log de transações Em caso de defeito do equipamento, mesmo resgatando-se os últimos backups completos dos bancos de dados gerados anteriormente, não teríamos disponíveis os backups dos logs para aplicá-los sobre os bancos de dados atualizando-os para a situação mais recente (instante da última hora cheia em que foram gravadas as cópia dos logs), que é o objetivo único das suas gerações. Backup do log de transações É fundamental que as cópias dos logs sejam gravadas, através da rede, em outro computador que não seja o que está instalado o banco de dados, podendo ser um outro servidor ou estação de trabalho comum. Para isso, devemos criar um dispositivo lógico (device) de backup que “aponte” para outro computador da rede, onde, para cada banco, será criado um arquivo ‘.bak’ de gravação do log. Backup do log de transações Lembramos novamente ser importante analisar os scripts disponibilizados para efetuar as alterações necessárias, como a colocação do nome correto do banco de dados em todas as suas citações, o caminho (path) de criação dos dispositivos de gravação dos backups ( ‘\\nome-do-servidor\LogSql\’, linhas 19 e 51) e o horário de agendamento da execução (hora padrão inicial ‘08:00:00’ e final ’22:00:00’, linhas 125 e 128). Backup do log de transações Porém isso ainda não será suficiente. Normalmente, na instalação, definimos que o SQL Server seja inicializado através da conta SYSTEM do servidor, que não é um usuário da rede. Desta forma, quando o SQL tentar gravar o backup do log em um outro computador, receberá mensagem de erro por não possuir autorização de acesso e gravação à pasta definida para tal. Backup do log de transações Para solução desse problema, as seguintes medidas deverão ser tomadas: Criar na rede o usuário “sqlserver” e dar autoridade suficiente para operação do SQL Server (perfil Operador ou Administrador). Sugerimos que a senha seja a mesma do usuário “sa” de administração do SQL; Backup do log de transações Para solução desse problema, as seguintes medidas deverão ser tomadas: Configurar o SQL para iniciar com o usuário “sqlserver”. No próprio servidor onde está instalado, clicar com o botão direito do mouse sobre o nome do servidor localizado na árvore de console do SQL Enterprise Manager e escolher Propriedades e, em seguida, na aba Security, no quadro ‘Startup service account’, escolher ‘This account’ preenchendo o nome da conta (usuário) e senha conforme definidos; Backup do log de transações Para solução desse problema, as seguintes medidas deverão ser tomadas: Criar pasta ‘LogSql’ em um computador da rede para gravação dos logs, compartilhá-la e, nela, dar autoridade de controle total ao usuário “sqlserver”. Backup do log de transações Verificar sempre se os dispositivos lógicos de backup (arquivos ‘.bak’) completo do banco de dados e do log de transações estão sendo gravados nas pastas especificadas. Verificar também a data e hora de criação e atualização dos arquivos. Backup do log de transações Através do SQL Enterprise Manager, poderá ser verificado o histórico de execução de cada serviço (job) que foi criado. Dentro da pasta Management/SQL Server Agent/Jobs ficam armazenados os jobs existentes, bastando clicar sobre cada um deles com o botão direito do mouse e escolher a opção ‘View Job History...’. Microsoft SQL Server Rogério Felix Analista de Sistemas Sr. Gerência de TI SESC Depto Nacional E-mail: [email protected]