Uma das ferramentas mais úteis para um DBA de SQL Server, é o SQL Server Agent. Na maioria dos ambientes em  que dou manutenção, vejo a subutilização dessa ferramenta, onde muitos DBAs, ou por preguiça, ou desconhecimento, só criam algumas rotinas básicas.

Esta série de artigos pretende ajudar aos iniciantes e, porque não, aos mais experientes, pois existem diversas utilizações que serão discutidas ao longo dela.

Em 2010, comecei a trabalhar em uma entidade financeira, onde os ambientes de desenvolvimento, testes e homologação, eram “Terra de Ninguém”, pois todos faziam de tudo, o que comprometia a qualidade das entregas; a justificativa para todos serem db_owner de todos os bancos de dados de todas instâncias, era de que as atualizações dos bancos, com o conteúdo de produção, retiravam as permissões dos logins e, por causa deste problema, os DBAs perdiam entre 4 e 5 dias para realizar o serviço de reconfiguração dos ambientes de uma forma correta, mas o script de liberar db_owner para todos, além de mais simples, já estava pronto.

Após entender o cenário, propus utilizar o SQL Server Agent e algumas stored procedures para resolver este problema, concedendo os privilégios que cada um necessitava, ou seja, o mínimo necessário; fui chamado de louco, de idiota e de um monte de outros adjetivos carinhosos, porém, após algum tempo, problema resolvido. Todos os refreshs de dados de todas as instâncias de desenvolvimento, testes e homologação, eram realizados em aproximadamente 12 horas. Estamos falando de replicar o ambiente de produção, que possuia algo em torno de 5TB, com mascaramento de informações, em mais de 65 instâncias, algumas com 60 bancos de dados. Ao todo, eram 5 ambientes, cada um com 3 trilhas, cada trilha com 4 servidores, além de alguns servidores de projetos.

Qual o segredo do sucesso deste projeto?

O SQL Server Agent.

Ok, está bem! Algumas stored procedures e algumas tabelas de controle, permitiram desenvolver toda a lógica, mas o maior aliado foi o SQL Server Agent. Em um dado momento, foi tentado um outro agendador de tarefas (Control-M) mas sua complexidade e forma de funcionamento, não proporcionaram a mesma performance.

Nesta série de artigos, vou dar uma visão geral de utilização do SQL Server Agent, abordando suas principais funcionalidades.

Configuração e Visão geral do SQL Server Agent

O SQL Server Agent é o serviço de agendamento e alerta de tarefas do SQL Server e, quando usado corretamente, pode simplificar consideravelmente a carga de trabalho de um Administrador de Banco de Dados (DBA).

A principal característica do SQL Server Agent é a facilidade para executar tarefas em lote; um “lote” é simplesmente um “conjunto de ações”, muitas vezes definido usando um script Transact-SQL; estes podem, então, ser executados como um job, em uma programação específica, que você pode optar por ser em momentos em que poucos usuários estão acessando o sistema; esses jobs podem ser qualquer coisa, desde scripts T-SQL triviais até tarefas complexas e multi-ambiente, executadas em várias instâncias do SQL Server e em vários servidores.

O SQL Server Agent também permite que você notifique os usuários, por e-mail, por exemplo, quando uma determinada tarefa é concluída ou quando ocorre um erro.

É muito provável que você, como um DBA, seja imediatamente capaz de fazer uso do SQL Server Agent para agendar jobs em uma nova implantação do SQL Server; por exemplo, o primeiro job pode ser fazer backup dos bancos de dados do sistema, com o envio de um alerta por  email, para informar se a tarefa de backup foi concluída ou falhou.

Os jobs subseqüentes provavelmente incluirão a criação e o agendamento de backups de bancos de dados de usuários, operações de manutenção de índices (recriações ou reorganizações) e assim por diante.

Alguns DBAs podem usar o SQL Server Agent para agendar os pacotes de Planos de Manutenção; eu, particularmente, não gosto dos planos de manutenção, pois são muito engessados, o que barra a criatividade e, em alguns casos, não consegue entregar o que você realmente necessita, como por exemplo, ao término de um plano desses, de um servidor importante para a empresa, você tem que enviar um email para o CIO e o CFO, você vai enviar o email tosco, sem formatação, que é proporcionado pelo padrão de envio de emaisl do SQL Server, ou é melhor enviar um formatado em HTML, ou criado em PDF?

Para a maioria das tarefas que estão disponíveis nos Planos de Manutenção do SQL Server, os scripts que desenvolvi são mais eficientes e permitem enviar relatórios HTML para quantas caixas de email forem necessárias.

Neste artigo mostrarei como configurar o SQL Server Agent e criar o seu primeiro job.

Esta série irá focar no SQL Server 2014, no entanto, a maioria das informações nesta série também se aplica a outras versões, anteriores e posteriores.ao SQL Server 2000.

