Learning PowerShell and SQL Server Introduction
Learning PowerShell and SQL Server - Introduction
Author Recent Posts Shawn MeltonShawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada.
After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.
He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).
View all posts by Shawn Melton Latest posts by Shawn Melton (see all) Learning PowerShell and SQL Server – Introduction - April 23, 2018 Connecting PowerShell to SQL Server – Using a Different Account - January 24, 2017 How to secure your passwords with PowerShell - January 18, 2017
SQLShack
SQL Server training EspañolLearning PowerShell and SQL Server – Introduction
April 23, 2018 by Shawn MeltonIntroduction
This article is the first step among many that I hope will help give you a foundation of knowledge to get started utilizing PowerShell. The focus in these steps will be specific to using PowerShell with SQL Server, but I have to cover some of the basic things. Which once you grasp the basics of PowerShell and using it, in a general sense, you set yourself up for easily learning how to use it with other products. In this article I’m going to touch on the following items: History Lesson (short reference for a timeline on releases) Windows PowerShell vs PowerShell Core SQL Server and PowerShell (as it is today) Terminology (some terms that help to understand) PowerShell EditorsHistory lesson
It never hurts to know where we started because it adds that much more excitement to see where we are going. PowerShell Timeline SQL Server PowerShell TimelineWindows PowerShell vs PowerShell Core
You can see from the timeline above that a ton of things have changed since Windows PowerShell 1.0 was released in 2006. As expected with each release of Windows PowerShell we got more functionality and a crazy increase in commands. The biggest thing in the last few years was the announcement that PowerShell became open-source. The release cycle of Windows PowerShell dropped around the times new Operating System versions were being released. However, with PowerShell Core you can see just up to March of 2018 they have been releasing updates fairly frequent. With the ability now for the community to contribute to PowerShell it has lead to a number of little “annoyances” being fixed. (I mean even while preparing this article, the PowerShell repository on GitHub is one commit shy of 6,000 total commits, with 196 contributors…that is insane!) Now things that the community see as bugs can be discussed directly with the maintainers of the repository (Microsoft) then actually get fixed and released in a more timely manner. There are a few things worth noting that changed in PowerShell Core due to the cross-platform support: Two Products You now have basically separate products, they are not editions like Window Server Standard or Window Server Core…completely separate products. As of the release of PowerShell Core 6.0.0, there will be no further development or enhancement on what is known as Windows PowerShell. That means that the last version of Windows PowerShell to be released is version 5.1, which for all extensive purposes is suitable for managing Windows Operating System. Now PowerShell Core (or just “PowerShell”) being cross-platform you can use it on Windows OS along with Mac OS and various distributions of Linux. The thing to understand is that PowerShell Core is just PowerShell. In Windows PowerShell you had PowerShell but it was packaged with commands and modules specific to Windows Operating System. With PowerShell Core, getting it to support cross-platform the OS specific stuff was stripped out. You can see the list of breaking changes that came with 6.0.0 release here. No more PowerShell.exe A showcase of how making PowerShell an open-source can lead to an open discussion between the community and Microsoft is in this issue. In PowerShell Core with the 6.0.0-rc release, the executable or binary that you run for PowerShell Core was renamed to pwsh.exe or pwsh. So when you are calling or running PowerShell Core across any OS platform it is just pwsh. I can go on to explain why this was done and all, but the best write-up I think came from Mark Kraus which is one of the contributors to the PowerShell repository. You can find his write-up on this change here. Commands PowerShell Core was moved to run with .NET Core Support (another open-source product from Microsoft). Windows PowerShell, if you have ever installed it yourself, requires certain versions of .NET Framework based on the version of Windows PowerShell you were installing. That means with the move to .NET Core, the majority of the modules that ran on Windows PowerShell will not work in PowerShell Core “out of the box”. The maintainers of those modules will have to port their module over to PowerShell Core. An example of that is the SQL Server module that we will discuss more on shortly, it was only recently in April 2018 ported over to PowerShell Core. You will find multiple modules that were specific to Windows are not going to be ported over, mainly because they are for Windows. Understand that if you need to manage Windows OS and only Windows OS, then you do not have to install or deploy PowerShell Core in your environment. It falls into that category of using the right tool for the job. If you are not an administrator that goes between different OS platforms, you may have no need to utilize or install PowerShell Core.SQL Server and PowerShell
I do not believe there is a reason to drudge up bad memories, so I’m not going to go into the discussion on SQLPS because as far as the community is concerned (or should be) it is behind us now. If you happen to be using older versions of SQL Server, the sqlserver module is supported against those versions as well. A big undertaken came from Microsoft with SQL Server in that they split the management tools out of the main SQL Server release cycle. It is now no longer part of the SQL Server installation process. SQL Server Management Studio (SSMS) for example as of SQL Server 2016 is now a stand-alone release and installation. We see releases on SSMS almost monthly now with each new iteration fixing bugs or improving the performance of the application as a whole. The sqlserver module being published to the PowerShell Gallery offers the ability to get updates more easily (even as a non-Administrator) and for Microsoft to release new features in the module faster. One of those came in April 2018 in that the module now supports minimal use under PowerShell Core. The team that manages all the client related tools for SQL Server have made significant steps in improving functionality and performance of the tools.Terminology
It helps to know the lingo when you are learning something new and PowerShell is no different… Host This is the console you start up to type in commands that PowerShell will execute. By definition it is the “interface that Windows PowerShell engine uses to communicate with the user”, you input something it outputs something or performs an action for you. The host in Windows PowerShell would be “powershell.exe” or if you use the PowerShell Integrated Scripting Environment (ISE) would be “powershell_ise.exe”. Then also with PowerShell Core, we add “pwsh.exe” to the list. Script or ps1 file A PowerShell script, whether you are using Windows PowerShell or PowerShell Core, has the file extension “ps1”. You can have a series of commands in the file and when you call it in PowerShell it will execute those in a top-down order. Cmdlet A cmdlet (pronounced as “command-let”), these are commands that are based on compiled .NET language. They are generally tied to a DLL file on the host system. Function A function is written in the PowerShell language. A function can be as complex as validating input and output or just basically calling a series of commands. Module A module is a package of grouped commands (cmdlets and/or functions). To utilize a module you import it into your session of PowerShell. You can do this explicitly with “Import-Module” but as of Windows PowerShell 3.0 the first time you call a command of a module it will automatically import that module for you. Although, it can save time by simply importing it explicitly in your scripts otherwise that first command you run will take longer than normal based on the size of the module. Dot Source When someone refers to “dot sourcing a file” they are loading the contents of that file into their current session of PowerShell. So if you have a function named “Find-MyDinner” in the file “myscripts.ps1” you cannot utilize that function until it is loaded into your current session. The method of dot sourcing a file is basically importing it and looks like the following: 1 . C:.ps1 This is a common practice if you have a script file that you use to say keep a library of utility functions that you use commonly across all your scripts. You would dot source that file withing other scripts so you have access to call those functions. Variable A variable will be a name that is prefaced by a $ sign. It can hold various types of data from a single string to a collection of values. You do not have to declare a variable like you would in T-SQL but at times you may set a variable to a specific type. A simple example: You can see that just setting a date value to “$d1″ PowerShell will set it to a string type. On “$d2” I set the type of the variable by setting it to the “datetime” type. You can use just about any .NET type available to you in PowerShell. Pipe or Pipeline To “pipe” a command to another command (.e.g Get-Process Out-File C:.txt) is taking a series of commands and passing the output from each preceding command to the next one. So the example I am taking the output of “Get-Process” and it is being passed to the “Out-File” command. One thing to remember is not all commands will support piping output to it. The command or function has to be able to support the output from one command as input, and if it does not do that properly it the command will fail. Parameter A parameter will look similar to a strong typed variable, because it is to a degree. Take the example I have below: 1 function Get-MyData { param( [string]MyName,[string[]]MyFavoriteFood ) <# do some work #> } In the above function, I declare “$MyName” and “$MyFavoriteFood” to be string values. These are the inputs the function will accept. So when you call the function it will be Get-MyData -MyName “Shawn” -MyFavoriteFood “Hot dogs”,“hamburgers”,“steak”. Based on that I can then take those input values and do something with them. A special note on “$MyFavoriteFood” parameter is you can see I passed in multiple values. When you use “[]” (open and close brackets) within the type it means that parameter will support multiple values being passed in. Now in order to accept multiple values, your code has to be able to handle processing it as well. Many more The list above are not the only terms you can find when reading through the docs. Once you start your journey on learning PowerShell you will pick up other terms, just like learning SQL Server or any other product. I tried to cover above just the main ones that give you a foundation to start on.PowerShell editors
Starting out learning PowerShell you can use Notepad if that is all you can access. It does not really matter. The purpose of PowerShell Editors is to aid in the development of PowerShell scripts or modules. Majority of the editors offer things like intellisense and parsing indicators (the red squiggle lines). Microsoft started out with Windows PowerShell by including an editor called PowerShell Integrated Scripting Environment (ISE). This is included with the installation of Windows PowerShell. It is worth noting that with PowerShell Core they removed the ISE due to the fact it cannot run on Linux, it did not meet the cross-platform requirements. The common editor that is growing and Microsoft is choosing to put focus is with Visual Studio Code, referred to as just Code or VS Code. This is an open source editor that works on Windows, Mac OS and various flavors of Linux (if you have desktop GUI installed). It is extensible based on what language you want to program in, so for PowerShell will require the PowerShell extension to be installed. There are other editors out there worth mentioning that are capable of being used for PowerShell development/scripting Atom Sublime Sapien’s PowerShell Studio Visual Studio Community EditionWrapping up
The first step can always be the hardest when you are learning a new technology. In future steps, I will build upon this one. I encourage you to continue reading up on PowerShell using the links referenced.Author Recent Posts Shawn MeltonShawn Melton is a SQL Server consultant at Pythian, a global IT services company based out of Ottawa - Canada.
After spending 6 years in the system and network administration world he found he enjoyed working and learning SQL Server. Since 2010 he has been involved in SQL Server.
He is passionate about PowerShell and automation around SQL Server and Windows. His experience is focused in SQL Server administration and some BI development (SSIS, SSRS).
View all posts by Shawn Melton Latest posts by Shawn Melton (see all) Learning PowerShell and SQL Server – Introduction - April 23, 2018 Connecting PowerShell to SQL Server – Using a Different Account - January 24, 2017 How to secure your passwords with PowerShell - January 18, 2017