Press "Enter" to skip to content

Category: Powershell

Automating a Permissions Audit with dbatools

Published 2020-09-09 by Kevin Feasel

Garry Bargsley walks us through the process of a permissions audit made easier:

My post for T-SQL Tuesday #130 is going to be how I automated my companies SQL Server Permissions Audit process. Even though this only occurs every quarter it is something that I as the DBA never look forward to and the business partners never really enjoy. In an effort to streamline the process from start to finish I built automation using PowerShell, dbatools and ImportExcel. The process now builds the permissions audit file, extracts individual users from Active Directory and e-mails the information to the appropriate team(s) for review.

Read on for details about the process.

Comments closed

Automating Azure Resource Deletion

Published 2020-09-09 by Kevin Feasel

Drew Skwiers-Koballa has put together a runbook to remove Azure resources tagged in a certain way:

Microsoft puts a lot of effort into making it easy for you to log on to portal.azure.com and spin up a VM, SQL Database, Function, or other instance. The Visual Studio Enterprise subscription with $150 credit per month can go quickly if you’re not careful to stop or remove big ticket items as soon as you’re done with them. Even if you have a virtually unlimited Azure account connected to a credit card, expense account, or a trust fund – you probably don’t want to accidentally leave something running longer than it is needed.

With an Azure Automation account and a Powershell workflow runbook I’m able to use resource tags to set resources for autodeletion by date or immediately that evening.

Click through for that runbook.

Comments closed

Data Importation and Exportation with dbatools

Published 2020-09-03 by Kevin Feasel

Mikey Bronowski continues a series on dbatools functionality vis-a-vis SQL Server Management Studio:

The SSMS offers to script out lots of the SQL Server objects, however it can be limited in some areas. Using Get-Dba* commands and piping them into Export-DbaScript may add few more options. For example SQL Agent jobs:

Click through for just shy of a dozen cmdlets to help you run your data import-export business.

Comments closed

Dealing with Failing SQL Agent Jobs

Published 2020-09-02 by Kevin Feasel

Garry Bargsley has started a four-part series:

SQL Server Agent Jobs are one of the core features of SQL Server. Agent Jobs perform many actions, from maintenance tasks (server and database), data movement flows, replication, log shipping, data cleanup, health checks and many more. Since Agent Jobs are a critical component in a data organization, it is important to know when Agent Jobs do not succeed. There are several ways to accomplish the monitoring of failed Agent Jobs, from easy to more complex. This four part series is going to cover how you, the DBA, can be notified of failing Agent Jobs in your SQL Server environment.

Click through for part one, which is all about finding failed jobs and filtering down to relevant jobs using dbatools.

Comments closed

Loading dbachecks Results into Excel

Published 2020-08-31 by Kevin Feasel

Jess Pomfret shares two methods of moving dbachecks results into Excel:

I got a message from a friend on Twitter last night asking ‘Is there an easy way to get dbachecks backup info into an Excel spreadsheet?’.  I sent them a couple of ideas, but figured this is a great use case that many people might be interested in. Pairing infrastructure testing using dbachecks with creating Excel reports with the ImportExcel module is a great addition to your automation tool belt. I also had ImportExcel on my mind this week after watching some great demos from Mikey Bronowski (b|t) at a user group earlier this week.

Click through for both examples.

Comments closed

Creating a Fail-Safe Agent in SQL Server

Published 2020-08-26 by Kevin Feasel

Garry Bargsley wants the buck to stop somewhere:

Did you know it is possible for SQL Server Agent to  alert you of problems if something goes haywire with your Agent? Have you ever had an issue with Alerts not being sent after critical events? Then you might need to configure the SQL Server Agent Fail-Safe Operator to save the day. A Fail-Safe WHAT you might say?? This is a special SQL Agent Operator configured in the SQL Agent Alert System in the chance any of the following situations occur.

Click through for the situations as well as configuration steps using Powershell + dbatools.

Comments closed

Managing User Input and Creating Menus in Powershell

Published 2020-08-19 by Kevin Feasel

Mark Wilkinson wants a bit of user interaction:

Fully automated hands-off PowerShell scripts can be extermely useful for the DBA or System Administrator, but what if you need to get input from the user, or maybe you want to implement a menu system? Like most things related to PowerShell, you have a few options:

– Read-Host
– [Console] object methods

Most use cases are covered by Read-Host, but if you need something a little more flexible, the [Console] methods might be the way to go.

Read on to see how these work, as well as one way to create a menu.

Comments closed

Working with Central Management Servers in dbatools

Published 2020-08-17 by Kevin Feasel

Mikey Bronowski continues a series on dbatools:

The built-in feature of the SSMS allows us to configure a group of SQL instances and run queries against multiple instances at once. With the registered servers you can also build a list of SQL Servers in one place, so everyone with access to the CMS can see them. First, we will start by creating registered servers and server groups.

This is an underrated set of functionality for SQL Server and dbatools does a good job working with it.

Comments closed

Splatting in Powershell

Published 2020-08-14 by Kevin Feasel

Mark Wilkinson describes splatting in Powershell and shows how you can use it to handle optional parameters:

I have to start of by saying I hate the name “splatting”. I didn’t come up with it, and I don’t like using it, but it’s the only word we have. Splatting is a way to pass parameter values to a function using a single array or hashtable. In this post we’ll be talking about hashtables because I think it is the more useful of the two.

Splatting is easy to explain in an example. 

And then that’s exactly what Mark gives us. Click through for the example as well as how you can set those optional parameters.

Comments closed