Press "Enter" to skip to content

Category: Administration

Group Replication in MySQL

Published 2023-08-29 by Kevin Feasel

Aisha Bukar continues a series on replication in MySQL:

MySQL Group replication is a remarkable feature introduced in MySQL 5.7 as a plugin. This technology allows you to create a reliable group of database servers. One of the most important features of MySQL’s group replication is that it allows these servers to store redundant data. This allows the database state to be replicated across multiple servers making it efficient in the situation where there is a server breakdown, the other servers in the cluster can agree to work together.

This technology is built on top of the MySQL InnoDB storage engine and employs a multi-source replication approach which we discussed in part 3 of the replication series. In this article, we’d be looking at an overview of the group replication technique, configuring and managing group replication, and also best practices for group replication. So, let’s get started!

Read on to see how it works and some recommendations around using it.

Comments closed

Advanced Scenarios for Private Endpoints to Azure SQL MI

Published 2023-08-25 by Kevin Feasel

Zoran Rilak digs in:

In the previous installment of this mini-series, we covered basic scenarios involving private endpoints. If you aren’t familiar with private endpoints and Private Link in general, it might be a good idea to quickly review them to get the feel of how they apply when Azure SQL Managed Instance is in the mix.

In this article, we’ll dive into more involved scenarios that build on those from last week:

5. Hub and spoke topology

6. Partner or ISV giving access to their customers

7. Two SQLs talking to each other: linked server, transactional replication

8. Failover group listener using private endpoints

Read on for architecture diagrams and descriptions for each of these scenarios.

Comments closed

Load Balancing in Postgres Clusters with pg_cirrus

Published 2023-08-23 by Kevin Feasel

Muhammad Ali explains how load balancing works in Postgres:

Load balancing is a critical component of high availability clusters that optimises performance, scalability, and fault tolerance. By evenly distributing database connections across multiple servers, load balancing prevents bottlenecks, efficiently handles increased workloads and improves response time.

In this blog, we will explore how standby nodes contribute to efficient workload distribution and achieving optimal query execution by directing all read/select queries to these standby nodes.

Read on to see how you can use pg_cirrus to perform query load balancing.

Comments closed

The Trials and Tribulations of Migrating SSISDB

Published 2023-08-23 by Kevin Feasel

Andrea Allred packs up boxes and moves the database across town:

Recently I needed to move a dev instance from a physical server to a virtual one and needed to do SQL upgrades. We decided it was time to spin up a new dev instance and that required moving SSISDB. I will be honest, I was not prepared for all the things. I also did not have a password for the encryption so I had to force things to work. Because this was a dev server, I wasn’t as worried about what I would lose, I just wanted it to work. Here is what I did.

There’s a lot more here than “restore database from backup,” so click through to read the steps.

Comments closed

Index Maintenance in Azure SQL DB with Elastic Jobs

Published 2023-08-22 by Kevin Feasel

Scott Klein continues a series on index maintenance in Azure SQL Database:

It’s finally here: the third and final blog post about Azure automation. The first blog covered how to automate Azure using Runbooks, the second blog post showed how to do it using Azure Functions, and this blog post will cover how to do it using Azure Elastic jobs.

To be fair, I titled this blog “Automating Azure with Elastic Jobs”, but Elastic Jobs isn’t part of Azure Automation, so please don’t get confused. The goal with this is to demonstrate how to automate some Azure database DBA tasks.

Read on for a brief primer on elastic jobs and how to use them.

Comments closed

ALTER TABLE SWITCH and Errors 4907, 4908, and 4912

Published 2023-08-18 by Kevin Feasel

Eitan Blumin works out some problems:

When it comes to managing tables and indexes in SQL Server, the ALTER TABLE SWITCH statement is a powerful tool for “moving” data swiftly between tables. However, this convenience can sometimes be met with frustrating roadblocks, such as errors 4907 and 4908.

These errors may be confusing about their underlying cause, particularly when the source and target tables have identical partitions, including in non-clustered indexes.

Read on to see what these error messages mean and how you can correct them.

Comments closed

Deploying Resource Governor with Minimal Blocking

Published 2023-08-17 by Kevin Feasel

Michael J. Swart doesn’t want to wait (or cause anyone else to):

Just like sp_configure, Resource Governor is configured in two steps. The first step is to specify the configuration you want, the second step is to ALTER RESOURCE GOVERNOR RECONFIGURE.
But unlike sp_configure which has a “config_value” column and a “run_value” column, there’s no single view that makes it easy to determine what values are configured, and what values are in use. It turns out that the catalog views are the configured values and the dynamic management views are the current values in use:

Read on for a variety of scripts to help configure resource governor.

Comments closed

Tracking Historical Database Snapshots

Published 2023-08-15 by Kevin Feasel

David Fowler goes sleuthing:

This is a question that’s come up twice this morning, firstly where can we find a history of database snapshots and secondly where can we find a history of restores from snapshot?

Frustratingly, SQL doesn’t make this at all easy for us and if this is something that you want to record, you’re going to have a do a little extra work.

Let’s take a look at each part in turn.

Read on for a messy solution.

Comments closed

Database Concurrency in Postgres

Published 2023-08-14 by Kevin Feasel

Mohan Saraswatipura explains how database concurrency works in Postgres:

Concurrency control is an essential aspect of database systems that deals with multiple concurrent transactions. PostgreSQL employs various techniques to ensure concurrent access to the database while maintaining data consistency using atomicity and isolation of ACID (stands for Atomicity, Consistency, Isolation and Durability – https://en.wikipedia.org/wiki/ACID) properties.

The majority of the article focuses on Multi-Version Concurrency Control, which is also the concurrency option which would be least well-known to SQL Server users.

Comments closed