Voice of the DBA

Tag: AI

  • Constitutional AI

    I will admit that I don’t know a lot about AI (Artificial Intelligence) systems and how they are built. I’ve been playing with them a bit and haven’t been overly impressed with the results. I think some of this is that the my work is creative and I’m both used to being creative and I find the AIs less creative. And less accurate. And require a lot of editing. I don’t mind editing, but not if it takes longer than just writing things myself.

    From my understanding, a lot of the models behind AI systems (chatbots, recommenders, etc.) are built with humans giving them feedback on their responses in what’s known as RLHF (Reinforcement Learning from Human Feedback). Essentially paid (often low paid) people that help to “guide” the AI into responses that are useful.

    I don’t quite know how that looks, and I certainly don’t want a job doing that. Definitely not if it’s looking at a lot of UIs like the one in this article. Can you imagine being paid to read things like this and then try to rank them? I can’t imagine they keep getting great input from evaluators across the day. Maybe 9am-10am, but I’d bet the 4pm-5pm responses are quick clicks.

    There was an article about a company trying something different: constitutional AI training. There’s a better description on the Anthropic website. It seems in this case they are creating some principles and limited human feedback, but then relying on an AI to give feedback to another AI? Or itself? I have to admit that I’m not completely sure of what happens here.

    Ultimately, I like the idea here, but I think the idea of a single LLM/AI model that suits every situation, or one that works in every geography doesn’t make sense. We have different thoughts among people and different cultures all over the world. I’d expect that we might have different types of AIs in different situations or environments. The one that helps decide how to deal with nuclear safety likely needs to be different from the one governing traffic signals. I certainly don’t want one TruthGPT to be the one true voice on all things.

    The idea of AI systems, assistants and more seems  complex and more strange than anything I’d have imagined from reading science fiction. As with many things, the reality is far different from the speculation I’ve had about how I would respond or want a system to behave. I think that’s the nature of science fiction; it picks specific situations and tailors the story to fit. The real world is much more messy.

    I don’t know where we go, but I’m curious as many of you have had more exposure to AI. Is it helping? Hurting? Useful? Are you excited or worried for the future? I’m curious what you think, mostly because I’m not sure what I think.

    Steve Jones

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

  • We’re Not Faster with AI

    At Redgate Software, we’ve been trialing Copilot from GitHub with our developers. I managed to get access for this experiment and have tried a few things, though I’m not sure I’ve found it very useful. I’ll continue to work with Copilot, but for now, I just don’t find Copilot AI helping me with the types of tasks I do.

    However, our chief of technology, Jeff Foster, was interviewed about how he sees AI and Copilot in Redgate. It’s a good read, and I found a few fascinating things in the piece. First, I was worried about this a bit, as we have explicit guidelines in developers that say we can’t take the output from an AI and put it in our codebase. We worry about legalities since we sell software. How does that work with Copilot?

    Well, I learned that there are a couple of things in here. First, with a business subscription, we can have the AI not show us copies of public code. If more than 150 characters of public domain code is in a suggestion, it’s hidden. This is in the Copilot docs as well. I don’t know if 150 is a good length or too long, but at least there are limits. We also found that Copilot doesn’t retain any snippets, which is important for us. We don’t want to share Redgate code with others, especially competitors unless we are explicitly putting a public repo out there.

    The second thing I found really interesting is that we have a lot of guardrails around the way we produce code. We have tests, lots of automation to evaluate things, and plenty of peer review. I see requests for PRs in Slack constantly amongst our teams. We know that the code which gets generated likely needs some editing, but also that we want to be sure that whether it’s sent as is or edited by a developer, the code is still reviewed. Having these guardrails, test, etc. help us experiment with new ideas without impacting our productivity.

    Perhaps the most interesting part for me was near the end. Jeff doesn’t think that Copilot makes us faster. He says “We probably invest ten times more time finding the right problem to solve than on the actual coding.” That makes sense, given what I’ve observed over the last 15 years. We are deliberate and careful about picking problems to solve and producing high-quality code. Sometimes it makes me a little crazy as I want things to move faster, but I’ve learned to appreciate that overall, this is an approach that doesn’t get us into places where we struggle to onboard developers to new projects or adjust our code to meet new requirements.

    I’ve got a few quiet weeks coming up, so I’m going to give in more and use Copilot a bunch to see what I can get out of it. If you’ve tried it, let me know what works or doesn’t. If you want me to try and solve some problem for you, drop a note in the discussion with the challenge you have.

    Steve Jones

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

  • Getting AI Written Unit Tests

    As a part of my AI experiments, I decided to ask CoPilot to write some unit tests. Here is what happened.

    The Prompt

    To get started, you enter a prompt as a comment and put the cursor on the next line. Then I can open the CoPilot completions panel. Here was my prompt:

    -- for an orderheader table with orderdate and ordertotal columns
-- write a tsqlt test to ensure that the orderdate is not null
-- and that the ordertotal is greater than zero

    Once I completed this, I looked at the results.

    The Solutions

    The first few solutions weren’t very helpful. I’m not even sure what was the thinking for 1 and 2. Three at least starts to get me to think about different cases.

    2023-05-10 14_01_56-● -- for an orderheader table with orderda • Untitled-1 - zerodowntime - Visual

    Solution 6 started to have promise:

    2023-05-10 14_02_37-● -- for an orderheader table with orderda • Untitled-1 - zerodowntime - Visual

    Then solution 8 was here. It opens with a short summary of what to do. Not terribly useful here, as this type of structure is something that a developer learns once and then remembers.

    Suggestion 8

    The solution was a mix of code and text, which I assume is assembled from somewhere on the web. Leaving aside the problems of Intelletual Property, here’s what I saw. All the results are formatted as code here.

    CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    -- Act

    -- Assert

