Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Why powershell? Expand / Collapse
Author
Message
Posted Wednesday, April 17, 2013 4:01 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:11 AM
Points: 277, Visits: 699
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?
Post #1443170
Posted Wednesday, April 17, 2013 4:18 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1443532
Posted Thursday, April 18, 2013 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:32 AM
Points: 1,324, Visits: 2,941
Quite honestly I have never even looked at Powershell. Probably doing a mis-service to me but I just don't have the time.


Post #1443848
Posted Thursday, April 18, 2013 10:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:18 PM
Points: 25, Visits: 267
Thanks for asking the question! I had wondered what I was missing as well.
Post #1444000
Posted Thursday, April 18, 2013 4:17 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, December 12, 2014 12:03 PM
Points: 4,363, Visits: 9,547
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1444185
Posted Thursday, April 18, 2013 8:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 7:11 AM
Points: 277, Visits: 699
Thanks for the reply. Really appreciated the time you had put in to reply the question.
Post #1444210
Posted Tuesday, April 23, 2013 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1445397
Posted Tuesday, April 23, 2013 7:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, December 18, 2014 2:59 AM
Points: 1,356, Visits: 1,180
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
Post #1445412
Posted Tuesday, April 23, 2013 4:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:11 PM
Points: 35,772, Visits: 32,441
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1445720
Posted Tuesday, April 23, 2013 5:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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
Post #1445725
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse