Voice of the DBA

Tag: AI

Quick AI Help from Bing

I needed to run a few commands recently and I decided to use the Bing Copilot AI to help me. Here are a few examples of what happened.

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

Finding Sample Data

I was trying to replicate an issue from a client and needed to bcp in a lot of data. First, I asked about data sets. I got this response.

This looks good, but if I click on any of these three links, I don’t get datasets. The first two go to questions on importing datasets, but not links to data. The third goes to an aritcle on importing data.

Is that better than asking this question of a someone who might not be paying and gives me these links? Maybe.

Rephrasings was better, as I clicked the first link and was able to get a site with data.

Both of these were less helpful than a Google search for “sample large csv”. The NZ site below had some very large 6mm plus rows.

Note for the record, I went back and tried this phrase in Bing and got the same datablist site above.

Creating a bcp Command

Next I needed the bcp to import a file. I used this prompt, and this worked well.

The only change I needed was to change the path, since my file was on d:. My account defaulted to the right db, so this worked.

Removing ScheduleID from an Agent Job

One of the things I was discussing with a friend was the way SSMS scripts jobs. If I alter a job on my dev instance and script it, the jobid and the scheduleid are included. Since the creation of the returns a different GUID for each, I don’t want to use these if I update jobs. I just want the name.

I asked Copilot to help.

If I follow this advice, then I would first script out the job from SSMS. In my case, I’ll grab a sample backup job and get this code:

USE [msdb]
GO

