Voice of the DBA

Tag: software development

Building a SQL Saturday Archive

Some time ago I started gathering the SQL Saturday XML files. I started to parse and work with these, but the data is a mess and it never was that important.

I regret that now.

In any case, with the announcement of insolvency from PASS recently, I asked people to save some data. One of those that responded was Ben Weissman (b|t), and he actually rendered out all the available schedules as PDFs (using VB, of course). He sent them to me and I decided to load them up.

Tl;dr; You can see my page here: https://dataplatformdaysweb.azurewebsites.net/

I had started an initial project for replacing SQL Saturday, but hadn’t planned on anything more than a static site. Actually, I wanted a sortable grid, but that was beyond the time and web skills I had at this time. That’s still a goal. This is a quick look at how I built things.

I am not a real software developer, at least not on the current, interactive web. This stuff likely makes sense to any junior web dev, but it was learning for me.

Azure DevOps and Dot Net Core

I wanted a simple site, but I wanted this built in Azure DevOps, so it has some control and tracking. I thought about a simple HTML site, but producing a build with that didn’t seem intuitive to me, so I fired up Visual Studio.

I chose a Dot Net Core ASP.NET REACT application, as I may move this to Linux. It’s cheaper

In any case, I took the defaults. No real reason other than I’ve tried MVC and that was hard, and lots of people seem to like react. I also have people I can bug at Redgate.

I got the default project to build locally. Then I changed the names of the pages and loaded this into an Azure DevOp repo. Once up there, I took a default build process.

I pointed this at my repo and then clicked Save and Queue… and it failed.

Changes to the Build

I got a message that the nuget restore wouldn’t work with dotnet core 3.1. I could fall back to 2.2, but when I did that, the project wouldn’t build locally.

I realized I’d initially selected a Windows VS-2016 hosted agent, but I had built the project on VS2019. I changed that to the Windows 2019 agent and it worked.

Deployment to Azure

I’d set up an Azure App Service already, and I created a very simple release step. I linked my artifact and selected a release to an Azure App Service Plan. I had to authorize my plan, but once I did that, I was able to select the App Service I’d set up. No configuration needed.

I clicked save, built a release, and ran. I found the default React Site at my URL.

Changes to the Project

I made a few changes to the project as well, to remove some of the defaults. First, I needed to load my PDFs into the project. I had originally created an Assets folder in the root of the project, but that did not get included in the artifact that was built.

Looking at the project, and searching around Google a bit, led me to see that the main page, index.html, was in the ClientApp/public folder. I moved my Assets folder below this, and then saw all my files included in the build artifact and deployed.

I also wanted to remove some of the default sample menu items. I found these in the ClientApp/src/components folder in the NavMenu.js. I deleted the two entries, leaving just a “home” there for now. I may do some other grouping later.

Building the Archive

This was the more interesting item for me. Ben had sent me a ZIP file with all the PDF files in it. I unzipped these and I saw this view:

Originally I thought a simple list of numbers and files would get me started, but there are hundreds of files. How can I do this?

My first thought as PowerShell can help. I popped this open and use Get-ChildItem to get a list of files and compile this into a variable. I have been wanting to use Azure Data Studio more for PoSh, and that’s where I did this.

This got me a basic HTML list of my files. I had trouble with the pathing, so rather than futz around and try to build production code here, I just used this and then a “search and replace” of the [a href=”] to add a [a href=”/Assets/PDF/”] got me the correct paths.

I need to learn how to properly get paths working here in PoSh, but this string manipulation wasn’t important for a one off task.

Once I had this, I had something. Of course, at this point, Ben sent me his index list of the event names, which was what I really wanted. I could have taken the source of his page and used search and replace to get the pathing, but I did something stupider.

In a hurry, I copied and pasted his list of events into SSMS in a new Query Window. One of the reasons I do this is that the cross line editing is superior (IMHO) to VS and VSCode. I’ll repeat the process with just a few lines here, but keep in mind I had like 800. This is a useful text trick as well for some data changing.

I had this list:

I wanted to make this a table, so I use the Select+Alt+Arrows to select the entire first column.

I then added my table HTML.

I could do this in VSCode, but the reason I like SSMS is that I can space over to the right and then get a vertical edit link, rather than a set of end-of-line cursors. I then can create another edit point and add other code, like this:

I wrapped this in the table beginning and ending and had my table.

What about the URLS? Well, I could easily add the paths, but then getting the individual file names was hard. Or was it?

I used the same trick. I pasted my list code into SSMS and selected all the file names:

I copied and pasted this set of vertical text into my table, and viola, I had a working table that looked, well, about as good as I could make it quickly.

More to come, as I try to archive and preserve the SQL Saturday data and history as best I can.

December 28, 2020
The Challenges of Splitting a Table

I ran across a discussion on Reddit about splitting a table. In this case, the original post had to do with a vertical partition of data, which is a technique that can help you better manage data in your database. However, I haven’t often seen this technique employed in the real world.

I wonder how many of you have considered a vertical partition when you are modeling data. Often we may not think about this early in the lifecycle of an entity, but as it grows, you might think about reducing the amount of data you often query in some way, and a vertical partition can help.

Is there some criteria that you might use in deciding this? Or how you can evaluate if there is a need? I once worked on a system with a very hot table, lots of queries, lots of updates against this table from our online system. In response to some requests, the developers wanted to add some columns to the table. This was important, and we needed to capture the data.

These were valid columns, but they were large in terms of data size, and not every one would always be used. This was before the option of sparse columns, so that wasn’t an option. I had no interest in an EAV table, despite the fact that it might have worked well at this scale. Instead, this was a situation where I thought a vertical partition would work. In fact, I thought a few of the other columns in this entity could be moved as well, as they were rarely queried and contained significant data.

We split the table, and performance actually improved for the main table, as it had less data. Just like an index, we had more rows on every page and less IO for range queries, and even key lookups for data that wasn’t already indexed.

There are lots of good techniques in database development for dealing with the challenges we face in data modelling and with performance. I’d urge you to learn about some of them and understand when they can be useful. I would also practice implementing them, making changes to existing tables, and learning how you can deploy them if the need arises.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

September 15, 2020
The Challenges of Resetting Databases

I was working on a demo recently where we had a database in version control and a development database. This was a team environment, with a few of us making changes and syncing them across our dev systems using git. We had some advanced technology with our dev environments in containers, Flywaydb, and GitHub. Once we had our scenarios working, someone wanted to reset our git repo and capture a new database image.

However, when we reset the repo back, we had some issues with the database. In this case, there were changes in the database that didn’t exist in the repo, giving us a mismatch. Not a big problem, but cleaning things out to get the db to match the repo, without putting those changes into the repo, was a challenge.

A developer I was working with got a little frustrated, because when working in C#, there is no state. If we reset the repo and sync our local copy, we have everything ready to go. However, a database repo isn’t the same because there is often a database that exists separately.

This is the main challenge when working with databases, relational or otherwise, in a development environment. Experiments, bug fixes, even testing data changes persist over time. Resetting data to repeat tests, or even automating tests, can be hard.

This is one reason I think containerization and subsetting of production datasets will become very important over time as we try to ensure we can react to business requirements and keep our teams coordinated. These technologies ensure we always have a known starting point for our databases. At least at any particular moment. We certainly need to update this foundation as we deploy changes to databases.

Hopefully Microsoft, more vendors, and us as developers help advance these technologies, as well as help all developers build more skills. I’m grateful to Andrew Pruski, Anthony Nocentino, and others for the information they share about containers and databases. Hopefully we see more people engaging in these areas over time.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 31, 2020
Microservices

Recently I wrote about software trends from 2019 and how applicable they might be today. One of those was microservices, which I don’t see implemented that often with relational databases. I do see developers wanting to use them, but I don’t see them often with a RDBMS backend.

I did see a piece on microservices, which mirrors that view. Lots of talk about the architecture, but not a lot of implementation. O’Reilly did a survey in Feb 2020, just before the world changed, and the found that only 10% reported complete success, but 54% felt mostly successful, and 92% some success. That’s better than I thought.

The respondents are self-selecting, so it’s not surprising that there might be some success. It’s not surprising that a third of these people are moving over half of their legacy systems to this architecture. That doesn’t mean they’ll be successful in the move, or that they’ll continue.

They also note that containers are important, and I think this might be more the nature that containers lend themselves to microservices. The companies I’ve know that heavily use containers tend to use microservices, almost as if the two technologies reinforce each other.

I think culture plays an important part in any success, and if I were in an organization, I’d focus on this. Perhaps just like DevOps, with a small team proving some success and using that as evidence to start convincing others to move. However, I’d also need to have a good argument about why we should rewrite applications, or even bet heavily on a new architecture. I’ve yet to see a good reason why microservices work well with an RDBMS, but I’m open to someone convincing me.

Steve Jones

Listen to the podcast at Libsyn, Stitcher or iTunes.

August 27, 2020