Como executar o SQL Server Agent

SQL Server Agent não está habilitado por padrão durante a configuração inicial do SQL Server, portanto, a primeira etapa simples para usar o SQL Server Agent é obter o serviço SQL Server Agent em execução.

Configurando o agente para iniciar automaticamente com o SQL Server

Para configurar o SQL Server Agent para iniciar automaticamente, abra o utilitário SQL Server Configuration Manager, encontrado no Menu Iniciar do Windows, “Microsoft SQL Server” / “Configuration Tools” / ”SQL Server Configuration Manager”.  Você precisará ser um administrador local do Windows (ou ser amigo de um) para executar este utilitário.

Uma vez iniciado, o utilitário deve ser automaticamente ligado à sua máquina local. Clique na opção “SQL Server Services” na árvore. No painel direito, você verá uma lista dos serviços instalados do SQL Server. Um deles deve ser “SQL Server Agent (instancename)” onde instancename é o nome da instância usada quando você instalou o SQL Server ou “MSSQLSERVER” se você estiver configurando uma instância padrão. Se o Start Mode já estiver configurado para automático, ótimo, ele foi configurado corretamente durante a instalação. Se não, clique duas vezes em SQL Server Agent, clique na guia Service na caixa de diálogo de propriedades que aparece e clique no menu suspenso ao lado de Start Mode; selecione Automático e, em seguida, clique em Apply (veja figura 1.1); pronto, agora o SQL Server Agent será iniciado automaticamente quando o servidor for reiniciado e o SQL Server estiver em execução. Se, por algum motivo, o SQL Server Agent não estiver em execução, inicie o serviço agora.

Seleção de conta de serviço

Você precisa garantir que você tenha uma conta de serviço apropriada configurada com base no que você planeja fazer com o SQL Server Agent. Se você estava seguindo esse tutorial, você está na guia Service das propriedades do SQL Server Agent; clique sobre a guia Logon.

Para operações básicas do SQL Server Agent, você pode usar uma conta interna, como Network Service (a conta de serviço será definida para o que foi selecionado durante a instalação inicial do SQL Server). A Figura 1.2 mostra a caixa de diálogo de seleção. Basta selecionar Network Service na lista em Built-in Account. Se você pretende que o SQL Server Agent se conecte a máquinas remotas (para copiar arquivos, por exemplo, ou para administrar várias instâncias), você deve alternar para usar uma conta de usuário de domínio (a opção This Account da Figura 1.2) criado especificamente para este fim; isso é algo que você precisara solicitar do seu administrador de domínio. Você precisará de uma conta de usuário comum na perspectiva do Administrador de Domínio. Em qualquer dos casos, selecione a conta de serviço correta e, uma vez que você fez as alterações, você precisará selecionar o botão “Restart” para que as novas alterações entrem em vigor. Depois de concluir isso, clique no botão OK para concluir a configuração do serviço do Agente SQL Server. Você pode ler mais sobre seleção de conta de serviço em http://msdn.microsoft.com/en-us/library/cc281953.aspx.

Criando seu primeiro job

Agora que o SQL Server Agent está configurado e em execução, você pode criar seu primeiro JOB. Por meio dessa demonstração, vamos configurar um JOB para executar uma das tarefas críticas do DBA, que é fazer backup dos bancos de dados do sistema para que, ao invés de ter que se lembrar de fazer backup desses bancos de dados manualmente, você possa criar um JOB automatizado, que vai fazer isso por você.

Jobs

Um job é o contêiner principal para uma única tarefa lógica, como fazer backup de um banco de dados; essa tarefa conterá um ou mais steps; o job pode incluir notificações (por exemplo, enviar um e-mail para o DBA se um job falhar), schedules (quando você deseja que o job seja executado em horários pré-definidos) e até mesmo executar em várias instâncias. Para o nosso primeiro job de backup vamos fazer “o simples”.

Para criar o job, abra o SQL Server Management Studio (SSMS) e conecte-se à instância onde deseja criar o job. Expanda o nó SQL Server Agent, clique com o botão direito do mouse em Jobs. Selecione a opção para criar um novo job ( “New Job …”), como mostrado na Figura 1.3.

Informações Gerais do  Novo JOB

Vamos nomear o job “DBA_JOB_BACKUP”. Esse será o nome usado para se referir ao trabalho na GUI do SSMS ou no PowerShell; Category pode ser uma forma útil de organizar seus trabalhos, mas não é importante para o seu primeiro trabalho, definir uma categoria (você aprenderá como especificar categorias em um artigo posterior); na caixa Description, escreva algo que irá ajudá-lo a lembrar por que você criou este trabalho e o que é suposto fazer. Lembre-se que outra pessoa pode muito bem ter que administrar este sistema mais tarde e, uma vez que não criou este job, ela pode não saber o que este job realiza e se é ou não é crítica.

