Press "Enter" to skip to content

Category: Administration

Postgres Backup and Recovery with Barman

Published 2024-02-23 by Kevin Feasel

Muhammad Ali shows off another way to back up and restore data in Postgres databases:

Barman is a production grade tool for managing the backup and recovery process of PostgreSQL databases. It not only handles the physical backups but also provides automatic management of retention policies, compression, near zero Recovery point objective(RPO) and enables recovery at any desired point(PITR) in time within the recovery window.

Barman’s Stream Archiving feature stands out as a key component in achieving zero Recovery Point Objective (RPO). This is achieved by using pg_recievewal utility which continuously backs up Write-Ahead Logging (WAL) files in real-time to a designated Barman server. This capability is particularly important for applications where even minimal data loss is unacceptable.

Read on for a couple questions about it and a demonstration of how Barman works.

Comments closed

The Risk of Query Failure on Readable Secondaries in SQL Server

Published 2024-02-22 by Kevin Feasel

Kendra Little explains a problem:

If you use readable secondaries in Availability Groups or Read-Scale out instances in Azure SQL Managed Instance, you may have queries fail repeatedly if there is a glitch and statistics are not successfully “refreshed” on the secondary replica. Those queries may keep failing until you manually intervene.

This has been the case for a few years, and it’s unclear if Microsoft will fix this. There is a well established support deflection article which documents the issue and provides ‘workarounds’.

Read on for Kendra’s thoughts. I haven’t run into this before, myself, but I also don’t tend to make very heavy use of readable secondaries.

Comments closed

Feature Toggles for Databases

Published 2024-02-20 by Kevin Feasel

Phil Factor discusses feature toggles for the database:

In software development the concept of feature toggles are used to selectively turn on and off features. They are, for example, used to restrict some newly introduced features to a select group to see how these features work. While this concept has been long used for user-facing application code, it is also a practice that is useful for database code.

In databases, feature toggles are used for several loosely related purposes. One common use is to separate, or decouple, rollout of database features from deployment of new versions of the application code. This allows developers to release software faster and with less risk.

The idea of using roles to determine whether a given user should see a particular feature makes sense, though I was a bit confused at first because I was thinking about feature flags: behavioral changes you slowly turn on for groups of people. This is a bit different.

Comments closed

Enabling SQL Agent after Upgrading from Express Edition

Published 2024-02-19 by Kevin Feasel

Warwick Rudd ponies up the cash:

With the above limitations, this product can provide all of the functionality for your application to operate as required. Because there is no SQL Agent service in this edition this makes it more difficult for you to implement scheduled database maintenance. We are not going to discuss ways to implement database maintenance on an Express edition of SQL Server in this post.

But at some point in time you may find yourself in the position that you need to perform an upgrade of editions. This may or may not include and upgrade of versions. An upgrade to the version can be achieved and can bring benefits of its own. Normally, I would not be writing about and recommending performing in-place upgrades. However, sometimes due to restrictions of vendor applications it is a requirement to perform an in-place upgrade. 

Read on to see what you’d need to do, knowing that Express Edition does not come with a SQL Server Agent and does not perform the appropriate setup for one.

Comments closed

Checking SQL Server Connectivity with Powershell in Parallel

Published 2024-02-16 by Kevin Feasel

Rod Edwards builds a script:

The chances are that you have other systems monitoring your SQL servers already, so this task isn’t required at all. However, sometimes a quick ‘knock on the door of SQL’ to confirm a response isn’t a bad thing as a sanity check.

So, building on that, we’re going to use the same technique to essentially perform a sweep of our estate (with a few bells and whistles added) to give us a colour coded quick view of service status like below, with a little bit of additional info.

Click through for the script and explanation.

Comments closed

Troubleshooting a Problem with sp_send_dbmail

Published 2024-02-16 by Kevin Feasel

Shane O’Neill applies the Sgt. Schultz defense:

I don’t like posting about issues unless I fundamentally understand the root cause. That’s not the case here. A lot of the explanation here will be hand-waving while spouting “here be dragons, and giants, and three-headed dogs”, but I know enough to give you the gist of the issue.

Click through for a fun story about Kerberos and behavioral changes after absolutely nothing happened.

Comments closed

Horizontal Scale in Postgres with PL/Proxy

Published 2024-02-14 by Kevin Feasel

Umair Shahid gives us the rundown on one mechanism to scale out Postgres:

As databases scale and user demands intensify, the challenge of maintaining performance increases. While PostgreSQL was originally designed (40 years ago!) to scale up, but increasing demands on the database have created the need to enable it to scale horizontally. PL/Proxy is a database partitioning system designed to simplify the process of distributing database loads across multiple servers. It acts as a transparent layer between the application and the database, enabling queries to be directed to the appropriate shard or partition based on predefined rules.

This article will dive into PL/Proxy, taking a look at its benefits, use cases, and best practices to enhance PostgreSQL’s performance through effective scaling.

Click through to learn more about this product.

Comments closed

Troubleshooting Performance around a Data Purge Process

Published 2024-02-14 by Kevin Feasel

Andy Mallon troubleshoots an issue:

In January, one of our Staff Engineers sent the following message to the DBRE help channel in Slack:

Morning folks, we had a pretty significant wait spike on the [database]. Circuit breakers closed and reopened quickly. Is anyone immediately aware of a reason why this could’ve happened?

Read on for Andy’s quick analysis and then the root cause and solution.

Comments closed

Forced Quorum Failures with WSFC

Published 2024-02-13 by Kevin Feasel

Eitan Blumin can’t reach quorum:

The incident started with a late-night phone call from one of our customers (it’s always a late-night phone call, isn’t it?).

They reported that during a DR exercise on their production environment (Chaos Engineering, anyone?) their entire cluster failed and they weren’t able to bring any of the replicas back online.

Click through for the full story, including what happened, why it happened, and what you can do to prevent similar problems in the future.

Comments closed

Automating Microsoft Fabric Capacity Scaling via Logic App

Published 2024-02-12 by Kevin Feasel

Soheil Bakhshi does some scaling:

In a previous post I explained how to manage the capacity costs of a Fabric F capacity (under Pay-As-You-Go pricing model) using Logic Apps to Suspend and Resume it.

A customer who read my previous blog asked me “Can we use a similar method to scale up and down before and after specific workloads?”. This blog post is to answer exactly that.

This is pretty neat, though I wonder how long it takes and how much downtime it produces.

Comments closed