Voice of the DBA

Tag: T-SQL Tuesday

Moving T-SQL Tuesday to Jekyll

I got a message a few months back that Microsoft was deprecating the MySQL server version that I was using in Azure. The cost was going up, and while I don’t mind donating the $12-15/month to the site, I didn’t want this to be really expensive.

It’s also a perfect fit for a static site as the content rarely changes and doesn’t really need to have database access to server pages. Jekyll, which powers a number of others sites (SQL Saturday, SQL Memorial) seemed like a good fit.

This covers a few of the things I did to move the site, which was surprisingly easier than I expected.

Exporting the Data

There is a plugin on the WordPress marketplace that you can add to your site to export your data as markdown for jekyll. I tried to add it to the live site, but the plugin didn’t want to activate. My PHP was old.

I could upgrade PHP, but I might break something, and ultimately, I don’t need another job.

Instead I decided to move the site local. That’s easy with a container, and I found this post on running WP in a container. In a couple of minutes after getting the Docker image downloaded, I had a new WP site running on my laptop. With the latest PHP and WP versions.

While the image was downloaded, I used the main WP export to download a copy of data.

The next step was to import this locally. Since I had a brand new WP site in a container, the easiest thing is an import, rather than a database restore. I’m a database pro, but I prefer simple and efficient processes.

I picked the export file and let it run.

Once I ran the import, I could see the same site in my local container as I see at tsqltuesday.com. Now I need a plugin. When I searched around, the Jekyll exporter was recommended, but I couldn’t install this on older version or WP.

However, in my container, this worked fine.

Once the plugin was installed. I could see it in my list.

I also had a new menu item under my Tools, which was export to Jekyll. Once

Once I picked this, I got a file to download (from the container to my local drive), and all of my files were zipped up.

Inside of this file, I had a bunch of folders, which were structured for Jekyll. The _posts is the main folder where the various posts from your WP site are stored. As you see here, all the files are .md, which are markdown files. The _posts are the same format.

Now that we have a set of Jekyll compatible files, let’s get this moving to GitHub.

Loading into Git

The first step was to create a repository on GitHub. I’m moving this to the SQL Saturday organization, so it can be a part of the charitable foundation (and I can hand it to someone at some point).

I created the repo and then downloaded an empty repo to my local drive. If you want to help improve the site or change things, fork this repo and then you can have your own URL.

I then copied my exported structure into this folder, but I had some issues rendering with the default GitHub themes. So I deleted everything and then grabbed a fork of Beautiful Jekyll, which is the same theme running the SQL Saturday site. Essentially I copied all of the Beautiful Jekyll files to this local repo, then copied the T-SQL Tuesday export on top of those files and committed the files.

In GitHub, I also added the automation for Actions by grabbing the workflows folder and added that. In the pages setup, I then enabled pages, which gave me a .github.io URL, which is fine for testing.

This worked, as once I committed code, I saw a build.

And it worked well.

Sort of. The theme stuff didn’t quite work well, so I ended up copying over the SQL Sat site with the Beautiful Jekyll theme and then deleted posts and added back the T-SQL code.

I had some work to get the site working well, and when I did, I moved it. I created an official repo here where people can send PRs if needed: https://github.com/sqlsaturday/tsqltuesday

Once I had things here, I had a little DNS work to do for the custom domain, but T-SQL Tuesday was again live at https://tsqltuesday.com/

And with an SSL cert, thanks to GitHub. However, I wasn’t done.

Data Cleanup

While I was hoping the plugin would convert things easily, I realized quickly there were a bunch of problems. Not a crazy number, but some.

The data cleanup was a manual process. I could perhaps have found a way to parse and automate this, but with 174 invites, this just wasn’t enough work for me to spend time building and testing something. Plus, I needed to get this done before the next month.

First, I had a bunch of posts converted with long WordPress like URLs. That’s fine, but I had inconsistency as some posts weren’t linked in this way. I also had some links that were absolute, using the https://tsqltuesday.azurewebsites.net/ URL as a base.

I decided to simplify things.

First, I added a permalink to each page, similar to what you see in this one: https://raw.githubusercontent.com/sqlsaturday/tsqltuesday/refs/heads/main/_posts/2009-12-01-t-sql-tuesday-001-datetime-tricks.md

I set this to be /xxx, where xxx is the invite number. While this might be a problem if we get to 1000, I certainly won’t be dealing with that one.

Next, some links were absolute and pointing to the old site, which was on Azure. I changed these, mostly in the Host Index, to relative links using a simple formula for the posts, which was a /xxx, where xxx is the invite number. This is a simple, clean way of managing the links.

Lastly, I had issues with some individual posts, where I’d see things like this:

That HTML wasn’t rendering inside the markdown correctly, so I had to delete these items. And, of course, the end tags.

