April 6, 2009 at 4:22 pm
At PASS Summit 08, I was introduced to Powershell, and I was pretty excited about using it to track information about some of the SQL servers at my company. I'm aspiring to become a DBA, but I'm a developer at the moment. I was wondering if there were other DBA's out there or general administrators who use Powershell to monitor and track information about their servers. If so, what information are you tracking? Is powershell a good administrative tool to be using or should I focus on something else?
I'm currently tracking disk space, SQL job errors, SQL log file sizes, SQL data file sizes, last SQL backup dates, system errors, and application errors.
Thanks for your time and input
April 6, 2009 at 4:48 pm
Powershell is being built into Windows and Exchange as the admin language for systems from Microsoft. I bet that you'd find tons of people using it in other areas with Windows systems. I'd run a Google search for monitoring servers with Powershell.
April 6, 2009 at 7:03 pm
We've started using PowerShell more and more, but not so much for monitoring since we use Operations Manager for that. We've mainly been using it as a means for cleaning up backups & log files. I've also created a script that simply verifies connectivity on all our servers. As soon as I learn how to spawn threads (can you?) I'll create a massive DBCC check to run against all our servers after a power down event (we get one a year and they're a royal pain in the back side).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 6, 2009 at 10:01 pm
Grant Fritchey (4/6/2009)
We've started using PowerShell more and more, but not so much for monitoring since we use Operations Manager for that. We've mainly been using it as a means for cleaning up backups & log files. I've also created a script that simply verifies connectivity on all our servers. As soon as I learn how to spawn threads (can you?) I'll create a massive DBCC check to run against all our servers after a power down event (we get one a year and they're a royal pain in the back side).
Not in v1 (AFAIK) - but, I hear that v2 not only has remoting capabilities but also the ability to create multiple threads (jobs).
I use powershell scripts to get information loaded into a central repository to track data space usage, last backup, version info, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 7, 2009 at 5:23 am
Jeffrey Williams (4/6/2009)
Not in v1 (AFAIK) - but, I hear that v2 not only has remoting capabilities but also the ability to create multiple threads (jobs).I use powershell scripts to get information loaded into a central repository to track data space usage, last backup, version info, etc...
Ah, that stinks. Well, then I'll still be running a whole bunch of TSQL windows in SSMS like usual. It works well enough. It's just a pain in the bottom to set up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2009 at 8:24 am
I'll have to check out Operations Manager. It appears there is a lot you can do with Powershell, and I wasn't sure what other people on this site were using it for.
Thanks for your replies.
April 15, 2009 at 2:41 pm
Some of the things that I have been using it for on some of our lower level servers (that we do not have licenses for commercial monitoring tools) are to:
verify SQL Agents are Running
verify SQL connectivity
Ping Servers
Send messages on failures of these things
storing all of this sort of data within SQL for reporting later
These things can be useful if you can not afford Idera DM or Quest Spotlight for your servers, since it can give you most of that same inforamation.
As mentioned above we also use it for space tracking on all of our servers (SQL and Non-SQL). WMI is great for that sort of thing.
I have also found Powershell useful if you are the kind of person automated copying/deleting/maintenance of files. It gives a very easy interface for that sort of thing, and once you create the script you can just keep calling the same script with different arguments, which makes it very flexible.
I am always looking for other ideas to use PS for so I would love to see if anyone else has thoughts.
May 20, 2009 at 8:45 am
I've used it to help create a script to delete old backups that SQL Server wasn't getting rid of and also to create an at a glance report to tell me what agent jobs have failed, what errors are in the sql log, and disk space available quickly. I don't have the luxury of admin tools at a lot of my clients so I've started creating some things on my own that are useful.
May 21, 2009 at 2:09 pm
I am actually excited at the possibilities of automating a lot of my work with Powershell. I enjoy scripts (having had fun with shell scripts and Perl in the past), and if I could integrate them in a way that saves a lot of time for me, I'm all for it.
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein
May 22, 2009 at 10:57 am
I've started using PowerShell only recently myself. Personally, I was really disappointed when they replaced SQL-DMO with SMO/AMO/RMO because they are not scripted interfaces. For me, the reason I love powershell is that it has turned SMO into a scripted interface again.
I've posted some powershell scripts on my blog, most of which came out of my powershell script for database mirroring. I wrote a powershell script that performs the entire setup of database mirroring from the script. The real power of it is that I can run the script from my laptop to set up mirroring on remote servers that do not have powershell installed.
That script won't be on my blog though. You'll have to buy my book to get that one. 😀
January 8, 2010 at 9:37 am
Has anyone used PowerShell 1.0 to configure ReportServices in Sql Server 2008?
July 9, 2012 at 6:08 am
I have just started to use it.....so far I have created scripts to:
1. Show all SQL Agent jobs with status - across all of my SQL servers
2. Show all backup dates - across all of my SQL servers
3. Collect information on my databases - across all of my SQL servers
All 3 reports get put into an excel spreadsheet.....
I would be interested in how you filter out the log messages across your servers....
July 9, 2012 at 9:29 am
If just starting out I highly recommend getting into PowerShell. It really has no peer in the Microsoft stack in terms of a command-line scripting tool. VBScript and JavaScript are not in the same league. A Windows build of Perl, or a combination of Perl plus some of the Linux/Unix shells running in Cygwin might be the closest comparison I could make. I use PowerShell for lots of things. Since I am almost always in an environment where I am responsible for some aspect of multiple instances PowerShell is where I go. Running the same script against multiple instances can be done in a SSMS multi-query window, but that's a manual chore and quite a PITA if doing something non-trivial or trying to compile results for more analysis. PowerShell lets me automate most everything. My most ambitious use of PS to date is a script that runs a set of Policies against ~20 SQL 2008R2 instances, stores the results in a table and emails a report of failed policies. I also use it to read Windows Event logs on all those servers and bring them into a central table so I can report on Windows errors. PowerShell is a strategic play for Microsoft so it is a safe bet to invest in learning. I would encourage you to embrace it, especially now since you're just starting out and already have a programming background. I think it would be a natural fit for you.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply