Press "Enter" to skip to content

Category: Administration

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

Changes to the IaaS Agent for SQL Server on Azure VMs

Published 2023-04-11 by Kevin Feasel

Aditya Badramraju has an announcement:

SQL Server on Azure Virtual Machines is powered by the SQL IaaS Agent extension which provides many features that make managing your SQL Server easy. This blog will discuss new features and changes we’ve recently released in this extension. 

Click through for those changes. I was prepared, upon seeing the “Retiring Modes” section, to have a cynical response about forcing everyone into what was effectively Full mode, but that proto-take ended up being way off base and the truth is much nicer.

Comments closed

Finding Columnstore Index Storage and Memory Allocations

Published 2023-03-31 by Kevin Feasel

Jose Manuel Jurado Diaz has a few scripts for us:

Today, we got a new question how much is the size used by a columnstore index table at storage level and memory usage.

TSQL to obtain the total number of rows, size per schema, table and index.

Using the view sys.column_store_row_groups (Transact-SQL) – SQL Server | Microsoft Learn we could see the total number of rows and space usage at storage level.

Click through for that script, as well as a few more to learn how much space and memory that columnstore index is taking.

Comments closed

Accelerated Database Recovery in SQL Server 2022

Published 2023-03-29 by Kevin Feasel

Perry Skountrianos takes us through some recent changes:

In SQL Server 2019 (15.x), the ADR cleanup process is single threaded within a SQL Server instance. Beginning with SQL Server 2022 (16.x), this process uses multi-threaded version cleanup (MTVC), that allows multiple databases under the same SQL Server instance to be cleaned in parallel.

MTVC is enabled by default in SQL Server 2022 and uses one thread per SQL instance.

Read on to see how you can change that, as well as additional product updates.

Comments closed

Restoring an Azure SQL Database

Published 2023-03-29 by Kevin Feasel

Andrea Allred recovers from a mistake:

Recently, the wrong table got dropped and we needed to bring it back. I had never done a restore in an Azure Managed Database before so I learned something really fast.

Click through for the process. And yeah, it is quite easy, though I’ve noticed that restore times are a bit slower than if you were using local hardware on-premises.

One neat trick with database restores in Azure SQL DB: you can’t restore over an existing database, something a client wanted me to do last week. What you can do, however, is restore the database under a new name, so we might have messedupdb and then messedupdb_restore. Well, in this case, messedupdb had no changes since “the incident,” so what we were able to do was rename messedupdb to messedupdb_dropme and rename messedupdb_restore to messedupdb. Azure SQL DB happily rolls on with this and after ensuring that the database was now in prime condition, we could drop the old version. It’s a little more complex than simply restoring over the existing database, but all the relevant metadata Azure SQL DB needs stayed in sync along the way, so the process was smooth.

Comments closed

(Possible) Shifts in Zero Downtime Mentality

Published 2023-03-27 by Kevin Feasel

Steve Jones notices a trend:

The last year, however, has had more people looking to implement database DevOps and speed up their development, but not a lot of questions or demands for zero downtime during these deployments. I find that interesting as the world depends more and more on computer systems, and the customer base for many organizations may demand access to the systems at any hour of the day or night.

However, it doesn’t seem that as many people are concerned about small moments of downtime.

My ego would like to tell people that it’s probably because everybody read my post about how zero downtime isn’t really a thing.

Ego aside, it is pretty interesting that Steve’s not finding as much push for minimal downtime. Steve does include a few conjectures as to why it may be so.

Comments closed

Updating SQL Server Containers on Kubernetes

Published 2023-03-24 by Kevin Feasel

Amit Khandelwal rolls out some updates:

I’m sure you’ve thought about how to update SQL Server containers running on a Kubernetes cluster at some point. So, this blog attempts to answer the question. According to the Kubernetes documentation, there are two Update strategies for statefulset workloads. For your convenience, I’m quoting the summary below:

  1.  OnDelete update : When a StatefulSet’s .spec.updateStrategy.type is set to OnDelete, the StatefulSet controller will not automatically update the Pods in a StatefulSet. Users must manually delete Pods to cause the controller to create new Pods that reflect modifications made to a StatefulSet’s .spec.template.
  2. Rolling update : When a statefuleset’s .spec.updateStrategy.type is set to RollingUpdate, the StatefulSet controller will delete and recreate each Pod in the StatefulSet. It will proceed in the same order as Pod termination (from the largest ordinal to the smallest), updating each Pod one at a time. This is the default update strategy.

Read the whole thing to learn how these two strategies of updating containers work.

Comments closed