April 17, 2013 at 4:01 am
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?
April 17, 2013 at 4:18 pm
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
Change is inevitable... Change for the better is not.
April 18, 2013 at 7:32 am
Quite honestly I have never even looked at Powershell. Probably doing a mis-service to me but I just don't have the time.
April 18, 2013 at 10:47 am
Thanks for asking the question! I had wondered what I was missing as well.
April 18, 2013 at 4:17 pm
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\someshareExample 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 18, 2013 at 8:46 pm
Thanks for the reply. Really appreciated the time you had put in to reply the question.
April 23, 2013 at 6:55 am
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
April 23, 2013 at 7:27 am
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
April 23, 2013 at 4:52 pm
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
Change is inevitable... Change for the better is not.
April 23, 2013 at 5:19 pm
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
April 23, 2013 at 5:46 pm
opc.three (4/23/2013)
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 😉
Correct me if I'm wrong... If you're sitting at a command prompt and you delete files using either DOS or Powershell, there will be no difference. To log the deletes, you have to turn something else on to log it. PowerShell has no advantage there. It also doesn't trace you activity any more than running a batch file would unless you have some other tool running.
Yep... I agree that if you do it through an SQL Server Job, SSIS, or xp_CmdShell, the only logging it may do is to identify the server/service that did the deletes. No arguments there. But just using PowerShell over something else is no real security enhancement. It's "just" another shell.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 23, 2013 at 6:42 pm
The point is, using xp_cmdshell limits your options. It paints you into a corner. PowerShell has 100% functionality coverage, and infinitely more, over CmdShell. And when run separate from SQL Server we are afforded so many more options from an auditing and security standpoint it's really no contest, really, no contest. How anyone could continue to argue the contrary is beyond me.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 24, 2013 at 4:23 am
VBS is out of fashion.
There is a new kid on the block with huge marketing budget attached to it.
Everybody must immediately forget about the old and proven tool.
_____________
Code for TallyGenerator
April 24, 2013 at 4:57 am
opc.three (4/23/2013)
The point is, using xp_cmdshell limits your options. It paints you into a corner. PowerShell has 100% functionality coverage, and infinitely more, over CmdShell. And when run separate from SQL Server we are afforded so many more options from an auditing and security standpoint it's really no contest, really, no contest. How anyone could continue to argue the contrary is beyond me.
Heh... it hasn't painted me into any corner. I can do everything I need to do with files in a flexible and easy to do manner using the "old" ways.
You keep talking about all of the options for auditing for PowerShell. What are they, how do you implement them, and why are they any different that auditing DOS commands? And so far as security goes, what exactly is the advantage of PowerShell over, say, using a DOS DEL command operated from the Command Prompt?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 24, 2013 at 7:51 am
Xp_cmdshell does not maintain a users identity all the way through the stack, which impedes auditing and allows for obfuscation of the identity of the person running the command.
Once you start building process around it, it opens you up for ad hoc requests from malicious users because its enabled and execution of it is hard to differentiate.
It's just hard to justify why anyone would ever start using it when there are more feature-rich, secure, and auditable tools available.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply