Press "Enter" to skip to content

Category: Administration

Linked Servers into Azure

Published 2024-12-18 by Kevin Feasel

Andy Brownsword goes old-school:

Connecting different versions of SQL Server can allow us to combine or transfer data between environments. This can become a challenge when the versions are really different.

Have you tried to connect SQL Server 2008 to a SQL database in Azure? – it can throw up a few curve balls.

In this post we’ll look at how to solve 3 of the issues you might come up against.

When reading the title, my first response was, “But why not use PolyBase?” Then Andy threw the SQL Server 2008 bit at me, and then my response was, “But why not use a product that isn’t nearly old enough to vote?”

Nonetheless, Andy does a great job of demonstrating how this would work, and it can work for later versions of SQL Server as well.

Comments closed

Recovering SQL Server Data via FlashArray Snapshot

Published 2024-12-17 by Kevin Feasel

Andy Yun does a bit of data recovery:

Almost all of us have experienced a time when someone incorrectly modifies or deletes a subset of data in a database. And usually the only recourse is to run a database restore to get the data that we need back.

But… what if we cannot simply run a RESTORE operation over the entire database. Maybe the accident occurred a few hours prior and you cannot roll everything back. You need to do a side-by-side RESTORE.

But… what if the database in question is absolutely massive and will take hours to restore? And what if your server doesn’t have enough available storage to fit a second copy of that database at all?

Andy has answers to these questions. Granted, they require some expensive (and very good) hardware and a proper configuration of said hardware, but it does work beautifully.

Comments closed

Running Oracle on Windows

Published 2024-12-16 by Kevin Feasel

Kellyn Gorman embraces the better part of valor:

For many DBAs, the thought of running Oracle on a Windows OS induces a collective cringe. Even for someone like me, with a career spanning both Microsoft and Oracle technologies, it’s a combination I typically avoid.

However, there are scenarios—driven by licensing, software requirements, or other factors—where deploying Oracle on Windows becomes the logical choice.

Read on for some pain points and a few tips to minimize them.

Comments closed

VACUUM FULL in PostgreSQL

Published 2024-12-16 by Kevin Feasel

Umair Shahid goes full vacuum and you never go full vacuum:

If you have worked with PostgreSQL for a while, you have probably come across the command VACUUM FULL. At first glance, it might seem like a silver bullet for reclaiming disk space and optimizing tables. After all, who would not want to tidy things up and make their database more efficient, right?

But here is the thing: while VACUUM FULL can be useful in some situations, it is not the hero it might seem. In fact, it can cause more problems than it solves if you are not careful.

Read on to learn what it does and why it’s not always a good idea.

Comments closed

Waiting for Locks in Postgres

Published 2024-12-13 by Kevin Feasel

Hubert Lubaczewski wants to make a change:

I once wrote about this problem, but given that we now have DO blocks and procedures I can make nicer, easy to use example.

Over the years there have been many improvements to how long ALTER TABLE can take. You can now (in some cases) change datatype without rewrite or add default value.

Regardless how fast the thing works, it still needs extremely heavy (though shortlived) lock: Access Exclusive.

Read on to see how you can write a SQL operation that waits for a lock and, if it does not get this lock, retries with backoff.

Comments closed

Against Using SQL Server Database Mail

Published 2024-12-12 by Kevin Feasel

Brent Ozar lays out an argument:

So you’re writing T-SQL code and you’ve decided that you want to send emails to customers, employees, or the general public. Perhaps you need to send order updates or low stock notifications.

Stop right there. You don’t really wanna do that with SQL Server. Let’s talk about why.

Read on for good reasons why. The bit of fuzziness here is “employees,” as Database Mail is quite useful for DBAs or individuals responsible for the upkeep of database-related operations. But the further you move away from “People whose job it is to keep the SQL Server instance up and running,” the less viable Database Mail is as a product, for the exact reasons Brent mentions.

Of course, you could always do what I did at one consulting engagement and set up sending Slack messages via CLR. I’m not saying it was a good idea, but it was pretty cool that it worked as well as it did.

Comments closed

High CPU with SQL Server Database Mail

Published 2024-12-12 by Kevin Feasel

Vitaly Bruk deals with a CPU issue:

Today, I investigated an interesting issue.

One of my clients called me and complained about high CPU on his server. Server with 0 load at this time frame.

Using my favorite “Activity” script and the sys.dm_os_ring_buffers DMV, I clearly saw that the CPU is being used by a non-SQL server service. Next, I’ve asked to connect SQL Server machine and opened a task manager.

Surprise, surprise! The CPU was being used by the SQL Server process! Well, kind of…

The moral of the story is to keep your systems patched.

Comments closed

Trace Flag 3625 and Masking Failed Logins

Published 2024-12-11 by Kevin Feasel

Patrick Keisler notes an interesting data capture result:

Several months ago, I discussed my customer’s intention to enable trace flag 3625. Since that time, we have observed an intriguing phenomenon when encountering a login failure.

To demonstrate this, our initial step is to ensure we are capturing failed login attempts. In Server Properties, select the Security tab, click on Failed Logins Only, and then click OK.

Patrick has a link to a description of trace flag 3625, but the short version is that it masks sensitive information in error messages from non-sysadmins.

Comments closed

A Mechanism for Change Management for DBAs

Published 2024-11-27 by Kevin Feasel

Terri Hurley shares one path:

Back in the day when I received an email from a developer who requested a Production Change, I would copy and paste SQL code from an email to SQL Server to run against a production database. I never bothered to save that code. But later when I was asked for a history of database changes, it was awkward to locate all the previous email requests asking for production changes. Fast forward to present day, most businesses have embraced Change Management Separation of Duties in companies of all sizes.

My preferred path to change management is:

  • All changes go into source control. You can either have the people making changes create the actual scripts to run, or generate them from base changes in source control, dealer’s choice.
  • Each changeset goes to a specific environment when it gets PR’d into the appropriate branch—that is, dev, QA, test, pre-prod, prod, post-prod, next-gen-post-dev-unprod, whatever.
  • DBAs are on pull requests, at least after a certain point. PR to dev? Let the team handle that code review. Once you move nearer to prod, at least one DBA performs a code review.
  • After PR gating happens, your CI/CD system automatically deploys the changes, assuming it passes any continuous integration tests.

This takes time to get right, but it’s very much worth the effort. I worked at a company where they built the release management processes on top of Git + Azure DevOps. You can also use tools like Flyway.

Comments closed