Press "Enter" to skip to content

Category: Administration

Copying Azure SQL Managed Instance Databases

Published 2024-04-25 by Kevin Feasel

Scott Klein performs a migration:

So, back to our customer. They essentially lifted and shifted their on-premises databases to Azure SQL Managed Instance and have been using it successfully for nearly two years. Again, this is awesome.

Last week they came to us and asked about reporting with Managed Instance. They were looking at data marts and data warehouses, but we needed more information. It turns out they have some people that just want the ability to query the databases, and potentially hook up Excel to these databases for data analysis.

The caveat is that the people I was talking to didn’t want to give the other group direct access to the production environment. Toootally get that. Yeah, like 100% get it. So, what are the options?

Read on for the solution Scott came up with.

Leave a Comment

Query Store Size-Based Cleanup Performance Issues

Published 2024-04-24 by Kevin Feasel

Kendra Little has a public service announcement:

I’m a huge fan of SQL Server’s Query Store feature. Query Store collects query execution plans and aggregate query performance metrics, including wait stats. Having Query Store enabled makes troubleshooting performance issues such as bad parameter sniffing, much, much easier. Because Query Store is integrated into SQL Server itself, it also can catch query plans in a lightweight way that an external monitoring system will often miss.

When performance matters, it’s important to ensure that you’re managing Query Store so that Query Store cleanup does not run during high volume times. Query Store cleanup could slow your workload down significantly.

Read on for more information. I’d also like to plug qdstoolbox, an open-source solution some of my former colleagues worked on. This includes QDSCacheCleanup, which works considerably better than the built-in cleanup process.

Leave a Comment

Searching for Tenant Settings in Microsoft Fabric

Published 2024-04-23 by Kevin Feasel

Nicky van Vroenhoven performs a search:

You probably also use the same method as I did to search through the Admin portal and tenant settings: CTRL + F from your browser. It does the trick, but not very well. 

For example, it only searches the titles of the settings, not the descriptions.

Next to that, you also can get a lof matches that you have to scroll or loop through, which makes it not very clear because more often than not, you don’t know in what section of the tenant settings you ended up.

Read on for an alternative method of searching. Or, I guess, two of them because without Nicky’s post, it can be easy to confuse the two search boxes.

Leave a Comment

Processes in PostgreSQL

Published 2024-04-19 by Kevin Feasel

Semab Tariq continues a series on internals in PostgreSQL:

PostgreSQL is a client/server type relational database management system. It has a multi-process architecture that runs on a single host. A collection of multiple processes that manage a database cluster is usually referred to as a PostgreSQL server. In PostgreSQL, every operation is treated as a process, and each action we undertake within PostgreSQL follows an append-only approach. This means that every time we execute an action such as an insert, update, or delete, a new tuple is created rather than modifying the existing data directly.

Consequently, PostgreSQL does not execute updates or deletes in place. Instead, it appends new data or marks existing data as obsolete. This append-only methodology ensures data integrity and allows for efficient management of database changes over time.

Read on to learn more about how these processes work.

Leave a Comment

Enhanced Patching for SQL Server on Azure VMs

Published 2024-04-17 by Kevin Feasel

Taryn Pratt has an update:

We are pleased to announce the GA release of enhanced patching capabilities for SQL Server on Azure VMs using Azure Update Manager. When you register your SQL Server on Azure VM with the SQL IaaS Agent extension, you unlock a number of feature benefits, including patch management at scale with Azure Update Manager.

Read on to see what this does, how you can set it up, and how you can migrate from the SQL Server IaaS agent extension’s automated patching service.

Leave a Comment

Elastic Jobs for Azure SQL DB

Published 2024-04-17 by Kevin Feasel

Josephine Bush digs into Elastic Jobs:

I know if you are a SQL Server DBA using Azure SQL DB, you’ve been sorely missing the agent. Enter Elastic Jobs to help you schedule jobs more easily against Azure SQL DB. I will cover setting up and scheduling Elastic Jobs to execute Ola index maintenance. If you’ve used Elastic Jobs in the past, there are some very nice improvements with the recent GA release, so don’t feel discouraged if you didn’t like it in the past—it’s way better now!

Read on for a deep dive into Elastic Jobs.

Leave a Comment

Using a Snake Draft Order in SQL Server

Published 2024-04-16 by Kevin Feasel

Aaron Bertrand makes use of an ordering:

In my previous post, I showed how to borrow a snake draft concept from fantasy football, or a packing technique from the shipping industry, to distribute different portions of a workload to run in parallel. In the previous example, we determined a distribution order for databases based on size – though you can rank by literally any attribute (or combination of attributes). Once we’ve determined how to build out this order, we may want to store that data somewhere because, sometimes, the source of that data is not directly accessible.

Read on for tips on storing the results in a table, querying the results, and using them to drive SQL Agent jobs.

Leave a Comment

Resetting the sa Password in SQL Server on Linux

Published 2024-04-16 by Kevin Feasel

Vlad Drumea blames the intern:

This is pretty useful if you’ve inherited a SQL Server instance running on Linux, but the last person™ didn’t bother saving the sa password in your teams password manager vault.
Or, if you’re like me, and spin up test instances with random passwords for sa that you don’t bother saving anywhere.

Click through to see where you can find out how to reset the password, and then the actual mechanics of password reset.

Leave a Comment