A Fine Slice Of SQL Server
Daria Nikolaenko walks through a presentation:
I’ve started talking about something that happens with almost every Postgres database — the slow, steady growth of data. Whether it’s logs, events, or transactions — old rows pile up, performance suffers, and managing it all becomes tricky. My talk was focusing on practical ways to archive, retain, and clean up data in PostgreSQL, without breaking queries or causing downtime.
Read on to learn more.
Comments closedDo you know who is logging into your SQL Server?
I was once asked about the need to track SQL Server logins. Many servers were already tracking failed logins. Where the issue came up in this case was tracking successful logins to determine login usage. Let’s take a quick look at how we can track both failed and successful logins.
Security-oriented me always wants both failed and successful logins, as you want to know if the person who failed to log in eight times did in fact successfully log in on the ninth attempt.
Comments closedBen Johnston looks at the art of the possible:
In my last post I described issues that might stop your migration to a SQL Server Managed Instance (SQL MI). This covers configuration items that differ or are not supported in SQL MI. These likely won’t stop your migration, but they could slow you down if you aren’t ready for these changes.
As with previous issues discussed, testing your migration is key. Validate all of your settings and be prepared to make some changes during your migration process. Most of the incompatible options make sense when you think about the purpose of SQL MI – it is controlled by Microsoft. Hardware settings, local file access, high-availability settings, and auditing are configured differently or completely disabled.
Click through to see what you can and cannot do when it comes to configuration.
Comments closedKevin Hill shares some thoughts:
Here’s the truth: if you’re doing this daily on indexes smaller than 10,000 pages, you might be chewing up CPU, bloating your logs, and annoying your users for zero gain.
Let’s fix that.
I disagree with Kevin on index reorganization, in that I would never perform index reorgs—there’s no there there. Even so, I firmly agree with the thrust of Kevin’s argument and believe that, in the majority of cases, companies with DBAs (or people who have stumbled through maintenance plans before) are maintaining indexes too much rather than not enough.
Comments closedUmair Shahid pushes the contents of the write-ahead log to another machine:
Physical streaming replication in PostgreSQL allows you to maintain a live copy of your database on a standby server, which continuously receives updates from the primary server’s WAL (Write-Ahead Log). This standby (or hot standby) can handle read-only queries and be quickly promoted to primary in case of failover, providing high availability and disaster recovery.
In this guide, I will walk through provisioning a primary PostgreSQL 16 server and a standby server on Linux, configuring them for streaming replication, and verifying that everything works. I assume you are an experienced engineer familiar with Linux, but new to PostgreSQL replication, so I will keep it friendly and straightforward.
Click through for the process.
Comments closedDavid Wiseman shows off an open-source product:
For T-SQL Tuesday #186, Andy Levy asks, “How do you manage and/or monitor your SQL Server Agent jobs?”
This is a great opportunity for me to discuss how DBA Dash can help monitor SQL Agent jobs. DBA Dash is a free and open-source monitoring tool for SQL Server, created by me. It’s used to monitor thousands of SQL Server instances within Trimble alone, and it’s gaining popularity in the SQL Server community.
Read on to see how the product can help if you have a series of SQL Agent jobs.
Comments closedStephen Planck adds one more layer of complexity:
SQL Server’s database-snapshot feature is a wonderfully simple tool: at the instant you create the snapshot, every page in the database is marked “copy-on-write.” Nothing is copied across the wire, no blocking locks appear, and the snapshot opens immediately as a read-only database on the local replica. Queries against the snapshot see the world exactly as it looked at that moment while the live workload keeps changing pages in the primary data files. Because snapshots live only in sparse files on the server that owns them, they are not a replacement for backups—but they are perfect for ad-hoc reporting, quick “before-and-after” comparisons, or a safety net when you want an easy way to back out a risky change that should finish within minutes or hours.
But read on to see how they interact with high-availability features such as transactional replication and availability groups.
Comments closedDeborah Melkin has three laws for robots, or at least SQL Agent jobs:
How do I manage this all?
I have scripts to create and modify SQL Agent jobs.
And those scripts get checked into source control.
And those scripts get added to the deploy process.
And most importantly, those scripts get tested in all environments from development through production.
Read on for more context around this.
Comments closedRodrigo Riberio Gomes digs in:
In more than 10 years of experience, I have dealt with cases where someone has performed incorrect operations on a table, such as updating or deleting wrong rows, in a SQL Server database that does not have full backups available. There are multiple reasons for no full backup: corrupted backups, taking too much time to restore, etc.
In this post, I want to show an alternative for these cases, an ace up one’s sleeve, that you can use to recover data. This method also provides a deep understanding of the internal workings of how your SQL Server stores data. So, in addition to learning how to recover data, you will gain more insights into the internals of SQL.
Read on to see how. Rodrigo also points out some limitations or things that would need to change if you have index compression. I consider this a very neat thing you might need to know but never want to use.
Comments closedTim Radney violates Betteridge’s Law of Headlines:
I was recently asked if you can restore multiple differential backups in preparation for a migration. I responded that yes, technically you can restore multiple differential backups, however it will not speed up your cutover.
As soon as I read the first sentence, the answer in my head was “Yes, but why?” Tim explains the person’s reasoning and then demonstrates that this reasoning doesn’t quite work.
Comments closed