Voice of the DBA

Tag: AI

Assistants in the Age of AI

When I started working for a living, there were secretaries in many organizations. These were people who actually did a lot of correspondence (written or verbal) and busy work for managers or executives. Over time, as email and computers became commonplace on desks, I saw fewer of these positions. As more people started to send email, we had to actually alter software to allow assistants to impersonate their bosses and manage the volume of communications that many of us deal with.

We’re in a new age of assistants with the emergence of Generative AIs powered by LLMs that can appear to respond in a conversational style to requests and perform actions on our behalf. In this new era, will AIs function as old-style secretaries, handling simple, but important tasks? Are they the trusted helpers that secretaries used to be for many executives? Are we all going to have an assistant, and do we want one, or need one?

There was a post on the role of AIs in this new world, and their ability to not only be a cheap, reliable assistant for many of us, but also a powerful tool for those that still have personal assistants helping them manage their workload. However, it’s not a tool that takes the place of a secretary, for many reasons mentioned in the post. It’s just a tool that can help manage some work, but isn’t really intelligent, empathetic, or able to discern subtleties that come from the context of the humans involved in a situation.

In many ways, that’s what I see for Copilot-like AIs used by technical people. They are assistants, and they can help with tasks, but with general, tedious, common tasks. They are a better search engine, and they can handle small tasks, but they aren’t replacing talented people, and they certainly don’t always understand enough of the context of a particular situation. If a general or common solution works, AIs are good, but in terms of being efficient and optimal when solving subtle, complex problems, we still need a human to guide the AI and assess whether the response is appropriate.

I am both enamored by AI, but also very skeptical that the technology will do more than provide faster searching for information and light guidance of options. Perhaps I’ll be proven wrong, but I think that continuing to improve your own judgment while learning through experience will ensure that you not only are more valuable than an AI, but that you can use one effectively as a tool. A useful assistant, but one that you know to overrule when appropriate.

Steve Jones

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

March 6, 2024
Using AI for Security

AI (Artificial Intelligence) systems and technology has been all over our industry for the past year or so, ever since ChatGPT released the initial public version in late 2022. It seems that there is a lot of hype around the possibilities, with plenty of excitement and skepticism, depending on who is talking about the tech. However, there do seem to be some places where the technology is working well, and security is one of them.

There is an article about how Microsoft is using AI to help spot ransomware, which seemed to have run rampant a few years ago. It’s still around, though it seems fewer exploits are being publicized. That might be because systems are better protected, perhaps there are fewer attacks (unlikely), or maybe more organizations are getting better at covering up their issues. They might be better prepared to restore backups or quicker to pay a ransom.

In any case, Microsoft is exploring machine learning (ML, a subset of AI) to detect patterns and behaviors that can indicate a ransomware campaign is starting on a system. Looking through logs of activity for unusual behavior is something ML might be much better at, or faster at, than humans.

I certainly know that if I were running queries that might look at my activity on systems, taking a guess about whether or not the activity this week is “regular” and matches patterns from last week is hard. Often exact matches of activity patterns cause lots of false positives if they are too tightly written. If we loosen the parameters too much, we miss potential attacks. A fuzzy view of the pattern is needed, something ML is good at detecting. After all, we need to look at all activity from all users, and determine if Steve’s activity this week is different than last week, and at the same time, is Grant’s activity unusual and a sign that his account is compromised?

Some humans are very good at spotting patterns in activity, but only at a limited scale. We get tired, our minds wander, and we can’t only focus on looking for patterns in log files. We’ll get bored, distracted, and start to make mistakes. AIs don’t get tired, and while they might miss some anomalous activity, and certainly will report plenty of false positives, humans can focus on this subset of reports and perhaps partner with AIs to do a better job helping secure our systems.

I lean towards the idea that AI technology will help us better spot malicious activity in the tremendous amount of data we capture about our networked systems when humans are attempting to hack us. What I’m not sure about is how well criminal actors will use AI tech to further disguise their activity. I can certainly see a future where lots of AI bots battle each other at blinding speed while humans watch and hope the defenders manage to outwit their attacking AI opponents.

Steve Jones

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

March 1, 2024
Ransomware vs. AI

Ransomware has been a growing and shrinking problem in the modern world. Every time I think that some new defenses and protections are preventing ransomware from being a problem, I see another issue. Recently, I saw Subway got hit with with an attack and a few friends have recently noted their companies were restoring systems after a portion of their network was locked down.

With the advent of Rasnsomware-as-a-service, where criminals deploy software and then sell access to others, better detection and protection become more important. As with any software, criminal human operators will use the ransomware software in different ways. That means that we don’t necessarily have a simple threat that can be easily programmed against with anti-virus technology.

Microsoft has been using AI technology to help them track and combat ransomware campaigns. Since there are similarities between how ransomware is used by different individuals and how it appears in systems, AI technology can be helpful here. There aren’t the same simple signatures on files that we’ve seen in the past with viruses, but rather more complex patterns. Humans might discover how ransomware appears in their environment with lots of knowledge on what their network ought to look like, but this pattern matching across many different networks and organizations is something that AI/ML might do quicker and at scale. Once successful, ransomware can be hard to recover from, so early detection is important.

In the article, it seems that Microsoft is capturing lots of traffic and analyzing it for patterns, with multiple types of anomalous activity, and then aggregating this across devices to guess whether this is an attack or not. In some of their testing, they find the ability to stop an attack with only a few percent of assets getting encrypted. That’s not perfect, but better than finding 90% of your nodes are encrypted over morning coffee.

I suspect this is just the latest escalation in cyber attacks and defenses. I’m sure that hackers will come up with new and novel ways to cause problems, but I do think that this is a place where AI, especially ML, technology can be useful to provide better security. I also think that database technology, especially graph queries, is particularly helpful here. I hope we learn more about how they are building protections as I think this is likely a great data analysis story.

Once again, the database is critical to making software better because all of that data has to be stored and queried somewhere.

Steve Jones

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

February 7, 2024
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