Voice of the DBA

Tag: T-SQL Tuesday

T-SQL Tuesday #144–Data Governance

This month’s topic is something I wouldn’t have considered a decade ago. Especially in the US, this isn’t something that I’d actually have cared about for most of my career. Even when companies talked about governance, it was more a checkbox exercise.

Dr. Victoria Holt brings us this invitation from the UK, and I think it’s a great one. This is a data topic that will impact more and more of us in the future. Not all of us, but certainly plenty of us.

If you haven’t thought about this topic, I’d urge you to write something, even if it’s a view that this isn’t necessary. Share your thoughts, this month or in the future.

Data Governance Effort

My employer, Redgate Software, has been interested in this for a number of years. We are a UK company, and when the GDPR passed, we knew this would be a concern for us and our customers.

We built a product to help customers classify and understand their exposure in databases. I hope this expands out to other sources, because I feel this is a constant challenge. While lots of data is in database, we are constantly pulling it out and sharing it in files, reports, and more.

Security is hard, and I don’t know that we’ll end up completely controlling the access and data, but I do think that we need to understand where our risk and exposure are as we share data.

This is a hard job, and a boring one. I can’t see many people enjoying the process of dealing with data governance. We need to make this easy to do, in a distributed fashion by many people as they notice something is not being managed. At the same time, I think that we need to be regularly reviewing and examining security against policies. We have far too many public access points being used to share information when things should be secured.

I don’t know how we reconcile those things, and decide on the value of doing them. Some products may work well, some I think are too targeted, and some too expensive.

I believe data governance matters, but not sure how we actually get to a place that it’s well done in many organizations. Especially in the US.

I hope I’m wrong.

November 9, 2021
T-SQL Tuesday #143 – Short Code
I’m late, but I wanted to get this out. I started the post, then got distracted with travel to Europe late last week.

In any case, this month the host for T-SQL Tuesday is John McCormack, with his invite to write about short code examples. It’s a good technical invite, and one that immediately got me thinking about things I do often.

If you haven’t ever tried a T-SQL Tuesday, set up a blog and put out a post on the topic, this week or whenever you read this.

Snippets

I work for Redgate Software, though I’d likely be using SQL Prompt no matter what. I was never thrilled with native intellisense, but SQL Prompt has been great. One of the things I like most about Prompt are the snippets, which let me insert code with a few keystrokes.

Here’s a quick example. If I type “” in SSMS, I see this:

Prompt detects this as a snippet first, then has other matches. I can hit Tab, and I’ll get this:

I get a quick “top 10” from a table. Often I use SELET * for a quick look at the shape of data.

However, I can add custom snippets, and one I use often is this:
```
WITH myTally(n)

AS

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)

)

SELECT *

FROM myTally
```
This is my “tt” snippet, so I can type “tt<Tab> and get this code for a tally table. This s handy in many string manipulation and problem solving situations.

If I need more than 100 rows, I can easily copy the cross join line, add a new alias, and I have 1000 rows with this:
```
WITH myTally(n)

AS

(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
  FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
   CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)

)

SELECT *

FROM myTally
```
Short, but effective code.
October 13, 2021
T-SQL Tuesday #142–What is Bicep?

It’s time for the monthly blog party, and this month the host is Frank Geisler. I was reaching out to various people last year to find hosts, and also (silly-ly) looking for first names to fill out the alphabet on the host page.

Frank is a fellow MVP and a BI expert. He was gracious enough to agree to host and I thought he picked an interesting topic. This month’s invitation is about scripting to deploy resources. I don’t do a lot of deployments over and over, but I have been interested in one area. It’s actually been a tab in my browser open for a few weeks, so I took this opportunity to dig in.

Bicep

I’ve been seeing this term used in various blogs, and in the MVP email list we have. I wasn’t sure what it was, and I kept thinking of this:

That’s not it at all, and I was glad I went through this resource.

Bicep is a transpiler, meaning it takes one language and translates it into another. In this case, the Bicep language will move code into the ARM JSON templates. JSON is really for machines, not humans, so the idea is to give sysadmins and developers an easy way to describe resources they need to deploy into Azure.

The language is new, and it’s on Github. This is a DSL (domain specific language), which means it was designed for a specific purpose. With the 0.3 release, the language is built into the Azure CLI and Azure PoSh utilities, so this will do the transpilation for you. There’s also a decompiler to go from an ARM template back to Bicep. It’s also supported by Microsoft, which is always a plus if you need to call for some issue.

I don’t know a lot about Terraform, but this appears to be another language designed to do the same thing. The FAQ in GH notes this is a revision of ARM, which I guess makes sense. I’m guessing at some point they’ll try to get people to not use ARM (or stop supporting this) and only work with Bicep. Azure supports Terraform, so I’m guessing this is more an attempt to deprecate ARM and the challenges of customers working with JSON.

