July 31, 2017 at 2:04 pm
I have been reading articles over the last 6 months that have come my way which discuss the crossroads of Powershell and DBA responsibilities, but I'm just not seeing the utility. I feel like there's something I'm missing. For instance, I'm currently listening to an instructional video on the topic, and it's not convincing (they were describing how Powershell can tell you an instance's compatibility version, and now demonstrating how you can list table names by database).
If you're willing to share, what was your "Aha!" moment, when you realized that Powershell was actually offering you something you didn't have, and wasn't just another means to an end?
Thanks,
--=Chuck
July 31, 2017 at 2:07 pm
When I needed something outside of SQL Server 😉
Within SQL, I expect I'll be using a LOT of powershell shortly as I begin a migration of 300 databases to Azure SQLDB. Provisioning servers, etc. PoSH will save a lot of time.
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
July 31, 2017 at 2:22 pm
I've had a couple of aha moments, and like Kevin3F noted, it for things that are outside of SQL servers control, but inside the DBA's sphere of responsibility.
the first big one was documentation: I needed/wanted to export the definition of every object, in every database, so that I could stick it in source safe.
on every database.
on every server.
powershell, reading my Central Management servers as the input list, and using SMO made it easy.
My second big one was when I landed at a brand new consulting job, and the need arose to export every SSIS package from the SSISDB on some servers, from msdb on other servers, and, of course, export out all the SSRS reports from the ReportServer databases.
Another big one was exporting all Active Directory users and groups into a table via Powershell, so they could be queries.
now my library of PowerShell scripts is almost 150 examples, that I use as my base for a variety of other things...deleting old backup files, exporting linked servers with the password that was used by their credential, taking over a server with lost sa passwords, and so much more.
Lowell
July 31, 2017 at 2:28 pm
I use PowerShell scripting periodically, but mainly for things that would be too complicated to script in a .CMD batch file. Those handle parameters well, but calculations and expressions can be awkward or impossible in some cases. PowerShell treating everything like a set has a natural fit for us database type people, but I find the syntax a bit cumbersome at times to do simple things. I suppose it will become indispensable to me when a script can more easily be called from Windows Task Scheduler... and they decide to depreciate .CMD scripting. 😉
July 31, 2017 at 2:29 pm
My big moment for PowerShell was when we were moving the data center. We had to physically disconnect all our drives and move all the hardware into a new location. As part of the process, we needed to run backups across hundreds of servers and thousands of databases. We were doing it in shifts. I was the second shift. I got there and the first DBA told me how he opened connections to 20 or so servers and then started running T-SQL on each one to run backups and then monitored the query windows to see which ones were finished. I almost threw up.
I had just started working with Powershell, but I knew there was a way to do parallel execution. I quickly figured it out, wrote up a small script that connected to a list of servers I provided and then we ran our "backup all the databases" T-SQL through the PowerShell connections. The output was to a series of files, so I could review to ensure there were no problems. I took a process that had taken about 6 hours for the first run down to less than an hour for the second run (plus about 45 minutes scripting time while I figured out a couple of things). I then passed that script on to the next shift and the entire process sped up.
For what we needed, yeah, I could have written it in almost any language, but PowerShell was there, it was easy, and it worked.
Now, the biggest bang I see is with Azure stuff. It's so much easier to control things through calls to ARM from PowerShell than using the GUI.
"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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply