Press "Enter" to skip to content

Category: Powershell

Settings for Tuning Backup Performance

Published 2020-05-06 by Kevin Feasel

Stuart Moore takes us through backup tuning options in dbatools:

Backup-DbaDatabase produces a lot of output, but the one you’re really going to be interested in is Duration. You’re job is going to be to minimise this as much as possible without impacting database performance.

To test changes in isolation you’ll want to remove the biggest variables in backup performance, network and storage speed. To do this, there’s a magical backup device that’s got near infinite speed and storage you can use. This is the black hole known as the bit bucket, /dev/null or NUL:, everything thrown into this pit disappears as fast as it’s put in.

Read the whole thing. And if you’re interested in a systematic method of testing and understanding the impact of these settings on your system, I have a presentation on the topic.

Comments closed

Using Powershell to Configure Database Mail and SQL Agent Alerts

Published 2020-04-30 by Kevin Feasel

Eric Cobb shows us how to use Powershell to set up database mail and SQL Agent alerts:

As a DBA, you need to know when there’s a problem on your SQL Servers. And while I highly recommend you use a full-fledged monitoring system, there are also some things you can set up on your SQL Servers so that they will tell you when certain things go wrong. This doesn’t replace a full monitoring system, but setting up the below alerts will give you notification when SQL Server encounters things like corruption or resource issues.

Even with a full-fledged monitoring system, there are places where you can still make use of mail and side alerts.

Comments closed

Developing for Databricks with VS Code

Published 2020-04-29 by Kevin Feasel

Gerhard Brueckl tells us what comes after notebooks for users with development backgrounds:

For those users Databricks has developed Databricks Connect (Azure docs) which allows you to work with your local IDE of choice (Jupyter, PyCharm, RStudio, IntelliJ, Eclipse or Visual Studio Code) but execute the code on a Databricks cluster. This is awesome and provides a lot of advantages compared to the standard notebook UI. The two most important ones are probably the proper integration into source control / git and the ability to extend your IDE with tools like automatic formatters, linters, custom syntax highlighting, …

While Databricks Connect solves the problem of local execution and debugging, there was still a gap when it came to pushing your local changes back to Databricks to be executed as part of a regular ETL or ML pipeline. So far you had to either “deploy” your changes by manually uploading them via the Databricks UI again or write a script that uploads it via the REST API (Azure docs).

Gerhard has a nice extension for Visual Studio Code which helps with this. I’m also a huge fan of the DatabricksPS module, so I’ll happily plug that here.

Comments closed

Operations Testing with Pester

Published 2020-04-22 by Kevin Feasel

Sheldon Hull takes us through using Pester to automate operations tasks:

In my example, let’s start small and say you just have PowerShell, and some servers.

What I’ve discovered is that to actual validate DevOps oriented work is completed, you typically go through the equivalent of what a Cucumber test would have. This “checklist” of validations is often manually performed, lacking consistency and the ability to scale or repeat with minimal effort.

Consider an alternative approach to helping solve this issue, and expanding your ability to automate the tedious testing and validation of changes made.

Read on for an example as well as some additional thoughts from Sheldon.

Comments closed

Documenting SQL Server Tables

Published 2020-04-17 by Kevin Feasel

Phil Factor has a way to create table documentation in source control and propagate it to the actual database:

It has always been a problem that documentation in the source, where it should be, is not then passed into the live database when the build script is executed. In a table, you have columns, constraints and indexes that you are likely to document using line-ending comments and block comments. You probably have a big block comment at the start, explaining the table. This information should be available in the live database. Microsoft don’t have a good answer and vaguely go on about adding comments in extended properties. Well, that’s fine but it hasn’t happened, unsurprisingly: Have you ever tried to do it? It is an almost impossible task, even with SQL Doc.

My solution is to execute my finely-documented build script as usual to create the latest version of the database, and then process the same script in PowerShell to add all the comments and documentation as extended properties in the right place in the live database.

It’s an interesting approach to a classic problem.

Comments closed

Installing Power BI Management Cmdlets for Powershell

Published 2020-04-16 by Kevin Feasel

Nicky van Vroenhoven takes us through installation of the Power BI Management commands for Powershell:

The first hurdle I encountered was the fact that there was already an older version installed. After some fiddling, trying to uninstall the old modules and going through the fixed described below, I figured the documentation was probably the best start, and it was. 🙂

Nicky documents several issues but was ultimately able to pull it in.

Comments closed

Powershell 7 Pipeline Chain Operators

Published 2020-04-13 by Kevin Feasel

Patrick Gruenauer show off a pair of new operators in Powershell 7:

With PowerShell 7 new operators were introduced. We call them chain operators. Chain operators enables you to do something after doing something. They use the $? and $LASTEXITCODE variable to determine whether a command on the left hand of the pipe failed or succeded.

Let’s cover this topic by demonstrating some examples to fully understand the new pipeline technology.

This is definitely Bash-inspired and I’m happy they made this move.

Comments closed