A couple things I did like about this. First, there are modules, so you can reuse some code. There is also intellisense, validating and getting you actual resources from Azure that are available. I do worry about deprecation that makes it difficult to deploy a copy of an existing resource when Azure is trying to push you to use some new resource. However, I do think the intellisense and validation in the VS Code extension improve productivity for developers and sysadmins.

Reuse also helps infrastructure teams assist developers by providing them modules that adhere to good or best practices.

The language looks like a mix of Python and YAML. Not too hard to understand, and with help, this should be easy to use to create a resource.

I’m pressed for time today, so I’m not going to experiment or set up an environment, but I need to add this to a list of things to do.

Why call it Bicep? No idea. I can’t find an answer, but I did someone say these are “ARMless deployments”. Not sure that fits with Bicep, but I like the logo.

September 14, 2021
T-SQL Tuesday #141–Finding a Balance

This is a great topic, and the host is a good friend, TJay Belt. We’ve met each other’s families and a lot of our conversations revolve around non-work stuff. I actually am friends on Facebook with TJay and follow his outdoor adventures on a weekly basis as he hikes, bikes, boats, and more.

This month he asks us to write about Work/Life Balance, which is an important topic to me.

Work From Home

My first attempts to find balance started at the turn of the millennium. Actually in 1999, when I worked at a financial services company. We had instability in systems, to the point that I kept a pillow in my office as I spent more than a few nights there.

After that I changed jobs a few times until I finally went to work for myself at SQLServerCentral. Since then, I’ve been at home for 18 years, including through this pandemic. A few things that I’ve learned in that time.

Be Flexible

The first time I was at home regularly was sharing an office with my wife, who had been telecommuting for a company for quite a few years. She was on the phone a lot, so I learned to keep headphones around, or be ready to grab my laptop and go work downstairs.

I also learned that things come up at home. Maybe my wife was planning on getting kids and a meeting popped up, or vice versa. It was helpful to be flexible and ready to adapt.

Set Some Rules

When my wife started working from home, we had a 5yr old. He was pretty good with rules and learned to be quiet when the door was closed. A couple years later, our second was born, and that was a challenge. We had a nanny, and that was helpful, but we slowly had to teach the 2nd (and 3rd) rules about noise and disturbances.

On the plus side, we learned to be flexible and make time for kids. If they needed some attention and we weren’t busy, give it to them. Include them in breaks, walks, or head to school. I loved going to lunch with my kids when they were at school in 1st, 2nd, 3rd grade.

Blending Chores

One thing that helped me is that I blended work and life when at home. My boss or coworkers wouldn’t hesitate to ping me if they needed something at 6pm, or now, 7am, and that’s OK. However, I also know that doing laundry, or prepping something for dinner, or even cutting grass are things I might do as a break from work or while listening to a call.

I mixed my life with work, taking advantage of the proximity. In an office, I might wander to the break room, or go talk to someone and do a little less work. Here, I try to get something done. I’ve taken an hour to change oil or put on winter tires in the middle of the day, and it sometimes lets me return to work refreshed.

At the same time, if I have a bunch of chores, it’s much easier to go back to work after dinner for an hour if I need to get something done.

Set Some Hours

At first I tried to make a strict set of office hours and work from 8-4, like I was in office. That didn’t work for me. It felt like I was not taking advantage of being at home, around kids at times, and more.

However, I also learned to get away. I am lucky in that I’ve often had a separate office, but I have had laptops or other devices around the house. I’ve learned not to check email or do work when I’m not working. If I use the computer to check something for life, I don’t spend time looking to see if I’ve missed something at work.

Spoiler: I’ve always missed something and it’s almost always not that important.

I do, however, make up time if I have had a bad week. When I struggle to work, it’s hard as a creative person. In an office, I’d listlessly sit at my desk and let time pass me by. Now I get up and go do something to help myself.

I’ll then make up that time in the evening or on the weekend.

Time Zones Suck

I work for a UK company in Colorado. I’m GMT-7, which means I really have a few hours in the am when I can reach people in the UK and work together. I also get some people scheduling things at 7am, or even 6am, my time. Not often, but it happens. We have an office in CA, and I sometimes get messages around 6pm my time, just before someone there knocks off.

I roll with this. My wife hasn’t been thrilled, but during the pandemic I’ve had a lot more 7am calls than ever before. A ton at 8am. I just deal with it because it’s not every day, and the flexibility is a trade off. I’ve done plenty of 9am yoga classes, taking an 8:00-8:50 call on the way and in the parking lot. They deal with it, so I can as well.

Remember

We work to live, not the other way around.

August 10, 2021