A New Word: Candling

Posted on January 12, 2024 by way0utwest

candling – v. intr. the habit of taking stock of your life on the occasion of your birthday, letting it serve as a kind of internal referendum on all your goals and qualities and relationships and accomplishments so far – which makes you want to dress a little nicer that day, as if you’re standing before a parole board that convenes once a year to adjudicate your release from childhood.

Such an interesting and contradictory definition, at least to me. I like the first part, taking stock, but it’s not as though I’m being judged by others. Of course, this is a dictionary of obscure sorrows.

Maybe even the “dress nicer” part makes sense to me, as that’s sorrow for me. I like to dress like a bum, or wear wild shirts, and just be comfortable. One of my bucket list items used to be to not wear ties anymore. However, as I age, and I see funerals, I accept it’s important to others I do so at times.

Enough sorry.

I used to do this, even in my 20s. I didn’t love attention or celebrations of me, especially on my birthday. They’ve always made me a little uncomfortable, which sounds funny coming from someone that does enjoy presenting and teaching people from a stage. However when I do that, I think it’s about others, and what I can do to help them.

Many birthdays I went off by myself, at least for part of a day. Biking or on a kayak/sailboard on the water earlier in life, hiking or skiing later, to take stock of where I was and what I wanted to do in life. What direction? I didn’t do a life quest, but I did try to think about the direction in which I wanted to move.

After getting married, I often used my birthday as a marker to decide if I was happy with my career and if I’d want to do something else. That’s helped me to decide when to continue on with a position and when to move.

I still do a bit of that, but my candling has become a more regular thing, often when I hear of someone having success in some way, or making a change, or sadly, when someone has passed. And I know often do so with my wife, discussing where I am and what life would be like if I changed something.

From the Dictionary of Obscure Sorrows

Posted in Blog | Tagged , | Comments Off on A New Word: Candling

Quick AI Help from Bing

Posted on January 10, 2024 by way0utwest

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.

2023-12-21 11_52_48-Microsoft Edge _ What's New and 23 more pages - Personal - Microsoft​ Edge

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.

2023-12-21 11_57_02-Download Sample CSV Files for free - Datablist and 27 more pages - Personal - Mi

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.

2023-12-21 11_58_43-sample large csv - Google Search

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.

2023-12-21 11_36_40-Microsoft Edge _ What's New and 23 more pages - Personal - Microsoft​ Edge

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.

2023-12-22 14_09_11-Copilot and 27 more pages - Personal - Microsoft​ Edge

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:

2024-01-05 17_58_00-the view from the South Island of New Zealand, looking South to Antarctica with

2024-01-05 17_57_53-the view from the South Island of New Zealand, looking South to Antarctica with

2024-01-05 17_57_44-the view from the South Island of New Zealand, looking South to Antarctica with

2024-01-05 17_57_35-the view from the South Island of New Zealand, looking South to Antarctica with

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.

2024-01-05 17_59_35-Photo - Google Photos

Posted in Blog | Tagged | Comments Off on Quick AI Help from Bing

Re-Evaluating the Cloud

Posted on January 10, 2024 by way0utwest

Last year 37 Signals (makers of Basecamp and Hey)  announced they were leaving the cloud. I wrote about the decision, and wondered if they’d look back at this as a great decision or one they’d regret and backtrack to the cloud again. They planned to build their own tooling, buy a bunch of servers, and run their own data center (or rather, rent space in someone’s data center).

Recently there was an update, in an FAQ, about how the transition has gone. In short, very well. One of the founders, David Heinemeier Hansson answered several questions about the move and the financial status. They didn’t hire more people, so their payroll is the same. They used a service to unpack and rack their servers, so they could just connect to them remotely and not deal with hardware. They built their own redundancy across two data centers where they rent space, and they think they will save well over $7 million in the next 5 years. They had a $3.2million cloud budget per year, so that appears to be halved (3×5=15 – 7 = 8ish) with their move.

There are some answers to various questions, which are likely of interest to many data professionals who might feel pressure to move to the cloud. First, they discuss resource optimization in the cloud. I think that’s really hard, to do well and if they gave the details of what they’d done, that might help others decide if 37 Signals did a good job, or if there are things others could do. I think many people might struggle to optimize their usage, especially if you are only responsible for one thing, like the database. In many orgs, once something gets deployed, or developers build a PoC using some cool, new service, it’s hard to get rid of it or even change how it’s used. I think 37 Signals has some agility here that many orgs struggle to implement.

