Press "Enter" to skip to content

Category: Administration

Roles and Privileges in Oracle versus PostgreSQL

Published 2024-11-22 by Kevin Feasel

Umair Shahid continues a series on migrating from Oracle to Postgres:

When moving from Oracle to PostgreSQL, one of the key differences lies in how each database handles roles and privileges. Oracle’s privilege model is deeply ingrained in enterprise systems, with fine-grained user controls and a strict distinction between users and roles. PostgreSQL, while just as capable, approaches roles and privileges differently, offering flexibility and simplicity, but it also requires a shift in mindset for Oracle users.

This article provides a practical guide for Oracle experts to understand and implement roles and privileges in PostgreSQL, addressing the structural differences, common challenges, and best practices to make this transition smooth.

Read on for the differences between the two platforms.

Leave a Comment

CPU Monitoring in SQL Server with Datadog

Published 2024-11-22 by Kevin Feasel

Kendra Little has a recommendation:

What makes me a raving fan is the flexibility of Datadog’s notebooks and dashboards, combined with the ability to create all sorts of custom metrics and monitors. There are always things in SQL Server monitoring packs that I have strong opinions about. Datadog lets me take what I want, build what I need that isn’t contained in that, and ignore the rest. For a team that has the budget to afford Datadog paired with dedicated database staff with the time and resources to do this work, this can be a great fit.

One of the weirdest and worst parts of the Datadog SQL Server monitoring tooling, though, is how it handles wait stats. In my opinion, it’s a case of someone reinventing a wheel that didn’t need to be reinvented, and then not documenting what they did clearly (at least not in a way I can find).

Two of the most confusing Datadog “waits” are labeled “CPU” and “Waiting on CPU”. I opened a support ticket with Datadog a while back to ask what these are, because I couldn’t find any way they correspond to actual wait stats in SQL Server. I learned they aren’t wait stats at all. In fact, I think you should largely ignore them. Here’s why.

Read on for the full story.

Leave a Comment

Making a SQL Server Table Read-Only

Published 2024-11-21 by Kevin Feasel

Chad Callihan has no need to write:

There are situations when you need to set a database to read-only, but what do you do when you need a single table to be read-only? It’s not as straightforward as setting the entire database read-only, but it can still be done.

Let’s take a look at the steps necessary to set both a database and a single table to read-only.

Another option is to put the table into a filegroup that you make read-only, especially if this is a permanent change. That way, you can rebuild the table’s indexes with a fill factor of 100% and reduce the number of pages. In addition, you can back up that read-only filegroup separately from active filegroups and restore filegroups individually. Now, if your read-only filegroup is a large percentage of total data in the database, you don’t need to back it up nearly as frequently because the data isn’t changing.

Leave a Comment

Metadata-Only NOT NULL Column Insertion

Published 2024-11-20 by Kevin Feasel

Andy Brownsword has the need for speed:

When adding a new column and wanting to default the value for existing records, it used to be a painful task. As of SQL Server 2012 that became much easier.

But nobody told me, until Simon casually mentioned it in conversation recently. I had to see it for myself, so I thought I’d share for those who weren’t aware.

Read on to see how. I rarely self-promote in other people’s blog posts (hush, person who knows all the times I’ve done it), but I do have a talk on the topic of near-zero downtime database deployment strategies which includes this and quite a few other notes on what you can do without blocking others. For these sorts of changes, what you’re looking for is asynchronous processing and a Sch-M (schema modification) lock at the very end, such as when rebuilding an index with ONLINE = ON in Enterprise Edition. Alternatively, look for a Sch-M lock only on a metadata table and not the actual data. Andy’s post is an example of the latter.

Leave a Comment

Renaming a Database in SQL Server

Published 2024-11-14 by Kevin Feasel

Steve Jones asks, what’s in a name?:

I had someone ask me how to rename a SQL Server database recently. They were doing some development work and wanted to rename databases to test an application. I thought I remembered, but in this post, I show I learned something.

Read on for the answer, as well as some notes about it. One additional thing I’d point out is that renaming the database doesn’t rename the underlying files.

Leave a Comment

The Challenge of Importing Items into a Fabric Workspace

Published 2024-11-13 by Kevin Feasel

Marc Lelijveld performs an airing of grievances:

Obviously, you don’t want to start every solution from scratch. Therefore, it might be beneficial to kick-start your new solution by just importing components you already developed at earlier stages. Recently, I wanted to import a notebook to a Fabric workspace but was a bit confused. In this blog, I will further elaborate on the confusion and show how, in the end, I successfully imported the notebook to the workspace.

Read on for a story of pain.

Leave a Comment

Scenarios Leading to Autovacuum in Postgres

Published 2024-11-11 by Kevin Feasel

Semab Tariq covers autovacuum:

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance

To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.

Read on for a list of scenarios that can trigger autovacuum.

Leave a Comment

Configuring Azure Database Watcher

Published 2024-11-08 by Kevin Feasel

Rod Edwards configures Azure Database Watcher to watch databases in Azure:

First off, at the time of writing, this is still in Preview, and is only for Azure SQL PaaS offerings, namely Azure SQL DB and SQL Managed Instance, so if you’re out of luck if you’re using SQL on VM. Expect this to be added at some point in future, its number 2 on the published roadmap.

Preview or GA…the long and short of it is that it allows collection of performance AND config data into a central datastore for ALL of your SQL MI and Azure DB estate. With all of the data in one place, then dashboards are connected to here for easier estate-wide visualisations.

Read on for a step-by-step guide on configuring it. But also pay attention to Rod’s note near the end that troubleshooting setup is a pain—there aren’t many useful logs that show exactly why it isn’t working.

Leave a Comment

Verification of PostgreSQL Checksums with WAL-G

Published 2024-11-08 by Kevin Feasel

Marat Bogatyrev talks checksums with us:

A key aspect of maintaining backup integrity is understanding data checksums. Without proper checksum validation, detecting data corruption becomes virtually impossible. Therefore, we will start with The Importance of Data Checksums.

Read on to learn more about how PostgreSQL, in particular, deals with checksums. SQL Server has similar functionality for pages and backup files and it’s worth knowing about and enabling checksum capabilities in both database platforms.

Leave a Comment