Press "Enter" to skip to content

Category: Administration

Loading WhoIsActive Data on Azure SQL DB

Published 2023-04-26 by Kevin Feasel

Andrea Allred wants to know who’s doing what on this system:

I needed to collect sp_WhoIsActive into a table, but the twist was that it is on my Azure Managed Database, so I had to get creative with how I did it. We needed an Azure Pipeline to run it, but we wanted to record it every minute and firing a pipeline every minute adds up fast. So we decided that we would kick it off once an hour and have the process wait for a minute and then fire until the hour ended. Then it fire again at the top of the next hour and the same process would happen.

That’s an interesting way to do it. Another alternative might have been an Azure function app, which you could schedule to run every minute. I think that’d be a lot less expensive than running an Azure Pipeline, and this goes to show you that there are many ways to solve the same problem in Azure.

Comments closed

Upgrading an Expired SQL Server Evaluation Edition

Published 2023-04-21 by Kevin Feasel

Chad Callihan loses track of time:

SQL Server Evaluation editions are great to use for testing early releases. When the real deal comes out, it’s best to upgrade then and there (probably Evaluation to Developer if you’re testing and experimenting). But what happens if you let an evaluation edition expire by accident? The good news is you can still upgrade but it’s a bit tedious.

Click through to see how.

Comments closed

Transport Layer Security and SQL Server

Published 2023-04-21 by Kevin Feasel

Michael Howard provides an explanation:

Invariably, when I ask people what Transport Layer Security (TLS) does, they say something like, “it protects my credit card info when I buy things online.”

The response is not incorrect, but it’s not the whole story, either, and let’s just ignore the server-side credit card protection requirements and PCI compliance for a moment.

TLS provide three security services, with an optional fourth.

Click through for those services and some of the ways we can improve our security posture when connecting to (or hosting!) a SQL Server instance.

Comments closed

Handling Error 574 during a SQL Server Upgrade

Published 2023-04-19 by Kevin Feasel

Tom Collins sorts out a nasty issue:

Script level upgrade for database ‘master’ failed because upgrade step ‘msdb110_upgrade.sql’ encountered error 574, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Read on to learn what caused this error and how Tom was able to work around it.

Comments closed

Purging WSUS Synchronization Events

Published 2023-04-17 by Kevin Feasel

Hannah Vernon has a script:

Since Windows Server Update Services synchronizes the list of Windows Updates from the source Microsoft Servers on a regular basis, the history of sync events can become quite tedious to load in the User Interface. Frustratingly, there is no way through the user interface to remove old history for synchronization events. The SQL Server T-SQL code below creates a stored procedure that can be used to cleanup old events prior to a particular cut-off date. I run the code via a SQL Server Agent job daily, with a cut-off date of 30 days ago.

Click through for the stored procedure.

Comments closed

Learning Important Postgres Settings for SQL Server DBAs

Published 2023-04-17 by Kevin Feasel

Ryan Booz helps a SQL Server DBA out:

Five years ago, I began my transition back to using PostgreSQL full-time, and it wasn’t the smoothest journey. One of the biggest reasons I struggled to make progress learning PostgreSQL early on was simply not knowing what I should be looking for. In fact, I often have conversations multiple times a month about the differences between SQL Server and PostgreSQL with folks setting out on a similar journey.

My guess is that you’re trying to figure out the same things, which is how you ended up on this series of posts.

Read on to take advantage of Ryan’s pain and suffering.

Comments closed

In-Place Upgrades or New Builds?

Published 2023-04-13 by Kevin Feasel

Ben DeBow asks the tough questions:

An in-place upgrade involves upgrading the existing SQL Server to a newer version without creating a new instance. In other words, the new version of SQL Server is installed on the same server as the existing version, and the databases are upgraded to the new version.

On the other hand, a side-by-side upgrade involves creating a new instance of SQL Server and migrating the databases to the new instance. This approach requires more planning and preparation than an in-place upgrade but can be less risky.

Click through for the comparison. Historically, I’ve always been on Team New Build, though more recent versions of SQL Server do a better job of in-place upgrades, to the point where I’m fairly comfortable suggesting one to a client.

Comments closed

Cost Governance via Azure Policy

Published 2023-04-12 by Kevin Feasel

Felipe Binotto helps us save a bit of money in Azure:

Cost governance is an essential aspect of managing any cloud infrastructure. Azure Policy is a powerful tool that can help implement cost governance measures within your Azure environment. With Azure Policy, you can define and enforce rules to control costs, monitor usage, and optimize your resources.

These policies can be used to prevent the creation of resources that are not compliant with cost-saving measures or to apply tags to resources that identify them as cost-related resources. You can also use policies to track resource usage and generate alerts when certain thresholds are reached, allowing you to take proactive measures to optimize your resources and control costs.

Throughout this article I will provide some examples of Azure Policies you can use for cost optimization.

There’s some solid advice in here. Most of it boils down to knowing what you have running so things don’t slip between the cracks.

Comments closed

Putting tempdb on an Azure VM Temp Disk

Published 2023-04-11 by Kevin Feasel

Daniel Hutmacher uses a temp disk for a temp database:

Almost all Azure virtual machine sizes come with a temporary disk. The temporary disk is a locally attached SSD drive that comes with a couple of desirable features if you’re installing a SQL Server on your VM:

  • Because it is locally attached, it has lower latency than regular disks.
  • IO and storage are not billed like regular storage.

As the name implies, the temporary disk is not persistent, meaning that it will be wiped if you shut down your VM or if the VM moves to another VM host (as part of maintenance or troubleshooting). For that reason, we never want to put anything on the temporary disk that we need to keep.

I’d say this was a lot more popular several years ago, back when spinning disk was the default for Azure storage. There can still be benefits from doing this, though if you’re using Premium storage with high IOPS, the biggest remaining benefit is around latency.

Comments closed