May 18, 2015 at 3:07 pm
Rod at work (5/18/2015)
mister.magoo (5/17/2015)
It would be great if that post had some links for further reading 🙂Agreed. I know it's possible to search for PowerShell tutorials, etc., but it would be nice to see some recommendations.
You could try
2. http://www.powershellpro.com/powershell-tutorial-introduction/ : tutorials are in the section on the right called :POWERSHELL TUTORIALS"
If you have PowerShell installed, you have 2 ways to 'use' it.
1. "start"->type powershell in search box->click on 'Windows PowerShell' -- this is what you would traditionally consider a shell window. You can write or run scripts from this shell, but it is not the best development environment.
2. "start"->type powershell in search box->click on 'Windows PowerShell ISE' -- this is a more traditional development environment where you can more easily view/write/run code
May 18, 2015 at 4:18 pm
xsevensinzx (5/18/2015)
Jeff Moden (5/18/2015)
TomThomson (5/18/2015)
Jeff Moden (5/17/2015)
Heh... I look at the recommendations for "scripting" languages with a bit of disgust. There are so many things that SQL Server does very well through T-SQL but people would rather learn a scripting language and be burdened by scripts instead of creating a relatively simple stored procedure and scheduling it.Yes, scripting is indeed useful, but it's not a panacea and too many people try to make it one because many people have proclaimed it to be a "shinny object", particularly PoSH. Hence the most recent addition to signature line below that starts out as "Just because you can..."
I agree that scripting is no panacea, but I think it's not just useful - there are times when its essential.
I'm almost tempted to give myself a signature line:-
"Just because you CAN'T do something in T-SQL, doesn't mean you SHOULDN'T do it at all."
One of the things I did a lot in the bad old days when I had to worry about keeping customers happy (retirement is wonderful, now I can worry about keeping my wife and descendents happy instead) was write software that could crawl around all the COM/COM+ stuff on the system, work out where we had been slugged by either an undocumented and unfixed Microsoft problem, an undocumented and unfixed Cisco problem, or an undocumented and unfixed problem in our one of our own C++ components and decide what objects to modify or destroy to get things up again with a minimum of disruption and end-users not seeing any problem (or maybe one of them would have to be asked to repeat an input). I wanted to do this when the database indicated that imaginable way to do that in T-SQL, and there was no imaginable way I was going to permit C++ (a really awful language) to be used for something that sensitive, so I wrote the original version in Javascript and people took it forwards in Javascript. It was easy to invoke this stuff from T-SQL (either with XPs or by using SQL Agent, when using Agent either not requiring response or polling for it or taking response in new connection).
So my view of scripting is that it's not just useful, it's essential, and everyone should be able to do it. Maybe not everyone should be able to crawl around various structures and find holes, but that's the point at which, if you reach it, you realise that it's no longer at all possible to argue that you can avoid scripting. But scripting can save you a lot of trouble even before you reach that point.
I certainly don't object to scripting. I couldn't have made my enterprise-wide disk space report without it. It uses xp_CmdShell to call a PoSH script that makes WMI calls to each server. Same with the ETL stuff. It took scripts called from SQL server to login to the various sources and download the information.
The CmdShell calls is a good argument on why scripting is needed and why it should be kept separate. I can't do CmdShell calls. I rather a separate system handle everything I need and HAND it off to SQL Server as it should be. Separate, secure and completely safe without having to issue operating system commands directly in the command shell with T-SQL code.
Is there something that leads you to believe that calls to xp_CmdShell are insecure or unsafe?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2015 at 4:21 pm
Steve Jones - SSC Editor (5/18/2015)
Plus it's a skill that can help you work in Exchange, AD, Windows, etc if you get called to help out.
Heh... yet another excellent reason for me to claim I know nothing about it. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
May 18, 2015 at 6:44 pm
Jeff Moden (5/18/2015)
xsevensinzx (5/18/2015)
Jeff Moden (5/18/2015)
TomThomson (5/18/2015)
Jeff Moden (5/17/2015)
Heh... I look at the recommendations for "scripting" languages with a bit of disgust. There are so many things that SQL Server does very well through T-SQL but people would rather learn a scripting language and be burdened by scripts instead of creating a relatively simple stored procedure and scheduling it.Yes, scripting is indeed useful, but it's not a panacea and too many people try to make it one because many people have proclaimed it to be a "shinny object", particularly PoSH. Hence the most recent addition to signature line below that starts out as "Just because you can..."
I agree that scripting is no panacea, but I think it's not just useful - there are times when its essential.
I'm almost tempted to give myself a signature line:-
"Just because you CAN'T do something in T-SQL, doesn't mean you SHOULDN'T do it at all."
One of the things I did a lot in the bad old days when I had to worry about keeping customers happy (retirement is wonderful, now I can worry about keeping my wife and descendents happy instead) was write software that could crawl around all the COM/COM+ stuff on the system, work out where we had been slugged by either an undocumented and unfixed Microsoft problem, an undocumented and unfixed Cisco problem, or an undocumented and unfixed problem in our one of our own C++ components and decide what objects to modify or destroy to get things up again with a minimum of disruption and end-users not seeing any problem (or maybe one of them would have to be asked to repeat an input). I wanted to do this when the database indicated that imaginable way to do that in T-SQL, and there was no imaginable way I was going to permit C++ (a really awful language) to be used for something that sensitive, so I wrote the original version in Javascript and people took it forwards in Javascript. It was easy to invoke this stuff from T-SQL (either with XPs or by using SQL Agent, when using Agent either not requiring response or polling for it or taking response in new connection).
So my view of scripting is that it's not just useful, it's essential, and everyone should be able to do it. Maybe not everyone should be able to crawl around various structures and find holes, but that's the point at which, if you reach it, you realise that it's no longer at all possible to argue that you can avoid scripting. But scripting can save you a lot of trouble even before you reach that point.
I certainly don't object to scripting. I couldn't have made my enterprise-wide disk space report without it. It uses xp_CmdShell to call a PoSH script that makes WMI calls to each server. Same with the ETL stuff. It took scripts called from SQL server to login to the various sources and download the information.
The CmdShell calls is a good argument on why scripting is needed and why it should be kept separate. I can't do CmdShell calls. I rather a separate system handle everything I need and HAND it off to SQL Server as it should be. Separate, secure and completely safe without having to issue operating system commands directly in the command shell with T-SQL code.
Is there something that leads you to believe that calls to xp_CmdShell are insecure or unsafe?
I'm likely being a bit over dramatic on the issue because I know there is a number of steps to take to reduce the risk that has nothing to do with xp_CmdShell directly.
I just find it's a good practice to not keep enabling that window to be open when I can handle those tasks elsewhere. For example, all the cases I ran into where I might need it, I handle it elsewhere with Python. In the end, Python is working with SQL Server so one service is not taking all the work. It's divided among separate services, separate environments and processes that are sharing the workload and promote better performance.
May 18, 2015 at 6:52 pm
xsevensinzx (5/18/2015)
The CmdShell calls is a good argument on why scripting is needed and why it should be kept separate. I can't do CmdShell calls. I rather a separate system handle everything I need and HAND it off to SQL Server as it should be. Separate, secure and completely safe without having to issue operating system commands directly in the command shell with T-SQL code.
That sounds a bit confused.
CmdShell calls are a way of invoking CmdExec scripts, so suggesting that scripting is somehow more separate, secure and safe is just nonsense - and I can certainly do easily as much damage in an Active-X script or a PowerShell script as I can in a CmdExec script. If you want scripts to be unprivileged, for example, it is trivial to configure SQL Server so that CmdShell will execute the CmdEXEC script as an unprivileged user. Do you have any reason to think that a CmdShell running a CmdEXEC script is more dangerous than SqlAgent running a CmdEXEC script as a jobstep? Or that Using CmdShell to call CScript (to run an Active-X script) is more dangerous than having an Active-X jobstep run by SQL Agent?
Tom
May 18, 2015 at 8:18 pm
TomThomson (5/18/2015)
xsevensinzx (5/18/2015)
The CmdShell calls is a good argument on why scripting is needed and why it should be kept separate. I can't do CmdShell calls. I rather a separate system handle everything I need and HAND it off to SQL Server as it should be. Separate, secure and completely safe without having to issue operating system commands directly in the command shell with T-SQL code.That sounds a bit confused.
CmdShell calls are a way of invoking CmdExec scripts, so suggesting that scripting is somehow more separate, secure and safe is just nonsense - and I can certainly do easily as much damage in an Active-X script or a PowerShell script as I can in a CmdExec script. If you want scripts to be unprivileged, for example, it is trivial to configure SQL Server so that CmdShell will execute the CmdEXEC script as an unprivileged user. Do you have any reason to think that a CmdShell running a CmdEXEC script is more dangerous than SqlAgent running a CmdEXEC script as a jobstep? Or that Using CmdShell to call CScript (to run an Active-X script) is more dangerous than having an Active-X jobstep run by SQL Agent?
Yeah, I'm being a bit confusing. I'm referring to executing scripts elsewhere in a separate environment rather than in the SQL Environment. My ETL process for example starts in a separate environment where for example, my API's might live. Data is prepped, split into fragments and cleansed so SQL Server doesn't have to do as much. As it's entirely separate, the use of cmdshell to invoke scripts is reduced in result along with increase performance of loading that data because it's been prepped way before SQL Server handles it.
May 19, 2015 at 7:18 am
I too was going to recommend http://www.microsoftvirtualacademy.com. The PowerShell courses are run by a a duo half of which is Jeffrey Snover. He architected PowerShell.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
May 20, 2015 at 4:05 pm
A good source for PoSH scripts is the Hey, Scripting Guy! blog[/url]. He has several scripts in his archives for SQL Server use, I haven't had time to look at those yet. He's not exclusively PoSH, but he does a lot with it.
I just started studying PoSH, I did a lot with Perl in the past for log analysis. Great tool and crazy fast considering it's interpreted, but unless you live in Perl you're pretty much creating write-once code.
-----
[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]
May 22, 2015 at 9:53 am
I'll check a few of these links out, I would like to get into powershell, just need to make the time.
May 22, 2015 at 12:53 pm
There's also the nine part Powershell Stairway series here.
http://www.sqlservercentral.com/stairway/91327/
-----
[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]
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply