Press "Enter" to skip to content

Category: Administration

Creating a Role for Procedure Execution

Published 2024-08-16 by Kevin Feasel

Andy Brownsword brings out the hangman:

We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.

Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.

So let’s fix that.

The nice thing about this role is that you can then introduce module signing and allow the stored procedures to do things that you might not want regular users to do, such as truncate tables, start and stop SQL Agent jobs, etc. That way, users don’t have these advanced permissions, but the application (whose account has the stored procedure execution role) can do what it needs to do. You can, of course, also have other roles, like one for the kinds of things I mentioned and another to execute most stored procedures. Maintaining that gets a little trickier, but is doable.

Comments closed

Blob Storage Account Lifecycle Maintenance

Published 2024-08-09 by Kevin Feasel

Andy Brownsword deletes some files but wants to keep other files:

A hierarchy of directories which contain files. That’s how we typically think about file storage. That’s not quite the same everywhere. In Blob Storage a file can appear to be in a directory, but when it’s removed so is the directory.

This can occur when using Lifecycle Management to help purge legacy blobs, which can be unexpected. Let’s look at a way we can help remediate this.

One important thing to remember about Azure blob storage accounts and S3 buckets is that there’s really no concept of a directory structure. It’s all keys, where your key might be dir1/dir2/dir3/file.txt. This is a bit different for Azure Data Lake Storage Gen2 and its notion of hierarchical namespaces (i.e., folders). But Andy does walk through some of the consequences of this and how to work with lifecycle maintenance policies to delete only certain sets of files.

Comments closed

Filesystem Access for Database Restoration via dbatools

Published 2024-08-07 by Kevin Feasel

Andy Levy shares a lesson learned:

While performing an instance migration this spring, I happened upon something I didn’t expect in [dbatools](https://dbatools.io/). It should have been a simple backup/restore copy of the databases, with the backup files residing on a fileshare on the destination server after being copied there. I kept getting a warning that the backup files I was attempting to restore couldn’t be read, and the restores (via Restore-DbaDatabase) wouldn’t execute.

I checked permissions on the server over and over again. Both on the filesystem and for the share that I was attempting to read from. Even more curious, if I executed the restore database statements directly from within Management Studio, the databases restored without issue.

After doing quite a bit of digging, I managed to find the reason.

Read on to learn more about necessary permissions, as well as the issue Andy hit, as well as the solution.

Comments closed

Instrumenting Postgres

Published 2024-08-06 by Kevin Feasel

Grant Fritchey goes looking:

I’m still learning PostgreSQL and one of the things I’ve been looking at a lot lately is instrumentation. What do I mean? Well, if you’re in SQL Server, think, Dynamic Management Views (DMV), Extended Events, Query Store, <hack, spit> Trace <spit>. How do we know how long a query took to run? PostgreSQL can tell you, but, and this is one of those wild, cool, but, honestly, slightly frustrating things about PostgreSQL, not natively.

Read on for what Grant’s seen.

Comments closed

Fun(?) with CPU Affinity

Published 2024-08-06 by Kevin Feasel

Rod Edwards plays with fire:

Here we have the options, to set the processor affinity mask for all process, and also the I/O affinity mask. The default is to let SQL handle this automatically as above, as it will provide better performance for most workloads.

On a basic level, these settings restrict which CPUs can be used when restricting SQL server processing and/or SQL IO operations.

There are very specific use cases in which setting CPU affinity makes sense. Rod does a really good job of showing just why mucking with CPU affinity is not for the faint of heart.

Comments closed

Finding Uses of xp_cmdshell

Published 2024-08-02 by Kevin Feasel

Steve Jones goes looking:

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

Read on for Steve’s solution. There are also other places where you might find these calls: SSISCatalog if you use Integration Services, specific company metadata tables if you use those to build T-SQL statements, etc. But this does give you a good start.

Comments closed

A Review of Useful pg_stat_statements

Published 2024-08-01 by Kevin Feasel

Umair Shahid tracks some statements:

pg_stat_statements is an extension for PostgreSQL that tracks execution statistics of SQL statements. It is designed to provide insight into the performance characteristics of database queries by collecting data on various metrics such as execution time, number of calls, and I/O operations. This extension is immensely useful for database administrators and developers looking to optimize their SQL queries and improve overall database performance.

Click through to learn more about pg_stat_statements, including how to install and configure it, as well as some of the things you can do with it.

Comments closed

Linux Memory Overcommit and PostgreSQL

Published 2024-07-30 by Kevin Feasel

Laurenz Albe shares a warning:

Linux tries to conserve memory resources. When you request a chunk of memory from the kernel, Linux does not immediately reserve that memory for you. All you get is a pointer and the promise that you can use the memory at the destination. The kernel allocates the memory only when you actually use it. That way, if you request 1MB of memory, but use only half of it, the other half is never allocated and is available for other processes (or the kernel page cache).

Overbooking is a concept that airlines all over the world have been using for a long time. They sell more seats than are actually in the plane. From experience, airlines know that some passengers don’t show up for the flight, so overbooking allows them to make more profit. By default, Linux does the same: it deals out (“commits”) more memory than is actually available in the machine, in the hope that not all processes will use all the memory they allocate. This memory overcommit is great for using resources efficiently, but it has one problem: what if all the flight passengers show up, that is, what if the processes actually use more memory than is available? After all, you cannot offer a computer process a refund or a free overnight hotel room.

Read on to learn more about memory overcommit and what you can do about it.

Comments closed