Voice of the DBA

Author: way0utwest

  • Fill Out The State of the Database Landscape Survey and Maybe Win

    If you’re like me, you sometimes wonder how different other environments are from the one I work in. Well, the ones I used to work in. These days I see lots of customers environment, build PoCs, and help them solve problems, but I don’t have much of an environment for myself.

    We’re taking responses for the State of the Database Landscape survey. http://rd.gt/survey Share your thoughts on platforms and what your org does. This survey is

    I’m trying to get Redgate to offer some prize, but I’ll do one from my SQL Server Central budget (if I can slip it in). I’ll get a list of responses and pick 4 people from those that leave their name and email at the end. You have to fill out the survey and share the link on socials somewhere.

    Spread the word, and you send me a link of where you’ve shared the contest (LinkedIn, FB, Twitter, Thread, etc.) then I’ll add an extra entry in my contest for each share. I’ll pick people who fill out the survey and have shared it and send each a USD$25 Amazon GC (or Starbucks or other major brand).

  • Decrypting Stored Procedures The Cumbersome Way

    I had a client that was struggling with some encrypted stored procedures. They needed to decrypt them, which I know is a pain in the #@$%@#$@#$#@. I had to do this one. This post shows how I sent them some code to do this.

    Note, SQL Compare 15 does this easier and simpler. If you own it, I’d use that instead. A future post will show how easy that it.

    Setup

    I tested this on SQL Server 2017/2019/2022. I don’t have older instances handy, so I can’t verify that for those. However, I ran this code in databases on each instance:

    CREATE OR ALTER PROCEDURE dbo.DecryptionTest
WITH ENCRYPTION
AS
SELECT 2 AS Two;
DECLARE @i INT = 1;
IF @i = 2
   SELECT 3 AS Two;
ELSE
   SELECT 2 AS Two;
GO
CREATE PROCEDURE [dbo].[DecryptionTest2]
WITH ENCRYPTION
AS
   SELECT 2 AS Two;
GO

    I had shown how to detect these are encrypted in a previous post.

    Decryption

    I had initially sent the client these links, since I was sure they’d worked at one point. I thought that SQL Server 2000 and earlier had a different algorithm. Don’t quote me on that and there’s so much Google-noise, I can’t verify this.

    Those links don’t work, and I can only guess that either I had used different procs or these were edited.

    In any case, I found a gist here: https://gist.github.com/jstangroome/4020443

    This has code that does work. I ran this on my instances, and I got similar output to this on all instance:

    2023-07-21 12_54_19-SQLQuery3.sql - ADMIN_ARISTOTLE_SQL2017.Compare2 (ARISTOTLE_Steve (53))_ - Micro

    I didn’t clean this or try to extract the value from the XML, but for the client this worked.

    It’s not the cleanest or best way, but it does decrypt the procs I’ve created.

  • Using Clear Language to Avoid Confusion

    A long time ago a software engineer advised me to try and ensure that I made my interfaces clear to users, especially those that are busy focusing on some other task the software enables. The phrase he used was to make a “Fisher-Price” interface, referring to the toy company that makes big and brightly colored buttons for their toys. The idea being that we didn’t want to cram too many things on the screen or possibly confuse a user.

    As a kid, I watched Mr. Roger’s Neighborhood on television and enjoyed the show. Lots of kids had a similar view, but they might not have known that the main actor took care in choosing his words to convey concepts and ideas to children. In fact, the former producer said that the writers joked that his manner of speaking was its own language, called “Freddish.”

    I saw an article that talks about the care Fred Rogers took in explaining things, with examples of how he made choices in how to communicate with his audience. The piece got me thinking about not only Fisher-Price interfaces, but also the language that we use to communicate with developers when we ask them to build something.

    Often I find in software development that we use a model or a shortcut to represent what we want. We partially describe something, or we assume that the words we choose mean the same thing to us and the other party. This goes both ways, with developers needing to be clear with their clients (and each other) and clients needing to be clear with developers.

    I don’t think I’d advocate for adopting Freddish, but I do think that we ought to pay attention to the words we choose, especially when we find the end result isn’t quite what is expected. Examining where the design went wrong and if we could communicate differently, or more often, earlier in the process to avoid problems.

    Communication is a difficult skill. I find constant examples at work, in my hobbies, even within my family where someone says one thing, but means another.

    To me. To themselves, they feel perfectly clear. Context, innuendo, implication, we depend on these a lot in communications, but those can cause issues when we aren’t very familiar with others. As we move to remote work, as we find less bonds with our co-workers because we don’t see the same body language, as we don’t eat meals together, as we don’t often share a context of work, we need to be more careful about the words we use and find ways to ensure we all are saying the same thing that the other person hears.

    Steve Jones

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

  • Friday Flyway Tips: Searching Migrations

    When a Flyway Desktop (FWD) project (or Flyway project) has been around for a long time, there can be a lot of migration scripts. That can be a pain for users, but there is a way to find your changes or limit what you see. This post looks at how to do this starting with Flyway Desktop 6.5.4.

    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.

    A Busy Screen

    I’ve got a lot of migrations in this project. You can see below in the image, noting the scroll bar goes up and down here.

    2023-08-10 12_30_54-Flyway Desktop

    Many customers have many more migrations, which could run into the 100s. I’ve worked with large Oracle migrations in the past where there were 100s for an upgrade from v4 to v5. The project would have been 1000s in its lifetime.

    Use Search

    At the top of the Migrations tab, there is a new search box. This was added somewhere in Flyway Desktop 6, and I just noticed it. However, it’s very, very handy. If I start typing in there, like “New Table”, then I see just those migrations.

    2023-08-10 12_33_47-Flyway Desktop

    I can even search versions, like the specific migrations in the 3.8 release that I am wondering about.

    2023-08-10 12_35_55-Flyway Desktop

    This works by checking the version and description fields. The rest aren’t as useful, but it does mean if you come up with some standards for the description in your team, you can easily use this to find changes in your project.

    Try it 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: