A Great Case for Powershell

Question

A Great Case for Powershell

Viewing 13 posts - 16 through 27 (of 27 total)

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

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 1

Heh... guess I'll have no need for Python, either, then. I do the dynamic pivoting all in T-SQL, as well. 😀

--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)

ManicStar SSCoach Points: 17992 More actions · Answer 2

Jeff Moden - Tuesday, May 22, 2018 7:30 AM
Heh... guess I'll have no need for Python, either, then. I do the dynamic pivoting all in T-SQL, as well. 😀

Pivoting, isn't that what Excel is for :D?

HighPlainsDBA SSCrazy Points: 2611 More actions · Answer 3

Jeff Moden - Tuesday, May 22, 2018 5:32 AM
HighPlainsDBA - Monday, May 21, 2018 1:00 PM
Jeff Moden - Monday, May 21, 2018 11:01 AM
HighPlainsDBA - Monday, May 21, 2018 9:45 AM
It's worth learning IMO, I automatically restore and DBCC CHECKDB our production backups each night with a PoSh script (includes email alerting) I wrote that's only about 200 lines of code. A T-SQL implementation would have been pure spaghetti code. Having a decent procedural language in your tool belt really helps with the administrative tasks.
I wrote such a thing in about the same number of lines of T-SQL code. It certainly didn't turn out to be "pure spaghetti code". 😉
Interesting, did you have to do much with the your backup files in terms of figuring out which one to use? I don't know how I could write this more concisely in T-SQL.
#Find the latest FULL and DIFF backup.
    $FullBackup = Get-Childitem Microsoft.PowerShell.Core\FileSystem::$PartialPath $fullExtension | Where-Object {$_.Name -match $Row[2] + "_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]*" } | Sort-Object LastWriteTime -descending | Select-Object -FIRST 1
    $DiffBackup = Get-Childitem Microsoft.PowerShell.Core\FileSystem::$PartialPath $diffExtension | Where-Object {$_.Name -match $Row[2] + "_[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]*" } | Sort-Object LastWriteTime -descending | Select-Object -FIRST 1
