Press "Enter" to skip to content

Month: March 2023

The Importance of Monitoring Tools

Louis Davidson talks turkey about tooling:

When I was a DBA involved with the management of a large number of database servers, I didn’t have many third-party tools to help me do my job. For the most part, I relied on scripts that I found or wrote. I enjoyed writing scripts to manage the servers, as it taught me a lot about the internals of SQL Server. Many of these scripts were eventually automated using SQL Server’s agent to run and save data on the different servers so we could review the results, looking for issues.

Some of these tools written over 20 years ago still run to this day. We captured tons of data about everything we wanted to know about the server in case there were issues. Loads and loads of data. We had some processes that would scan that data and send emails when obvious errors occurred, but it was hard to keep synchronized over many different servers.

Click through for Louis’s thoughts. I believe good tools can make a DBA’s life a lot easier, though mediocre tools might make it worse: you become the proverbial drunk looking for his keys under a streetlamp because that’s where the light is.

Comments closed

Combining CSV Files via Powershell

Chad Callihan smooshes files together:

I recently had a handful of CVS files that needed reviewed. Each CSV file was the same format, and while I could have opened them each individually to sort and review, I thought it would be much easier to combine them into one file. It was time to turn to PowerShell. Let’s look at a few examples of how PowerShell can be used to combine multiple CSV files into a single file.

A core assumption here is that the structure of each file—particularly the number of columns but also the semantic meaning of each column—is the same.

Comments closed

pmap and imap Examples in purrr

Steven Sanderson has a multi-parter for us. First up is a look at the pmap() function in R’s purrr library:

The pmap() function in R is part of the purrr library, which is a package designed to make it easier to work with functions that operate on vectors, lists, and other types of data structures.

The pmap() function is used to apply a function to a list of arguments, where each element in the list contains the arguments for a single function call. The function is applied in parallel, meaning that each call is executed concurrently, which can help speed up computations when working with large datasets.

Next up is the imap() function:

The imap() function is a powerful tool for iterating over a list or a vector while also keeping track of the index or names of the elements. This function applies a given function to each element of a list, along with the name or index of that element, and returns a new list with the results.

The imap() function takes two main arguments: x and .fx is the list or vector to iterate over, and .f is the function to apply to each element. The .f function takes two arguments: x and i, where x is the value of the element and i is the index or name of the element.

Both of these sound a little complex and abstract at first, though as you get more familiar with them, you get to see just how powerful they are.

Comments closed

AR and VR in Data

Corrinna Peters differentiates augmented reality from virtual reality:

Virtual Realty (VR) and Augmented Reality (AR) is everywhere, with a broad variety of applications across many industries, and the potential to revolutionise many others. The potential of VR and AR technology is endless and drives digital transformation. Lots of market research studies are projecting that VR and AR is forecasted to grow exponentially in the next few years. With this in mind, the questions people are starting to ask themselves are – what does this mean for me? What does this mean for my business? How will this change data and analytics? What are the differences?

In the medium term, I am quite pessimistic on the topic. There are specific use cases where virtual reality can be interesting, such as a virtual house walk-through. But for the most part, the problem with VR is that optical quality is still not good enough, meaning that a lot of people struggle to use a VR headset for more than an hour or so before getting nauseous. There are also problems with the lack of tactile sensation (and haptic feedback can only go so far) and ergonomic challenges when you’re constantly raising your arms to perform actions.

Augmented reality has an easier sell, though, in cases where you’re willing to hold a phone or tablet up against something. For this scenario, think museum pieces, where you hold the phone up and get more information about the piece, artist, and style. Google does have AR for walking directions, with the cost of burning a whole bunch of battery life. But the general failure of HoloLens and wearable AR devices, as well as the inherent privacy concerns from flashing your active camera around crowded areas, dampen the mood a bit for AR.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed

GitHub Actions for CI/CD against SQL Server 2022

Kevin Chant has a new template for us:

In this post I want to cover performing CI/CD for SQL Server 2022 using GitHub Actions. For a couple of reasons.

First one is due to the fact that last week I was answering a query on the well known SQLHelp hashtag about deploying updates to SQL Server using GitHub Actions. That is when it dawned on me that I had never shared a repository to solely perform CI/CD for SQL Server using GitHub Actions.

Second reason is due to the fact that I wanted to show how to create an SDK-style database project for SQL Server 2022. By Using the ‘Microsoft.Build.Sql‘ .NET SDK for database projects.

Click through for the GitHub repo and plenty of links and information.

Comments closed

Building a URL Redirect Service in Azure

Bob Pusateri spams the 302 response code:

In WordPress, I used a plugin called Redirection, which enabled me to create URLs that would redirect to other websites. They can come in handy, especially when presenting. I’ll often create a custom redirect link for each presentation I give that will point my attendees toward my resources for that topic. For example, if I were giving a talk about isolation levels, I might direct attendees to visit https://sqlbob.com/isolation, which would redirect them to my GitHub repo with links to slides, demo code, and other materials.

But a HTML file really can’t redirect you to another page. To be fair, you can do it via a <meta> tag, but it’s not guaranteed to work in all browsers. I’m told there’s also a way to do it in JavaScript, but I really wanted a seamless experience for users like the Redirection plugin offered. I also was in need of a little project over the winter months.

Click through for Bob’s solution to the problem.

Comments closed

Trying Azure SQL DB Hyperscale Serverless

Reitse Eskens ran out of money on our behalf:

In one of my last blogs, I wrote about my first encounter with the Azure Hyperscale Serverless offering. Now it’s time to dig a bit deeper and what it’s up to.

Disclaimer. Azure Hyperscale Serverless is in preview and one of the things that isn’t active yet, is the auto shutdown. This means that it will stay online 24/7. And bill you for every second it’s online. In my case, this meant that my Visual Studio credits ran out and I couldn’t use my Azure subscription anymore. Keep it in mind when testing this out, especially if your credit card is connected to said subscription.

Click through to see what Reitse was able to do in the meantime, before those Azure credits ran out for the month.

Comments closed