PowerShell is a Hit
If the feedback from our readers today is representative of the PowerShell experience as a whole, I would say it has a great following from the SQL Server DBA crowd.
Many of us work in shops where the high end monitoring tools are too expensive to cost justify. However, with a little learning, a great deal of monitoring may be done safely with PowerShell.
Here is a blog where a developer generates a server status report and sends it through email. The report is nicely formatted with HTML and even contains graphs driven from a PowerShell script…that’s just crazy cool.
Here’s some feedback from yesterdays editorial…
Colin:
I just read your article and I just thought I would reply. I agree that Powershell is a fantastic tool, and I think it is an excellent tool in the DBA toolbelt. I think that not enough DBA’s have taken PowerShell seriously and I think that we are only going to see more and more cmdlets to interface with PowerShell. SQL Server 2012 did introduce some new ones. But as you point out, Powershell makes it so easy to do things like monitor, and not just at the database level. You can use WMI to check for your SQL Server processes, you can check disk space, and so many other things. And again as you point out, you can send out alerts based on what Powershell finds. I had a script that would check for the agent service and if it was down it would attempt to start it. If the agent could not be started, then the script would notify the DBA’s. If the box was a production box, the DBA would be paged. If Dev then the DBA would just see a note in his inbox in the morning.
That is just a small example of how I have used Powershell. I have blogged about it, among other things, at http://itknowledgeexchange.techtarget.com/dba/. Anyway, good article and I hope that it gets more DBA’s to learn powershell.
Roger:
How many of those are you getting? If they’re happening about once an hour, then that definitely seems consistent with the consistency jobs run with the replication. We have seen this from time to time, but the impact has been minimal in the other cases.
The consistency jobs do leverage VSS to quiesce the filesystem and applications to ensure application-consistent points in time.
Short answer, I would expect this, and as long as the impact is acceptable, we’re good to go. If you’re seeing a significant impact, let me know and we might be able to work out a plan.
Jason:
I work with approximately 50 instances of SQL Server in a clustered environment. I needed a tool to monitor active processes, locks/blocks, SQL log, backup status, and other pertinent items. Our budget did not allow for a third party tool to assist us in monitoring the environment. When I took Ed Wilson’s mini-session on PowerShell during a SQL Saturday, I was thoroughly impressed by its capabilities.
Over the last year I started piecing together scripts that can monitor the environment in a snapshot. I use PowerShell every morning to get an overview of the system status and what occurred over the past day. It only takes a few seconds for the script to run and display the critical information I need. When I get a report that there is a production issue during the day, the PowerShell scripts are my first line of defense in the clustered environment.
In a nutshell, PowerShell is one of the best tools I have encountered to help me with my day to day job. The best part is that there is no extra cost in using it!
John:
I’ve been working with Powershell for 4 years now and it’s a DBAs dream for two reasons:
- It erases software boundaries. Powershell hits SQL server DMO, SQLCMD) , IIS, the registry, Event Logs, Folder structure,WMI and can leverage .Net components while still being able to leverage legacy VBscript, CScript, any EXE you want, DOS commands and batch files – all in one scripting platform. I’ve written scripts to give me "full picture analysis" of my SQL Servers so I could drop in and get a full picture of whats going on and then email the results. This also allows me to continue my investment in all my utility scripts I’ve built over the years.
- Powershell has Job scheduling and remoting capability built in so if you don’t have access to the SQL Job agent (like at many hosting providers such as GoDaddy or DiscountASP.Net) or Windows Tasks Scheduler (because you’re the just a Non-DBA developer type and they separate responsibilities of Developer, DBA and Windows Admin), you can still schedule jobs on remote machines.
By the way, on that Services example you may want to be careful about hitting the services the "get-service" way. There’s two ways to hit the SQL services gui-style and Microsoft recommends the use of SQL Server Configuration manager because of additional calls it makes that the Windows Services component doesn’t. The powershell SQLDMO equivalent is a safer bet and you’ll find more options in the ServerInstance.Services collection of SQLDMO. I have a script that sweeps the network and if the SQL server instance isn’t running, my script tries to start it up, applies whatever changes I want, then shuts the server down again. I did this because we were changing notification settings and wanted to be sure all servers had the same settings. I’m even able to change the service account and password as well.
Only one thing remains on my Powershell wishlist: Multithreading. Closest I’ve seen is the use of Powershell Job scheduling but I haven’t tested the full limitations of that yet. Powershell 2.0 is a SQL 2012 installation requirement too and Powershell 3.0 is in the pipeline with more goodies.
Max:
How many of our readers have become proficient in PowerShell? That’s a good question. In Florida, I’ve been evangelizing about PowerShell and SQL Server since 2007 in most our existing IT Community events: SQLSaturday, Code Camps, and IT Pro Camps. I can tell you that within the last few years I’ve seen an increase of interest in using PowerShell with SQL Server. But, it’s all about having the SQL Administrator (and Developer) to stepup to the plate and realized that this a valuable tool. Nowadays this is a very important skillset and I’ve recently seen a lot of recruiter asking for it.
To learn PowerShell isn’t hard, it evolves with you. You have all the help at your fingertips, and the PowerShell community is always willing to help. In SQL Server you need to break the ice in using SMO which is the heart and soul of the SQL Server PowerShell cmdlets. For example, the following few lines of PowerShell with SMO build a .NET PSobject with wealth of information about your SQL Server engine:
## – 1. Loading the SMO .NET Assemblies
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")
## – 2. Add SQL Server name:
$SQLServerName = ‘.’;
## – 3. Collect all your SQL Server Database Engine information into a PowerShell .NET Objects:
$SQLobjects = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerName;
## – 4. Select some items objetc and display on screen some information:
$SQLobjects.Information | Select Netname, Product, Edition;
If you’re curious how we got the selected items, then you can use the "get-member" cmdlet to explore your $SQLobject:
## – Exploring your PSobject displaying result to screen:
Get-Member | $SQLobjects
Executing the above commad will provide you with a list of your collection of .NET objects create by line "## – 3", and in here is where select some of its properties: such as Netname, and Product.
The rest is all about getting familiar with your tools, start with a simple script and let it grow. Once you start using PowerShell, you will never stop.
You don’t want to lose a job opportunity just because you didn’t took the time to learn PowerShell.
Thanks for all the great feedback sent to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Materialized Query Tables…and the Death of Denormalization?
This brief overview of MQTs should help to show how you can optimize access to complex data structures. With MQTs, DBAs can create a fully normalized physical database implementation – and then create “denormalized” structures using MQTs. This brings the benefit of data integrity because the database is fully normalized, along with the speed of retrieval using materialized query tables.
Featured White Paper(s)
Go Beyond SCOM Monitoring with Foglight for SQL Server
read more)
Featured Script
dba3_fx_LuhnCheckDigitValid
checks argument returning 0 or 1 based on valididty of included final checkdigit in the argument… (read more)