/****** Object: Job [Daily Backup]    Script Date: 12/22/2023 2:11:43 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]    Script Date: 12/22/2023 2:11:44 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N’Daily Backup’,
         @enabled=1,
         @notify_level_eventlog=0,
         @notify_level_email=0,
         @notify_level_netsend=0,
         @notify_level_page=0,
         @delete_level=0,
         @description=N’No description available.’,
         @category_name=N'[Uncategorized (Local)]’,
         @owner_login_name=N’ARISTOTLE\Steve’, @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Ola Backup User DB]    Script Date: 12/22/2023 2:11:44 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Ola Backup User DB’,
         @step_id=1,
         @cmdexec_success_code=0,
         @on_success_action=1,
         @on_success_step_id=0,
         @on_fail_action=2,
         @on_fail_step_id=0,
         @retry_attempts=0,
         @retry_interval=0,
         @os_run_priority=0, @subsystem=N’TSQL’,
         @command=N’EXECUTE dbo.DatabaseBackup
@Databases = ”USER_DATABASES”,
@Directory = ”C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup”,
@BackupType = ”FULL”,
@Verify = ”Y”,
@Compress = ”Y”,
@CheckSum = ”Y”,
@CleanupTime = 24′,
         @database_name=N’master’,
         @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Daily 0200′,
         @enabled=1,
         @freq_type=4,
         @freq_interval=1,
         @freq_subday_type=1,
         @freq_subday_interval=0,
         @freq_relative_interval=0,
         @freq_recurrence_factor=0,
         @active_start_date=20230103,
         @active_end_date=99991231,
         @active_start_time=20000,
         @active_end_time=235959,
         @schedule_uid=N’03d00e19-863e-497f-94a6-42a244f219a2′
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Now, if I delete the line with EXEC @ReturnCode, I get this script:

USE [msdb]
GO

/****** Object: Job [Daily Backup]    Script Date: 12/22/2023 2:11:43 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]    Script Date: 12/22/2023 2:11:44 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Ola Backup User DB]    Script Date: 12/22/2023 2:11:44 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Ola Backup User DB’,
         @step_id=1,
         @cmdexec_success_code=0,
         @on_success_action=1,
         @on_success_step_id=0,
         @on_fail_action=2,
         @on_fail_step_id=0,
         @retry_attempts=0,
         @retry_interval=0,
         @os_run_priority=0, @subsystem=N’TSQL’,
         @command=N’EXECUTE dbo.DatabaseBackup
@Databases = ”USER_DATABASES”,
@Directory = ”C:\Program Files\Microsoft SQL Server\MSSQL16.SQL2022\MSSQL\Backup”,
@BackupType = ”FULL”,
@Verify = ”Y”,
@Compress = ”Y”,
@CheckSum = ”Y”,
@CleanupTime = 24′,
         @database_name=N’master’,
         @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

I don’t think that will work.

Perhaps I didn’t describe this well. I’ll keep working on my prompting.

A Little Fun

I decided to try Dall-E for an image. I asked this: the view from the South Island of New Zealand, looking South to Antarctica with a sailing vessel moving away from you. I got these:

The sailing vessels in a few of these are weird, but none are right. From the South Island, there’s nothing for hundreds (thousands?) of miles. You can’t see Antarctica. I’ve been to NZ and it’s a great view, but of ocean. And family if they get in the way.

January 10, 2024
Has AI Changed Software Development?

I saw a piece that asked the question about AI and software development, specifically ChatGPT, a year after its release. Has software development changed? It’s a question I’ve been trying to ask for myself, though I do think the way AI works for lots of code is fundamentally different than how it works for databases. Databases need a lot of context, with schema and relationships, whereas a lot of other code can be structured in a more narrow sense, focused on an algorithm rather than specifically needing to reference other objects.

That’s not to say that a generative AI can write application code better than database code. I think in both cases it can be helpful or hurtful. As I read the article, I find myself agreeing that AI has changed software development. It’s a tool, and it can be useful. It can help structure code, point out issues, and help with building tests.

That being said, I also agree with this sentence in the article: “…given that to write anything resembling maintainable code with AI, you’ll need some level of expertise in programming.” Everything I’ve seen from AIs still requires a review from a human that understands if the AI is on the right track in approaching the problem. I’ve written this a few times (and discussed it with others) that I think an AI produces junior level code in many cases, and it takes a subject matter expert to decide if that’s OK for the situation.

However, I feel that AI does a few things really well. First, it’s a great search engine, able to understand multiple prompts in a row that narrow in the thing I might spend much more time searching for. It also does some nice predictive things in guessing what code is needed next. In most application languages, this is helpful. In SQL, not so much, since the SQL language lays code out in a way that makes it harder to predict things. The LINQ structure of from, where, select is much more cleaner in many ways. If only RDBMSes natively executed LINQ…

If you’ve tried AI, what has worked, what prompts give you help? Is there anything especially time-saving you’ve found? I, and many others, are looking to understand where it can help us.

Steve Jones

January 6, 2024
AI In the Nov 2023 Enterprise

AI is everywhere. I can’t seem to get away from stories on the technology in 2023, and while I don’t know that I’ve found it that helpful, I keep looking at it because it’s becoming a pervasive technology that most enterprises will experiment with in some way. There is a look at generative AI in the Enterprise in the O’Reilly Radar, which tracks how technology is changing and influencing the world.

The report talks about most of the respondents to a survey using AI, which makes sense as the people responding likely have some interest in the technology. I always take these trends with a grain of salt as people who are busy and not interested might not respond. Only a small percentage of uninterested people will actually answer these things.

As I look at the numbers with that in mind, I find it interesting that about half of the users think AI will lead to greater productivity and a small number (4%) think this means less headcount. However, only a minority (41%) have been using this for over a year. That likely means in most cases that people are experimenting like I am. This isn’t a pervasive technology in the enterprise, though I’d argue that while DevOps might be in use in most enterprises, I wonder if it’s in use in most projects at most enterprises. Culture change is hard for most people and I still meet lots of people who aren’t trying to get better at building software.

The big challenge in many companies is finding appropriate business cases. I think that’s my struggle as well, in trying to think about how to use AI to write or build code, I struggle to think of how to prompt or what to prompt. Often by the time I define the problem, I can just write the code. If I were scaffolding out basic classes or tables, maybe I’d feel differently, but as our CTO put it, we spend most of our time figuring out the problem, not writing the code. There are legal concerns, but those are from a minority of respondents. I have a meeting with our legal department soon, which will help me iron out some of my concerns and get guidance.

Interestingly, 77% of people are using AI in programming, with about half of those using it for work. I don’t know if we’ve done a good job understanding the IP/copyright issues here, so that’s surprising. I would guess in many organizations that don’t sell software, they don’t care about this at all. If they get code from an AI that was copied from somewhere else, if it works, who cares?

I find that much of the code generated isn’t great. It’s junior developer level, which might not matter to many organizations. After all, they employ junior developers, and some employ senior developers with 6 months of experience 10-20 times over who write that level of code. If the AI does it faster, all the better.

I think AI is a technology that is going to impact our lives as technical and data professionals. Whether you use it to write code or use it as a glorified search engine, it’s a tool that you want to understand and develop some skill with. Writing prompts and learning how to navigate an AI system is helpful. If it actually gives you something useful, even better. And if you learn more about building models and prompt engineering, you might find yourself with some interested opportunities in the future as I expect those jobs to grow in number across the next few years.

Steve Jones

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

December 20, 2023
Data Security and AI

I ran across an article on the best AI meeting assistants, which can help you with note-taking and summarization tasks. Most of these cost money, but there was something that struck me about these tools.

They aren’t allowed for a lot of my meetings.

At Redgate, I’m not sure if these are allowed. Our policy is to seek permission before using a tool, so I’ve sent a query off for more information. In many of my customer meetings or in Microsoft meetings, I’ve been told these tools aren’t allowed as there isn’t sufficient data security to allow their usage.

I think a lot of people are concerned about their data when these AI LLMs (large language models) are involved and if their data is being used to train future models. If it is, then that could be a security problem. Already many people and organizations are upset about their data sets being used for training without their knowledge. Not everyone agrees.

I don’t quite know how I feel about this issue, but I do think there ought to be guidelines and discussions about how data is used, and where data is stored (or where it transits a network). Certainly, organizations might need to set this in place to ensure their employees aren’t exposing confidential or internal data in ways that could increase their risk of a breach. After all, without any guidelines or rules, you may have no recourse if employees just use tools and data escapes your network.

At Redgate, we’ve begun experimenting with SQL Prompt and AI. As of now, we’ve had quite a few queries about how customer data is being used. Our EAP EULA notes that schema information will leave your network, but not data in rows. That might be a problem or might not, but it’s important to disclose that, which we’ve done.

I’d like to think that as hardware power increases and models become more mature, we’ll stop having to send data for many AI-related tasks away from a device that might perform a task. At some point, I hope your mobile phone or laptop can process the AI model without a network connection, which would be helpful for data security.

AI is here to stay, and we have to learn how to work with it, and where it works well. It feels a little like we haven’t really answered many of these questions as an industry or inside our organizations. That’s something that needs to change and quickly as the number of places AI is being used continues to rapidly increase.

Steve Jones

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

November 27, 2023