Monitorando o SQL Server com PowerShell

Havia algum tempo eu sentia falta de uma solução própria para fazer coleta de contadores diversos dos servidores que administro, a dois meses venho trabalhando forte em scripts Powershell e finalmente cheguei a uma solução definitiva, robusta e muito flexível.

Para exemplificar elaborei um pequeno tutorial.

Permissões necessárias:

S.O-Adminsitrador local do servidor SQL Server

SQL Server-SA

O primeiro passo é verificar se o servidor onde fará a execução do script está devidamente configurado, para isto abra o Powershell (execute as Administrator)

PS C:\Windows\system32> get-executionpolicy

Restricted

PS C:\Windows\system32>

Note que está como restrito, isto significa que scripts provenientes de outros locais não poderão ser executados, como eu tive alguns problemas com isto alterei este parâmetro para Unrestricted da seguinte forma

PS C:\Windows\system32> set-executionpolicy Unrestricted

Alteração da Diretiva de Execução A diretiva de execução ajuda a proteger contra scripts não confiáveis. A alteração da diretiva de execução pode implicar em exposição aos riscos de segurança descritos no tópico da ajuda about_Execution_Policies. Deseja alterar a diretiva de execução? [S] Sim [N] Não [U] Suspender [?] Ajuda (o padrão é "S"): s

PS C:\Windows\system32> get-executionpolicy

Unrestricted

Pronto agora vamos ao que interessa
Abra o Management Studio e crie uma credencial

Agora, com a credencial criada, precisaremos criar um Proxy para esta credencial, você deve fazer desta forma

Note que aqui eu coloquei o PowerShell e o CMDExec (ambos são necessários)
Agora configure os usuários do SQL que terão direito de usar este proxy (este é o ponto mais importante da segurança, permitir que qualquer usuário faça uso do CMDExec e Powershell pode comprometer gravemente a segurança do seu servidor)

