Voice of the DBA

Author: way0utwest

Building a Data Center

Most of you reading this have likely seen a data center, and maybe sat in one while connecting to a database, but how many of you have had to try and do some of the work to set one up? It’s not something most data professionals do, but since we often have some of the most expensive boxes in one, I thought it would be interesting to discuss what goes into getting one running.

There’s a blog post that starts to cover what it takes to set up a data center. I found it interesting to read as I’ve been a part of the group choosing a space a few times, and a lot of the issues discussed in the blog were things I dealt with or experienced. Both with large and small companies, I’ve helped to spec out and decide what we need from a colocation facility or our own room in an office. In smaller companies, I’ve had some of the “what-the-duck” moments outlined in the post when I tried to rack and connect systems, only to find some issue with power, connections, etc.

When I read that post, I think about the months of getting everything set up, it’s hard to see this as more cost-effective than the cloud in some ways. Certainly setting up racks and servers is easy. Getting them powered, having a cold and hot aisle, and deciding what gets connected to where these are relatively simple things. However, making decisions on networking, routing, and other complex tasks can take specialized expertise. For a single company, finding, hiring, and retaining someone who knows this stuff well is hard. Startups might get someone excited, but the really good people who can design this stuff are often working at a cloud company of some sort.

Maybe those people are tired of the FAANG/Microsoft world and want to come to your startup, but for how long? And how do you replace them? Lots of companies found maintaining a data center to be more expensive than it was worth.

I suppose a similar situation in the database world is setting up a complex system, perhaps something like a new Firebolt warehouse. If the person that did that leaves, can the rest of the company fill their shoes? Do you know how Firebolt works? Would it be better to use a more popular system, like Snowflake or Databricks, with lots of other people writing articles and blogs about how things work? I think it would be, if for no other reason than there are other models to follow and more people that can give you guidance.

A data center, whether a private one or a cell in a large cloud provider’s footprint, is a complex beast. The days of using a closet with servers on a bookshelf are likely gone for most commercial enterprises. Even SQL Server Central moved on to hosted systems. We used to rent a rack in the early 2000s in a co-location facility, one I enjoyed visiting, as long as the visits were few and far between. Now I like connecting to EC2, which is much simpler and easier.

Steve Jones

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

Note, podcasts are only available for a limited time online.

February 5, 2025
Picking a Starting Table in Test Data Manager
I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and a follow up post on using your own backup. One of the things I didn’t show from my own database was that it had no FKs, so the subsetting didn’t quite work as I wanted.

This post shows how to correct things and add starting tables for the subsetter to look at in order to customize your setup.

This is part of a series of posts on TDM. Check out the tag for other posts.

The Setup

When I ran the subsetter PoC with my own backup, I showed this screen comparing the size of the dbo.players table before and after.

Here’s what was missing. Let’s look at the counts from all tables. This seems OK, 10%-ish for most tables. The smaller ones are excepted there.

However, if I look at the subset and my LahmanID = 11, I see the player, but no batting stats.

That’s not useful. We got a random 10% from these tables, and my data isn’t intact. In a larger database, I might miss that I had incomplete, unmatched data across tables and write reports or queries that seemed to work, but really didn’t.

Let’s fix this.

Customizing the Subset

We have the ability to customize the way the TDM tools work by adding in things we know, which can’t be detected. Like FKs between tables that aren’t declared. In this case, I want to add in a relationship.

Note: the best place to do this is in a settings file, which I’ll customize for my purposes.

If I look in the TDM-Automasklet repo, there’s a settings file already setup that grabs certain orders for Northwind.

I’ll change this as follows to just grab all players born in CA. I have no idea how many this is, but let’s filter on that.

Let’s start the TDM-AutoMasklet and just run through the subset. To use my settings file, I’ll alter this line (20) in the file:

Since I added my file to another repo, I’ll put in the full path:

I’ll run the PoC and I get to the subset section. I’ll stop, but I’ll copy the subsetter command, which is highlighted below.

This command is this (broken into lines for clarity):
```
rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite
```
However, as of Jan 15, this is missing the options file. I’ll add that to the command, which will now look like this:
```
rgsubset run --database-engine=sqlserver --source-connection-string="server=localhost;database=BB_FullRestore;Trusted_Connection=yes;TrustServerCertificate=yes" --target-connection-string="server=localhost;database=BB_Subset;Trusted_Connection=yes;TrustServerCertificate=yes" --target-database-write-mode Overwrite --options-file E:\Documents\git\TDM-Demos\rgsubset-options-bb.json
```
When I run this

