Usages for Powershell

Question

Usages for Powershell

JaybeeSQL

Hall of Fame

Points: 3501
More actions
May 24, 2015 at 7:49 am

#298519

Hi all,
Would appreciate any and all ideas on how Powershell has made your work more efficient, how you've saved time etc.
I know one great feature is that it allows you to run the same command across multiple servers simultaneously. Not so much interested in HOW to configure PS, but rather what you used it for, and the benefits.
Cheers!!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 1

I don't think being able to run commands across multiple servers or instances is the greatest feature....at all.

I use it to complete day-to-day tasks, troubleshooting (standalone and cluster), gathering metrics, document server configurations, create report on server config and performance metrics for clients. I use it to access information when I don't have permissions through the GUI (e.g. if you don't have Local Admin rights to view all processes via Task Manager, Get-Process cmdlet still works for you).

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

simon.harvey 41170 SSC Enthusiast Points: 127 More actions · Answer 2

I'm not sure about SQL Server but there are certainly operations against SharePoint that are impossible through any means other than powershell - so that's a bit of a productivity boost 😉

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

I use PowerShell in a manner similar to what Shawn posted and probably don't even need it for that but it was convenient because someone posed a loop example that seemed a bit easier than using a DOS FOR command. What I actually use if for tis to get a disk status from all of my servers. I have an xp_CmdShell call that calls PowerShell that loops through the servers I have in a table that issues a WMI command for each server that returns the data to xp_CmdShell output that gets loaded into a table that has computed columns to parse the information and then an INSERT statement to save it all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wayne West SSC-Insane Points: 22586 More actions · Answer 4

There was an editorial just a week ago on scripting, and lots of people talked about PowerShell and other methods of doing things. I've used Perl for monitoring several servers and will be developing an Access/SQL Server/PowerShell script for adding users. I could not have imagined doing things like that 10-20 years ago.

Tech is pretty awesome.

http://www.sqlservercentral.com/articles/Editorial/125869/

And if you look at the left margin of this site at the Stairways series, there's a Stairway to PowerShell series of articles that might give you some good ideas and examples.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 5

By the same token, I've seen people use PowerShell inappropriately (think "Hammer") for things that SQL Server can easily do... just like we've seen abuse with SQL CLR, Cursors, While loops, and a whole gambit of other "shinny objects".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Wayne West SSC-Insane Points: 22586 More actions · Answer 6

Jeff Moden (5/25/2015)
By the same token, I've seen people use PowerShell inappropriately (think "Hammer") for things that SQL Server can easily do... just like we've seen abuse with SQL CLR, Cursors, While loops, and a whole gambit of other "shinny objects".

Absolutely. I just started using CTEs, I'm pretty slow to adopt new features of T-SQL. Personally I like abusing languages to get them to do things they weren't really intended to do just to see if I can do it. I wouldn't do it for anything remotely connected to production, just as an intellectual exercise.

The project that I need to do will be to add users to a SQL user table through Access (row-level security), then have Access create a PS and T-SQL script that will add the person to the server and then properly add them to SQL as a user. It might be possible for me to add a user to the OS via CLR and keep it all within SQL Server, but I'm not wanting to spend the time learning it when I have a sufficient understanding of PS. So there might be a better way to do it, but this is within my skill set without spending a lot of time that could be better spent on other things. Over time I need to add probably 200 users to the system mostly one at a time to the OS, SQL Server, and application: this'll save a lot of time. And I'm fairly confident that I can make it work.

Besides, cut it large and kick it in to place! Motto of builders everywhere! 🙂

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 7

Wayne West (5/26/2015)
Personally I like abusing languages to get them to do things they weren't really intended to do just to see if I can do it. I wouldn't do it for anything remotely connected to production, just as an intellectual exercise.

Heh... You're a man after my own heart except, if it's T-SQL or something having to do with xp_CmdShell, I test the hell out of it for functionality, performance, scalability, and safety, and then put it into production. 😛 And I don't believe in the myth of portability. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

schleep SSChampion Points: 12583 More actions · Answer 8

When I need to create a new instance of our application's DB on several servers, nothing beats PoSh.

I have a script which:

restores to a new db from the current base version of the db on each server;

creates 14 AD groups

creates the relevant logins from these groups on each server depending on it's use (prod, dev, testing, reporting, training, backup)

creates users from those logins in the new DB

add users to the user groups in the new DB

add users to several other resource databases

registers the new DB (instance of our application) with the central app db.

This used to be a long and painful exercise with plenty of room for errors.

Now the entire process takes about 25 seconds and spits out a nice report.

I've since created a GUI for gathering all the parameters for the above script.

Another thing PoSh renders trivial is exporting data with column names.

You'd think this would be a no-brainer from within SQL Server by now, even a little switch in BCP like -IncludeColumnNames...

I also use a PoSh script to do restores from prod to dev. Getting the default backup folder in TSQL requires xp_regread.

In Posh, get an instance of a server object, $Srv -- which you'll need anyway -- and $Srv.BackupDirectory is all you need.

And that's only the tip of the SQL Server uses I have for PoSh. Then there's network, server and workstation applications which it enables with very little effort.

Cheers!

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 9

schleep (6/5/2015)
I also use a PoSh script to do restores from prod to dev. Getting the default backup folder in TSQL requires xp_regread.
In Posh, get an instance of a server object, $Srv -- which you'll need anyway -- and $Srv.BackupDirectory is all you need.

Why is that a problem? Ostensibly, only someone or something with "sysadmin" privs would be doing this anyway.

And, no... that's not a reason to not use PoSh like you did. I just don't see it as a particular advantage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

schleep SSChampion Points: 12583 More actions · Answer 10

Only a problem insofar as you have to actually know that the info is in HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory, as opposed to simply asking for the server object for it's default backup folder.

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 11

schleep (6/5/2015)
Only a problem insofar as you have to actually know that the info is in HKLM\Software\Microsoft\MSSQLServer\MSSQLServer\BackupDirectory, as opposed to simply asking for the server object for it's default backup folder.

Ah. Got it. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

podmate SSCommitted Points: 1676 More actions · Answer 12

I use PowerShell mostly for file/folder manipulation.

Zipping/Unzipping, renaming files/folders, validating file headers, getting file line counts, moving/copying files/folders from one location to another and stuff like this.