PowerShell: Conheça este Poderoso Recurso do SQL Server 2008

Depois de varias noites e paciência de alguns MVP´s (especialmente de Jvierra e Shay Levy  MVP’s  gringos e com certeza  Powershellman) consegui montar este script (com base em alguns da net) que faz isso em algumas linhas de código.

O ponto principal que eu encontrei no Powershell foi a produtividade, flexibilidade no código e performance na execução.

Vou passar por todos os scripts que montei para chegar ao final, pois assim podemos ver as dificuldades que tive e passar para vocês como resolver.

A minha intenção era poder ter um script para verificar os Jobs com erro, criar um txt formatado para enviar por email e subir para uma tabela do SQL Server ….tudo em PS

Bom, vamos ao código.

Nota: Fique atendo aos exemplos pois em alguns pontos dos códigos você deverá trocar a barra / pela barra de separação de diretório.

Primeiramente para todos os scripts, gere um arquivo chamado servidores.txt na pasta C:/dadosps. Este arquivo conterá o nome dos servidores (você não precisa estar num domínio). No meu caso ficou assim

SERVER1/MSSQLSERVER_1
SERVER2/MSSQLSERVER_2

1 – Meu primeiro Script

Peguei-o da Net

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”)|out-null
foreach ($svr in get-content “C:/dadosps/servidores.txt”)
{
   write-host $svr
   $srv=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
$_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate -autosize
}

Vamos Entende-lo:

#Carrego a SMO
[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

#Para cada linha do txt servidores.txt, é carregado para a variável $svr
foreach ($svr in get-content “C:/dadosps/servidores.txt”)
{
#Escreve na tela o nome do servidor
   write-host $svr

#Instância a SMO e coloca o filtro | todos que falharam e estão enabled | formata a saída na tela

   $srv=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
$_.isenabled -eq $TRUE} | format-table name,lastrunoutcome,lastrundate –autosize

}

Grave este codigo num arquivo .ps1 e execute pelo powershell
/../../nomearquivo.ps1. No meu caso, ficou assim

Como vocês podem ver, a exibição é feita na tela. Certo.Bem legal..mas me perguntei : o que faço com isso ? Neste script nada, pois somente exibe na tela.

2 – Meu segundo Script
Comecei a “fuçar”.Estava normal ainda

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
foreach ($svr in get-content “C:/dadosps/servidores.txt”)
{
   write-host $svr
   $srv=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $srv.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
$_.isenabled -eq $TRUE} | Select-Object name,lastrunoutcome,lastrundate | Export-Csv “c:/dadosps/Jobs.txt”
}

Percebemos que aqui temos dois cmdlet a mais…

Select-Object name,lastrunoutcome,lastrundate | Export-Csv “c:/dadosps/Jobs.txt”

Este cmdlet me retorna as propriedades (colunas) do objeto e exporta pra um arquivo .txt. Aqui tive dois problemas.

1 – Formatação do txt . Vejam como ficou..isso e nada pra mim é a mesma coisa.

#TYPE System.Management.Automation.PSCustomObject
Name,LastRunOutcome,LastRunDate
TesteJob_2,Failed,”19/10/2008 08:58:58″

2 – Apesar de eu ter duas instancias com 4 jobs falhos (3 na primeira e 1 na segunda, ele esta somente me exibindo a segunda..porque ? Depois de pensar alguns segundos (heheheh este é um segredo que levarei para o túmulo)
CLAAAAAAAAROOOO…o export-csv esta sobrescrevendo o arquivo. Ou seja, ele cria um com os Jobs do 1 server e depois sobrescreve o segundo.

3 – Meu terceiro Script
Fuçando mais um pouco e já ficando zureta…pensando na janta..foreach batatinhas fritas select arroz ,bife

 [reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
$datas = (get-date).toshortdatestring()
foreach ($svr in get-content “C:/dadosps/servidores.txt” )
{
   $Servidor=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $Retorno=$Servidor.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
   $_.isenabled -eq $TRUE}   | select name ,lastrunoutcome,lastrundate
   $Retorno0 = [string] $Retorno[0]
   $Retorno1 = [string] $Retorno[1]

   $Retorno0 = $Retorno0 -replace(“@{Name=|lastrunoutcome=|LastRunDate=|}”,””)
   $Retorno1 = $Retorno1 -replace(“@{Name=|lastrunoutcome=|LastRunDate=|}”,””)

   $RetornoTotal0 = $datas + “;” + $svr + “;” + $Retorno0 | out-file c:/dadosps/jobscomfalha.txt -encoding ASCII -append
   $RetornoTotal1 = $datas + “;” + $svr + “;” + $Retorno1 | out-file c:/dadosps/jobscomfalha.txt -encoding ASCII -append

}
 
$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=USUARIO-9E926B0SQL2008_2; Initial Catalog=PS_2; Integrated Security=SSPI”)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText =”BULK INSERT tb_ListadeJobscomFalha
FROM ‘c:/dadosps/JobsComFalha.txt’
WITH
(
FIELDTERMINATOR =’;’
)”
$cmd.Executenonquery()
$conn.Close()
del c:/dadosps/jobscomfalha.txt

Este código varre todos os servidores previamente cadastrados em um TXT, procura por jobs com erro em cada servidor, grava num txt e sobe pra uma tabela do SQL SERVER (detalhe , sem linked server ou qualquer outro recurso do sql server a não ser a dll SMO , criação de uma tabela e um JOB para executa-lo)
Lindo e maravilhoso ate que percebi (duas semanas depois) que só funcionava pra dois servers…affffff..de volta a estaca zero

4 – Meu Quarto Script
Fuçando fuçando fuçando ..”Bom dia amor”…”Bom dia porqueeeeee?”
Afff…algumas noites virado..Sim..tomei banho e troquei a cueca.
Bom..cheguei ao que eu queria..

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
$datas = (get-date).toshortdatestring()

foreach ($svr in get-content “C:/dadosps/servidores.txt” )
{
  
   $data = “”
   $Servidor=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $data = $Servidor.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
   $_.isenabled -eq $TRUE}  | foreach {
   $NomeJob = $_.name
   $lastrunoutcome = $_.lastrunoutcome

   $lastrundate = [string] $_.lastrundate
   $lastrundate = $lastrundate.substring(3,2) + “/” + $lastrundate.substring(0,2) + “/” + $lastrundate.substring(6)
 
   $svr+ “;” + $datas+”;”+$nomejob+”;”+$lastrunoutcome +”;”+$lastrundate |  out-file c:/dadosps/jobscomfalha.txt -encoding ASCII -append

   }
} 

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=MEUSERVIDOR; Initial Catalog=PS; Integrated Security=SSPI”)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText =”set dateformat dmy BULK INSERT tb_ListadeJobscomFalha
FROM ‘c:/dadosps/JobsComFalha.txt’
WITH
(
FIELDTERMINATOR =’;’
)”
$cmd.Executenonquery()
$conn.Close()
del c:/dadosps/jobscomfalha.txt

Este código SIM varre todos os servidores previamente cadastrados em um TXT, procura por jobs com erro em cada servidor, grava num txt e sobe pra uma tabela do SQL SERVER (detalhe , sem linked server ou qualquer outro recurso do sql server a não ser a dll SMO , criação de uma tabela e um JOB para executa-lo)

Crie uma pasta para conter o TXT com os servidores. Ele será útil não só para este script mas para todos os outros que você montar.
Também crie uma tabela para receber este TXT, no meu caso como meu arquivo gerado pelo código era…

Obs: Neste exemplo, troque a barra que separa a palavra SQL2008 por uma barra de separação de diretório

19/10/2008;USUARIO-9E926B0/SQL2008;TesteJobPS,Failed;19/10/2008 13:07:25
19/10/2008;USUARIO-9E926B0/SQL2008_2;TesteJobPS_2,Failed;19/10/2008 13:07:21

A tabela fica assim…

CREATE TABLE [dbo].[tb_ListadeJobscomFalha](
 [Data] [datetime] NOT NULL,
 [Servidor] [varchar](50) NOT NULL,
 [NomeJob] [varchar](50) NOT NULL,
 [Msg] [varchar](50) NOT NULL,
 [UltimoVezRodou] [datetime] NOT NULL
) ON [PRIMARY]

1 – Criar um arquivo txt contendo a lista de todos os seus servidores. Este será acessado pelo script  no foreach ($svr in get-content “C:/dadosps/servidores.txt” )

O TXT é simplesmente isso, o nome dos servidores. Eu tenho duas instancias na minha máquina, então ficou….

Obs: Neste exemplo, troque a barra que separa a palavra SQL2008 e SQL2008_2 por uma barra de separação de diretório.

MINHAMAQUINA/SQL2008
MINHAMAQUINA/SQL2008_2

2 – Vamos entender um pouquinho o código. Faço referencia a SMO

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null

Carrego a data de hoje para colocar no TXT final. Fiz isso pois como uso o BULK INSERT e na tabela tem este campo, para não ter que usar um arquivo .fmt trago-o no TXT

$datas = (get-date).toshortdatestring()

Agora que é o X. Abaixo é feito um foreach para cada servidor achado no TXT. Repare a produtividade do código, em uma linha eu carrego o txt e cada servidor do TXT é atribuído na variável $SVR

