I´m Sorry by my english, hope you guys can understand 🙂
I believe that you have read the Blog Post from Chad Miller called The T-SQL Hammer. It is a great example of productivity in Powershell.
I took the liberty to use the same name in this Blog Post , because reading his words:
“The over-reliance on a familiar tool is best described with the quote, “if all you have is hammer, everything looks like nail” and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL. And why not? In many cases T-SQL provides an ideal solution to SQL Server administration scripting problems, however there are certain scenarios where another tool, Powershell provides a more elegant solution.”
I belive we can show another example in “why use powershell ?”.
Surely this is a great article that Rodney Landrum shows , a very interesting solution to consolidate SQL Server Error Logs and you can read in here: Consolidating SQL Server Error Logs from Multiple Instances Using SSIS
In this article he shows how to use SSIS and some T-SQL to do this operation. One of the things changed in my version is that I would not use the “merge statment” .
I have one Server and Database repository, one table repostory to save the logs and I created another control table called ServersInfo with only two columns, ServerName and last collection Date.
So when I access the error log, I filter by date always above or equal the latest date recorded in this table.
But lets see the code :
First T-SQL to create the repository table and Server/date control table. Obviously you will create these tables on your server and database repository.
Now let’s populate the Servers table 9Serversinfo) with all servers to have the error logs collected.
After that, the Powerrshell code : remember I am using SQLPSX
I’m not handling errors, but it is very simple to implement. Just a try catch before the SQLPSX functions e we close all possibilities of error logging them into a file. You can find this code in my Simple-Talk Articles.
With that , the data is collected and save into SQL Server table.
We can schedule to run this script once a day and OK, we will not have much information to bring the servers and I believe it is a good period for collecting the data.
You can apply summations, aggregates, filters and whatever is necessary to set up your monitoring 🙂
See you later 🙂
POWERSHELL ROCKS !!!
“Once there was this kid who
Got into an accident and couldn’t come to school
But when he finally came back
His hair had turned from black into bright white
He said that it was from when
The cars had smashed soooo hard
Mmmm Mmmm Mmmm Mmmm, Mmmm Mmmm Mmmm Mmmm
Once there was this girl who
Wouldn’t go to change with the girls in the change room
But when they finally made her
They saw birthmarks all over her body
She couldn’t quite explain it
They’d always just been there”