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 de 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 a 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 bancos de dados e dos requisitos de disponibilidade, você pode executar todas essas atividades todas as noites. No entanto, em sistemas de produção, as operações de manutenção geralmente são distribuídas ao longo da semana, pois tanto a manutenção do índice quanto as verificações de consistência são muito intensivas de E/S e normalmente são feitas durante o fim de semana.
Muitos DBAs escalonam backups de bancos de dados grandes, executando um backup completo por semana e usando backups diferenciais e de log de transações para gerenciar a recuperação para um ponto específico no tempo. 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 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 software livre para manutenção de banco de dados para obter mais flexibilidade e controle sobre as atividades de manutenção.
Melhores práticas para planos de manutenção
Os planos de manutenção não só ajudam você a executar a manutenção do banco de dados, mas também oferecem opções para podar dados do banco de dados msdb, que serve como o 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.
Verifique se o período de retenção de backup é 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 o histórico de backup suficiente para se recuperar de possíveis corrupçãos detectadas durante verificações de consistência. Observe que a operação de backup não detecta corrupção em um banco de dados, portanto, é possível ter corrupção em 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 melhor prática é criar um plano de manutenção separado para cada tipo de tarefa e, possivelmente, até mesmo para bancos de dados específicos em seu 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ário. Além disso, você pode ter um plano de manutenção separado para lidar com o backup de um banco de dados de usuário 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 SSMS (SQL Server Management Studio). Você precisa especificar um nome para seu plano de manutenção e uma conta de execução como. 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 de um compartilhamento de arquivos acessível apenas por uma conta específica, você usará 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 normalmente são 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 para 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 trabalho
As programações de tarefas fazem parte do sistema de tarefas na base de dados do sistema msdb. As tarefas e agendamentos do SQL Server Agent têm uma relação de muitos para muitos, ou seja, cada tarefa pode ter vários agendamentos e cada agendamento pode ser atribuído a várias tarefas. No entanto, o Assistente de Plano de Manutenção não permite a criação de agendas independentes. Em vez disso, ele cria um agendamento específico para cada plano de manutenção.
O exemplo a seguir mostra a agenda de uma execução semanal, mas você também tem a opção de criar uma agenda com recorrência diária ou por hora.
A próxima etapa é 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 – essa tarefa executa o DBCC CHECKDB comando para validar a consistência lógica e física de cada página de banco de dados. Você deve executar essa tarefa regularmente e alinhá-la com a janela de retenção de backup. Certifique-se de concluir uma verificação de consistência antes de descartar backups anteriores para evitar a transferência de corrupção.
Reduzir o banco de dados – essa 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. Depois que espaço suficiente for liberado, ele poderá ser retornado ao sistema de arquivos. É recomendável não incluir essa ação na manutenção regular, pois ela causa fragmentação de índice severa, 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/Recompilar índice – essa tarefa verifica o nível de fragmentação nos índices de um banco de dados e recria ou reorganiza o índice com base no nível de fragmentação definido pelo usuário. A recriação de um índice também atualiza suas estatísticas.
Atualizar estatísticas – essa 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 o otimizador de consulta tomar as melhores decisões. Você pode escolher quais tabelas e índices examinar e o percentual ou o número de linhas a serem digitalizadas. A taxa de amostragem padrão geralmente é suficiente, mas talvez você precise de estatísticas mais detalhadas para tabelas específicas.
Histórico de limpeza – Essa tarefa exclui o histórico de operações de backup e restauração do msdb banco de dados, bem como o histórico de trabalhos do SQL Server Agent. Ele ajuda a gerenciar o tamanho do banco de dados msdb.
Executar o trabalho do SQL Server Agent – Essa tarefa executa um trabalho do SQL Server Agent definido pelo usuário.
Backup de Banco de Dados (Completo/Diferencial/Log) – essa tarefa realiza o 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 no 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 você fizer um backup completo no domingo e um backup diferencial a cada noite de semana, para restaurar seu banco de dados ao meio-dia de quinta-feira, você restaurará o backup completo de domingo, o backup diferencial de quarta-feira e os backups de log de transações do diferencial de quarta-feira para quinta-feira ao meio-dia.
Tarefas de Limpeza de Manutenção – Essa tarefa remove arquivos antigos relacionados a planos de manutenção, incluindo relatórios de texto e arquivos de backup. Ele só remove backups nas pastas especificadas, portanto, todas as subpastas devem ser explicitamente listadas ou serão ignoradas.
Cada tarefa pode ter como escopo bancos de dados de usuário, bancos de dados do sistema ou uma seleção personalizada de bancos de dados e cada uma tem opções de configuração específicas.
Após a criação, o plano será exibido como um trabalho no SQL Server Agent. Ao adicionar uma agenda durante o processo de criação ou depois, o trabalho criado será executado e as tarefas de manutenção serão realizadas.
Ambiente multisservidor
Em um ambiente multisservidor, o SQL Server Agent permite designar um servidor como um servidor primário que pode executar trabalhos em outros servidores, conhecidos como servidores de destino. O servidor primário armazena a fonte principal dos trabalhos e os distribui para os servidores de destino. Os servidores de destino se conectam periodicamente ao servidor primário para atualizar seus agendamentos de trabalho. Essa configuração permite definir um trabalho uma vez e implantá-lo em sua empresa. Por exemplo, você pode configurar tarefas de manutenção de banco de dados no servidor primário e efetuá-las por push para um grupo de servidores de destino, garantindo uma implantação consistente.