Com a parte de segurança do SQL Server devidamente configurada, vamos ao script
Crie uma pasta no seu servidor, no meu caso optei por um path facil, e crie um arquivo no meu caso WMIPowerShell.ps1

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString ="server=SERVIDOR;database=DATABASENAME;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Aspas="'"
$arrComputers = get-Content -Path "E:\ColetaContadoresWMI\Computers.txt"
$Contador = @{"Data"=$DataHora}
$Contador.Clear()
foreach ($strComputer in $arrComputers)
{
$SO =Get-WmiObject -query "Select Version from Win32_OperatingSystem" -computer $strComputer
$Queue =get-wmiobject -query "select Name,AvgDiskQueueLength from Win32_Perfformatteddata_perfdisk_physicaldisk where Name<>'_Total'" -computername $strComputer
foreach ($objItem in $Queue)
{
$ContadorSrv = $objItem.Name
$Contador[$ContadorSrv]=$objItem.AvgDiskQueueLength
$Valor=$objItem.AvgDiskQueueLength
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv-AvgDiskQueueLength$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$CPU =get-wmiobject -query "select PercentProcessorTime from Win32_Perfformatteddata_perfos_processor where name='_Total'" -computername $strComputer
foreach ($objItem in $CPU)
{
$ContadorSrv = "PercentProcessorTime"
$Contador["$ContadorSrv"]=$objItem.PercentProcessorTime
$Valor=$objItem.PercentProcessorTime
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$Memoria=get-wmiobject -query "select AvailableMBytes from Win32_PerfFormattedData_PerfOS_Memory" -computername $strComputer
foreach ($objItem in $Memoria)
{
$ContadorSrv = "AvailableMBytes"
$Contador["$ContadorSrv"]=$objItem.AvailableMBytes
$Valor=$objItem.AvailableMBytes
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$Espaco=Get-WmiObject -query "Select DeviceID,Freespace from Win32_logicaldisk where DriveType=3" -computername $strComputer
foreach ($objItem in $Espaco)
{
$ContadorSrv = $objItem.DeviceID
$Contador[$ContadorSrv]=$objItem.Freespace
$Valor=$objItem.Freespace
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv-FreeSpace$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
if ($SO.version -like "6.*")
{
$Buffer =get-wmiobject -query "select Buffercachehitratio from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique
foreach ($objItem in $Buffer)
{
$ContadorSrv = "Buffercachehitratio"
$Contador["$ContadorSrv"]=$objItem.Buffercachehitratio
$Valor=$objItem.Buffercachehitratio
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$Stolen=get-wmiobject -query "Select Stolenpages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique
foreach ($objItem in $Stolen)
{
$ContadorSrv = "StolenPages"
$Contador["$ContadorSrv"]=$objItem.Stolenpages
$Valor=$objItem.Stolenpages
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$TargetPages=get-wmiobject -query "Select TargetPages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique
foreach ($objItem in $TargetPages)
{
$ContadorSrv = "TargetPages"
$Contador["$ContadorSrv"]=$objItem.TargetPages
$Valor=$objItem.TargetPages
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
$TotalPages=get-wmiobject -query "Select Totalpages from Win32_PerfFormattedData_MSSQLSERVER_SQLServerBufferManager" -computername $strComputer |Select -Unique
foreach ($objItem in $TotalPages)
{
$ContadorSrv = "TotalPages"
$Contador["$ContadorSrv"]=$objItem.TotalPages
$Valor=$objItem.TotalPages
$Command.CommandText = "Insert into TB_CONTADORESWMI ([Server],[Name],[Value]) values ($Aspas$strComputer$Aspas,$aspas$ContadorSrv$Aspas,$Valor)"
$Command.ExecuteNonQuery()
}
}
}
$Connection.close

Muito bem agora note que há um parâmetro que é alimentado por outro arquivo, semplismente crie-o onde desejar e aponte o path no script, este arquivo que é carregado são todos os servidores que você deseja coletar dados, então vamos supor que você precise monitorar os servidores Server1, Server2 e Server3, basta escrever o nome no arquivo e pronto.

Server1
Server2
Server3

Agora, com tudo configurados vamos voltar ao Management Studio e criar a tabela onde iremos armazenar os dados que coletamos

CREATE TABLE [dbo].[TB_CONTADORESWMI](
[Registro] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [varchar](255) NULL,
[Value] [numeric](18, 0) NULL,
[Date] [datetime2](7) NULL,
[Server] [varchar](255) NULL
) ON [PRIMARY]
CREATE CLUSTERED INDEX [IX_TB_CONTADORESWMI] ON [dbo].[TB_CONTADORESWMI]
(
[Date] ASC,
[Server] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TB_CONTADORESWMI] ADD CONSTRAINT [DF_TB_CONTADORESWMI_Date] DEFAULT (getdate()) FOR [Date]

Com nossa tabela criada, vamos configurar um job para ficar coletando as informações e desta forma alimentar nossa tabela

O tempo do Schedule fica a seu critério, eu configurei a execução a cada 10 minutos, monitorando 8 servidores o tempo de execução do job é de 32segundos.
Com estes dados você será capaz de gerar vários relatórios e acompanhar o consumo de:
-CPU
-Memória disponível
-Espaço em disco
-Buffer CachHitRatio
-Avg. Disk Queue Lenght
Poderá ainda fazer suas próprias implantações adicionando outros contadores (requer um conhecimento básico de powershell).
O resultado deste trabalho foram gráficos apresentados a gestores e clientes onde atuo, recebi 100% aprovação nesta implantação, principalmente por possibilitar a demonstração de dados que softwares do mercado custariam quantias significativas de forma gratuita e muito profissional. Eu utilizei Reporting Services para criar meus relatórios, demonstrando assim um ótimo profissionalismo da solução. Sem contar a flexibilidade que a união das soluções Powershell+SQL Server+Reporting Services nos proporcionam.

Eis um exemplo de uma das implatações:

Interessante para aprender como usar WMI, mas acredito que poderiamos enxugar bastante :

1 - Da uma olhadinha na SQLPSX , la tem um modulo que eu escrevi para coleta de dados. Voce configura XML´s para todos seus servidores com os contadres e com uma linha voce starta a coleta para todos eles, EM MODO ASSINCRONO.

2 - Neste codigo poderiamos usando a Out-DataTable e Write-DataTable , também da SQLPSX, :

$Counters = @(

'\PhysicalDisk(*)\Avg. Disk Queue Length',

'\Processor(*)\% Processor Time'

'\Memory\Available MBytes'

'\LogicalDisk(*)\% Free Space'

'\SQLServer:Buffer Manager\Buffer cache hit ratio'

'\SQLServer:Buffer Manager\Target pages'

'\SQLServer:Buffer Manager\Page life expectancy'

)

Get-Counter -Counter $Counters -computername (get-content c:\temp\Servers.txt) `

| select -ExpandProperty countersamples `

| select @{E={$_.Path};L="Name"},

@{E={$_.CookedValue};L="Value"},

@{E={(Get-Date -Format "yyyy/MM/dd hh:mm:ss")};L = "Date"} ,

@{E={($_.path).Split('\')[2]};L = "Server"} `

| Out-DataTable | Write-DataTable -Database model -ServerInstance R2d2 -TableName TB_CONTADORESWMI

Voce pode colocar um while e ir inserindo :

while ($true){

Get-Counter -Counter $Counters -computername (get-content c:\temp\Servers.txt) `

| select -ExpandProperty countersamples `

| select @{E={$_.Path};L="Name"},

@{E={$_.CookedValue};L="Value"},

@{E={(Get-Date -Format "yyyy/MM/dd hh:mm:ss")};L = "Date"} ,

@{E={($_.path).Split('\')[2]};L = "Server"} `

| Out-DataTable | Write-DataTable -Database model -ServerInstance R2d2 -TableName TB_CONTADORESWMI

}

Netse exemplo Estou fazendo de modo sincrono, usando um .txt com todos os servers no parametro computer name.Mas eu firmamente aconselho a fazer este tipo de operação em modo assincrono, usando background jobs ou runspaces, como é o modulo da SQLPSX :)

| |

Comentários

Laerte Junior

13 jul 2012 8:42

Interessante para aprender como usar WMI, mas acredito que poderiamos enxugar bastante :

1 - Da uma olhadinha na SQLPSX , la tem um modulo que eu escrevi para coleta de dados. Voce configura XML´s para todos seus servidores com os contadres e com uma linha voce starta a coleta para todos eles, EM MODO ASSINCRONO.

2 - Neste codigo poderiamos usando a Out-DataTable e Write-DataTable , também da SQLPSX, :

$Counters = @(

'\PhysicalDisk(*)\Avg. Disk Queue Length',

'\Processor(*)\% Processor Time'

'\Memory\Available MBytes'

'\LogicalDisk(*)\% Free Space'

'\SQLServer:Buffer Manager\Buffer cache hit ratio'

'\SQLServer:Buffer Manager\Target pages'

'\SQLServer:Buffer Manager\Page life expectancy'

)

Get-Counter -Counter $Counters -computername (get-content c:\temp\Servers.txt) `

| select -ExpandProperty countersamples `

| select @{E={$_.Path};L="Name"},

@{E={$_.CookedValue};L="Value"},

@{E={(Get-Date -Format "yyyy/MM/dd hh:mm:ss")};L = "Date"} ,

@{E={($_.path).Split('\')[2]};L = "Server"} `

| Out-DataTable | Write-DataTable -Database model -ServerInstance R2d2 -TableName TB_CONTADORESWMI

Voce pode colocar um while e ir inserindo :

while ($true){

Get-Counter -Counter $Counters -computername (get-content c:\temp\Servers.txt) `

| select -ExpandProperty countersamples `

| select @{E={$_.Path};L="Name"},

@{E={$_.CookedValue};L="Value"},

@{E={(Get-Date -Format "yyyy/MM/dd hh:mm:ss")};L = "Date"} ,

@{E={($_.path).Split('\')[2]};L = "Server"} `

| Out-DataTable | Write-DataTable -Database model -ServerInstance R2d2 -TableName TB_CONTADORESWMI

}

Netse exemplo Estou fazendo de modo sincrono, usando um .txt com todos os servers no parametro computer name.Mas eu firmamente aconselho a fazer este tipo de operação em modo assincrono, usando background jobs ou runspaces, como é o modulo da SQLPSX :)

Tópicos Populares

Ajuda & Opinião

Popular Contributors

Monitorando o SQL Server com PowerShell

Produtos

Recursos

Soluções

Atualizações

Avaliações

Sites relacionados

Treinamento

Certificações

Outros recursos

Por produtos

Outros links