SQL Saturday Chicago #291 – 2014 – I’m speaking

I am very honored to say that I am speaking at SQL Saturday Chicago 2014. A LOT, really a LOT, of  very well known-names in the SQL Server Community will be there and I have the opportunity to be there with them.

My Session will be :

How to properly gather performance counter data using Windows PowerShell

We DBA´s know how important it is to collect performance data. In this session, I will show to you how to properly use the native Get-Counter PowerShell cmdlet to get all information you need from your servers. It has never been as easy to collect performance counter data and save it to a CSV file or a SQL Server table for baselining and later analysis. You may be thinking “But I don’t know nothing about PowerShell and explicitly using .NET classes in my code”. Don’t worry; you don’t need to know this to collect counters. Although this is a 100-level session, you’ll be producing 400-level results and your scripts will scale as the number of servers increase, because we’ll use asynchronous collection and processing. And better yet, we’ll schedule all of it. All this using native PowerShell cmdlets and a minimal amount of script code

Hope to see you there :)

Thanks again for the SQLSaturday Chicago Staff.

http://www.sqlsaturday.com/eventhome.aspx

 

Posted in Algo que Esqueci de Categorizar | Leave a comment

Novo emprego, novo País.

Pois é galerinha, to muito feliz em dizer que após uma exaustiva série de entrevistas, fui selecionado para trabalhar na Costa Rica como DBA de Infra-Estrutura na empresa Intertec International, uma multinacional americana com base nos USA (claro), Europa e Costa Rica. O cliente que eu atenderei é um das maiores Instituições Financeiras dos USA.

Dia 8 agora, sábado, viajo para os USA e fico em treinamento durante 2 meses e depois me mudo para a Costa Rica (Mãe to no Caribe).

Pelas conversas que tive, meu time é composto por excelentes profissionais (incluindo uns caras muito bons em PowerShell) e o parque de servidores é gigantesco. Isso será perfeito para aprimorar meus conhecimentos em PowerShell, SQL Server, automatização de processos. Também será uma oportunidade para melhorar meu Ingles e aprender Espanhol.

Meus amigos mais chegados sabem o quanto eu batalho para uma oportunidade dessas, então imaginem minha felicidade :)

Haaaaa..para aqueles que ainda perguntam se precisam aprender PowerShell, minhas entrevistas técnicas foram em média 10% SQL Server, 10% Windows e 80% PowerShell e automatização de Processos. Aprendam PowerShell

É isso aí. Vamu que Vamu !!!!

Posted in Algo que Esqueci de Categorizar | 4 Comments

PowerShell 4.0 PipelineVariable, With or Without, I can´t live…..Without you.

I am writing my new Simple-Talk article of the series The Posh DBA. This one is how to properly use Get-Counter to gathering Performance Counters (windows/SQL/.net..etc) and save to a csv or a SQL Server Table. While writing , I came across an cool example of how to use the new common parameter.

In this article, I am showing that you dont need to know .net classes or even have a intermediate knowldge of PowerShell to write your script to collect the counters. Why ?

Because we are using only the native cmdlets of PowerShell 3.0 and with a script code level 100, I will show to you how to write a process level 400, as collect the data for several servers in asynchronous mode and in background or  even better,  scheduling the entire process using PowerShell 3.0 Scheduled Jobs. I ´ve submitted a session showing all this process to SQLBITS and SQLPASS as well. Fingers crossed

Welll, but this post is about the new PowerShell 4.0 common parameter pipelinevariable. My fellow PowerShell MVP and guru Keith Hill already wrote about it and his EXCELLENT  post is plenty of examples. You should read it before this one, because I am only showing a practical example that I am using.

Keith Hill – PowerShell V4 – PipelineVariable Common Parameter

Generally speaking, this new common parameter allows you to store in a variable  the current pipeline object to use in the further pipeline process. Why that is so F Awesome Amazing Cool ? Just because I dont need to use , as in my example, a Foreach-Object just to create the variable to store the current pipeline object.

Lets see my code without PipelineVariable :

Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' | ForEach-Object { $CounterCategory = $_ $CounterCategory | Select-Object -ExpandProperty Counter | ForEach-Object { $CounterName = $_ $CounterName | Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} | Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"}, @{E={$CounterCategory.Description};N="Description"}, @{E={$CounterName};N="Counter"} } } | Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation

In this example I need to store the first pipeline to $CounterCategory and the second one in $CounterName variables , but I need to use Foreach-Objects , to allow me to use the information for these objects in the further pipeline with Select-Object. If I dont do that I dont have the information of my first  and second pipeline objects :(

With the New Super UItra Yo-Bro PipelineVariable common parameter that is possible and I dont need to use Foreach-Objects, because the current pipeline object is stored in the variable defined in the pipelinevariable parameter :) :

Get-Counter -ListSet 'LogicalDisk','SQLServer:Buffer Manager','SQLServer:Memory Manager' -PipelineVariable CounterCategory | Select-Object -ExpandProperty Counter -PipelineVariable CounterName | Where-Object {$CounterName -match '(sec/Transfer|Avg. Disk Queue Length|Buffer Cache|CheckPoint|Target Server|Total)'} | Select-Object @{E={$CounterCategory.CounterSetName};N="CounterSetName"}, @{E={$CounterCategory.Description};N="Description"}, @{E={$CounterName};N="Counter"} | Export-csv C:\temp\BaselineCounters.csv -NoClobber -NoTypeInformation -Append

PipelineVariable,

With or without you
With or without you
I can’t live

……. without you

Posted in Algo que Esqueci de Categorizar | Leave a comment

PASS PowerShell VC Meeting of March with Kendal Van Dyke

