Criar um plano de manutenção do SQL Server
As atividades típicas que você pode agendar para manutenção regular do SQL Server incluem:
- Backups de banco de dados e log de transações
- Verificações de consistência do banco de dados
- Manutenção de índices
- Atualizações de estatísticas
É crucial entender a importância dos backups, bem como da manutenção de índices e estatísticas, para todos os seus bancos de dados. As verificações de consistência do banco de dados, também conhecidas como CHECKDB (usando o comando DBCC CHECKDB), são igualmente importantes porque são a única maneira de verificar se há corrupção em um banco de dados inteiro. Dependendo do tamanho dos seus bancos de dados e dos seus requisitos de tempo de atividade, poderá realizar todas essas atividades diariamente à noite. No entanto, nos sistemas de produção, as operações de manutenção são frequentemente distribuídas ao longo da semana, uma vez que tanto a manutenção do índice como as verificações de consistência são muito intensivas em E/S e normalmente são feitas durante as horas de fim de semana.
Muitos DBAs escalonam backups de grandes bancos de dados, realizando um backup completo por semana e usando backups diferenciais e de log de transações para gerenciar a recuperação em um point-in-time específico. O SQL Server oferece uma maneira interna de gerenciar todas essas tarefas usando Planos de Manutenção. Os Planos de Manutenção criam um fluxo de trabalho de tarefas para dar suporte aos seus bancos de dados e são criados como pacotes do Integration Services, permitindo que você agende suas atividades de manutenção. Além disso, muitos DBAs usam scripts de código aberto para manutenção de banco de dados para obter mais flexibilidade e controle sobre as atividades de manutenção.
Práticas recomendadas para planos de manutenção
Os planos de manutenção não apenas ajudam a executar a manutenção do banco de dados, mas também oferecem opções para remover dados do banco de dados msdb, que serve como armazenamento de dados para o SQL Server Agent. Além disso, os planos de manutenção permitem especificar a remoção de backups de banco de dados mais antigos do disco. A remoção de arquivos de backup antigos reduz o tamanho do volume de backup e ajuda a gerenciar o tamanho do banco de dados msdb.
Certifique-se de que o período de retenção de backup seja maior do que a janela de verificação de consistência. Por exemplo, se você executar uma verificação de consistência semanalmente, deverá manter um histórico de backup suficiente para se recuperar de possíveis danos detetados durante as verificações de consistência. Observe que a operação de backup não deteta corrupção em um banco de dados, portanto, é possível ter corrupção dentro de um arquivo de backup. As atividades do plano de manutenção são agendadas como trabalhos do SQL Server Agent para execução.
Criar um plano de manutenção
Você pode criar um plano de manutenção usando o SQL Server Management Studio, conforme mostrado abaixo. No exemplo, várias tarefas de manutenção são combinadas em um plano de manutenção. No entanto, a prática recomendada é criar um plano de manutenção separado para cada tipo de tarefa e, possivelmente, até mesmo para bancos de dados específicos no servidor. Por exemplo, você pode criar um plano de manutenção para fazer backup de bancos de dados do sistema e outro para fazer backup de bancos de dados de usuários. Além disso, você pode ter um plano de manutenção separado para lidar com o backup de um banco de dados de usuários particularmente grande. A imagem abaixo e os exemplos a seguir demonstram como criar um plano de manutenção usando o Assistente de Plano de Manutenção.
A imagem mostra a primeira tela do Assistente de Plano de Manutenção no SQL Server Management Studio (SSMS). Você precisa especificar um nome para seu plano de manutenção e uma conta run-as. A maioria das tarefas de manutenção será executada como a conta de serviço do SQL Server Agent, mas, para fins de segurança, algumas tarefas podem precisar ser executadas como uma conta diferente. Por exemplo, se você precisar fazer backup em um compartilhamento de arquivos acessível apenas por uma conta específica, use um usuário proxy, que é um componente do SQL Server Agent.
O que é uma conta proxy?
Uma conta proxy é uma conta com credenciais armazenadas que o SQL Server Agent pode usar para executar etapas de trabalho específicas como um usuário designado. As informações de logon desse usuário são armazenadas como uma credencial na instância do SQL Server. As contas proxy são normalmente usadas quando etapas de trabalho específicas exigem direitos de segurança muito granulares.
Suponha que você tenha um trabalho do SQL Server Agent que precise fazer backup de um banco de dados em um compartilhamento de arquivos de rede. Se a conta de serviço do SQL Server Agent não tiver acesso ao compartilhamento de arquivos, você poderá criar uma conta proxy com as permissões necessárias. Essa conta proxy pode ser usada para executar a etapa de backup, garantindo que ela tenha os direitos de acesso necessários.
Agendas de tarefas
As agendas de trabalho fazem parte do sistema de tarefas no banco de dados do sistema msdb. Os trabalhos e agendas do SQL Server Agent têm uma relação muitos-para-muitos, o que significa que cada trabalho pode ter várias agendas e cada agendamento pode ser atribuído a vários trabalhos. No entanto, o Assistente de Plano de Manutenção não permite a criação de agendas independentes. Em vez disso, cria um cronograma específico para cada plano de manutenção.
O exemplo a seguir mostra o agendamento para uma execução semanal, mas você também tem a opção de criar um agendamento com recorrência horária ou diária.
O próximo passo é selecionar as tarefas de manutenção a serem adicionadas ao plano. O exemplo a seguir mostra as operações disponíveis para serem executadas pelo seu plano de manutenção.
Verificar a integridade do banco de dados - Esta tarefa executa o comando DBCC CHECKDB para validar a consistência lógica e física de cada página do banco de dados. Você deve executar essa tarefa regularmente e alinhá-la com sua janela de retenção de backup. Certifique-se de concluir uma verificação de consistência antes de descartar quaisquer backups anteriores para evitar a transmissão de corrupção.
Shrink database - Esta tarefa reduz o tamanho de um banco de dados ou arquivo de log de transações movendo dados para o espaço livre nas páginas. Assim que houver espaço suficiente, ele poderá ser retornado ao sistema de arquivos. Recomenda-se não incluir essa ação na manutenção regular, pois causa fragmentação grave do índice, prejudicando o desempenho do banco de dados. A operação também é muito intensiva em E/S e CPU, o que pode afetar significativamente o desempenho do sistema.
Reorganizar/Reconstruir de índice - Esta tarefa verifica o nível de fragmentação nos índices de um banco de dados e reconstrói ou reorganiza o índice com base no nível de fragmentação definido pelo usuário. A reconstrução de um índice também atualiza suas estatísticas.
Atualizar estatísticas - Esta tarefa atualiza as estatísticas de coluna e índice usadas pelo SQL Server para criar planos de execução de consulta. Estatísticas precisas são cruciais para que o otimizador de consultas tome as melhores decisões. Você pode escolher quais tabelas e índices digitalizar e a porcentagem ou o número de linhas a serem examinadas. A taxa de amostragem padrão geralmente é suficiente, mas você pode precisar de estatísticas mais detalhadas para tabelas específicas.
Histórico de limpeza - Esta tarefa exclui o histórico de operações de backup e restauração do banco de dados msdb, bem como o histórico de trabalhos do SQL Server Agent. Ele ajuda a gerenciar o tamanho do banco de dados msdb.
Executar tarefa do SQL Server Agent - Esta tarefa executa um trabalho do SQL Server Agent definida pelo utilizador.
Backup Database (Full/Differential/Log) - Esta tarefa faz backup de bancos de dados em uma instância do SQL Server. Um backup completo captura todo o banco de dados e serve como ponto de partida para uma restauração. Os backups diferenciais capturam as páginas que foram alteradas desde o último backup completo, fornecendo um ponto de restauração incremental. Os backups de log de transações capturam as páginas ativas em seu log de transações, permitindo que você defina seu objetivo de ponto de recuperação. Observe que os backups de log de transações não podem ser executados em bancos de dados no modo de recuperação SIMPLE.
Por exemplo, se fizeres um backup completo no domingo e um backup diferencial em cada noite da semana, para restaures o teu banco de dados até ao meio-dia de quinta-feira, deves restaurar o backup completo de domingo, o backup diferencial de quarta-feira, e os backups do log de transações desde o diferencial de quarta-feira até ao meio-dia de quinta-feira.
Maintenance Cleanup Tasks - Esta tarefa remove ficheiros antigos relacionados com planos de manutenção, incluindo relatórios de texto e ficheiros de cópia de segurança. Ele só remove backups nas pastas especificadas, portanto, todas as subpastas devem ser listadas explicitamente ou serão ignoradas.
Cada tarefa pode ter como escopo bancos de dados de usuários, bancos de dados do sistema ou uma seleção personalizada de bancos de dados, e cada um tem opções de configuração específicas.
Após a criação, o plano aparecerá como um trabalho no SQL Server Agent. Se você adicionou um cronograma durante o processo de criação ou depois, esse trabalho será executado e as tarefas de manutenção serão executadas.
Ambiente multisservidor
Num ambiente multiservidor, o SQL Server Agent permite-lhe designar um servidor como servidor principal que pode executar trabalhos noutros servidores, conhecidos como servidores alvo. O servidor principal armazena a fonte principal dos trabalhos e distribui-os para os servidores-alvo. Os servidores alvo ligam-se periodicamente ao servidor principal para atualizar os seus horários de tarefas. Essa configuração permite que você defina um trabalho uma vez e implante-o em toda a sua empresa. Por exemplo, pode configurar tarefas de manutenção da base de dados no servidor principal e enviá-las para um grupo de servidores alvo, garantindo uma implementação consistente.