Press "Enter" to skip to content

Curated SQL Posts

Automating Azure Resource Deletion

Published 2020-09-09 by Kevin Feasel

Drew Skwiers-Koballa has put together a runbook to remove Azure resources tagged in a certain way:

Microsoft puts a lot of effort into making it easy for you to log on to portal.azure.com and spin up a VM, SQL Database, Function, or other instance. The Visual Studio Enterprise subscription with $150 credit per month can go quickly if you’re not careful to stop or remove big ticket items as soon as you’re done with them. Even if you have a virtually unlimited Azure account connected to a credit card, expense account, or a trust fund – you probably don’t want to accidentally leave something running longer than it is needed.

With an Azure Automation account and a Powershell workflow runbook I’m able to use resource tags to set resources for autodeletion by date or immediately that evening.

Click through for that runbook.

Comments closed

R: Avoid apply() with Large Datasets

Published 2020-09-08 by Kevin Feasel

George Pipis walks us through the performance cost of using the apply() function against a large dataset in R:

When we are dealing with large datasets and there is a need to calculate some values like the row/column min/max/rank/mean etc we should avoid the apply function because it takes a lot of time. Instead, we can use the matrixStats package and its corresponding functions. Let’s provide some comparisons.

Click through for a demonstration of how much slower it can be in certain cases. H/T R-Bloggers.

Comments closed

Ensuring Trace Flag Consistency Across Instances

Published 2020-09-08 by Kevin Feasel

Taiob Ali creates a process to track ensure specific trace flags are in use:

Recently during a SQL Server upgrade, I noticed some inconsistency with trace flags between production and non-production instances. Investigating further also found a similar inconsistency between Always On Availability Group replicas.

My team members fixed this issue by leveraging startup stored procedures.

Click through for the explanation.

Comments closed

Restoring a Database in an Availability Group

Published 2020-09-08 by Kevin Feasel

Rajendra Gupta walks us through the process of restoring a database which is currently in an Availability Group:

You might think a question here– We can take production database backup and restore it on the development database. What difference does it make in a standalone database restore or availability group database restore?

Database restore works with the standalone database, but if the database is configured in the availability group, we cannot directly restore the database. It requires additional steps because of the AG configurations. Our database should be in the same state (AG synchronized) after the database restores as well.

In this article, let’s cover the steps to restore an existing availability group database in the SQL Server Always On Availability Group.

Read on for the answer.

Comments closed

Generating Scripts in SSMS and ADS

Published 2020-09-08 by Kevin Feasel

Rob Farley walks us through automation options in SQL Server Management Studio and Azure Data Studio:

User interfaces are great, but I simply don’t want to have to remember to do everything the same way each time.

To that end, I want to wax lyrical for a moment about the Script button on most dialog boxes in SQL Server Management Studio (SSMS), and make a quick mention of what things (currently) look like in Azure Data Studio (ADS). (I say “currently” because ADS is still under very active development and could have changed even by the time I publish this.)

The Script button is one of the best features of Management Studio. The UI lets you know what can be done, and the Script button lets you know the process. It’s also a nice reminder that this functionality is not unique to the UI. Management Studio’s implementation of the Script button isn’t perfect, but it is good.

Comments closed

Filtering with dplyr Using Strings as Expressions

Published 2020-09-04 by Kevin Feasel

Kenneth Tay shows how to build arbitrary expressions to execute in dplyr’s filter function:

This took me a while to figure out and so I thought I would post this as future reference. Let’s say I have the mtcars data and I want to filter for just the rows with cyl == 6. I would do something like this:

library(tidyverse)
data(mtcars)
mtcars %>% filter(cyl == 6)

What if I had the filter condition as a string instead?

Read on to see how you can do this. Given the answer, I wonder if there’s any chance that could be turned into an injection vulnerability. H/T R-Bloggers

Comments closed

The State of R Packages Locally

Published 2020-09-04 by Kevin Feasel

Maelle Salmon and Gabor Csardi walk us through some details about installed packages on a system:

Now how do you know where any of your installed packages was installed? You can use find.package() and path.package()!

To check whether a package is installed, it is better to use find.package() than installed.packages() because the latter, as its docs state, can be slow on some systems. In both cases, it does not mean the package is usable, for that you’d need to use library() or require().

Read on for several tips around where packages are located, what their contents look like, and learning a bit more about the actual code in packages.

Comments closed

Triggers Should Handle Multi-Row Scenarios

Published 2020-09-04 by Kevin Feasel

Kenneth Fisher has a public service announcement:

While I was helping him with part of it I noticed that the trigger was only set up to handle single row updates. When I told him it needed to be able to handle multiple row updates he asked me in all seriousness, “Why? Since only one value can be changed at a time why should I worry about multiple rows being changed at once?”

Read on for Kenneth’s response, which approximates to Au contraire mon frère. Unhandled assumptions often become bugs.

Comments closed

Messy Code and Reasonable Expectations

Published 2020-09-04 by Kevin Feasel

Rachel by the Bay has a doozy of a story:

One day not so long ago, I was in a meeting listening to a team explain why their service had gone down and taken out a big chunk of a business. They were one of those things that has to exist and work in order for the actual “thing that makes money” to go. Think of delivering pizzas, connecting dog walkers with dogs who need to be walked, that kind of thing.

It turned out they had been crashing every time a request came through for a certain part of the country. That is, not all pizzas, dog walkers, or whatever it was were handled identically, so they had their own city or region configurations. Think of differences in pricing, taxes, features, or whatever. Trying to process a request for this one particular region had caused the entire process to die when it hit a new config that was “bad” somehow.

Read on for the story. This sounds like a boundary issue. Boundaries are messy and need thorough examination to handle as many possible points of failure as is reasonable. Taking seriously the point that it makes the code messy, the answer is not “Don’t do the checks,” but rather “Put the checks in a place where their messiness has a minimal impact on the rest of my beautiful code but still does the important work we need them to do.” Failing that, live with the mess and have a working process.

Comments closed

Building dacpac Files on Non-Windows Machines

Published 2020-09-04 by Kevin Feasel

Erik Ejlskov Jensen provides another advantage for Azure SQL Database’s database projects:

For a while now, it has been possible to publish a .dacpac file (meaning apply it to an new or existing database) using the cross-platform version of sqlpackage.

But authoring and building a database project (sqlproj) was only possible on Windows, as the .sqlproj project type is based on the classic .NET Framework .csproj project type.

Now, thanks to the new Database Project extension in Azure Data Studio Insiders build, it is now possible to author, build and manually publish a SQL Server Database project.

And by using the new MsBuild.Sdk.SqlProj SDK and project type, is is also possible to build and publish a Database Project from a build agent (CI pipeline), without having to install the sqlpackage tool. Read on!

You heard Erik.

Comments closed