Press "Enter" to skip to content

Curated SQL Posts

Password Handling in Powershell Automation Scripts

Ajay Dwivedi gives us a tip:

I have been writing automations using PowerShell for many years now. One common issue I notice with people’s code is the improper handling of passwords. In this blog, I share how to set up a Credential Manager on a SQLServer and use the same for handling passwords in automation.

To setup Credential Manager, we need to download and execute the following steps using scripts from my Github repo SQLMonitor.

One point I’d like to clarify in Ajay’s scripts is that the passwords in the database aren’t hashed. Hashing is a one-way operation, so you’d never be able to decrypt it with a passphrase. The password is encrypted and the passphrase isn’t a salt—salts are a way of making a hash unique from the plaintext to prevent multiple users with the same plaintext password from having the same salt. Encryption instead of hashing is the correct answer here because you need the plaintext of the password to perform the automated operation.

As for ENCRYPTBYPASSPHRASE(), it’s okay if you’re running SQL Server 2017 or later. For 2016 and earlier, it uses Triple DES with 128 bit key length and that’s no good.

I’d also look into the Powershell SecretStore module and possibly integrate into an existing key vault if you have one.

Comments closed

Batching Text Analytics with Azure AI Services

Matt Eland tries out the TextAnalytics client:

We’ll talk about each one of these capabilities briefly as we cover the results, but at a high level what we want to do is:

  • Perform sentiment analysis to determine if the text is positive, negative, neutral, or mixed.
  • Summarize the text using abstractive summarization which summarizes the text with new text generated by a large language model (LLM).
  • Summarize the text using extractive summarization which summarizes the text by extracting key sentences or parts of sentences to convey the overall meaning.
  • Extract key phrases of interest from the text document.
  • Perform entity recognition and linked entity recognition to determine the major objects, places, people, and concepts the document discusses.
  • Recognize any personally identifiable information (PII) present in the document for potential redaction.
  • Analyze the text for healthcare specific topics such as treatment plans or medications.

Read on to see how a certain passage of text fares.

Comments closed

Triggers in Postgres

Semab Tariq explains how triggers work in PostgreSQL:

In the dynamic world of relational databases, where precision and efficiency are paramount, PostgreSQL emerges as a robust solution. At the heart of its functionality lies a feature that adds a layer of intelligence and automation to database operations – Triggers.

What exactly are triggers? They are predefined actions that the database executes automatically when a specific event occurs, such as an insertion, update, or deletion of records in a table. Think of triggers as the silent guardians behind the scenes, ensuring data integrity, enforcing business rules, and automating repetitive tasks.

These trigger examples also work for SQL Server, and my advice for triggers probably would be the same between platforms: use triggers sparingly because they can cause performance problems and it can be easy to forget that they exist when you’re troubleshooting why some operation isn’t working the way you expect.

Comments closed

ML Models and Data Warehouses in Microsoft Fabric

Tomaz Kastrun continues a series on Microsoft Fabric. First up is creating ML models:

Protip: Both experiments and the ML model version look similar, and you can intuitively switch between both of them. But do not get confused, as the ML Model version applies the best-selected model from the experiment and can be used for inference.

Then we switch context to data warehousing:

Today we will start exploring the Fabric Data Warehouse.

With the data lake-centric logic, the data warehouse in Fabric is built on a distributed processing engine, that enables automated scaling. The SaaS experience creates a segway to easier analysis and reporting, and at the same time gives the ability to run heavy workloads against open data format, simply by using transact SQL (T-SQL). Microsoft OneLake gives all the services to hold a single copy of data and can be consumed in a data warehouse, datalake or SQL Analytics.

Comments closed

Searching for a String in a SQL Server Database

Vlad Drumea is looking for a string:

This post contains a script that I’ve written and used a few times when I’ve needed to search for a string in an entire SQL Server database.

You can find more of my scripts by checking out the SQL Server Scripts category of my blog.

As a warning, this is definitely going to be slow on larger databases. But for smaller databases, it can definitely be handy.

Comments closed

Saving Money on Azure SQL Database

Andy Leonard’s speaking my language:

Regular readers of this blog know I am all about saving money. I’ve blogged about ways to set up inexpensive instances of Azure SQL DB. I recently learned a couple new ways to save even more money working with Azure SQL. Both methods begin the same:

Read on for a pair of tips. Serverless mode does work really well for dev environments and cases where you don’t need it up that much (and can wait the 3-5 minutes for the service to spin up the first time you use it that day). But if you’re using it 12+ hours a day, it’s actually more expensive than using the standard model.

Comments closed

What’s Missing in Azure SQL Managed Instance?

Kendra Little gives us the lowdown:

Spoiler: a large amount of features from SQL Server 2022 are missing from Azure SQL Managed Instance. Some major features are missing that were introduced in SQL Server 2019– and here we are just a few weeks away from 2024.

But Microsoft’s top-line marketing claims about Azure SQL Managed Instance remain that “it’s always up to date with the latest SQL features and functionality.”

Let’s dig into some of the documented highlights on missing features, so you can decide for yourself what to think of that statement.

Read on for Kendra’s take. I agree with the overall argument: Managed Instance is an expensive service intended to serve as a SQL Server replacement. But there’s still a lot missing from the product and it still feels like it’s smarter simply to build a VM running SQL Server rather than play the Managed Instance game. Azure SQL DB I extend a bit more grace toward, as its intent is a bit different from the SQL Server box product or Managed Instance.

Comments closed

Trying out Azure Load Testing

Dieu Phan takes us through the Azure Load Testing service:

Azure Load Testing is a fully managed load-testing service that enables you to generate high-scale loads. The service simulates traffic for your applications, regardless of where they’re hosted so I would like to share a walkthrough Azure Load Testing in this post.

Okay, this post isn’t very data platform-centric, but I do like the Load Testing service and think more companies and people should use it.

Comments closed

Data Modeling for Sankey Charts in Power BI

Marco Russo and Alberto Ferrari explain when Sankey charts can actually make sense:

Picture this: you manage a company that sells subscription services on the web, and you want to track the evolution of your customers by analyzing different events to understand how many customers start a trial before they purchase or how many renew or cancel their subscriptions.

The report should look like this: the darker flow indicates the number of customers who skipped the trial and went directly from a phone call to purchasing a subscription.

Read on for that sales funnel example and how you can prepare the data to make best use of Power BI’s Sankey chart visual.

Comments closed