Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Why powershell?


Why powershell?

Author
Message
Shadab Shah
Shadab Shah
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 798
Hi all experts,

I have being reading about Powershell a lot this days. What i came to know about powershell is that , it is a powerfull tool to automate the daily process which a DBA usually Do.

But when we do have Sp's running through Job , to do the same task, then why do we need PowerShell. Is this a redundant functionality given by SQL Server. Or am i missing the purpose of PowerShell?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55362 Visits: 40405
I agree. While I admit there are some great uses of PowerShell, I think that people are overdoing it with PowerShell just like they did when Cursors first came out in 6.5, functions came out in 2000, SQL CLR came out in 2005, etc.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Markus
Markus
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1936 Visits: 3736
Quite honestly I have never even looked at Powershell. Probably doing a mis-service to me but I just don't have the time.



djacob 65569
djacob 65569
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 403
Thanks for asking the question! I had wondered what I was missing as well.
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5324 Visits: 9860
If you have ever created a .bat file to run BCP commands - then Powershell is probably something to look into. It is a command line utility that gives you options for automating processes.

Here is one example where I use it...

In one of our databases, we store the image file names in a table - and the image warehouse locations in another table. What I need is the full path to the file so I can identify which warehouse the image file actually resides in. Note: the application is built to search all available warehouses for the image file - so we don't have any relationship between the image and the specific warehouse.

Using powershell, I can query the database and return the list of image files I am interested in. Once I have that list, I can then loop over the list and check each warehouse to see if the file exists in that warehouse. Once found - I can output that data for the next step.

Example warehouse would be: \\someserver\someshare\
Example image file: somefolder\somefile.tif (note: folder is a different column in the same table)

So now I have a script where I can pass in some identifying information (e.g. customer number) - and the script will output the list of files with the full path.

Another example is not related to SQL Server...but I have a set of scripts I use to add printers to my print servers. These scripts allow me to build a list of printers on one server - and then install them on another server. Someone will probably think print migrator...and that is correct, but print migrator fails when trying to migrate printers from x86 to x64 systems.

So...Powershell is a useful tool to know, but not necessarily a tool that is designed to replace SSMS or T-SQL.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Shadab Shah
Shadab Shah
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 798
Thanks for the reply. Really appreciated the time you had put in to reply the question.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10615 Visits: 14376
If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you. PowerShell is also very good in a distributed environment where we need to communicate with and manage multiple instances that may not be directly reachable from one another rendering tools like Linked Servers and xp_cmdshell less than useful.

You could use tools like xp_cmdshell, the OLE Automation Procedures, Linked Servers, or other functionality built into T-SQL but you may find that in a security-conscious environment tools that access the file system or network from within T-SQL may not be sanctioned for use or will simply not be able to get the job done.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
DBA From The Cold
DBA From The Cold
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1869 Visits: 1736
opc.three (4/23/2013)
If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you.


Definitely, I have a powershell script that runs every morning showing me a disk space report across all the servers that I manage. It's run from a SQL Server Agent job and is really handy for a quick reference in the mornings.

Andrew
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55362 Visits: 40405
opc.three (4/23/2013)
If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you. PowerShell is also very good in a distributed environment where we need to communicate with and manage multiple instances that may not be directly reachable from one another rendering tools like Linked Servers and xp_cmdshell less than useful.

You could use tools like xp_cmdshell, the OLE Automation Procedures, Linked Servers, or other functionality built into T-SQL but you may find that in a security-conscious environment tools that access the file system or network from within T-SQL may not be sanctioned for use or will simply not be able to get the job done.


Gosh... sorry to be an itch but there's nothing about PowerShell that's any more secure than the other methods. There's no natural logging and there's certainly no natural traces on who accessed what or who deleted what using PowerShell. Yes, I absolutely agree that it's a wonderful tool... but not because someone is security conscious because, like the other tools, it offers no natural security.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10615 Visits: 14376
Jeff Moden (4/23/2013)
opc.three (4/23/2013)
If you ever find yourself needing to implement functionality that crosses the OS/SQL Server gap then PowerShell can fill that need quite nicely for you. PowerShell is also very good in a distributed environment where we need to communicate with and manage multiple instances that may not be directly reachable from one another rendering tools like Linked Servers and xp_cmdshell less than useful.

You could use tools like xp_cmdshell, the OLE Automation Procedures, Linked Servers, or other functionality built into T-SQL but you may find that in a security-conscious environment tools that access the file system or network from within T-SQL may not be sanctioned for use or will simply not be able to get the job done.


Gosh... sorry to be an itch but there's nothing about PowerShell that's any more secure than the other methods. There's no natural logging and there's certainly no natural traces on who accessed what or who deleted what using PowerShell. Yes, I absolutely agree that it's a wonderful tool... but not because someone is security conscious because, like the other tools, it offers no natural security.

There is no need to apologize Jeff. I know where you're coming from, but once again I must call you out on the falsehood of one of your many arguments in this area. Employing PowerShell for some tasks does in fact bring with it far fewer barriers in the areas of auditing and securing an environment than do some of the T-SQL methods. Now I'll apologize...sorry, but there really is no denying that fact ;-)

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search