Press "Enter" to skip to content

Category: Administration

Automating a SQL Server Build

Published 2025-08-28 by Kevin Feasel

K. Brian Kelley doesn’t need that George Jetson button-clicking finger:

I am frequently building up my SQL Server environments, especially my test lab. However, manually performing SQL Server backups, running scripts to set up security, and the rest of the tasks are time-consuming. What can I do to automate things?

Read on for the answer. If you are administering a larger number of SQL Server instances than you can count with, let’s say, one hand, it’s a great idea to script out your server setup and configuration process.

Comments closed

Resetting the sa Password on a Locked-Out SQL Server Instance

Published 2025-08-28 by Kevin Feasel

Tim Radney jimmies the lock:

Getting locked out of a SQL Server instance can happen due to a number of situations. The most common scenario I’ve encountered is when a SQL Server is moved from one domain to another without the domain being trusted or having a local SQL admin account. I recently encountered another incident where a DBA was attempting to fail over a log-shipped instance to another instance. Part of their run book included a script to disable a set of users to block production access to the instance. The problem was, the production instance was on a cluster, unlike the development and QA systems where the script had been tested. Disabling the users in production took down the instance preventing the tail log backups from occurring. We had to get the instance back up in order to take those final backups.

What can you do if you find that you’re locked out of a SQL Server instance?

Read on for that answer.

1 Comment

The Basics of Log Shipping

Published 2025-08-28 by Kevin Feasel

Kevin Hill explains why log shipping is still a viable disaster recovery approach, 25 years later:

In a world where shiny new HA/DR features get all the press, there’s one SQL Server technology that just keeps doing its job.

Log Shipping has been around since SQL Server 2000. It doesn’t make headlines, it doesn’t have fancy dashboards, and it’s not going to win you any architecture awards. But for the right environment and use case, it’s rock solid and can save your bacon job in a disaster.

Read on for a briefing on the topic.

Comments closed

Finding Necessary Permissions for DMVs

Published 2025-08-25 by Kevin Feasel

Randolph West has a script for us:

Part of my job at work is to update Transact-SQL reference content. System dynamic management views (DMVs) have permissions that are managed in the SQL Server Database Engine source code, so it’s a little tricky to figure out the required permissions for a specific DMV.

This blog post provides a stored procedure that uses sys.fn_builtin_permissions to calculate permissions, but keep in mind that any custom permissions that you’ve implemented, can override the defaults.

This can serve as a quick check for whether your user account has the necessary permissions to access DMVs in a particular database.

Comments closed

Configuring Alerts in Azure SQL Managed Instance

Published 2025-08-22 by Kevin Feasel

Aleksey Vitsko wants an alert:

You have an Azure SQL Managed Instance and you want to set up SQL Server alerts for errors with severity 17-25, similar what you would do for an on-prem SQL Server. You go to the SQL Server Agent folder in Object Explorer, expand it, and whoops – there is no Alerts folder.

As of time of writing this article (June 2025), Azure SQL Managed Instance doesn’t have this functionality, and we don’t have any ETA on when it will be implemented. So, how can we setup alerts in Azure SQL MI to notify us when there are issues?

Read on for a workaround and a warning.

Comments closed

Auditing Specific Data Access in SQL Server

Published 2025-08-21 by Kevin Feasel

Andreas Wolter wants to focus in on specific database objects:

In this article I want to share a targeted approach to audit access to specific objects within a database in Microsoft SQL Server.

  • In my last article, Evading Data Access Auditing in Microsoft SQL Server – and how to close the gaps, I showed multiple approaches to gain access to a chunk of sensitive data using the statistics object in SQL Server. The hardest one to capture is access to data that is exposed via the dynamic management function (DMF) dm_db_stats_histogram. This requires an additional Audit Specification in the master database for this system object. In the end we required 3 different Audit Action Groups to cover all the methods used to read data from our example table.

Read on to see what you can do as of SQL Server 2022.

Comments closed

Logging in PostgreSQL

Published 2025-08-21 by Kevin Feasel

Elizabeth Christensen saves some information:

A modern-day Postgres instance creates robust and comprehensive logs for nearly every facet of database and query behavior. While Postgres logs are the go-to place for finding and debugging critical errors, they are also a key tool in application performance monitoring.

Today let’s get set up with logging for Postgres – starting with the basics of what to log, how to log what you want, and as reward for your hard work – how to use these to monitor and improve performance. The Postgres docs on logs are excellent, so please consult those for the most up to date and comprehensive configurations. This blog reads between the lines a bit beyond the docs to offer some practical advice and settings. As always, your mileage may vary.

Click through for several tips and a lot of information on the topic of logging.

Comments closed

Auditing in Oracle

Published 2025-08-19 by Kevin Feasel

David Fitzjarrell digs into the options available for security audits in Oracle:

Security rears its ugly head yet again, this time with security audits. Depending upon the RDBMS auditing can be a breeze or a nightmare. Oracle has done wonders in making this task simpler for the DBA to enable, configure and maintain. Let’s take a dive into this topic and shed some light on it.

By default when Oracle is installed the newest foray into auditing, the Unified Audit Trail, is disabled. In releases prior to 12 basic auditing was automatically available, but it wrote to possibly three locations:

Read on for those three locations, how things have changed with Unified Audit Trail, and some of the information you can get back from it.

Comments closed

The Importance of Running DBCC CHECKDB

Published 2025-08-18 by Kevin Feasel

Kevin Hill has some advice:

You just ran DBCC CHECKDB for the first time in a while (or maybe ever) and saw something you didn’t expect: the word corruption.

Take a breath.

Don’t detach the database.
Don’t run REPAIR_ALLOW_DATA_LOSS.
Don’t reboot the server or start restoring things just yet.

There’s a lot of bad advice floating around from old blogs, well-meaning forum posts, and even some popular current LinkedIn threads. Some of it might’ve been okay 15 years ago. Some of it is dangerous.

Let’s dig in.

Click through to dig in.

Comments closed

Installing SQL Server Instances via dbatools

Published 2025-08-13 by Kevin Feasel

David Seis digs into another dbatools cmdlet:

As DBAs we install SQL Server for various reasons regularly. If you could save time for each installation for more critical tasks, would you?

In this blog post, we will audit the dbatools command Install-Dbainstance. I will test, review, and evaluate the script based on a series of identical steps. Our goal is to provide insights, warnings, and recommendations to help you use this script effectively and safely. Install-Dbainstance is a powerful tool to automate the install and configuration of a new SQL Server instance. It works well in scenarios that require frequent deployments of SQL Server instances.

You can definitely automate installation of SQL Server without the cmdlet, but the dbatools team does a good job of laying out what’s possible that you might not necessarily get just from the config script that the SQL Server installer spits out (and uses when you next-next-next your way to success).

Comments closed