Press "Enter" to skip to content

Category: Administration

I/O Analysis for SQL Server on Azure VMs

Published 2024-06-05 by Kevin Feasel

Ebru Ersan announces a new preview feature:

It is not easy to understand what’s going on when you run into an I/O related performance problem on an Azure Virtual Machine. It is a common, but complex problem. What you need is to figure out what’s happening at both the host level and your SQL Server instance where often, correlating host metrics with SQL Server workloads can be a challenge.

We developed a new experience that helps you do exactly that.

Click through to see how it works. Given that awful disk latency is a common problem in the cloud, this may at least tell you if you have things set up correctly.

Comments closed

GRANT Operations in Postgres

Published 2024-06-04 by Kevin Feasel

Shaun Thomas takes us through GRANT operations and roles in Postgres:

Not every database-backed application needs to be locked down like Fort Knox. Sometimes there are even roles that leverage blanket access to large swathes of available data, if not every table, simply for auditing or monitoring purposes. Normally this would require quite a bit of preparation or ongoing privilege management, but Postgres came up with a unique solution starting with version 14: predefined roles.

This topic comes up relatively frequently in Postgres chats like Discord, Slack, and IRC. Usually it’s along the lines of: “We have a low security application but have separated read and write access from the table owner to avoid accidents. That user should still be able to read or write any table in the database. What do I do?”

This is an area where Postgres and SQL Server are using the same terms but aren’t quite speaking the same language.

Comments closed

Data Encryption Options in Postgres

Published 2024-05-31 by Kevin Feasel

Greg Nokes gives us the options:

Operating system or disk-level encryption protects entire file systems or disks. This method is application-agnostic and offers encryption with minimal overhead. Think technologies like luks in Linux or FileVault in MacOS.

Read on for four options. They’re very similar to options available in SQL Server, so it’s easy enough to compare implementation ideas.

Comments closed

Finding Free Space in SQL Server Database Files

Published 2024-05-24 by Kevin Feasel

Greg Robidoux checks for free space:

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

In this tip we take a look at a few ways to find the current free space within a database, so you can better manage your database files.

Click through for several techniques.

Comments closed

Tracking Transaction Log Space in SQL Server

Published 2024-05-23 by Kevin Feasel

Greg Robidoux keeps track of available space:

Keeping track of your transaction log usage is key to understanding how your database transaction logs are being used as well as keeping track of how much space the transaction logs are using. Depending on the recovery model that is set within your database and also the backup jobs that you run, the size of your transaction log can vary quite a bit. So what is the best way to keep track of the transaction log usage?

Read on to see how. This is almost assuredly a part of any monitoring system but if you’re working to roll your own or find yourself in an environment without such a system, it’s a good thing to track.

Comments closed

Open Questions on Fabric Administration

Published 2024-05-23 by Kevin Feasel

Paul Andrew asks some great questions:

Microsoft Fabric is a big product with lots of different data handling capabilities. From a data engineering perspective creating and innovating with Fabric as a unified tool is a great experience, ultimately delivering data insights for the business and adding value, nice! However, as with all new developments, the creativity is the fun part. The governance and movement of code into production is less fun and can become the hard/ugly part if the change management, platform and governance aren’t mature enough.

Paul doesn’t have answers for us, though I do think many of these will eventually have answers most people find reasonable.

Comments closed

Using the pg_repack Extension

Published 2024-05-16 by Kevin Feasel

Muhammad Ali tries out an extension:

Regular updates and deletions within PostgreSQL tables can lead to various issues such as bloat, fragmentation, and a decline in performance over time. These challenges can significantly impact the efficiency and reliability of the database, potentially affecting critical operations.

To address these concerns, PostgreSQL introduced the pg_repack extension, which provides a robust solution for managing table maintenance without disrupting the production environment. By allowing tables to be rebuilt online, pg_repack tackles bloat and fragmentation issues, ensuring that database storage remains optimized and performance is consistently maintained.

Read on to see why vacuuming might not be enough and what pg_repack does.

Comments closed

Monitoring and Alerting on Fabric Capacity Metrics

Published 2024-05-13 by Kevin Feasel

Ron L’Esteve wants to know what’s happening:

With Microsoft Fabric now generally available, organizations are interested in implementing this flagship Unified Data and AI Intelligence Platform for several reasons. Its native integration within the Azure stack provides seamless and secure access to widely used technologies for data integration, business intelligence, and advanced analytics. Microsoft Fabric’s storage and compute capacity is utilized by resources within this unified analytics platform, including storage repositories, such as data warehouses and data lakes, and compute capacity for Power BI, Pipelines, DW processing, and artificial intelligence (AI)/machine learning (ML) workloads.

Fabric capacity can be purchased on Azure with a pay-as-you-go model, and a 60-day free trial (64 CUs) is offered to test the platform. Organizations that have an existing Power BI Premium capacity can easily enable access to Fabric by using the Microsoft Fabric admin switch. Enabling Fabric in Power BI Premium as opposed to Azure Portal creates a problem: there is no easy way to monitor and set alerts on your Fabric capacity metrics in the Azure Portal.

Click through to learn how to install and use the Microsoft Fabric Capacity Metrics App.

Comments closed