Yes... lots and lots of TRN files. We do nightly FULL backups, no DIFs, and have TRN files every 15-30 minutes depending on the database and the instance.
I'm a bit unconventional because I've proven to myself that certain best practices aren't and a lot of them are based on fear and old wives tales from the past. For example, I know that having xp_CmdShell turned off does nothing to improve security. It only gives people that may not know better the nice warm fuzzies. Unless someone has sysadmin privs, they can't use it when it's enabled and if it's disabled, only people with sysadmin privs can enable it. That means if an attacker can get in with sysadmin privs, they can enable it and use it with no problem. The key is to keep the bad guys from breaking in with sysadmin privs and to NOT give non-sysadmin users the privs to execute it directly. I have an hour long presentation on the subject which ends with how DBAs and stored procedures can use it safely along with some education on what DOS Injection is. There are some really cool things you can do with it. For example, the error reporting generated in error files by either BULK INSERT or BCP absolutely suck. Doing a little trick with a combination of xp_CmdShell and SQLCmd actually captures useful error reporting from a BULK INSERT where, normally, that information would only be available to a human that's actively watching the process. Ironically, the use of xp_CmdShell has increased security quite a bit when it comes to ETL. Because Bulk Insert relies on the user's privs and such things are frequently run manually by certain people or other systems (WebMethods, for example), we were either having to give users/other systems extraordinary privs on the source file system or give them write privs at the OS level so they could copy files to the SQL Server box and we all know the problems that brings with memory usage not to mention using valuable disk space on the SQL Server for such junk. It really got crazy when they started doing exports to files. Now, they just execute a well written stored procedure that has the privs to use xp_CmdShell to get the job done. The users/systems actually have no privs to do any of it directly. They can't even do a DIR to see the file names directly.
That being said, I capture the results of a DIR command in a table where life is made easy and I also tickle the registry (again, using what's available in T-SQL) to get the default paths for restores if the database has been dropped or is new to the server.   I also auto-magically repoint synonyms in the code based on the environment (Dev, QA, UAT, Staging, etc) and the name of the database, which changes depending on the environment it's in and reestablish the correct users and roles for databases that existed during the restore.
There are actually 348 lines of code to do all of that but half of that is embedded documentation in the form of comments and blank lines for the sake of readability.
Shifting gears a bit, what do you consider a line of code to be in PowerShell? I'm looking at your great examples (thanks for posting those) and, while each is technically only a single line because of the CrLf locations, in SQL Server each "piped" filter/command you have on those lines would be a separate line in SQL Server if you use the formatting conventions that I use. I'm thinking that if I were to single-line many of the simple SELECTs that I use and get rid of the blank lines that I use for formatting and drop all of the comments, I might actually have quite a bit less than 200 lines of code.
If someone absolutely loathes the use of xp_CmdShell, either a simple batch file or the use of xp_DirTree can help and, most certainly, the proper call to MSDB of the source server will do the trick for such restores. I'm still a bit confused as to why someone would allow the use of PowerShell with extraordinary privs and not xp_CmdShell with similar privs but more tightly controlled because general users can't actually use it directly nor do they need to be provided with elevated privs to any box at the OS level.
I certainly can't and won't speak for anyone never mind everyone but I've simply just not needed PowerShell to do the extraordinary things that can be done with PowerShell... and I still pass all the audits we have to go through every bloody year.

Thanks for the info on the xp_*'s. Maybe I should take another look at them. My script gets the path from a centralized admin database we maintain so I don't need to poke around much for that info. That's where the $PartialPath variable is coming from. As for "what do I consider a line of code", I do not have or abide by any specific definition of the term. Mine are simply rough estimates. In my code above I suppose I would count that as 2 lines, I don't consider comments as code. It pasted in as 4 lines but it's 2 on mine. I was wondering how you would navigate and filter on the directory tree with T-SQL? It seems to me it would require some more work, but I haven't much experience coding solutions using xp's. I've just never found string processing or error handling to be a strength of T-SQL. That said I do have lots of T-SQL in the script like:

#DBCHECK is the db I'm restoring to run DBCC on. I have to use secondary sites.
#Sanity check
    Write-Output $ErrorMsg
        Invoke-SQLCmd -Query "IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DBCHECK') DROP DATABASE [DBCHECK] " -ServerInstance "SERVERINSTANCE" -VERBOSE -querytimeout 0 -ErrorAction 'Stop'

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 4

Jeff Moden - Monday, May 21, 2018 6:02 AM
call.copse - Monday, May 21, 2018 3:22 AM
Fair point Jeff, but who would pay us if everyone knew how to do everything optimally? Fortunately it's not even close...
Heh... it's not "optimally". It's just a little common sense about some fairly well known best practices. I will agree, though, there are a whole lot of people that don't think they need to know such things and then end up having to resort to this type of thing to get out of the woods. The thing I don't understand is that even after getting out of the woods, they turn right around and walk back in because they never put two and two together and say, "It's an easy fix to never have to worry about this again... let's fix it". The cool part is that it does provide a living for a lot of the denizens of SQLServerCentral. 😀

Heh... and the hits just keep on coming. If you know this fellow, it might be a temporary source of income. I just don't understand how it is that people that claim to know SQL Server let this kind of thing happen to them when prevention is so very, very simple.
https://www.sqlservercentral.com/Forums/1949948/Need-to-rename-server-but-name-is-in-lots-code-alias

--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)

Frank W Fulton Jr SSCommitted Points: 1599 More actions · Answer 5

I would have to say to those of you that are not using POSH in any fashion - start. It is the new CMD, and CMD is deprecated. Time to start getting used to it.
It is powerful, hence the name "Power" shell. You will need to know it to run CORE installs of windows servers, which you will start seeing more and more since they don't have GUIs.
But don't worry most everything you have done in CMD can be done in POSH, and most time with a little learning easier and cleaner.
The CMD is Dead, long live the POSH!

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 6

