Voice of the DBA

Author: way0utwest

A Working Break on President’s Day

Today is President’s Day in the US, and it’s a holiday for me. At least from Redgate. I’m actually working my other job, perhaps with some overtime.

This is the end of a three-day weekend tournament up in Greeley, CO, where I’ve been coaching a 13-year old competitive volleyball team with my wife. We enjoy working with kids and we’ve been coaching in the spring for the last five years together. The RMR Showdown is a fun event, and it’s nice to get out of town. Hopefully we’ve had some success, but even if we haven’t been winning, it’s a good experience for the kids to grow, learn, and stress themselves across multiple days. Most of the time we do one-day local tournaments, so this is a chance for them to learn the eating well, hydrating, and sleeping are important.

We’ve also had a pregnant coach in the 18s division, and it’s entirely possible she can’t coach this weekend. She was due around this time, so as I write this, my wife volunteered us to take over her team if she’s giving birth. That means we’ll be working a morning wave with our team, from around 7a-2p, and an afternoon wave from 3p-9p. There might be some long days for us, but they’ll be fun.

Despite the work, it’s a recharging event for me in that I’m not doing anything with technology, and the time spent on this hobby is completely different than anything else in my life. It can be stressful during games, but it is very rewarding. I enjoy working with kids, and when it’s over, it’s over. The results of the competition are quickly forgotten, at least by me, but I see the lessons learned by players growing through the year.

However, work starts again tonight. As soon as we’re done, I’m heading to the airport for the DevOps Airways tour. No, I’m not flying a plane, but I am getting in one for our first stop tomorrow in San Jose. There are quite a few more, and I hope to see some of you at one of our events.

Steve Jones

February 19, 2024
Am I Good At My Job?

I was talking with a friend recently about some of their co-workers. In this case, they were complaining that another person couldn’t do some tasks that my friend considered relatively easy things. Deal with an unusual restore situation, write some PowerShell to reconfigure servers, build somewhat complex (to them) queries, etc. Things that my friend thinks most database people should be able to do after a year of experience.

However, my friend questioned whether they were evaluating co-workers appropriately. How can one tell? I might be able to tell if a coworker is better or worse at a task, but what’s my frame of reference and my experience. I might think them not capable at things I do well. At the same time, I can be wowed by someone with more experience with a technology I don’t know well, such as Azure Data Factory, but not really be able to judge if they’re average or an expert.

I suspect most people think they’re good at their jobs, or at least competent. They think they are earning their salary, and they likely are. However, that doesn’t mean they are better than the average SQL developer (or DBA or C# dev, etc.). That’s a different standard, and while most people in surveys think they are better than average, that’s not how averages work. Half of us are below the average.

It can be hard to determine where you might relate to others in a skill level. I think this is one reason why we struggle to interview and hire well. It’s just hard to determine what a senior person should do, and in a short Q&A period, we can’t often determine if a candidate can do all the things we might ask them to do later. I think many people get good at a small number of things their organization asks them to do, but often aren’t well rounded outside of those boundaries.

The people I’ve known in any business who excel are those who are always learning and improving themselves. Whether they’re technologists, doctors, or chefs, they are looking to get better at their existing skills and regularly add new ones. That’s a good outlook for life in general, and I would hope many of you adopt that attitude.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

February 16, 2024
A New Word:Maugry

maugry – adj. afraid that you’ve been mentally deranged all your life and everybody around you knows, but none of them mention it to you directly because they feel it’s not their place.

I don’t know that I quite feel maugry, though I certainly sometimes wonder if I view the world much differently (and strangely) than others. I certainly think in polite society that others often won’t point out something that others do or say that is very strange.

I also like to think that often I have lots of empathy and understanding of how others view the world, though maybe that means I am actually deranged from others’ point of view and I should feel maugry.

From the Dictionary of Obscure Sorrows

February 16, 2024
Friday Flyway Tips–Capture the Filegroup for Tables
A customer asked recently why Flyway doesn’t detect the filegroup for some changes. I showed them it does and decided to write a post on this.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Setting Up Filegroups

I don’t see a lot of SQL Server databases using filegroups. They can be helpful, and they can create complexity, but they aren’t good or bad. Some people use them, and for various reasons. If you use them, you’ve probably run some code like this:
```
ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO
```
Here I’ve added a new filegroup, called indexes, and a file in this group. This isn’t set as the default.

Since I’m working with Flyway Enterprise and generating scripts, I do need to also alter my shadow database, so that I can both detect changes and also verify my scripts. I’ll use this code:
```
ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO
```
Now we can detect changes.

Detecting Table Changes

First, let’s actually create a new table on this filegroup. To do that, I create a table, but I add an ON clause, as shown here. After the table creation, I use ON with the name of the filegroup. This ensures this table exists on the file(s) for this filegroup.
```
CREATE TABLE [dbo].[newtable](
     [myid] [int] NULL,
     [mychar] [varchar](20) NULL
) ON [indexes]
GO
```
Once this is done, I can go to Flyway Desktop and detect the change. When my schema model refreshes, I see the change, but no filegroup.

Hmmm, what’s wrong?

By default SQL Compare ignores filegroup information. If I click the Comparison options button, I get a dialog.

In here, go to the Comparison options dialog and type “file”. Hopefully the lower option below will be fixed to say “filegroups” and also add commas soon (sorry, I’m an editor).

We ignore these by default, so I’ll uncheck the lower box.

I’ll save this and refresh my schema model. Now I see the proper script with the ON clause.

Problem solved. If I care about where objects are created, I now have the filegroup location captured as part of my code.

Try Flyway Enterprise out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:
February 16, 2024