foreach ($svr in get-content “C:dadospsservidores.txt” )
{

Aqui eu instancio a SMO, na classe JOBSERVER para o SERVIDOR $SVR

   $server=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”

Agora eu faço a consulta somente nos jobs que falharam e estão enabled, trazendo as propriedades que eu quero (select name,lastrunoutcome,lastrundate), coloco outro foreach para andar em todos os Jobs jogando pra variaveis. Na variável $lastrundate  eu  usei o substring pois quando passa pra string [string] ele fica mm/dd/yyyy..e eu queria passer dd/mm/yyyy pois  no bulk insert uso o set dateformat dmy

   $data = $Servidor.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
   $_.isenabled -eq $TRUE}  | foreach {
   $NomeJob = $_.name
   $lastrunoutcome = $_.lastrunoutcome

   $lastrundate = [string] $_.lastrundate
   $lastrundate = $lastrundate.substring(3,2) + “/” + $lastrundate.substring(0,2) + “/” + $lastrundate.substring(6)
 
   $svr+ “;” + $datas+”;”+$nomejob+”;”+$lastrunoutcome +”;”+$lastrundate |  out-file c:/dadosps/jobscomfalha.txt -encoding ASCII -append (troque a barra / desta linha pela barra de separação de diretório)

Então para cada servidor do meu txt, o codigo pega os dados e vai inserindo no TXT. Aqui  eu instancio um adonet e faço o bulk insert e depois elimino o arquivo.

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=MEUSERVIDOR; Initial Catalog=PS_2; Integrated Security=SSPI”)
$conn.Open()
$cmd = $conn.CreateCommand()
$cmd.CommandText =”set date format dmy BULK INSERT tb_ListadeJobscomFalha
   FROM ‘c:/dadosps/JobsComFalha.txt’
   WITH
      (
         FIELDTERMINATOR =’”;’
      )”
$cmd.Executenonquery()
$conn.Close()
del c:/dadosps/jobscomfalha.txt

3 –  Crie um Arquivo .ps1 com este codigo e guarde numa pasta

4 – Crie um job com opção de powershell – NO step o Tipo vai ser POWERSHELL e no command mande executar o script

Invoke-expression /powershell/scripts/listajobs.ps1 (troque a barra / desta linha pela barra de separação de diretório)

(pasta que contem o .ps1)

Prontinho… Simples não ?

Bom e se eu quisesse somente inserir na tabela sem gerar o txt..Vamos lá.

[reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.Smo”) | out-null
$datas = (get-date).toshortdatestring()
$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=WINDOWS-6D9049DMSSQLSERVER_1; Initial Catalog=PS; Integrated Security=SSPI”)
$sql = “insert into tb_ListadeJobscomFalha(servidor,data,nomejob,msg,UltimoVezRodou)”
$conn.Open()
$cmd = $conn.CreateCommand()

foreach ($svr in get-content “C:/dadosps/servidores.txt” ) (troque a barra / desta linha pela barra de separação de diretório)
{
  
   $data = “”
   $Servidor=New-Object “Microsoft.SqlServer.Management.Smo.Server” “$svr”
   $data = $Servidor.jobserver.jobs | where-object {$_.lastrunoutcome -eq “Failed” -and
   $_.isenabled -eq $TRUE}  | foreach {
   $NomeJob = $_.name
   $lastrunoutcome = $_.lastrunoutcome

   $lastrundate = [string] $_.lastrundate
   $lastrundate = $lastrundate.substring(3,2) + “/” + $lastrundate.substring(0,2) + “/” + $lastrundate.substring(6)

   $cmd.commandtext = “set dateformat dmy insert into tb_ListadeJobscomFalha(servidor,data,nomejob,msg,UltimoVezRodou) values (‘$svr’,’$Datas’,’$NomeJob’,’$lastrunoutcome’,’$lastrundate’)”
   $null = $cmd.Executenonquery()
   }
} 
   $conn.Close()

Ta…e se eu quiser mandar por email ?

function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) {
    if ($smtpserver -eq $null) {$smtpserver = “smtp.seilacom.br.com”}
    $mailer = new-object Net.Mail.SMTPclient($smtpserver)
    if ($port -ne $null) {$mailer.port = $port}
    if ($timeout -ne $null) {$mailer.timeout = $timeout}
    $msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
    if ($html) {$msg.IsBodyHTML = $true}
    if ($cc -ne $null) {$msg.cc.add($cc)}
    if ($bcc -ne $null) {$msg.bcc.add($bcc)}
    if ($alert) {$msg.Headers.Add(“message-id”, “<3bd50098e401463aa228377848493927-1>”)}
    if ($attachment -ne $null) {
        $attachment = new-object Net.Mail.Attachment($attachment)
        $msg.attachments.add($attachment)
    }

#Autenticação

$myCred = new-object System.net.networkCredential
$myCred.userName = “laerte@seila.com.br“
$myCred.password = “senha”
$mailer.credentials = $myCred

    $mailer.send($msg)
}

Para chamar

  send-SMTPmail -to “laertejuniordba@hotmail.com” -from “laerte@seila.com.br”  -subject “ACORDAAAA!” -smtpserver “smtp.seila.com.br”  -body “O DBA..acorda…ce tem jobs com erro.” -attachment “c:/dadosps/listajobscomfalha.txt” (troque a barra / desta linha pela barra de separação de diretório)

Fiz testes iniciais de carga com 200 jobs em cada instância falhando e como a velocidade é de um BULK INSERT demorou 9 segundos para rodar o JOB. Fiz o Teste completo  – carregar a informação, criar o txt (so criei o txt para enviar por email senão poderia inserir direto na tabela), subir pra tabela e enviar email o resultado foi:

VBS – 1.44 segundos
SSIS – 1.23 s
PS – 58 s

Bom, espero que com este artigo deu para entender o potencial que temos nas mãos.

Obs: Assista também ao vídeo em http://cid-c16042a4306a1328.skydrive.live.com/browse.aspx/P%c3%bablico

Abraços
Laerte Junior

Avaliação: