Press "Enter" to skip to content

Curated SQL Posts

Puzzles with Powershell

Published 2019-11-13 by Kevin Feasel

Jana Sattainathan takes us through solving a couple of problems with Powershell:

As I said before, I am a regular at my local PowerShell user group. I keenly look forward to our monthly meetings. We share a lot of PowerShell code/tips/tricks among ourselves. It is a small but close-knit group. Yesterday, one of the members, Jason Walker came up with coding puzzles for the group. This blog post is about the puzzles Jason created for solving using PowerShell. This is the kind of simple stuff that keeps the meetings interesting and fun (besides the Pizza!).

My solutions may not be the most elegant or concise but they work and were created on the fly during the meeting!

Read on for the four puzzles as well as solutions.

Comments closed

What’s New with Query Store in SSMS 18.4

Published 2019-11-13 by Kevin Feasel

Erin Stellato takes us through changes to Query Store components in SQL Server Management Studio 18.4:

Last week the SQL Server Tools team released a new version of Management Studio, 18.4, and it dropped in the middle of my Query Store full day pre-con on Monday. Those two are related. There were some changes in SSMS 18.4 specific to Query Store, and for a few seconds over lunch I thought about downloading the new version and running it for my afternoon demos. But…I didn’t want to anger the demo Gods, so I stuck with 18.3.

Read on for info on the changes.

Comments closed

SQL Server Trends Worth Watching

Published 2019-11-13 by Kevin Feasel

Grant Fritchey follows up on a Kevin Hill tweet:

There are a million things to learn about in our rapidly shifting technological landscape, but I think this assessment, especially the way it was put, “no longer justify ignoring” really nails some of the fundamentals.

Let’s talk about why you can no longer ignore Docker, Git and DBATools either.

If you’re a DBA and aren’t familiar with Docker, Git, or DBATools, that’s a pretty good trio of things to spend some time learning. You can survive without them, but you’re more likely to thrive if you know them.

Comments closed

Stack Overflow DB, Columnstore Edition

Published 2019-11-13 by Kevin Feasel

Erik Darling has started a new series. Part one is the intro:

I really wanted a version of the Stack Overflow data dump that was all clustered column store. In SQL Server 2016, that didn’t really work because of restrictions around MAX data types. In 2017 it did, but… If your data warehouse has a bunch of max data type columns, that’s bad and you should feel bad.

The problem here is that once you drop out the “big” columns (AboutMe from Users, Text from Comments, Body from Posts), the entire ~300GB database compressed down to about 6GB. That means if we want a realistically sized data warehouse, we’d need a “Make Big” script, like people used to use for Adventure Works before it went out of business.

Part 2, like a noble spirit, embiggens the smallest man:

One thing I’d love feedback on is advancing dates. Right now, the script doesn’t do that at all. I thought of different ways to handle it, but didn’t like any of them, mostly because of how it might mess with the partitioning function I’m using. I felt like I was overthinking it quite a bit, and decided to leave dates as-is, and only increment User and Post Ids.

A quick note: This script assumes that a database called StackOverflow will be the source of the loads. If you need to use a different version, that’s a manual change. I didn’t want to go down the dynamic SQL route here until I gauged popularity.

If you want to play along at home, you can grab the data dump from archive.org.

Comments closed

Building a SQL Cluster: Availability Groups

Published 2019-11-13 by Kevin Feasel

Ryan Adams continues a series on building a SQL Server cluster as a lab:

You are going to create a multi-subnet Availability Group in Part 3 of our series on how to build a SQL Cluster Lab. First you give the Cluster Name Object rights in Active Directory and then install your AG. You will also ensure the AG endpoints are using the dedicated network created for them in Part 1. Here are links to the other articles in the series.

Click through for the video.

Comments closed

High-Value Software Testing

Published 2019-11-12 by Kevin Feasel

RJ Zaworski has some principles around high-value testing:

Whatever the tools and approach, software testing proves that software does what it claims to do. Tests help developers eliminate defects, build confidence, practice good design, and ideally all three. They also take time to write, run, and update–time that’s no longer available for other development tasks.

High-value testing seeks to maximize the return on that investment. Like much of software development, it’s as much art as science. But a few practical principles can help keep things pointed in the right direction.

There’s a lot of good advice in this post.

Comments closed

Thoughts on Large Datasets in Power BI

Published 2019-11-12 by Kevin Feasel

Teo Lachev has some early thoughts on large datasets in Power BI:

At Ignite 2019 Microsoft announced the public preview of large datasets in Power BI Premium. This is a significant milestone as now datasets can grow up to the capacity’s maximum memory (previously, the max size was 10 GB with P3 plan), thus opening the possibility of deploying organizational semantic models to Power BI. I consider this feature mostly suitable for organizational BI as I don’t imagine business users dealing with such large data volumes. I tested large datasets during its private preview, and I’d like to share some notes.

Teo has some open questions, and I’d like to see this shifted down to SSAS too.

Comments closed

Powershell Notebooks in Azure Data Studio

Published 2019-11-12 by Kevin Feasel

Aaron Nelson announces a new feature in Azure Data Studio:

In order to get all the nice intellisense and tab completion features of the PowerShell language inside your PowerShell Notebooks, be sure to install the PowerShell extension from the Azure Data Studio marketplace.

At this point, the biggest remaining language is R, though I’d love to see F# support as well (hey, Azure Notebooks offers F# support).

Comments closed

High Availability Announcements from Microsoft

Published 2019-11-12 by Kevin Feasel

Allan Hirt looks at a couple announcements from Microsoft:

I’m going to discuss what I feel are the biggest game changers. I knew licensing was changing as I had conversations with Microsoft around this months ago. I was not sure what the final result was going to be, but I’m fairly pleased. Is it perfect? No, but it’s much better than it was.

You’ll definitely want to read Allan’s thoughts on Microsoft’s SQL Server licensing changes, as well as a private preview of Azure Shared Disks.

Comments closed