Press "Enter" to skip to content

Category: T-SQL Tuesday

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

General Principles for Automation

Published 2020-09-09 by Kevin Feasel

Eitan Blumin thinks through a series of general principles around when to automate things:

What I’m gonna do is try to look at the topic of automation and productivity in more general terms, in the hopes of conveying to you my overall methodology while working on a given task.

I will try to formulate a list of “Laws” or “Maxims“, that together will try to represent my personal approach to handling tasks, with the main goal in mind to maximize productivity.

Click through for the list.

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

Ensuring Trace Flag Consistency Across Instances

Published 2020-09-08 by Kevin Feasel

Taiob Ali creates a process to track ensure specific trace flags are in use:

Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.

My team members fixed this issue by leveraging startup stored procedures.

Click through for the explanation.

Comments closed

T-SQL Tuesday 129 Round-Up

Published 2020-08-25 by Kevin Feasel

Tamera Clark wraps up T-SQL Tuesday #129:

Well, I don’t even know what to say, such great ideas and creativeness. The last couple of days I have internally struggled with, as Glenn wrote this ” human malware situation”. Reading all of these posts has made me smile.

Read on for twelve people’s thoughts on this month’s topic, a time capsule for SQL Server professionals.

Comments closed

T-SQL Tuesday Roundup: Incident Reports

Published 2020-07-27 by Kevin Feasel

Kerry Tyler rounds up the usual suspects:

Hello T-SQL Tuesday Readers! I’m sorry for being really late in getting this post out this week.

So! A couple of weeks ago, for this month’s topic, I asked everyone to post about something that broke or went wrong, and what it took to fix it. Last week, fourteen of you responded with your stories of woe so we could all learn from your incidents and recoveries in a constructive way, like pilots do. Here’s the recap of those posts, in the order that they came in.

Read on for a summary of all of the entrants for this month.

Comments closed

Lessons Learned from a Non-Standard Default Database

Published 2020-07-15 by Kevin Feasel

Richard Swinbank tells a tale of woe:

Migration day went pretty smoothly – it even looked like we’d found and amended every connection string likely to disable a downstream system. The instance from which we were migrating was a bit of a food court, so before signing off I opened SSMS to check on some other system issue… and found I could no longer log in.

Read on to understand why, as well as what Richard did to fix this.

Comments closed

Diagram Visualization with Graphviz

Published 2020-06-10 by Kevin Feasel

Mikey Bronowski walks through an introduction to the Graphviz diagramming language:

I came across Graphviz which is an open-source graph visualization software initiated by AT&T Labs Research. It can process the graphs that are written in the DOT language.

What is the DOT language?

In short, it is a graph description language that has few keywords like graph, digraph, node, edge. You cannot miss it has something to do with graphs.

I’ve used the R implementation of this as well. It doesn’t create beautiful diagrams, but it is fast, easy, and the output makes sense.

Comments closed