PERFCOUNTERS Module Updated – work with multiple servers and save directly into SQL Server Table


 

Hi all,

Finalized some changes in the modules before and show them I want again to thank Sir Allen White (Twitter | Blog) , who was the post that inspired me to build this module. Essential reading –> Performance data Gathering

If you want, the others pots about are :

Performance Counters Gathering using Powershell
PERFCOUNTERS Module Updated – works as background job,bulk insert to SQL, Error Handler and help

 

Some changes are made as work with multiple servers and  save into SQL Server Table, and all tests were done in 2 Hype-V 64 bits  Virtual Machines. One Is a Windows 2008 Server R2 domain controller with SQL Server 2008 R2 (Obiwan) and the other Workstation  Windows 7 with SQL Server 2008 (Chewie) :

Work with Multiple Servers

first have to enable Remote Registry service on remote machines if it is disabled.

image

If you want to read more about , I suggest –> Why run the RemoteRegistry Service?

at this point, I need to thanks again my good friend and Powershell/SysAdm Guru Shay Levy (Twitter | Blog).  Without his help, probably I have spent much time with this problem.

Now, You can Pipe the servers to Get-PerfCounterCategory function :

Get all counters starting with page life* in Buffer Manager category in servers ObiWan and Chewie

   1: "ObiWan","Chewie" |  Get-PerfCounterCategory -CategoryName "*Buffer Manager*" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "page life*" 

image

You can also create a Txt file with the servers and use :

   1: Get-Content c:\Temp\Servers.txt |  Get-PerfCounterCategory -CategoryName "*Buffer Manager*" | Get-PerfCounterInstance | Get-PerfCounterCounters -CounterName "page life*" 

 

image

Let’s create a complete example First choose the counters that we use. In case all the SQL Server Buffer Manager category in Chewie and ObiWan machines and save into c:\temp\BufferManager.XML

   1: "ObiWan", "Chewie" |  Get-PerfCounterCategory -CategoryName "*Buffer Manager*" | Get-PerfCounterInstance | Get-PerfCounterCounters | Save-ConfigPerfCounter -PathConfigFile c:\temp\BufferManager.XML -NewFile

 

And ONE FILE for each server will be created, using the name passing in –PathConfigFile parameter adding the server name :

image

Now, with the XML configured we can start the gathering using backgrounds Jobs and saving  into a SQL Server Table. We do not pass the table name as parameter so one table for each server will be created using the name PERFCOUNTERS_XMLNAME_YYYYmmDDhhMMss. You always have to pass the path to output file, even using SQL Table, because the output file alwasys will be created. Why ?. Lets say you lost you connection to the SQL Server respository. you do not lost the data, because they will be in txt file too.

   1: dir "c:\temp\*.Xml" | Set-CollectPerfCounter   -DateTimeStart  "05/24/2010 08:00:00" -DateTimeEnd "06/30/2010 22:00:00" -Interval 10  -PathOutputFile c:\temp\BufferManager.txt -ServerName ObiWan -DatabaseName Testes -NewTable -RunAsJob

image

 

And two jobs are created. Note the name of jobs starting using PERFCOUNTER, so you can use where-object to find all jobs used by Perfcounters Module :

image

Now if we see in ObiWan SQL Server, there are two tables : one for each server

image

and the data is gatheing in each server and save in respective table in background

 

image

And the TXT files with output gathreing are created too :

image 

You can use your table name too : I am passing the parameter –TableName with “BufferManager”..and for each server will be created one table called BufferManager_SERVER..in this case BufferManager_Chewie and BufferManager_ObiWan.

   1: dir "c:\temp\*.Xml" | Set-CollectPerfCounter   -DateTimeStart  "05/24/2010 08:00:00" -DateTimeEnd "06/30/2010 22:00:00" -Interval 10  -PathOutputFile c:\temp\BufferManager.txt -ServerName ObiWan -DatabaseName Testes -NewTable -RunAsJob -TableName "BufferManager"

image

Why I can do this ?. Lets say if yu stop the gathering and want to continue in another day, but in the same table. Just Pass the TableName parameter with the name of the table and not pass the switch parameter –newtable :

Remember, in this case you have to explicity the XML file.  Ih this case I will start again the gathering to Chewie, with xml file c:\temp\BufferManager_Chewie.xml and save to BufferManager_Chewie table.

   1: Set-CollectPerfCounter   -PathConfigFile c:\Temp\BufferManager_Chewie.XML -DateTimeStart  "05/24/2010 08:00:00" -DateTimeEnd "06/30/2010 22:00:00" -Interval 10  -PathOutputFile c:\temp\BufferManager.txt -ServerName ObiWan -DatabaseName Testes  -RunAsJob -TableName "BufferManager_Chewie"

 

The data is saved without creating new table and the output txt file is always created, as a data backup.

In this examples I use Windows Authentication, but you can pass the username and password as parameters.

For more information type get-help <function>  -examples.

I hope you like :)..Any questions or comments are welcome.

DONWLOAD PERFCOUNTERS MODULE 1.1

 

A big hug to everyone

POWERSHELL ROCKS !!!

“Oh, come on, come on, come on, come on!

Didn’t I make you feel like you were the only man —yeah!
Didn’t I give you nearly everything that a woman possibly can ?
Honey, you know I did!
And each time I tell myself that I, well I think I’ve had enough,
But I’m gonna show you, baby, that a woman can be tough.
I want you to come on, come on, come on, come on and take it,
Take it!
Take another little piece of my heart now, baby!
Oh, oh, break it!
Break another little bit of my heart now, darling, yeah, yeah,yeah.
Oh, oh, have a!
Have another little piece of my heart now, baby,
You know you got it if it makes you feel good,
Oh, yes indeed”

Piece Of My Heart

Janis Joplin

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

5 Responses to PERFCOUNTERS Module Updated – work with multiple servers and save directly into SQL Server Table

  1. Demétrio says:

    Excellent Laertes. Remember when we met? I talked to you about using the PS to collect and process data from PerfMon. Excellent article my friend.Regards,

  2. Laerte says:

    Of course I remember Man..hehehe..Now it is alive 🙂 Thanks my friend .

  3. Matt says:

    Hi, I can’t seem to find the download the link about takes me to SQLPSX? Is this still around for download?

  4. Russell Young says:

    could you email me a the new version, can’t find it either

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s