I also had issues where people would do something like Steve (twitter | blog) and the pipe character was seen as a table indicator. I had to change those to /.

Savings

The old costs, which usually came out of my MVP subscription, were about $12/mo for the db and $9 for the site. The requirement to move to a Flex server increased the db to about $23/mo.

The new Github site costs $0. Perfect for a charitable endeavor.

October 7, 2024
T-SQL Tuesday #178–A Recent Technical Problem

This month we have a good invitation from Deepthi Goguri, where she asks us about a technical problem. I think most of are technical people and we solve problems regularly. This is the same type of thing I ask people to blog on regularly, documenting their career.

It’s a great way to prepare for future interviews as well.

If you don’t blog, this is a good chance to start. Go to WordPress.com and get a free one. Then post on this topic.

If you want to host, ping me.

A Recent Technical Issue

My blog is full of stuff I run into, or my customers run into. I try to post short pieces on the things I’ve been dealing with. Quite a few of my posts are on Redgate products, sometimes me digging into a technical problem and finding a way to accomplish the requirement.

One of the more interesting ones recently was getting my Docker Desktop setup on a new machine. While installed the system was easy, I had to dig around and research a bit to try and understand how to use volumes. The documentation can be sparse, and it took some experimenting to decide that mapping specific folders in my compose file rather than trying to create permanent volumes was a better solution.

I also had to experiment a bit with a few settings and paths to decide that I needed a specific place on my disk to hold all my data, which makes it easy to manage over time. I also had to make the decision on naming the containers. It’s easy to lose track of ports, and it’s sometimes hard to remember how to get the port, so to keep it simple, I used naming that has the port name in the container name.

I wrote about the final process, which you can check out if you want to learn more.

September 10, 2024
T-SQL Tuesday #177–Keeping track of database code

This month’s invitation is from Mala Mahadevan, who has hosted 5 times. This latest one is one that is near and dear to my heart as I use source control most days and I think it’s important for database code.

I’ll explain why below, but I’m glad to see this as a topic. We have a wide variety of technical topics being proposed, but not a lot about software engineering as a discipline, which is part of what version control is. Not building features, but better managing your process.

If you want to host a T-SQL Tuesday, ping me.

Capturing Database Code

There are many ways to capture code, but I work for Redgate Software, so I use Flyway. Since I work there, I get a paid version, but I work with customers all the time and see a variety of things. If I didn’t have Redgate tools, I’d likely use something like SQL Compare to capture off database code, since that’s easy. Or SMO through SSMS.

As for how I work with code, I use Git to store the code. Git is ubiquitous and I rarely find customers without Git. Sometimes the data teams aren’t using it or don’t know how, but that’s why I’ve written some Git articles on getting started.

I also try to work in branches, with a protected main branch. This means no one can commit code to main, but rather need to commit it elsewhere and use a PR (pull request) to move the code into main. I do this with a lot of customers, helping them understand how to use version control to manage their code.

For my git work, I primarily work in GitHub in public repos. I’m at https://github.com/way0utwest, where I keep a lot of sample projects for things I work on with customers to demonstrate how to use Redgate tools, or just manage code better.

There are lots of ways to capture code, format it, and deploy it. However, you should use git and learn to manage your code within a team. I’d also suggest you use Flyway to deploy the code. There is an OSS version, and because it supports many platforms, if your company adopts PostgreSQL or DataBricks, you can still use a similar process to deploy code. Learn it and use it.

But first, get code into a Git repo.

August 13, 2024
T-SQL Tuesday #176: One piece of advice for Past Steve

I almost missed this month, so this is also a good #SQLNewBlogger post. I thought about it for a few minutes as I ate breakfast at my desk and then knocked this out.

This is the monthly T-SQL Tuesday blog party. I manage the site at tsqltuesday.com, trying to keep the party going. I have a lot of help from hosts each month running the topic, and I appreciate their efforts. Join in an write, and then host a month. Lots of people have done it.

Past Advice

I struggle with this, as I’ve had a great life. I wouldn’t change anything, given where I am today, 33 years after my first data job. However, knowing I’m not changing my life in some time travel way, this is something I wish I’d have known about in my early 20s.

Network and help others in the community.

I’ve done this lightly in my first few jobs, but mostly within organizations. As I’ve grown and changed jobs, I’ve seen tremendous power in people getting together to talk, to get to know each other, to share problems and solutions, to present their knowledge and learnings with others.

User groups were a core part of this, and out of them grew the PASS organization and Summit, the SQL Saturdays, and the amazing community we have. It’ s far different than other communities, and many of them see it as well. They wish their world was like the data platform world.

The power of networking is amazing. The rich world that comes from community is something special.

It’s still there today and it’s worth joining, no matter where you are in your career.

July 9, 2024