Press "Enter" to skip to content

Category: Administration

Handling Multiple Snapshots on a Database

Published 2024-07-26 by Kevin Feasel

Andy Brownsword lets things get out of hand:

Last week we looked at using Database Snapshots to help with rolling back upgrades. The snapshot maintained a point in time copy of the database which could be later restored.

We can go further – a database can have multiple snapshots.

Let’s suppose we want to take one before an upgrade, another once the upgrade is complete, and another before the start of business the following day. This would provide us multiple points to restore too.

This however makes restoring more complicated.

My recollection is that it’s not just restoration that gets more complicated, but also any database activity, to the point where too many database snapshots on a single database can have a considerable performance impact.

Leave a Comment

Working with Managed Entities in Azure SQL DB

Published 2024-07-26 by Kevin Feasel

Josephine Bush creates and uses a managed identity:

Benefits of Using Managed Identities and Entra Groups

  • Enhanced Security: Using managed identities eliminates the need to manage credentials, reducing the risk of credential theft.
  • Simplified Management: Entra Groups streamline the management of permissions for multiple users or managed identities, making it easier to apply consistent access policies.
  • Scalability: As your organization grows, you can easily manage access by adding new users or managed identities to Entra Groups without needing to update database permissions individually.

Read on to see how you can create one and what you can do with it.

Leave a Comment

Comparing Configuration of Two SQL Server Instances

Published 2024-07-25 by Kevin Feasel

Jana Sattainathan checks the labels on these bottles:

A lot of times, you have nearly identical database servers for an application running in Production, Test and Development but you may notice performance differences between them for the same data/queries that you could not attribute to any reason since CPU, Memory, Disk etc., may all be identical.

This is, strictly speaking, a comparison of configurations rather than data differences, indexing, and the like. Nonetheless, it’s useful to make this sort of comparison just to ensure that your instances have your desired state configuration.

Leave a Comment

Shared Library Preloading in PostgreSQL

Published 2024-07-25 by Kevin Feasel

David Wheeler talks pre-loading:

Recently I’ve been trying to figure out when a Postgres extension shared libraries should be preloaded. By “shared libraries” I mean libraries provided or used by Postgres extensions, whether LOADable libraries or CREATE EXTENSION libraries written in C or pgrx. By “preloaded” I mean under what conditions should they be added to one of the Shared Library Preloading variables, especially shared_preload_libraries.

The answer, it turns out, comes very much down to the extension type. Read on for details.

Read on for an interesting discussion of what pre-loading means and the circumstances you should consider along the way.

Leave a Comment

Testing Postgres Configuration Parameter Performance Changes

Published 2024-07-24 by Kevin Feasel

Semab Tariq follows up on a prior blog post:

In a previous blog post, we configured an EC2 instance and installed PostgreSQL on it. After the initial setup, we ran pgbench to measure the initial TPS (transactions per second). Then, we tuned PostgreSQL and reran pgbench to see how much we could improve the TPS. To tune PostgreSQL, we adjusted various parameters based on the system’s available resources. In this blog, we will identify which of those parameters contributed the most to the performance improvements.

If you want to read the initial blog post, you can find it here: Key PostgreSQL Configuration Parameters for Enhanced Performance.

We will use the same instance size on AWS EC2 as before, which is t2.large.

I suppose it shouldn’t matter very much if you’re running a performance test over a significant timespan, though it was an interesting choice to use a burstable VM type for the test.

Leave a Comment

Finding Long-Running Queries in Azure SQL DB

Published 2024-07-24 by Kevin Feasel

Kunal Rathi has a script for us:

Identifying and optimizing long-running queries is crucial for maintaining the performance and efficiency of your Azure SQL Database. Slow queries can lead to resource bottlenecks, affecting the overall user experience and system stability. In this post.we will see how to find long running queries in Azure SQL database. Whether you’re a database administrator or a developer, these insights will enable you to ensure your database runs smoothly and efficiently.

Click through for the script. It’ll work not only for Azure SQL DB, but also SQL Server and Azure SQL Managed Instance as well.

Leave a Comment

Calling a REST Endpoint from Azure SQL Database

Published 2024-07-23 by Kevin Feasel

Meagan Longoria makes a call:

External REST endpoint invocation in Azure SQL DB went GA in August 2023. Whereas before, we might have needed an intermediate technology to make a REST call to an Azure service, we can now use an Azure SQL Database to call a REST endpoint directly.

One use case for this would be to retrieve a file from blob storage. I explain how to set this up below.

Read on to learn more about the process, as well as a few ideas on when you might use it.

Leave a Comment

Calculating the Size of Dataflow Gen2 Staging Lakehouses

Published 2024-07-23 by Kevin Feasel

Sandeep Pawar busts out the calculator:

My friend Alex Powers (PM, Fabric CAT) wrote a blog post about cleaning the staging lakehouses generated by Dataflow Gen2. Before reading this blog, go ahead and read his blog first on the mechanics of it and the whys. Note that these are system generated lakehouses so at some time in the future, they will be automatically purged but until then the users will be paying the storage cost of these lakehouses. If you want to read more about how dataflow gen2 works and whether you should stage or not , read this and this blog.

Read on for a Python script using the SemPy library.

Leave a Comment

Creating Elastic Jobs in TerraForm

Published 2024-07-23 by Kevin Feasel

Josephine Bush builds a script:

If you aren’t familiar with Terraform and its requisite bits and pieces, I have a post that walks you through them. Let’s hit the ground running. To create Elastic Jobs with Terraform, you need to use the azapi_resource, but before we go down that path, you will use the azurerm resource to create some supporting objects.

Read on for a guide covering requirements and setup.

Leave a Comment

Basic Troubleshooting in SQL Server on Linux

Published 2024-07-22 by Kevin Feasel

Infant Arockiaraj gives people an idea of where to look:

Based on our extensive experience with customers using SQL on Linux, we have compiled a guide outlining fundamental troubleshooting steps and available tools to assist in resolving SQL on Linux issues. This guide aims to make it easier for SQL DBAs who have primarily worked on Windows operating systems over the years.

In this article, we will normalize three checks in Linux that we typically perform in Windows to start troubleshooting issues with SQL Server: 

  • System logs
  • SQL Server logs
  • Task Manager

Note: The screenshots in the below examples are taken from RHEL and Ubuntu machines, and while the Linux flavors are different, the commands are similar in both environments. 

Click through to learn more about these steps and where you can find the files..

Leave a Comment