PowerShell Notebooks in Azure Data Studio

The latest release of the insiders edition of Azure Data Studio brings the first edition of PowerShell Notebooks!

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.

Interestingly Shawn Melton ( t ) did

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

Getting SQL Server installation date with PowerShell using dbatools

Most of my writing time at the moment is devoted to  Learn dbatools in a Month of Lunches which is now available but here is a short post following a question someone asked me.

How can I get the Installation Date for SQL Server on my estate into a database with dbatools ?

You can get the date that SQL Server was installed using the creation date of the NT Authority\System login using T-SQL

SELECT create_date 
FROM sys.server_principals 
WHERE sid = 0x010100000000000512000000

With dbatools

To do this with dbatools you can use the command Get-DbaInstanceInstallDate command

Get-DbaInstanceInstallDate -SqlInstance localhost 

More than one instance

If we want to get the installation date for more than one instance we can simply create an array of instances for the SqlInstance parameter

Get-DbaInstanceInstallDate -SqlInstance localhost, localhost\DAVE

Get the Windows installation date too

You can also get the windows installation date with the IncludeWindows switch

Get-DbaInstanceInstallDate -SqlInstance localhost, localhost\DAVE -IncludeWindows 

Gather your instances

How you get the instances in your estate is going to be different per reader but here is an example using Registered Servers from my local registered servers list, you can also use a Central Management Server

Get-DbaRegisteredServer -Group local 

So we can gather those instances into a variable and pass that to Get-DbaInstanceInstallDate

$SqlInstances = Get-DbaRegisteredServer -Group local 
Get-DbaInstanceInstallDate -SqlInstance $SqlInstances 

Add to database

To add the results of any PowerShell command to a database, you can pipe the results to Write-DbaDbTableData

$SqlInstances = Get-DbaRegisteredServer -Group local 

$writeDbaDataTableSplat = @{
    SqlInstance = 'localhost'
    Table = 'InstallDate'
    Database = 'tempdb'
    Schema = 'dbo'
    AutoCreateTable = $true
}

Get-DbaInstanceInstallDate -SqlInstance $SqlInstances | Write-DbaDataTable @writeDbaDataTableSplat

This will create a table called InstallDate and put the results of the Get-DbaInstanceInstallDate command. Note – If you want to try this code, I would advise using a different database than tempdb!!

It is important to note that the table created may not have the most optimal data types and that you may want to pre-create the table.

So there you go, all the installation dates for your estate in a database table. Hope that helps you Jonny.

MEAP MEAP – #dbatoolsMoL – Live Book edition

It’s been a busy time!

As well as many other things, the fantastical BDFL of dbatools Chrissy Lemaire @cl and myself have written enough of a chunk of Learn dbatools in a Month of Lunches that our publisher Manning Publications have agreed to release it as a MEAP. Not a text book, this book is written in a fun conversational style and split up into chapters that you can read in a lunch-time.

It is impossible for me to hear MEAP and not think of this 🙂

but I expect you are wondering what a MEAP is?

What is MEAP?
A book can take a year or more to write, so how do you learn that hot new technology today? The answer is MEAP, the Manning Early Access Program. In MEAP, you read a book chapter-by-chapter while it’s being written and get the final eBook as soon as it’s finished. If you pre-order the pBook, you’ll get it long before it’s available in stores.

https://www.manning.com/meap-program

Basically, to make it easy to get and for those that like to get in early, you can order the book and get the first 4 chapters (three in reality) RIGHT NOW!! (It also means that Chrissy and I have to write the rest of book – dang still going to be busy!)

Simply head over to https://beard.media/bookblog and use the code mlsewell and you can get access to the book too.

This will also give you access to the live book.

live book

The live book is fantastic, you can read the whole book from within your browser. See the three icons that appear to the right of the book?

3 little icons (no porridge)

The left hand one enables you to bookmark an important part so that you can come back to it easily using the bookmarks link in the top right

bookmarks

The middle icon enables you to write notes for yourself, maybe ways that you can use the information or maybe comments about an awesome Italian.

Shoes

The last one is the way that you can make comments and engage us , the authors in conversation, ask questions, request clarification or wonder about Dutch data manglers

I think its down to PII

If you select a piece of text, another menu opens up

The first icon lets you highlight the text, to make it easier to find later

Hover over the highlight and you can choose different colours for different things.

or even create pretty pictures for Mathias

Mathias – Why isn’t he an MVP?

You can choose to annotate, which is sort of like highlighting and writing a note with the next icon

When you want to share a link to a particular part of the book with someone else, you can highlight part of it and click the link icon

It’s easy to start PowerShell as another user as long as you remember when to press SHIFT

Which will highlight the paragraph and open a dialogue at the bottom where you can create and copy the link.

By far the most important part for Chrissy and I is the last link. When you find something wrong you can mark it for our attention. Yes, even with Chrissy and I proof reading each others words, the fabulous proof reader Cláudio Silva (b | t) and awesome tech editor Mike Shepard (b | t)  as well as many community reviewers there are still, and will continue to be, issues. So when you find them, highlight them and click the right hand most link

with with more more than than one one

This will open up as shown so that you can fill in what was wrong (Please don’t report this error again Shane b | t has beaten you to it!)

You will have noticed on social media and elsewhere that we have left some easter eggs in the book

Whenever you find them or whenever you want to talk about the book on social media, please use the hashtag #dbatoolsMoL – you never know what goodies may end up in your inbox.

Oh and if you have got this far and don’t know what dbatools in a Month of Lunches is, listen to the hair and read more https://dbatools.io/meap/