Rewriting things are cloud-native is the way to go, in my mind, and I do have customers and clients who see applications working better in the cloud. However, I might argue that rewriting applications is hard and expensive, and many companies aren’t great at writing software that’s efficient, so rewrites are hard. I also think in most organizations, developers struggle to understand what cloud-native means. I think DHH is a little off here, as their developers probably could make better software written for the cloud, but they didn’t want to spend the time and/or money.

The points about security and reliability are fair, but I think those are a function of having good people implementing systems. You can get good security and reliability in the cloud or poor implementations of either. The thing about security is that the overall protections that detect some of the DDOS or attacks are better with Azure/AWS, but simple SQL injection or open firewalls are still a problem. Authentication and security are just hard for most people and often people do this poorly in the cloud. To be fair, most people don’t do this well on-premises, so at least in the cloud, there are some services/scanners/checks that let you know when you’ve messed up. Whether you change or implement their suggestions is another whole debate.

Is reliability better? AGs are hard in SQL Server, as are distributed clusters. The cloud services do this better for most people, but maybe not for you. That brings me to his super engineer point. He doesn’t think he has super engineers on staff, but I disagree. I think they can pick good people, especially because of their profile, and they don’t need a lot of people, so their average engineer is likely better than the average engineer at most organizations. They built some great software, and they’ve built an amazing framework and tooling to deploy their software. Don’t tell me that’s even close to the capabilities of the staff at many organizations. We have really good engineers at Redgate, and I think we’re above average as well. I don’t know how we compare to them, but we haven’t had people publicly write and release code that thousands of other developers use. At least not at quite the same high profile level.

The cloud has its place. It can work well and it can be very expensive. This journey is worth sharing with your management if they want to move to the cloud, especially if they want to lift and shift. That might create some flexibility and CapEx/OpEx changes that are worth it, but you ought to debate and question whether that’s reality or marketing hype from a vendor. After all, it’s not clear if a cloud move is really something that returns an ROI or one that reduces your profitability.

Steve Jones

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

Posted in Editorial | Tagged , | 1 Comment

T-SQL Tuesday #170–Abandoned Projects

Posted on January 9, 2024 by way0utwest

It’s the first T-SQL Tuesday of the new year. As we move forward, this month’s invitation is neat in that it’s looking back to learn how to move forward. I think while many people do look back, they don’t always use that information to move forward. This month’s host, Reitse Eskens, asks us about abandoned projects.

As always, if you’re interested in hosting, blog, and then send me a note about hosting. I still have space in 2024.

Abandoning a Project

I’m sure that many of my employers started something and then abandoned it, and there are no shortage of projects I’ve abandoned myself, but nothing big springs to mind. Mostly I find my employers muddle through very poorly written, poorly performing projects and are very hesitant to abandon them.

However, I will say that I have a couple things on my mind personally. This past December I completed the Advent of Cyber, which I wrote a bit about.  I actually got a certificate (of sorts).

2023-12-28 16_08_10-Window

That wasn’t abandoned, and I was glad I went through this, learning about a few tools.

However.

In the past, I’ve worked on the Advent of Code, and I’ve never finished it. I even built a repo, where I separated out puzzles by year. I invariably get a week in and then don’t have the time, and I struggle with coding out the puzzles. They get hard, and since this is a spare time thing, I run out of energy and interest.

I do think, however, I learn some things. A few years I’ve tried to solve puzzles in PowerShell, Python, and SQL, which makes me translate logic, and think about the issues. SQL is hard, as a number of the puzzles are really iterative, and SQL isn’t great at iteration.

I do learn.

Even when I abandon projects, I get better at writing code and solving problems. It requires me to think, and it also teaches me things about how to look at a requirement and then write code. I also love the skill of building in tests to verify my code from the samples, which is a good skill.

It’s an abandoned projects, and even as I write this, I feel like I should work on the 2023 puzzles a bit. I probably won’t because of time, but I do think starting and abandoning this project teaches you something.

Posted in Blog | Tagged , | 1 Comment