Voice of the DBA

Tag: software development

Do it. Try it. Fix it.

“Do it. Try it. Fix it.” – Sam Walton

That quote is interesting, and while I don’t think Sam Walton was thinking about software, this is the loop that I, and many others, follow. We see a problem, and we try some things. If they work, we move on, if not, we fix things. At least, that’s the approach most of us would like to take when we see an issue. Sometimes management gets in the way of us following a simple process.

The key part of that quote is the first part, the part Sam Walton talked about was acting in some way. Doing something, making a positive move towards improving something. Many of us want to take action, especially when we see a problem. Again, however, management can get in the way if the problem isn’t prioritized.

There’s one part in DevOps and software development that comes after the “fix it.”. We need to ensure we don’t repeat that type of mistake. This means that when we find a solution, especially a better pattern of building something, we share it. We use the feedback loops and learning ideas in DevOps to ensure others in our organization don’t need to do their own “Do it. Try it, Fix it” loop and repeat our work.

One of the hardest things to convince smart (or very busy) developers to do is change their work habits and patterns to implement something new. While it can slow things down, or create other work, in the long, or even medium term, we can all be more productive if we take the time to share what we’ve learned. We also need to take the time to learn what others share.

Of course, proving your way is better can be a challenge. You need to be sure of yourself, gather evidence, and learn to present a convincing reason why someone else should change. If you can do that, you will have more success.

Steve Jones

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

February 4, 2021
Live versus Stored Data

I’ve been working with various technologies as experiments over the last few years. I’m curious how useful I find them, as well as how they might help the clients and customers I run into at Redgate Software.

I think Jupyter notebooks are a technology that has a lot of promise, and their use is growing in many organizations. One of the interesting things with notebooks is they can store results inside them, which allows a transfer of information in an interesting fashion. If I re-run a cell, the results can change and comparing them is challenging, but that is a separate issue. At least I can capture the results and share them.

I’ve also been working with Power BI at times. I saw a demo of a query from PBI to Excel, where the data was then stored in the PBIX file. This query was disconnected, but it could be refreshed. You can also configure how this works, so you get the choice of live or stored data.

If you are distributing information to users, those of us in the database world often think about having live data available and queried from a database, but often the same data gets queried over and over, which creates a load on our system. I know the data is often cached in these situations, but cache is a previous resource, so limiting the repeat queries can be valuable.

I do think this is a decision point for some applications, where we might choose to limit the amount of live data v stored data. There are times when speed matters more than having exact data, so cached or stored data works well. Sometimes the most current data is critical, and you need to query the database.

How do you decide when you want a live connection to a database, and when stored data is acceptable. I don’t know that I have any rules, but I evaluate each situation and try to work with users to make a decision. I suspect most people do the same thing, but if you always use live data, let me know today.

Steve Jones

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

January 26, 2021
The Devil is in the Details

Some of us have run into perplexing technology problems, where we had to dig deep into an application to solve a problem. We might need to work with our own staff, vendor support, perhaps even coordinate people across multiple different organizations. This can be even more challenging when we don’t have access into the internals of all the code.

I ran across a neat story from Netflix, where an engineer had to dig into an issue with one of their partners. In this case, there were hardware and software components, and four different companies involved. The problem involved a playback issue, with deadlines and finger pointing over the issue.

Eventually the root cause discovered was a thread level issue in the Android OS, but the tale of how the engineer asks questions, looks at good, sets up tests, and more is a good examine of how to dive into an issue. While many of us wouldn’t get involved in threads on the data platform, we do need to understand the code and use metrics and data to narrow down the issues. It’s certainly possible we could discover a bug, but most of my experience is that I’ve found a problem in developer code or insufficient hardware resources.

The story is a nice read, and the final paragraph made me smile It had this quote: ” This story really exemplifies an aspect of my job I love:…” It’s always inspirational to find someone truly enjoying their job, and I find myself thinking about the things I love about my job, with problem solving being one of the best parts.

Steve Jones

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

January 5, 2021
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