Press "Enter" to skip to content

Category: Powershell

GetAllTheErrorLogs: Combining Multiple Log Sources

Published 2020-09-10 by Kevin Feasel

Aaron Bertrand has a new project:

There’s a whole lot of grunt work in there that shouldn’t have to be done by a person. I don’t think you could automate the whole thing, because it is hard to predict exactly what events will be interesting and not, but I think 90% is achievable.

A colleague mentioned that they want to build something that would help, but even when that happens, that would up in proprietary code that only helps us. I saw Drew Furgiuele’s post on Building a Better Get-SQLErrorLog, and that gave me some ideas for what I would build. After reaching out to Drew, I created a GitHub repository with a working name of GetAllTheErrorLogs. Its elevator pitch is a simple sentence:

Powershell to assemble a timeline combining salient events from Windows Event Log, Failover Cluster log, and SQL Server errorlog.

Click through for the details as well as Aaron’s current progress.

Comments closed

Automating dbachecks Runs

Published 2020-09-09 by Kevin Feasel

Jess Pomfret automates runs of dbachecks via scheduled task:

To automate the running of our daily checks we’ll first create a PowerShell script and then schedule that using task scheduler.  If you have other enterprise scheduling tools available you could easily use those instead to invoke the PowerShell script.

The script for my example, shown below, is pretty simple. I have a section to define where the data will be stored (the ability to save dbachecks result information straight into a database was introduced with dbachecks 2.0 and so I would highly recommend updating if you’re on an earlier version).

Click through for the full explanation and code.

Comments closed

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