Press "Enter" to skip to content

Month: May 2021

Handling Disaster Recovery

Published 2021-05-27 by Kevin Feasel

Randolph West has a disaster recovery plan:

I’ve had several occasions where hard drives have failed and attempts to recover data from these wonders of mechanical engineering have been mostly fruitless. I’ve experienced profound examples of data loss, in both cases losing years of email and contact details for people I met online.

This is all to say that I care deeply about data loss, and I take it personally when I’m asked to engage with potential customers to recover data in SQL Server.

This post is a high-level overview of how I tackle data recovery, whether personally or for professional consulting reasons.

Click through for the steps.

Comments closed

Visualizing with Text

Published 2021-05-26 by Kevin Feasel

Alex Velez shows how you can use simple text to share information:

If you’re unsure about what I mean when I say simple text, I’m referring to the idea that just because you have numbers doesn’t mean you need to build a graph—as in this simple text example. Sometimes words with big numbers written in bold fonts (aka BANs) are more effective, especially when communicating one or two data points.

Some people’s hesitancy with simple text is that they think it’s unrealistic: the notion that you might only share one or two numbers with an audience. It’s is a fair point; it does seem a bit silly to think that you’d only talk through a couple of specific values when presenting data—even though I’m sure there are such occasions. Rather than thinking of the ideal use-case for simple text as when you only have one or two numbers in totality, consider when it may make sense to draw attention to one or two numbers in your larger story.

This is a good reminder that you don’t need everything to be fancy, shiny, and visual-laden. A little bit of text can go a long way in laying out a visual. That said, the warning is that text seems to be a little easier for people to miss, especially if there’s a lot of it. That’s where Alex’s explanation really pays off.

Comments closed

Creating an Azure SQL Database from Powershell

Published 2021-05-26 by Kevin Feasel

Gijs Reijn shows how to automate the process of creating an Azure SQL Database using Powershell:

Before you can create an Azure SQL database, you must create an Azure SQL server to host it on. Assuming you’re already authenticated to Azure:

Open PowerShell on your local computer and create the Azure SQL server that will host the Azure SQL database.

The command below is creating an Azure SQL server called sqlestate in the prerequisite resource group with a SQL admin username of SqlAdministrator and a password of AVeryStrongP@ssword0. The command is saving the output of the New-AzSqlServer cmdlet to use attributes from the server created later.

Read on for the step-by-step breakdown and full script.

Comments closed

Searching through Stored Procedures using dbatools

Published 2021-05-26 by Kevin Feasel

Jess Pomfret has another way to search through stored procedure text:

When we’re looking for the command we need within dbatools to fulfil our needs I cannot recommend Find-DbaCommand highly enough.  This command will search all other commands for the pattern you pass in.  Today we know we want to find references in stored procedures so let’s see if there is a command that will help.

Seems like querying sys.sql_modules is a little easier, though if this is a step in a pipeline (such as finding old procedures based on some no-longer-appropriate code snippet and deploying new versions), this can be a good first step.

Comments closed

Azure Synapse Analytics Supports Apache Spark 3.0

Published 2021-05-26 by Kevin Feasel

Euan Garden has some great news for us:

Starting today, the Apache Spark 3.0 runtime is now available in Azure Synapse. This version builds on top of existing open source and Microsoft specific enhancements to include additional unique improvements listed below. The combination of these enhancements results in a significantly faster processing capability than the open-source Spark 3.0.2 and 2.4.

The public preview announced today starts with the foundation based on the open-source Apache Spark 3.0 branch with subsequent updates leading up to a Generally Available version derived from the latest 3.1 branch.

It still won’t be as fast as Databricks, but it should be a good bit faster than the Spark 2 they were running.

Comments closed

Determining the xp_cmdshell User

Published 2021-05-26 by Kevin Feasel

Kenneth Fisher asks the important Stockdale questions (Who am I? What am I doing here?):

It works when I run it this way™ but not when I run it through xp_cmdshell!

It’s a permissions issue. When you run xp_cmdshell you are running under the ??? account.

Little bit more detail. The extended stored procedure xp_cmdshell creates a windows command shell. This shell has to be run under a windows/active directory account. Obviously you can’t get access to a windows resource (a directory for example) using a SQL Server login. The trick is to be able to tell them what account xp_cmdshell is using within that shell. There are two possibilities here.

Read on to learn about those two possibilities.

Comments closed

Passing Defaults to Stored Procedures

Published 2021-05-26 by Kevin Feasel

Greg Dodd wants the usual order:

If you’ve done work with stored procedures, you are probably aware that stored procedures have parameters, and that the parameters can be defaulted when you declare them. I was recently caught out due to some application code that checked when a parameter was specified for a stored procedure, if the value for the parameter was NULL then pass in the keyword DEFAULT. The Code assumed that if I had gone to the effort of specifying the parameter but not the value, that I must want the default value of the Stored Procedure. I had expected it would pass in the SQL NULL keyword.

Read on to see what actually happens and how you can use a default value.

Comments closed

Understanding Consumer Lag in Apache Kafka

Published 2021-05-25 by Kevin Feasel

Loretta Jones takes us through the notion of consumer lag in an Apache Kafka topic:

Amongst various metrics that Kafka monitoring includes consumer lag is nearly the most important of them all. In this post, we will explore potential reasons for Kafka consumer lag and what you could do when you experience lag.

This post is fairly high-level, and it does a good job of explaining the notion to someone without much familiarity with Kafka.

Comments closed

Broadcast Variables in Apache Spark

Published 2021-05-25 by Kevin Feasel

The Hadoop in Real World team explains the notion of broadcast variables in Apache Spark:

Broadcast variables are variables which are available in all executors executing the Spark application. These variables are already cached and ready to be used by tasks executing as part of the application. Broadcast variables are sent to the executors only once and it is available for all tasks executing in the executors.

Read on to understand when they are useful and, just as importantly, when not to use them. They seem like the type of thing which a newer developer could easily misuse.

Comments closed

Testing TLS Protocol Versions with cURL

Published 2021-05-25 by Kevin Feasel

Anthony Nocentino has a tip for us:

Ever need to set your web server a specific protocol version of TLS for web servers and need a quick way to test that out to confirm? Let’s check out how to use curl to go just that.

This code here uses curl with the parameters --tlsv1.1 --tls-max 1.1, which will force the max TLS protocol version to 1.1. Using the --verbose parameter gives you the ability to see the TLS handshake and get the output sent to standard out.

Also, check the comments for a very helpful addendum.

I should note that cURL is built into Windows 10 as of v1803, and it’s been a part of MacOS and Linux for a long, long time.

Comments closed