While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features

In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.”

Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.

https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/

BWAAA-HAAA... remember back when they said ODBC was "dead"?

--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)

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 7

Yeah, not seeing command line dying on any OS. Those who are always tied to GUI will never understand this. On a related note, I've also been taught to never just rely on the GUI (i.e.: SSMS) in order to be a good DBA for that matter. This means, instead of right clicking to drop a table in the object explorer or whatever, write the syntax to drop the table.

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 8

xsevensinzx - Wednesday, May 23, 2018 5:29 AM
Yeah, not seeing command line dying on any OS. Those who are always tied to GUI will never understand this. On a related note, I've also been taught to never just rely on the GUI (i.e.: SSMS) in order to be a good DBA for that matter. This means, instead of right clicking to drop a table in the object explorer or whatever, write the syntax to drop the table.

Totally agree with that. There are also some things that just don't make sense doing through a GUI. Partitioning a table comes to mind especially if it's an older table that's going to require many partitions. Backups can survive through a GUI-created job but it won't be something you can copy and paste to multiple machines. Even with the improvements they made for index maintenance, the GUI method leaves a whole lot to be desired.

--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)

Frank W Fulton Jr SSCommitted Points: 1599 More actions · Answer 9

Jeff Moden - Tuesday, May 22, 2018 9:00 PM
While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features
In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.â€
Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.
https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/
BWAAA-HAAA... remember back when they said ODBC was "dead"?

I have no problem admitting I was wrong. I stand corrected. I must have been listening to the "Fake News" 🙂
I would like to offer my apology for spreading "Fake News", and ask the forum for forgiveness.
I am not going to offer any lame excuses, and take the hit.
Again my apologies to the forum.
Frank

Jeff Moden SSC Guru Points: 1004410 More actions · Answer 10

Frank W Fulton Jr - Wednesday, May 23, 2018 3:42 PM
Jeff Moden - Tuesday, May 22, 2018 9:00 PM
While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features
In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.â€
Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.
https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/
BWAAA-HAAA... remember back when they said ODBC was "dead"?
I have no problem admitting I was wrong. I stand corrected. I must have been listening to the "Fake News" 🙂
I would like to offer my apology for spreading "Fake News", and ask the forum for forgiveness.
I am not going to offer any lame excuses, and take the hit.
Again my apologies to the forum.
Frank

Takes a heck of a good person to man up like that, Frank. My hat's off to you. And you're (we're) not alone. There are none among us that haven't had to do so at one time or another. The difference is whether or not they actually did.

--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)

Sue_H SSC Guru Points: 90860 More actions · Answer 11

Frank W Fulton Jr - Wednesday, May 23, 2018 3:42 PM
Jeff Moden - Tuesday, May 22, 2018 9:00 PM
While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features
In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.â€
Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.
https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/
BWAAA-HAAA... remember back when they said ODBC was "dead"?
I have no problem admitting I was wrong. I stand corrected. I must have been listening to the "Fake News" 🙂
I would like to offer my apology for spreading "Fake News", and ask the forum for forgiveness.
I am not going to offer any lame excuses, and take the hit.
Again my apologies to the forum.
Frank

In your defense, there really was a lot of "fake news" around this. I can't remember how it all started but it was related to variations of what people started reading when Microsoft said that Powershell was replacing cmd.exe, some MS blogs about the changes with Win 10 and defaulting to Powershell, etc.
And everything went weird after that. Microsoft actually had to release a statement that Cmd wasn't being removed. This article addresses it to some extent:
Follow-up: MS-DOS lives on after all

Sue

x SSC-Insane Points: 23660 More actions · Answer 12