END

GO

```

The test should pass if the column is not null and the total is greater than zero.  The test should fail if the column is null or the total is less than or equal to zero.

OP 2019-09-18: The following is a solution that I put together:

```

CREATE PROCEDURE [test orderheader columns]

AS

BEGIN

    -- Assemble

    DECLARE @columnName sysname = 'orderdate'

    DECLARE @sql NVARCHAR(MAX) = N''

    -- Act

    SELECT @sql = N'SELECT ' + @columnName + N' FROM orderheader'

    EXECUTE sp_executesql @sql

    -- Assert

    EXECUTE tSQLt.AssertEquals @Expected = 1, @Actual = @@ROWCOUNT

END

GO

```

The assert statement will fail if the column is null.  If you want to assert that the column is not null, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

```

If you want to assert that the column is not null and the value is greater than zero, you can use the following:

```

EXECUTE tSQLt.AssertNotEquals @Expected = 0, @Actual = @@ROWCOUNT

AND @Expected = 1, @Actual = (SELECT COUNT(*) FROM orderheader WHERE orderdate > 0)
```

    That looks interesting. It’s a set of things I need, but not quite ready for us. However, it has some structure that I can use.

    Did It Help?

    Yes and no.

    There is code I can use. The problem for me is that I need to read through and copy/paste a bunch of things together. I also need to then decide what my test includes, and write more code that might make this work for my particular system.

    Supposedly we’ll get CoPilot for our internal systems at some point, where there isn’t a danger of feedback being sent to the public model. I think this could learn to be better and save me coding time if it could see my schema and use that to inform and guide the code it returned for me.

    Right now it’s not very helpful, and certainly at this time, it seems like it slows me down. However, maybe some of that is me learning to use it more and some is the model getting smarter (or more tailored).

    Still, an interesting experiment.

  • ChatGpt Experiments–Getting Documentation

    I get asked questions by salespeople all the time. Usually this is because a customer is struggling and I try to educate the salesperson as well as give them something to send to the customer. Here’s a place I used ChatGPT to make this quicker, or at least try.

    This is part of a series of experiments with the ChatGPT and other AI systems.

    Asking a Question

    A customer asked about configuring SQL Backup Pro to use Amazon S3 for backup locations. Rather than search out the docs, find URLs, etc. I asked ChatGPT. Here’s what I got:

    Prompt: How do I configure SQL Backup Pro to send files to Amazon s3?

    Answer:

    To configure SQL Backup Pro to send backup files to Amazon S3, you need to set up an Amazon S3 storage account and configure SQL Backup Pro to use it as the destination for backup files. Here are the general steps to configure this integration:

    1. Create an Amazon S3 bucket: Log in to your Amazon Web Services (AWS) account and create an S3 bucket where you want to store the SQL Server backup files. Take note of the bucket name and the AWS access credentials.

    2. Install SQL Backup Pro: If you haven’t already, download and install SQL Backup Pro from Redgate Software. Ensure that it is properly installed and licensed.

    3. Open SQL Backup Pro: Launch SQL Backup Pro and open the SQL Server instance you want to configure for backups.

    4. Configure backup settings: In SQL Backup Pro, navigate to the Backup tab and configure the backup settings according to your requirements, such as database selection, compression level, and encryption settings.

    5. Configure backup destination: In the backup settings, choose the backup destination as “File (network or local drive)”.

    6. Set the backup path: Specify a local folder where SQL Backup Pro will temporarily store the backup files before transferring them to Amazon S3. This can be any local folder accessible by the SQL Server service account.

    7. Configure transfer settings: Under the “Backup files to the network or local folder” section, select the option “After the backup completes, copy files to another location or device”.

    8. Choose the transfer type: In the transfer settings, select the transfer type as “Amazon S3”.

    9. Enter Amazon S3 credentials: Provide the AWS access credentials (Access Key ID and Secret Access Key) associated with your Amazon S3 bucket. These credentials authenticate SQL Backup Pro to access and upload files to your S3 bucket.

    10. Specify the S3 bucket details: Enter the bucket name and the desired S3 region where the backup files should be uploaded.

    11. Set other transfer options: Configure additional transfer options, such as file naming, retention policies, and logging preferences.

    12. Save and test the configuration: Save the settings and perform a test backup to verify that SQL Backup Pro can successfully transfer the backup files to Amazon S3.

    Once the configuration is complete, SQL Backup Pro will automatically transfer the backup files to the specified Amazon S3 bucket after each backup operation.

    Note: The specific steps and options may vary slightly depending on the version of SQL Backup Pro you are using. It’s recommended to consult the product documentation or contact Redgate support for detailed instructions based on your specific version and environment.

    Did it Work?

    While this was generating, I actually looked for docs to evaluate whether this was good advice. I don’t completely trust the AIs yet. What I found was:

    The instructions from ChatGPT don’t reference specific URLs, which I think is something that I would hope for. If I were sending instructions to a client, they might figure things out from the ChatGPT answer, but they’d be annoyed.

    A good example of where domain knowledge is needed, and still some work. This might be helpful if I had links it the answer to quickly check things.