Press "Enter" to skip to content

Category: Administration

A List of PostgreSQL Parameters

Semab Tariq has a list:

Have you ever experienced your database slowing down as the amount of data increases? If so, one important factor to consider is tuning PostgreSQL parameters to match your specific workload and requirements. 

PostgreSQL has many parameters because it is designed to be highly flexible and customizable to meet a wide range of use cases and workloads. Each parameter allows you to fine-tune different aspects of the database, such as memory management, query optimization, connection handling, and more. This flexibility helps database administrators to optimize performance based on hardware resources, workload requirements, and specific business needs.

In this blog, I will cover some of the important PostgreSQL parameters, explain their role, and provide recommended values to help you fine-tune your database for better performance and scalability. 

Click through for those parameters, including descriptions, default values, and recommendations.

Comments closed

Deleting All Items from a Microsoft Fabric Workspace

Sandeep Pawar has a script:

A handy function to delete all Fabric items in a workspace. Run this in a Python notebook in the workspace you want to delete items from. Everything, except that notebook, will be deleted. You need to have contributor+ role in the workspace. Delete the last remaining notebook manually.

Read on for the script. This one’s pretty straightforward, so there isn’t a lot in the way of additional commentary.

Comments closed

Migrating Azure PostgreSQL Single Server to Flex via pg_dump

Josephine Bush changes server type:

This is more complicated than using the Azure Migration method, but because it’s maxed out on resources for the last week in the east regions (and possibly central), and who knows when they will fix it, I had to resort to other methods. I’m getting on flex sooner than later. I want to get this over with and get to those performance improvements and better features. I will preface this all by saying, if you have big databases, this may not be the right path for you. Look into streaming replication or wait for Microsoft to fix their migration tool and do an online migration via that. Also, if you don’t have strong Postgres skills, this is far more complicated than the migration tool in Azure, far more complicated.

Click through for the step-by-step instructions.

Comments closed

Backup to URL via Managed Identity in SQL Server 2022

Joey D’Antoni doesn’t trust user logins:

Backing up databases to the cloud is not a new thing. Microsoft introduced the BACKUP TO URL functionality to SQL Server 2012 SP1 CU2. I’m not going to tell you how long ago. Still, it wasn’t last month, and Microsoft recently celebrated the 15th anniversary of Azure so that you can get an idea. When the feature started—it was minimal; you could only backup a database of up to a single terabyte and couldn’t stripe over multiple files. Additionally, you had to use the access key to the storage account, which gave complete control over the storage account—that wasn’t a good thing.

Read on for a quick overview of the feature and guidance on how it all works.

Comments closed

Migrating or Copying a Semantic Model across Microsoft Fabric Workspaces

Sandeep Pawar makes a move:

Here is a quick script to copy a semantic model from one workspace to another in the same tenant, assuming you are contributor+ in both the workspaces. I tested this for a Direct Lake model but should work for any more other semantic model. This just copies the metadata (not the data in the model) so be sure to set up other configurations (RLS members, refresh schedule, settings etc.). That can also be changed programmatically, thanks to Semantic Link Labs, but I will cover that in a future post.

Read on for the script, as well as an update from Sandeep on how you can do this even more easily.

Comments closed

Migrating a SQL Server Password without Knowing the Plaintext Value

Vlad Drumea rebuilds a machine:

Export-DbaLogin does a great job of exporting all logins, but it doesn’t offer a way to migrate the sa password to another instance.
In this case, I didn’t have the sa password and was required to ensure that the rebuilt instance is a 1:1 copy of the original one (edition excluded).

This also meant that the existing password used for sa had to be transferred to the rebuilt instance.

Read on to see how you can accomplish this.

Comments closed

Checking out the mssqlsystemresource Database

Stephen Planck goes into single-user mode:

Have you ever wondered where SQL Server actually stores the definitions for system objects like built-in stored procedures, system views, and functions? Enter the mssqlsystemresource database—often just called the “Resource” database. While this database remains hidden from everyday use, it plays a pivotal role in the internal workings of SQL Server. In this post, we will explore what the Resource database is, why it’s necessary, and what every DBA should know about it.

Read on for a primer on what’s in this database and why we typically shouldn’t mess with it.

Comments closed

Scaling with PostgreSQL

Shayon Mukherjee shares some tips about scaling in PostgreSQL:

“Postgres was great when we started but now that our service is being used heavily we are running into a lot of ‘weird’ issues”

This sentiment is frequently echoed by CTOs and senior engineers at high-growth startups when I speak with them.

Scaling PostgreSQL successfully doesn’t always require a full team of DBAs and experts. The beauty of PostgreSQL is that solutions often lie within the database itself – by rethinking your data access patterns from first principles, you can solve many business problems at scale.

In this post, I’ll address some common “weird” issues I’ve encountered and explore solutions that work at scale.

I like some of them, though I’m not a fan of eliminating or delaying foreign key constraints, as those are important for data quality. I’m not knowledgeable enough in PostgreSQL administration to have a strong opinion on these, however.

Comments closed

Kusto Query Performance in Microsoft Fabric

Dennes Torres checks some stats:

We already discovered how to investigate Kusto query history. Let’s discover how to analyse query performance considering the information on this history.

The query history returns 3 fields we can use to make a more detailed analysis of the queries: CachedStatisticsScannedExtentsStatistics and ResultsetStatistics.

Disclaimer: There are low to no documentation about this content. In this way, the content below may not be 100% precise but will give you good guidance.

Click through to learn more about these three.

Comments closed

SQL Server Migration via Distributed AG

David Fowler makes a move:

Because it doesn’t require a common cluster, a distributed availability group allows us to link servers in situations were a cluster isn’t possible. Servers could be in remote locations, members or different domains, different OS levels or even different operating systems (yes, we can link Windows and Linux based AGs).

The ability to link servers in this way gives us a very nice and easy way to replicate data between servers when thinking about a migration.

David’s scenario involves a SQL Server upgrade. I’ve seen this work really well in practice for a 2017 to 2019 upgrade. With applications pointing to the listener agent and everything in place, you can have as little as a few seconds of downtime for that upgrade, which is amazing when you think about how we’ve historically migrated to new versions of SQL Server.

Comments closed