Sue_H - Thursday, May 24, 2018 10:34 AM
Frank W Fulton Jr - Wednesday, May 23, 2018 3:42 PM
Jeff Moden - Tuesday, May 22, 2018 9:00 PM
While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features
In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.â€
Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.
https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/
BWAAA-HAAA... remember back when they said ODBC was "dead"?
I have no problem admitting I was wrong. I stand corrected. I must have been listening to the "Fake News" 🙂
I would like to offer my apology for spreading "Fake News", and ask the forum for forgiveness.
I am not going to offer any lame excuses, and take the hit.
Again my apologies to the forum.
Frank
In your defense, there really was a lot of "fake news" around this. I can't remember how it all started but it was related to variations of what people started reading when Microsoft said that Powershell was replacing cmd.exe, some MS blogs about the changes with Win 10 and defaulting to Powershell, etc.
And everything went weird after that. Microsoft actually had to release a statement that Cmd wasn't being removed. This article addresses it to some extent:
Follow-up: MS-DOS lives on after all
Sue

I remember that, I thought it was sort of that windows 10 was going to DEFAULT to powershell but cmd would still be available.

http://hexus.net/tech/news/software/99436-latest-windows-10-build-makes-powershell-default-command-line/

Sue_H SSC Guru Points: 90860 More actions · Answer 13

patrickmcginnis59 10839 - Thursday, May 24, 2018 10:38 AM
Sue_H - Thursday, May 24, 2018 10:34 AM
Frank W Fulton Jr - Wednesday, May 23, 2018 3:42 PM
Jeff Moden - Tuesday, May 22, 2018 9:00 PM
While I absolutely agree that PowerShell is powerful, Cmd.Exe is not deprecated and isn't likely to ever be. Neither is xp_CmdShell.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-2017
https://docs.microsoft.com/en-us/windows-server/get-started/deprecated-features
In fact, it seems that MS is committed to keeping the Windows Cmd Shell up and running. Here's the link and an important quote from that article, supposedly made from someone at MS authorized to say so.
https://www.computerworld.com/article/3149115/operating-systems/follow-up-ms-dos-lives-on-after-all.html?nsdr=true
"The Windows Cmd shell has a long heritage and is an essential tool used by millions of businesses, users, and developers every day. It remains an integral part of Windows.â€
Every article that I've seen about the speculated demise of Cmd.exe is trumped by the article above. In all the searching I've done on the "deprecation of Cmd.exe", I've not come across even one official MS document that said there are any plans to do so. Read about the most recent updates. Note the date in the article. March 7th, 2018.
https://blogs.msdn.microsoft.com/commandline/2018/03/07/windows10v1803/
BWAAA-HAAA... remember back when they said ODBC was "dead"?
I have no problem admitting I was wrong. I stand corrected. I must have been listening to the "Fake News" 🙂
I would like to offer my apology for spreading "Fake News", and ask the forum for forgiveness.
I am not going to offer any lame excuses, and take the hit.
Again my apologies to the forum.
Frank
In your defense, there really was a lot of "fake news" around this. I can't remember how it all started but it was related to variations of what people started reading when Microsoft said that Powershell was replacing cmd.exe, some MS blogs about the changes with Win 10 and defaulting to Powershell, etc.
And everything went weird after that. Microsoft actually had to release a statement that Cmd wasn't being removed. This article addresses it to some extent:
Follow-up: MS-DOS lives on after all
Sue
I remember that, I thought it was sort of that windows 10 was going to DEFAULT to powershell but cmd would still be available.
http://hexus.net/tech/news/software/99436-latest-windows-10-build-makes-powershell-default-command-line/

It was related to that Windows 10 version announcement. The early articles left off the piece about how Cmd.exe wasn't going away - they would just say that Powershell was the replacement. And the change to Win-X defaulting to Powershell didn't mention how that would be a setting that could be changed to get the command prompt back. People just started running with that twist of Powershell being the "new" cmd until Microsoft announced cmd wasn't going away. Maybe it's time to blame the media for everything instead of DBAs.

Sue