I have to start here. For the longest time, whenever anyone has asked me how I store my credentials for use in my demos and labs I have always referred them to Jaap Brassers t blog post
When people wanted a method of storing credentials that didnt involve files on disk I would suggest Joel Bennett’s t module BetterCredentials which uses the Windows Credential Manager
Sydney t gave a presentation at the European PowerShell Conference which you can watch on Youtube.
Good Bye Import-CliXML
So now I say, it is time to stop using Import-Clixml for storing secrets and use the Microsoft.PowerShell.SecretsManagement module instead for storing your secrets.
Notebooks are as good as blog posts
I love notebooks and to show some people who had asked about storing secrets, I have created some. So, because I am efficient lazy I have embedded them here for you to see. You can find them in my Jupyter Notebook repository
I was talking with my friend Mathias Jessen @IISResetMe on Twitter about notebooks and he said that another great use case was to use them on Stack OverFlow
Now Mathias is an active answerer on Stack OverFlow
and he puts a lot of effort into writing his answers, formatting them, including code and results. Basically exactly the same as a Notebook. However, with a Notebook, you can enable people to run the code as well on their own machines.
Mathias says he will use notebooks to help people when he answers their PowerShell questions on Stack OverFlow. If you are a Stack OverFlow Answerer then you can too.
My last post had a lot of information about the new .NET PowerShell notebooks including installation instructions.
.NET Notebooks are Jupyter Notebooks that use .NET core to enable C#, F# and PowerShell kernels.
Use Cases
One of the main benefits that I see for Jupyter Notebooks for Ops folk is that the results of the query are saved with the notebook. This makes them fantastic for Incident resolution.
If you have an incident at 3am and you know that you will need that information in the wash up meeting the next day instead of copying and pasting results into a OneNote document or a text file, you can simply run the queries in a notebook and save it.
In the meeting, you can simply open the notebook and the results will be available for everyone to see.
Even better, if you have a template notebook for those scenarios and you can then compare them to previous occurrences.
Using Pester
Using Pester to validate that an environment is as you expect it is a good resource for incident resolution, potentially enabling you to quickly establish an area to concentrate on for the issue. However, if you try to run Pester in a .NET Notebook you will receive an error
Describe:
Line |
3 | Describe "Checking Problem ...... by $($ENV:USERDOMAIN) $($ENV:UserName)" {
| ^ The 'Describe' command was found in the module 'Pester', but the module could not be loaded. For more information, run 'Import-Module Pester'.
Fixing it
When you try to Import-Module Pester you get the following error
Get-Command: C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psm1
Line |
94 | $script:SafeCommands['Get-CimInstance'] = Get-Command -Name Get-CimInstance -Module CimCmdlets @safeCommandLookupParameters
| ^ The term 'Get-CimInstance' is not recognized as the name of a
| cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included,
| verify that the path is correct and try again.
Import-Module: The module to process 'Pester.psm1', listed in field 'ModuleToProcess/RootModule' of module manifest 'C:\Users\mrrob\Documents\PowerShell\Modules\Pester\4.9.0\Pester.psd1' was not processed because no valid module was found in any module directory.
Thats odd, why is it failing there? Dongbo Wang from the PowerShell team explains in the issue that I raised
Yes, it was the CimCmdlets module from the system32 module path that got imported (via the WinCompat feature added in PS7). This is because currently the PS kernel don’t ship all the built-in modules along with it … The built-in modules are not published anywhere and are platform specific, it’s hard for an application that host powershell to ship them along. We have the issue PowerShell/PowerShell#11783 to track this work.
The way to resolve this is to Import the CimCmdlets Module from your local PowerShell 7 installation until the issue is resolved
Notebooks – A brilliant way of sharing what you did and the results that you got enabling others to follow along. You can do this with this Notebook. Download it and open it in your Jupyter Lab and you will be able to run it and see all of the errors and the fix on your machine.
— Maria Naggaga #BlacklivesMatter (@LadyNaggaga) February 6, 2020
PowerShell 7 Notebooks 🙂
A notebook experience for PowerShell 7 that sounds amazing. This will enable a true cross-platform PowerShell Notebook experience which is lacking from the Python version as it uses Windows PowerShell on Windows and PowerShell Core on other OS’s
The first thing I asked was – Will this come to Azure Data Studio. I got an immediate response from Sydney Smith PowerShell Program Manager saying it is on the roadmap
Moving this kernel into ADS is on our roadmap! Right now our kernel uses hosted pwsh 7 but we would love to know if you have scenarios that dont work with 7
To be able to run the notebook, you need to install some dependencies. First install the .NET CORE SDK which you can download from https://dotnet.microsoft.com/download This needs admin permissions to install.
You also need a Python installation – You can use Anaconda, which you can download from here https://www.anaconda.com/distribution/ This does not need admin to install
Add Anaconda to Windows Terminal
I have added the Anaconda prompt to Windows Terminal so that I have one entry point for all my CLIs. Open the settings file and add the code below. (It will also give you an icon and background.
{
// Make changes here to the Anaconda.exe profile
"guid": "{0caa0dad-35be-5f56-a7ff-afceeeaa6101}",
"name": "Anaconda",
"commandline": "cmd.exe /K C:\\Users\\mrrob\\Anaconda3\\Scripts\\activate.bat",
"hidden": false,
"backgroundImage": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
"icon": "C:\\Users\\mrrob\\Anaconda3\\Menu\\anaconda-navigator.ico",
"backgroundImageAlignment": "topRight",
"backgroundImageStretchMode": "uniform",
"backgroundImageOpacity": 0.1
}
and it appears in the drop down
With Anaconda installed, check that that the kernel is available on your path. If like me you have Azure Data Studio installed, you will have additional kernels but the important one line here is
Then you can install the .NET kernel in your Anaconda prompt using this command
dotnet interactive jupyter install
Sometimes new things have errors
I had an error when I tried this first time
Could not execute because the specified command or file was not found. Possible reasons for this include: * You misspelled a built-in dotnet command. * You intended to execute a .NET Core program, but dotnet-interactive does not exist. * You intended to run a global tool, but a dotnet-prefixed executable with this name could not be found on the PATH.
This is easily fixed by adding %USERPROFILE%\.dotnet\tools to my path with set PATH=%PATH%;%USERPROFILE%\.dotnet\tools
Running jupyter kernelspec list shows that the .NET kernel is installed for C Sharp, F Sharp and .NET PowerShell
Lets open a Notebook
Now you want to play with it! You can run the lab environment using `jupyter lab`
This opens a browser
You can open existing Azure Data Studio PowerShell notebooks (but not SQL ones)
Sometimes new things have errors Part 2
Unfortunately, I get errors when trying to import Pester which means I can not use my dbachecks notebooks in this blog post. I have raised an issue on the repo here.
Create a New Notebook
But it is easy to create a new Notebook
In the launcher page click the .NET PowerShell button
Which will open a new Notebook in the directory that you launched the lab from. You can then add Code or Markdown as I have described before here.
Then you can add code, markdown and images to create your notebook.
Once you have finished using the notebook lab, you can shut it down in the Anaconda prompt with CTRL + C
Here is a video of running a notebook which anyone can use to create a couple of Docker containers running SQL 2019 and query them with dbatools. You can find the notebook further down this post.
Sharing Notebooks
You can create notebooks to run common tasks. Even better, from the lab you can convert the notebook including the results to a variety of formats to share with other none-technical people. I used this functionality this week to export Azure Data Studio Notebooks to HTML and PDF for a Project manager 🙂
You can find the Export Notebook command in the File menu
Exporting to HTML did not export the images but it does include the results
You can share notebooks via GitHub – Either in a gist like this
This uses Docker to create an interactive Notebook. Create a Github repo like https://github.com/SQLDBAWithABeard/Notebooks (or just clone it) Copy your notebooks into the notebooks folder and push the changes to Github and then go to https://mybinder.org/ and add your URL to the repository.
You can see what it looks like by clicking the button below which Binder creates for you
Unfortunately the kernel only supports Python for the moment but you can see the possibilities 🙂
Until recently, this had worked successfully. In the last few weeks I have been receiving errors
Exception : Microsoft.PowerShell.Commands.WriteErrorException: Failed to generate the compressed file for module 'C:\Program Files\dotnet\dotnet.exe failed to pack: error
C:\Program Files\dotnet\sdk\3.0.100\Sdks\NuGet.Build.Tasks.Pack\build\NuGet.Build.Tasks.Pack.targets(198,5): error :
2 Index was outside the bounds of the array.
[C:\Users\VssAdministrator\AppData\Local\Temp\cbc14ba6-5832-46fd-be89-04bb552a83ac\Temp.csproj]
'.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\2.2.1\PSModule.psm1:10944 char:17
20 Publish-PSArtifactUtility @PublishPSArtifactUtility_Param ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Write-Error], WriteErrorException
2019-11-25T22:44:46.8459493Z + FullyQualifiedErrorId : FailedToCreateCompressedModule,Publish-PSArtifactUtility
This was very frustrating as it was stopping the continuous delivery to the PowerShell Gallery. It was even more confusing as I was successfully deploying the ADSNotebook module to the gallery using the same method as you can see here.
I asked the wonderful folk in the PowerShell Slack channel – Through the magic of automation, you can also interact with them via the powershellhelp channel in the SQL Server Slack as well but there were no answers that could assist.
So I had to go searching for an answer. PowerShellGet uses nuget for package management. I found that if I downloaded an earlier version and placed it in my user profile (in the right location) I could publish the module.
I found this out by removing the nuget.exe from anywhere useful on the machine and trying to publish the module. The error message says
NuGet.exe upgrade is required to continue
This version of PowerShellGet requires minimum version '4.1.0' of NuGet.exe to publish an item to the NuGet-based repositories. NuGet.exe must be available in
'C:\ProgramData\Microsoft\Windows\PowerShell\PowerShellGet\' or 'C:\Users\BeardyMcBeardFace\AppData\Local\Microsoft\Windows\PowerShell\PowerShellGet\', or under
one of the paths specified in PATH environment variable value. NuGet.exe can be downloaded from https://aka.ms/psget-nugetexe. For more information, see
https://aka.ms/installing-powershellget . Do you want PowerShellGet to upgrade to the latest version of NuGet.exe now?
If I said yes then I got the latest version and the error continued.
However, on my laptop I can go to the nuget downloads page and download an earlier version and place it in one of those paths then I could publish the module.
Can I Automate it?
I would rather not have to deploy manually though, and as I use hosted agents my access to the operating system is limited so I wondered if I could place the nuget.exe in the user profile and it would get used or if it would look for the the latest one. Turns out it uses the one in the user profile 🙂
So now I have this code as a step in my Azure DevOps Release pipeline before calling Publish-Module and we have automated the releases again.
and now deployments to the PowerShell Gallery are just triggered by the build and the pipeline is green again 🙂
I showed my silly example PowerShell code to create a PowerShell Notebook that created a PowerShell Notebook to my good friend Nick.
Nick is a fantastic, clever DBA who isn’t active on social media, which is a great shame as if he had time to share some of his fantastic work we would all benefit. He looked at that code and less than an hour later, came back to me with this code and idea which I have replicated here with his permission.
Thanks Nick.
The Use Case
The use case that Nick has is that he is converting some troubleshooting runbooks from their original locations (you know the sort of places – Sharepoint Docs, OneNote Notebooks, Shared Folders, the desktop of the Bastion Host) into a single repository of Azure Data Studio SQL or PowerShell Notebooks.
The idea is to have a single entry point into the troubleshooting steps and for the on-call DBA to create a Notebook from a template for the issue at hand which could be attached to an incident in the incident management solution. I suppose you could call it an Index Notebook.
Work Flow
When the DBA (or another team) opens this Notebook, they can choose the task that they are going to perform and click the link which will
copy the Notebook to the local machine
Rename the Notebook with the username and date
Open it ready for the work.
Once the work has been completed, the DBA can then attach the Notebook to the task or incident that has been created or use it in the Wash-Up/ Post Incident meeting.
This ensures that the original template notebook stays intact and unchanged and it is easy (which is always good when you are called out at 3am!) to create a uniquely named notebook .
Azure DevOps
Nick has placed this code into the deploy step in Azure DevOps which will deploy the template Notebooks from source control into the common folder and then this code will dynamically create the index Notebook each time there is a release.
Whilst the initial use case is incident response, this could easily be adapted for Notebooks used for Common Tasks or Run Books.
Notebooks
There are a number of Notebooks for different issue stored in directories. For this post, I have used the Notebooks from Microsoft that explain SQL 2019 features and troubleshooting which you can find in their GitHub repositories by following this link
The Azure DevOps deploys the Notebooks to a directory which then looks something like this
Some directories of Notebooks in a directory
Create an Index Notebook
Here is the code to create an index Notebook
This creates a Notebook in the root of the folder. It also uses the new -Collapse parameter in New-AdsNoteBookCell that creates the code blocks with the code collapsed so that it looks neater. The index Notebook looks like this in the root of the folder
Three O’Clock in the Morning
It’s 3am and I have been called out. I can open up the Index Notebook, find the set of queries I want to run and click the run button.
A new workbook opens up, named with my name and the time and I can get to work 🙂 I think it’s neat.
Here’s a video
Thanks Nick.
Maybe you can find him at SQL Bits next year. Did you know that SQL Bits 2020 was announced?
The latest update to the ADSNotebook PowerShell module I blogged about here now enables the creation of PowerShell notebooks with PowerShell.
You can install the module with
Install-Module ADSNotebook
or if you have already installed it you can use
Update-Module ADSNotebook
In the latest release, there is an extra parameter for New-AdsWorkBook of -Type which will accept either SQL or PowerShell
Create a PowerShell Notebook with PowerShell Rob
OK!
Here is some code to create a PowerShell Notebook. First we will create some cells using New-AdsWorkBookCell including all the markdown to add images and links. You can find my notebooks which explain how to write the markdown for your notebooks in my GitHub Presentations Repository
$introCelltext = "# Welcome to my Auto Generated PowerShell Notebook
## dbatools
dbatools is an open-source PowerShell Module for administering SQL Servers.
You can read more about dbatools and find the documentation at [dbatools.io](dbatools.io)
"
$SecondCelltext = "### Installation
You can install dbatools from the PowerShell Gallery using `Install-Module dbatools`
"
$thirdcelltext = "Install-Module dbatools"
$fourthCelltext = "### Getting Help
You should always use `Get-Help` to fins out how to use dbatools (and any PowerShell) commands"
$fifthcelltext = "Get-Help Get-DbaDatabase"
$sixthCelltext = "Try a command now. get the name, owner and collation of the user databases on the local instance"
$seventhCellText = "Get-DbaDatabase -SqlInstance localhost -ExcludeSystem | Select Name, Owner, Collation"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
$fourth = New-ADSWorkBookCell -Type Text -Text $fourthCelltext
$fifth = New-ADSWorkBookCell -Type Code -Text $fifthcelltext
$sixth = New-ADSWorkBookCell -Type Text -Text $sixthCelltext
$seventh = New-ADSWorkBookCell -Type Code -Text $seventhCellText
Then we will create a new workbook using those cells
This will create the markdown link for embedding PowerShell code in a Text Cell for a SQL Notebook as described in this blog post
New-ADSWorkBookCell
This command will create a workbook text cell or a code cell for adding to the New-ADSWorkBook command
New-ADSWorkBook
This will create a new SQL Notebook using the cell objects created by New-ADSWorkBookCell
Usage
Convert-ADSPowerShellForMarkdown
Convert-ADSPowerShellForMarkdown -InputText "Get-ChildItem" -LinkText 'This will list the files' -ToClipBoard
Converts the PowerShell so that it works with MarkDown and sets it to the clipboard for pasting into a workbook cell
New-ADSWorkBookCell
$introCelltext = "# Welcome to my Auto Generated Notebook
## Automation
Using this we can automate the creation of notebooks for our use
"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
Creates an Azure Data Studio Text cell and sets it to a variable for passing to New-AdsWorkBook
New-ADSWorkBook
$introCelltext = "# Welcome to my Auto Generated Notebook
## Automation
Using this we can automate the creation of notebooks for our use
"
$SecondCelltext = "## Running code
The next cell will have some code in it for running
## Server Principals
Below is the code to run against your instance to find the server principals that are enabled"
$thirdcelltext = "SELECT Name
FROM sys.server_principals
WHERE is_disabled = 0"
$Intro = New-ADSWorkBookCell -Type Text -Text $introCelltext
$second = New-ADSWorkBookCell -Type Text -Text $SecondCelltext
$third = New-ADSWorkBookCell -Type Code -Text $thirdcelltext
$path = 'C:\temp\AutoGenerated.ipynb'
New-ADSWorkBook -Path $path -cells $Intro,$second,$third
Creates 3 cells with New-AdsWorkBookCells to add to the workbook,
two text ones and a code one, then creates a SQL Notebook with
those cells and saves it as C:\temp\AutoGenerated.ipynb
Installation
You can install this Module from the PowerShell Gallery using
Install-Module ADSNotebook
Compatability
This module has been tested on Windows PowerShell 5.1, PowerShell Core 6 and PowerShell 7 on Windows 10 and Ubuntu
I have been asked a couple of times recently what my Visual Studio Code extensions are at the moment so I thought I would write a quick post and also look at workspaces and how you can enable and disable extensions within them
Listing Extensions
From the command line you can list your extensions using
You can also see them in the view on the left of default Visual Studio Code and open them with CTRL + SHIFT + X (unless like me you have Snaggit installed and it has taken that shortcut
Installing Extensions
You can install extensions by opening the Extensions view in Visual Studio Code and searching for the extension. The list I have below has the precise names for each extension which you can use to search
You can also install extensions from the command-line with
I am going to list these in alphabetical order by display name for ease (my ease that is!)
Because Chrissy LeMaire and I are writing dbatools in a Month of Lunches using AsciiDoc, it makes sense to have an extension enabling previewing and syntax, you can find it here
For interacting with Azure I use the Azure Account Extension – ms-vscode.azure-account
I use Azure CLI so I make use of the functionality of the Azure CLI Tools extension ms-vscode.azurecli
For interacting with Azure Repos I use the ms-vsts.team extension
When creating ARM templates, this extension is very useful msazurermtools.azurerm-vscode-tools
I have a few theme extensions, this one is for fun in demos 😉 beardedbear.beardedtheme
The blackboard theme is my default one gerane.theme-blackboard
Chasing closing brackets is much easier with the Bracket Pair Colorixer, I use the beta version coenraads.bracket-pair-colorizer-2
I am rubbish at spelling and typing so I use this to help point out the issues! streetsidesoftware.code-spell-checker
Using the Docker extension adds another view to Visual Studio Code to ease working with containers ms-azuretools.vscode-docker
As an open-source project maintainer it is good to be able to work with GitHub pull requests without leaving Visual Studio Code github.vscode-pull-request-githubPreview
GitLens is absolutely invaluable when working with source control. It has so many features. This is an absolute must eamodio.gitlens
Working with Kubernetes? This extension adds another view for interacting with your cluster ms-kubernetes-tools.vscode-kubernetes-tools
Visual Studio Live Share enables you to collaborate in real-time in Visual Studio Code with your colleagues or friends. I blogged about this here ms-vsliveshare.vsliveshare
I love writing markdown and this linter assists me to ensure that my markdown is correct davidanson.vscode-markdownlint
The Material Icon Theme ensures that there are pretty icons in my editor! pkief.material-icon-theme
I have both the PowerShell extension ms-vscode.powershell and the PowerShell preview extension ms-vscode.powershell-preview installed but only one can be enabled at a time
This suite of extensions enables easy remote development so that you can develop your PowerShell scripts, for example, inside a ubuntu container running PowerShell 7 or inside Windows Subsystem for LInux ms-vscode-remote.vscode-remote-extensionpackPreview
Writing for cross-platform means looking out for line endings and this extension will display them and any whitespace in your editor medo64.render-crlf
An absolutely essential extension which enables me to backup all of my Visual Studio Code settings, shortcuts, extensions into a GitHub gist and keep all of my machines feeling the same. shan.code-settings-sync
For working with SQL Server within Visual Studio Code and having a view for my instances as well as a linter and intellisense I use ms-mssql.mssql
Yaml files and spaces! I no longer get so confused with this extension to help me 🙂 redhat.vscode-yaml
Workspaces
Now that is a lot of extensions and I dont need all of them everytime. I use workspaces to help with this. I will create a workspace file for the project I am working on.
I open or create the folders I will be working on and then click File and Save Workspace As and save the file in the root of the folder.
Now, the next time I want to open the workspace, I can open the workspace file or if I open the folder Visual Studio Code will helpfully prompt me
Now I can have all of my settings retained for that workspace
For this folder, I am ensuring that the PowerShell extension uses the PSScriptAnalyzer Settings file that I have created so that it will show if the code is compatible with the versions of PowerShell I have chosen. I can define settings for a workspace in the settings file, which you can open using CTRL and ,
But I can also enable or disable extensions for a workspace
So everytime I open this workspace I am only loading the extensions I want
You can download the latest insiders edition from the link above, it can be installed alongside the stable release.
To access many of the commands available use F1 to open the command palette (like many of my tips this also works in Visual Studio Code). You can then start typing to get the command that you want.
You can then hit enter with the command that you want highlighted, use the mouse or use the shortcut which is displayed to the right.
In a new notebook, you can click the drop down next to kernel and now you can see that PowerShell is available
When you choose the PowerShell kernel, you will get a prompt asking you to configure the Python installation
If you have Python already installed you can browse to the location that it is installed or you can install Python. In the bottom pane you will be able to see the progress of the installation.
When it has completed, you will see
You may also get a prompt asking if you would like to upgrade some packages
Again this will be displayed in the tasks pane
Adding PowerShell
To add PowerShell Code to the notebook click the Code button at the top of the file
or the one you can find by highlighting above or below a block
I did not have intellisense, but you can easily write your code in Azure Data Studio or Visual Studio Code and paste it in the block.
This was because he had the PowerShell extension installed and I did not (I know !!) If you find you dont have intellisense then install the PowerShell extension!
Clicking the play button (which is only visible when you hover the mouse over it) will run the code
You can clear the results from every code block using the clear results button at the top
Otherwise, you can save the results with the Notebook by saving it. This is the part that is missing from running PowerShell in the Markdown blocks in a SQL Notebook as I described here
I am looking forward to how this develops. You can find my sample PowerShell notebook (with the code results) here