If I now look at rowcounts, I see this in the original (left) and subset (right) databases. Note that there rather than 10% of most tables, I now see 1800 players, but almost no rows in other tables.

This happens as I’ve selected a starting table, a parent, but since I don’t have declared relationships as FKs, the subsetter essentially had no idea where to go. It filtered based no dbo.players.birthState = ‘CA’, but that’s it.

I can add a second starting table, teams, as well. When I do that, I have this in my options file. Note, I’ve filtered on the years after 2000. If I don’t filter, I get all the values passed through. I think this is because this is a fairly small table and I haven’t specified a target size.

When I run this, it’s again quick and I see only 360 rows moved over in the dbo.teams table.

I could add other starting tables if I had different parts of my database that had unreleated entities. However, in this case, most of these tables are related, just not with explicit DRI.

This post has shown a way to start controlling the subsetting. In the next post, I’ll look at adding in the manual relationships.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below:
February 5, 2025
T-SQL Tuesday #183 Invite: Tracking Permissions
It’s time for T-SQL Tuesday again and this month I’m hosting. I realized that I didn’t host in 2024 and since I run the thing, I ought to be a part of it one of these days.

So, this is that month. The format is the invite comes out today and you have a week to write a post. Please post it on your blog and link back to this post or leave a comment on this post with the link to yours. That way I can summarize in a few weeks.

With that, the invite…

Managing Database Permissions

One of the things I see lots of people struggle with is the database permissions for their logins/users. DBAs or Ops people are always getting requests to add people, rarely requests to remove people, and not often enough, requests to audit who has permissions.

Lots of systems that live for years have people with too many permissions but no one knows this.

It could be SQL Server, Oracle, CosmosDB, RDS, PlanetScale, etc. Well not that last one, they haven’t been around for years, have they? They have, since 2018, so maybe people do have this issue in MySQL.

Ultimately, we have to audit or understand permissions at some point. Or we need to update permissions. This could be a developer task (as they add/change objects) or it could be strictly Operations. I’ve seen both.

This month, I’m wondering how you track permissions, which I assume involves some code. Show us a good way to do any of these:
- check permissions
- update permissions
- add new logins/users across systems
- remove people
- report on permissions at various levels (user, object, database, etc.)
You should publish your post on Feb 11, 2024 UTC. Link back to this post with a trackback/pingback or a comment.

Looking forward to what you come up with.
February 4, 2025
Declaring a Complex PK in a CREATE TABLE: #SQLNewBlogger
Recently I was talking with someone who had not named any of the primary keys (PKs) in their database. They used system generated names and when they ran comparisons, they got all sorts of drops and creates they didn’t expect. This post shows how easy it is to declare PKs with names, even with complexity.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

I’m going to use a schema that I have for baseball data. It illustrates the point well, I think.

For my friend, imagine a table like this one:
```
CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)
```
If I create this table, the system will decide what the PK is. In fact, after I run this, I can see in sys.objects that the name is some random code.

What’s worse is that if this were an FK and I were dropping these in some script, I’d have issues in other systems. This name is different on each system.

The better solution is to make a simple change. Before the Primary Key keyword, I can add Constraint and a name, like this:
```
CREATE TABLE [dbo].[salaries](
     [yearID] [int] NOT NULL,
     [teamID] [varchar](3) NOT NULL,
     [lgID] [varchar](2) NOT NULL,
     [playerID] [varchar](9) NOT NULL,
     [salary] [int] NULL,
CONSTRAINT salariesPK PRIMARY KEY CLUSTERED 
(
     [yearID] ASC,
     [teamID] ASC,
     [lgID] ASC,
     [playerID] ASC
)
)
```
If I run this, then I have a better named PK.

Be explicit in your work. It makes for better code and easier, repeatable, reliable deployments.

SQL New Blogger

This is a simple thing, a code smell, but one that wastes DBA and developer time. This post shows a simple thing you can do to have better code. This took me about 10 minutes and you could do something similar.

Write this and maybe someone asks you how to do this in an interview.
February 3, 2025