Tendo feito tudo isso, você basicamente terminou de criar o Job Shell, que é apenas o Container para todos os componentes que você vê como guias no “New Job Dialog”. As partes importantes são as que estão nas outras guias.

Steps

Um job no SQL Server Agent é composto de pelo menos um Step. Quando a maioria das pessoas pensa em um job executando alguma tarefa, o que realmente entendem é uma etapa (step). Um step é definido pelo tipo de ação que você deseja executar e inclui a capacidade de executar os seguintes tipos de steps:

• ActiveX
• Sistema operacional (CMDExec)
• PowerShell
• Uma variedade de tarefas de replicação
• Comando do SQL Server Analysis Services (SSAS) (por exemplo, XML / A)
• Consulta do SQL Server Analysis Services (SSAS) (MDX)
• Pacote de Serviços de Integração do SQL Server (SSIS) (pacote DTS no SQL Server 2000)
• Script Transact-SQL (T-SQL)

Para a maioria dos DBAs, a maioria dos seus trabalhos estará usando o último tipo de step, o T-SQL. Um job pode ser executado como proprietário do job (Job Owner), ou usando outro contexto de segurança, dependendo das permissões do job owner e da configuração dos proxies. Os steps serão abordados com mais detalhes em um próximo artigo.

Como você pode ver na interface do SSMS, a guia para controlar os steps é simplesmente chamada de Steps. Clique no botão Novo na parte inferior da caixa de diálogo, e o diálogo New Job Step é iniciado (consulte a Figura 1.6).

Dê ao step um nome útil – no nosso caso, o step vai chamar-se BACKUP_MASTER. O tipo será T-SQL, pois usaremos um comando BACKUP simples para nosso backup do banco de dados. Por enquanto, você pode ignorar a opção Run as, pois vamos executar o STEP de forma padrão, com o job owner, ou seja, nesse caso, você. O banco de dados está definido corretamente por padrão para o que estamos fazendo, que é trabalhar em master, mas dependendo do caso, poderá ser alterado. Eu prefiro deixar sempre no master e, no script, uso o comando USE <database>, quando o processo deve ser executado em outro banco, porque acho que fica mais explícito e se, acidentalmente, o parâmetro “Run As” for alterado, o resultado permanecerá inalterado.

Para o comando em si, você pode abrir um arquivo de script, ou copiar e colar algum T-SQL válido que você tenha escrito em uma janela de query. Para mantê-lo simples, estamos fazendo um comando de backup simples. Embora não seja o ideal, criei o script com o backup sendo armazenado na unidade C:, por uma questão de configuração da VM de testes que utilizo, que possui apenas uma unidade, mas você deve trocar para uma unidade exclusiva de backups.

Você não deve ver um SQL Server de produção em um único disco rígido como este!

USE master
GO 
BACKUP DATABASE master  TO DISK='C:\MSSQL\Backup\master.bak'
       WITH NOFORMAT, INIT, NAME = N'master-Full Database Backup', 
       SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10 GO

Schedules

A programação de execução do job (job schedules) controlam quando este realmente será iniciado, podendo ser visualizada na guia Schedules do configurador de jobs. Você pode criar várias programações de execução para um mesmo job, inclusive, utilizar uma mesma programação para executar vários jobs simultaneamente, mas isso é assunto para um outro artigo.

Então, vamos criar uma programação de execução para o nosso job. Na guia Schedules, clique em New; uma nova janela será apresentada, onde devemos informar um nome para o schedule que, no nosso caso, chamará BACKUP_FULL_NOTURNO.

Existem quatro tipos de schedules:

• Iniciar automaticamente quando o SQL Server Agent for iniciado
• Iniciar sempre que as CPUs ficarem ociosas
• Recorrente
• Um tempo

A maioria dos schedules será do tipo Recorrente (por exemplo, executar o meu backup uma vez por semana no domingo às 00:00). Neste caso, frequência de execução pode ser diária, semanal ou mensal, com o horário fixo, ou a cada intervalo de tempo (a cada 10 minutos, por exemplo).

O agendador também permite que o job tenha uma data inicial e final de execução.

Executando o job que você acabou de criar

Agora que criamos nosso job, é uma boa idéia executá-lo para verificar se está  tudo correto. Embora tenhamos programado o job para ser executado semanalmente, podemos sempre executá-lo manualmente, sob demanda, para isso, o SSMS, na pasta “Jobs”, você deve ver seu job. Clique com o botão direito do mouse nele e selecione “Start Job at Step …”, como na figura 1.9; ele será executado, e agora, o backup de seu banco de dados master é realizado.

Lembrando que este job é um exemplo, mas é funcional; baseado nos passos descritos, você pode incrementá-lo para a execução do backup de todos os bancos da instância. Em um próximo artigo, disponibilizarei uma stored procedure que executa o backup de todos os bancos e envia um email com o resultado para destinatários parametrizados.