Press "Enter" to skip to content

Category: Powershell

New dbatools Release

Published 2018-09-07 by Kevin Feasel

Chrissy LeMaire announces new changes to dbatools:

Marching onward to dbatools 1.0, a ton of commands have been renamed to align with our now mature naming scheme. These changes were made in today’s release, version 0.9.410 aka regularlegs, now available on GitHub and the PowerShell Gallery.

Here’s the general idea:

  • DbaDatabase has mostly been renamed to DbaDb with a couple exceptions

  • DbaSql has been changed to just Dba, no exceptions. Think of Dba as “Sql”.

  • DbaConfig has been renamed to DbatoolsConfig

  • TempDbConfiguration has been renamed to TempdbConfig

  • All Configuration commands are Config except SpConfigure

  • DbaDacpac has been renamed to DbaDacPackage. Dac is the prefix for our data-tier application commands.

  • DbaDbQueryStoreOptions has been renamed to DbaDbQueryStoreOption

Some of this is in preparation for breaking changes in dbatools 1.0.  There’s a lot of stuff in this release, so check it out.

Comments closed

Important Community Tools

Published 2018-09-04 by Kevin Feasel

Chrissy LeMaire shares some great community-driven Powershell modules:

dbops

dbops was created by Kirill Kravtsov.

dbops is a Powershell module that provides Continuous Integration/Continuous Deployment capabilities for SQL database deployments.

It is based on DbUp, which is DbUp is an open source .NET library that helps you to deploy changes to SQL Server databases. dbops currently supports both SQL Server and Oracle.

Read on for links to several more projects.

Comments closed

Confirmation In Powershell

Published 2018-08-29 by Kevin Feasel

Shane O’Neill shows off how easy it is to add confirmation checkpoints into Powershell code:

Finally it works… BUT…

  • It’s 26 lines long for this piece of code. If we have multiple then this is going to blow up size wise, and
  • There’s definitely more but I think I’ve gotten the point across (or I hope I have).

So let’s try and use what’s built in to PowerShell.

The built-in version is 3 lines of code and provides more functionality.  You probably want to use that version; click through to see this all in action.

Comments closed

Creating Timelines With dbatools

Published 2018-08-22 by Kevin Feasel

Marcin Gminski shows how to pull SQL Agent and backup history out of SQL Server and display it as a visual history timeline:

Currently, the output from the following commands is supported:

  • Get-DbaAgentJobHistory
  • Get-DbaBackupHistory

You will run the above commands as you would normally do but pipe the output to ConvertTo-DbaTimeline, the same way as you would with any other ConverTo-* PowerShell function. The output is a string that most of the time you will save as file using the Out-File command in order to open it in a browser.

Then, with the ConvertTo-DbaTimeline cmdlet, you can convert that into an HTML page which looks pretty good.

Comments closed

Deploying To Power BI Report Server Using Powershell

Published 2018-08-21 by Kevin Feasel

Rob Sewell shows us how to automate Power BI Report Server deployments:

But I dont want to have to do this each time and there will be multiple pbix files, so I wanted to automate the solution. The end result was a VSTS or TFS release process so that I could simply drop the pbix into a git repository, commit my changes, sync them and have the system deploy them automatically.

As with all good ideas, I started with a google and found this post by Bill Anton which gave me a good start ( I could not get the connection string change to work in my test environment but this was not required so I didnt really examine why)

I wrote a function that I can use via TFS or VSTS by embedding it in a PowerShell script.

Click through for the script.

Comments closed

Default Parameter Values In Powershell

Published 2018-08-16 by Kevin Feasel

Chrissy LeMaire explains what $PSDefaultParameterValues is and places where she finds it useful:

$PSDefaultParameterValues is a hashtable available in PowerShell that can set defaults for any command that you run. In it’s simplest form, setting a default parameter value can look like this:

After running the above code, Get-DbaDatabase will show verbose output every time it’s executed, without me having to specify -Verbose. If I need to override that verbose flag for some reason, I can simply add -Verbose:$false to my Get-DbaDatabase command.

Read on for plenty of good use cases and additional resources from sharp people.

Comments closed

Power BI Administration With Powershell

Published 2018-08-08 by Kevin Feasel

Adam Saxton shares a video covering what is available in the MicrosoftPowerBIMgmt Powershell module:

The Power BI PowerShell and the Admin API allow you to quickly inventory your organization and manage workspaces and access. Forget the hastle of setting up the App registration within Azure Active Directory for API access, the Power BI PowerShell cmdlets take care of it for you. Just install and start using today!

Click through for that video.

Comments closed

Configuring An Azure Runbook For Index Maintenance

Published 2018-08-03 by Kevin Feasel

Jim Donahoe explains how to perform index and statistics maintenance for Azure SQL Database, where you don’t have SQL Agent available:

I had a lot of issues when I created my first one, and after discussing with some folks, they had the same issues.  I searched for the best blog posts that I could find on the subject, and the one I LOVED the most was here: Arctic DBA.  He broke it down so simply, that I finally created my own pseudo installer and I wanted to share it with all of you.  Please, bear in mind, these code snippets may fail at anytime due to changes in Azure.

**IMPORTANT**

These next steps assume the following:

You have created/configured your Azure Automation Account and credential to use to execute this runbook.

Read on for a reasonably short Powershell script and a modified version of Ola Hallengren’s index maintenance procedures.

Comments closed

Building An Azure VM With Powershell

Published 2018-07-31 by Kevin Feasel

Garry Bargsley shows us how to provision and build a VM in Azure using nothing but Powershell:

I spent the bulk of my day Wednesday going through the Prelab steps outlined in the lab.  I was extremely impressed by this lab and how every step was correct and accurate down to the letter.  Then the more I thought about it, the steps are built around using an Azure Virtual Machine.  With this you get a common machine, framework and steps to build around.  You do not have to worry about the users’ local settings or scenario.  You are starting from the exact same point of reference every time.  So that was fun to connect via SSH to a Linux machine and install SQL Server 2017 and Docker from the command line.  While I know it was easy because someone was telling me what to type, it was still fun to see how the other side (Linux People) live.

Today I was in an adventurous mood to try something new.  I had been wanting to put together a PowerShell script that would deploy an Azure Virtual Machine.  I started down the path a couple time and got stuck so I lost interest.  I thought this was the perfect opportunity to get over the hurdle and combine the Prelab steps in this lab with doing those steps with PowerShell.  So below you will find my first go at building an Azure Virtual Machine using PowerShell to replace the manual steps in the Prelab process.  Not that there was anything wrong with those steps, I just want to try and use a tool that I have been working to learn and use on a day to day basis.  Wish me luck.

Read on for a step-by-step guide.

Comments closed

Parsing T-SQL Scripts With Pester

Published 2018-07-26 by Kevin Feasel

Rob Sewell shows us how to use Pester to ensure that a set of SQL scripts are valid T-SQL:

This is a quick Pester test I wrote to ensure that some SQL Scripts in a directory would parse so there was some guarantee that they were valid T-SQL. It uses the SQLParser.dll and because it was using a build server without SQL Server I have to load the required DLLs from the dbatools module (Thank you dbatools 🙂 )

It simply runs through all of the .sql files and runs the parser against them and checks the errors. In the case of failures it will output where it failed in the error message in the failed Pester result as well.

This particular example doesn’t ensure that the scripts do what you want them to do, but hey, Pester was built for that as well.

Comments closed