SQL AGENT JOB STATUS REPORTING WITH WINDOWS POWERSHELL

A critical, yet tedious, task DBAs perform on a daily basis is making sure SQL Agent jobs have run properly. Manually checking each instance is slow and doesn’t scale beyond a handful of servers. Agent alerts are helpful but can easily get lost and forgotten in your inbox; Monitoring tools make things a little easier – provided every one of your instances is monitored. What we really just need is a simple and efficient way to see the status of jobs across all instances. Windows PowerShell is a perfect fit for the task, and in this session you’ll learn how to automate building a daily report so that you can see what’s happening with SQL Agent jobs in one place.

Kendal Van Dyke is a practiced IT professional with over a decade of experience in SQL Server development and administration. He is currently a principal consultant with UpSearch SQL, where he helps companies keep their SQL Servers running in high gear. Kendal is a Microsoft MVP for SQL Server, a Microsoft Certified Professional in SQL Server 2008 and 2012, and president of MagicPASS (South Orlando’s PASS Chapter). He also serves as a Regional Mentor for the US Southeast Region of PASS.

 

Link to Register :

http://powershell.sqlpass.org/

Posted in Algo que Esqueci de Categorizar | Leave a comment

Some reasons to Darth Vader being a badass….

1 – Interview with the new System Administrator :

What? what you mean “I prefer next, next, finish”

powershell1

2 – After a nice vacation at Korriban (homeworld of Sith), Vader returns to Death Star and get the reports of the SQL Server servers :

Emperor :

“Vader, our new DBA wrote a cool backup process..humm..mmmm..on each server”

powershell2

3 – No Comments

“I agree, you never will spend, again, half of the morning checking jobs that failed. Apology accepted”

powershell5

4 – Checking the time frame of the Death Star employees

“WTF ? Is it true that you spent 1 hour manually repeating the same process on each server ?”

powersghell6

 

Dont be a “next, next, finish IT Professional”. Use the Force, or as it is known in the Galaxy, PowerShell :)

Posted in Algo que Esqueci de Categorizar | Leave a comment

New Position, New Country

I am very happy to say that I am moving to Costa Rica (yes…in the caribbean place) to work as Infrastructure DBA at Intertec International. My team, that have some very good guys with PowerShell and SQL Server (that is soooo cooool), will assist a large USA Finantial Institution.

It will be a amazing opportunity to learn more SQL Server and PowerShell, English and Spanish. Plus I will be in one of the most beatiful places in Central America.

I am traveling in a couple of weeks to USA for a training and then CR. I am excited, now it is truly the kind of opportunity I was looking for.

Where I will be ? Take a look :)  http://www.anywherecostarica.com/

I

 

Posted in Algo que Esqueci de Categorizar | 15 Comments

Being notified when a child background job is done

PowerShell is so cool that you can be notified when a background job is done. I will not show how to do that, because you can find an excellent explanation about that in this blog post from my good friend Boe Prox .

PowerShell and Events: Object Events

My point is , have you ever tried to use –asjob on Invoke-command ? It will run the scriptblock in a background job. cool !!! But, if you pass more than one server to run this process, lets say 4 servers,  it will create to you one job “Father” and 3 others child jobs. That father  job will  control and report the success of failure of the entire process (all the child jobs)

In PowerShell 2.0  you need to expand the property childjobs to know them. To know what jobs are child  I will create a simples process to sleep the job in a random count. I am using more than one server (in my case the same server more than one time)

If we use the PowerShell 2.0 and get the object $job we will see only one job. The father one :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader'

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id

The result will be :

image 

As we create the job for 3 servers, to check the other ones we need to expand the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs

image

 

Now imagine that you are dealing with a lot of long-running process in background jobs in different servers. How can I  check the child jobs status ?(to check how it is going the process in each server)  ?

PowerShell 3.0,  2 new parameters was added. They are -IncludeChildJob –ChildJobState. Lets take a look in the –IncludeChildJob

If I run the same command and now get the job but with –IncludeChildJob  parameter :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob

image

Cool !! Backing to our example, I have a lot of long-running process in background jobs in different servers. Now I want to know when they finished. I can use the example that I mentioned in the beginning of this post, but it will notify you only when all the child jobs finished because the event is subscribe to the father job. I want to be notifed for each child job in the process. Let´s say that I want to know when he process in each server finished.  The parameter –ChildJobState only will show for me the jobs with the specified state. I will not be notified.

Well, the idea is the same to the father job, I just need to go in each child job and register an event for each one :)

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id -IncludeChildJob | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }

image

if you are using PowerShell 4.0, you can use the new foreach method :)

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' (Get-Job $job.id -IncludeChildJob).foreach({ Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } })

Or you can do the same in the PowerShell 2.0, but expanding the property childjobs :

$job = Invoke-Command {start-sleep (get-random (1..500))} -AsJob -computername 'Vader','Vader','Vader' Get-Job $job.id | select -ExpandProperty childjobs | % { Register-ObjectEvent -InputObject $_ -EventName StateChanged -MessageData $_.Id -SourceIdentifier "Job.Monitor$($_.id)" -Action { write-host "Job $($event.sender.id) has changed from $($event.SourceEventArgs.PreviousJobStateInfo.State) to $($event.SourceEventArgs.JobStateInfo.state)" -ForegroundColor Yellow if ($event.SourceEventArgs.JobStateInfo.state -eq 'Completed') { $eventSubscriber | Unregister-Event $eventSubscriber.Action | Remove-Job } } }

Yes..I am using write-host. In this case as I just wanto to show in the screen, I dont need any output piped .

You can change the notification to send email, speak..whatever. It is up to you :)

Posted in Algo que Esqueci de Categorizar | 2 Comments