Reusing Tools for New Purposes

One of the things you learn early on in programming is that you ought to reuse code whenever possible. This often means refactoring code into functions or methods. This ensures that the code is more easily maintained and that the knowledge and work of solving the problem is reused in many places.

In database code we don't do this too often. We certainly can reuse some code by encapsulating it in a view, but this often brings performance penalties. We often don't want to reuse code in stored procedures and functions as embedding this into queries can cause lots of issues. In fact, it seems that much of the way databases optimize query performance isn't that amenable to reusing code.

That being said, I ran into an interesting case recently when thinking about maintenance in the cloud. Someone was asking about SQL Agent and the lack of support for it in many PaaS database systems. I understand that, and while there are some ways to do this, they feel complex compared to using a SQL Agent on a local instance, which is usually easy to set up and readily available. One of the speakers taking questions mentioned that they user shouldn't forget about Azure Data Factory as an automation agent.

That caught my attention as I hadn't thought about it before. This week, there was a blog on that very topic and I read through it to see what I thought. While this isn't as easy as SQL Agent, it does seem to be easier than Azure Automation, and likely more familiar than elastic jobs.  That's if you already have ADF running in pipelines. In many ways, this feels like using the maintenance plans in SQL Server, though just the call a stored procedure task. Since many people use a solution like Ola's, this is very easy to implement.

I like that this pattern reuses skills and a system that you may already be using, transferring the skills from one area (ETL) to another (administration). This might not seem like much, but limiting the tools and technology, reduces complexity and means that each person needs to know less to support your environment. I'm a fan of code re-use, outside of T-SQL), and I think reusing other technology systems, where appropriate, is a good idea.

Steve Jones - SSC Editor

Join the debate, and respond to the editorial on the forums

We’ve got some big plans for this year, and are looking to talk to people who use Flyway in a professional capacity. If you are willing to talk to us about how you first started using Flyway, then get in touch. Of course we’ll also look to say thank you for your time in our usual fashion.

Phil Factor offers a programmer's guide to Flyway's configuration settings, explaining the different categories of parameters, the role of each of parameter within each category, and how to exploit Flyway's multi-level configuration file system.

Not too long ago, I mentioned that compute instances in Azure Machine Learning are lacking one critical thing: auto-stop functionality. The single biggest problem I have with compute instances...

This is part six in a series on low-code machine l...

When working on different client servers, we often times get asked, why all their memory is not being used, and often times we are hitting the memory limits for...

Paul Randal starts a new blog series to dive deep into every aspect of the SQL Server transaction log. The post The SQL Server Transaction Log, Part 1: Logging Basics...

I’m not sure how many of you use Change Data Cap...

In this article we look at how to automatically add a final custom notification step to all existing SQL Agent jobs using PowerShell by reading and adjusting jobs steps...

I love playing around with test instances. Recently I've been doing a lot of preparation for some forthcoming blog posts and when trying to log into SSMS this morning I received the following error message:

Checkpoints are essential in SQL Server to help wi...

Thanks to Paul Randal and his recently published b...

In this tip I show how indirect checkpoints can help improve performance and stability of more than just checkpoint operations themselves, and stifle some (but not all) problems. I...

Click HERE to read Backup Retention Part 1. In part 1 I spoke about why you would want to restore from backups and some other reasons to have an...

Benchmarking a SQL Server environment for a hardware migration has a few challenges

The configuration setting cost threshold for parallelism has a default value of 5. As a default value, it’s probably too low and should be raised. But what benefit are...

I was doing a little work with CosmosDB recently, ...

I am not sure if many of my readers are aware but I am a fan of the work that Davide Mauri, and his team at Microsoft do on the Azure SQL Hyperscale team. I have had the pleasure of working with Davide through the MVP program and he is truly one of the more beautiful minds at Microsoft. Therefore, I am always excited when his team releases a new feature.

In this blog post we shall consider some of the strategies for improving data loading performance in Azure SQL Managed Instance.

This one is obvious, but hear me out. Every Synapse implementer knows that Synapse works fully integrated with the assigned Azure Data Lake storage account that’s set as its primary storage. However, Synapse–whether primary or not– is very easy to integrate with any storage account

This week, we are excited to announce the public preview for Map Data, a new feature for Azure Synapse Analytics and Database Templates! The Map Data tool is a guided process to help users create ETL mappings and mapping data flows from their source data to Synapse lake database tables without writing code.

In this tip we are going to discuss dimensional denormalization when working with Azure Synapse Analytics to reduce joins and improve performance.

A data lake is a scalable data storage repository that can ingest large amounts of raw data and make it available on-demand. Robert Sheldon explains the benefits and challenges of data lakes.

Introduction Do you need faster Ethernet at home? The obvious answer is that it “it depends”. In this case, it depends on several factors. First, just to be clear,...

If you plan to manage and work with Azure along with Terraform, using the Azure provider is a must. The Terraform Azure Provider lets you interact with the many resources supported by Microsoft Azure....

The Azure Container Instances provides a flexible solution for implementing container-based images in Azure infrastructure quickly. It does not require building the virtual machine, installing applications like Microsoft SQL...

When you start the build process for MySQL you will be shown the below screen, the question is what option would you select? It really does depend; I don’t... The...

I am honored to announce the next round of live an...

Well back at the end of 2019 I finished writing most of the checks related to the CIS Center for Internet Security requirements.  I have yet to write a... The...

Introduction In the previous installment of this s...

I was working on an upgrade recently, trying to move from SQL Server 2016 to SQL Server 2019. I wanted to perform an upgrade in-place on an Azure VM,...

Last week I made some passing references on Twitter to a new project I am working on this year. I thought I’d take a little time to explain it...

At the core of digital transformation lies data de...

(2022-Jan-19) David Eldersveld once said that a “cool solution is a completely insecure solution” - David Eldersveld on Twitter. That puzzled me, but only for a little while, since I... The...

SQLBits 2022 – Full Day of Training 9th March – Online or In Person at the London Excel https://sqlbits.com In this full day training data session, we’ll quickly cover...

This post is part of a series about SSIS Catalog Security. The other posts in this series are: One Way to Set SSIS Catalog Security One Way to Script...

In a recent project we needed to create an SSIS package to export data from a SQL Server table to a CSV file. One of the requirements was to dynamically append the date to the file name each time a file was generated.

As we know, it's crucial that you run Database maintenance regularly in order to keep your database performance up with the latest statistics and healthy indexes, here we provide another way to schedule this job using Azure Data Factory; this can be done on a scheduled time interval weekly/bi-weekly/monthly.

I recently released SSIS Framework File Community ...

In this article we look at how to identify columns that are candidates to be SQL Server sparse columns as well as automatically generating the scripts to make the...

Over the years Intel has prided itself on its ability to provide processors that fit into the ultraportable, professional market. We’re talking thin and light designs with obscene levels...

GEOMEAN: Returns geometric mean of given column reference. https://dax.guide/geomean/ GEOMEANX: Returns geometric mean of an expression values in a table. https://dax.guide/geomeanx/

Several DAX table functions can be used for paginated reports when you want all the data, not a summary. Adam Aspin explains how to use DAX table functions for...

SQRT: Returns the square root of a number. https:/...

Using ALLSELECTED with no arguments in a remote model later used in a composite model might produce unexpected results. In this video, we explain why ALLSELECTED requires special attention.

Video by: Reid Havens Learn how to apply some [DAX] wizardry to intelligently format string values when concatenating KPI values together. RELATED CONTENT Kane Snyder's Calculation Group PresentationJeff Weir's Twitter...

Understanding I’ve covered this sort of information in previous posts, like: What Kind Of Indexes Can You Create On Temporary Objects? When Should You Index Temp Tables? But some points should be made...

Stinko In this post, I’m gonna show you how stringing together a bunch of CTEs can cause performance problems with one of my paid training videos. If you like it,...

Everyone knows that you only get a single clustered index, right? Wouldn’t it be great though if you could have two clustered indexes? Well, you can. Sort of. Let’s...

Do That, But Faster Serial zones in parallel plans can leave a lot of performance on the table. One of the best ways to see that is with insert queries...

Apache Hive is an open-source data warehouse system used to query and analyze large datasets. Data in Apache Hive can be categorized into the following three parts :

The syntax for creating databases and tables in PostgreSQL is similar to other RDBMS. In this article, Grant Fritchey shows how to create databases and tables.

Speaking of ringing in the new, with all the hustle and bustle associated with the holiday season and the excitement of welcoming the new year, did anybody happen to notice the new feature quietly rolled out in Power Bi back in November to take the edge off creating and maintaining custom pages and bookmarks?

One of our fundamental principles of architectural design is simplicity. At Coeo, we have helped many customers implement Power BI Premium and deprecate their Analysis Services and Reporting Services infrastructure. While deploying SSRS reports to Power BI as Paginated Reports is a fantastic Software-as-a-Service solution, how do we replace our on-premises data-driven SSRS subscriptions?

Let's go into Adam's mind as he optimizes Power Query to make some Power BI Dataflows run faster to avoid resource usage. The result is INSANE AMAZING! Power Query...

Log Analytics contains information on the dataset,...

You can now see multiple improvements to the Copy Dashboard preview and try out a new feature which automatically retry a task in a pipeline when it fails.

We have released an update to Microsoft.Data.SqlClient, version 4.0.1. The update addresses several issues that are important to our customers.   Updates in Microsoft.Data.SqlClient 4.0.1 include:   Fixed Fixed Kerberos authentication failure when using...

I am happy to announce the availability of SSIS Catalog Browser v0.9.9.3. SSIS Catalog Browser is free. You are welcome. This post is part of a series about SSIS...

In this release, sp_Blitz shows some information about clusters and AGs, sp_AllNightLog and sp_DatabaseRestore get some love, and more. To get the new version: Download the updated FirstResponderKit.zip Azure...

Connect Azure Cache for Redis to storage accounts via managed identity.

General Availability enhancements and updates released for Azure SQL

With 1-minute frequency log alerts, query can be evaluated every minute for faster time to fire.

You can now create FIPS 140-2 enabled node pools in AKS

Data intensive AKS workloads can now take advantage of Azure ultra disks.

Provision your Azure Database for MySQL - Flexible Server today in the China East 2 and China North 2 regions.

New compliance certifications are now available on Azure Database for PostgreSQL – Hyperscale (Citus), a managed service running the open-source Postgres database on Azure.

One of the constants security professionals deal with is a high volume of alerts. Even after filtering out the noise and getting to the important events, that number is still usually much larger than what security teams can address in a normal shift.

Profiling is a technique to figure out how time is spent in a program. With this statistics, we can find the “hot spot” of a program and think about ways of improvement. Sometimes, hot spot in unexpected location may hint a bug in the program as well.

TLDR: funspotr provides helpers for spotting the functions and packages in R and Rmarkdown files and associated github repositories. See Examples for catalogues of the functions/packages used in posts by Julia Silge, David Robinson, and others.

The game Wordle has taken the world (or at least my facebook feed) by storm. It’s a really simple word game that’s a lot like the classic Mastermind. Here...

Bidirectional transnational replication is a specific transnational replication topology that allows two servers to exchange changes with each other: each server publishes data and then subscribes to a publication with the same data from the other server.

Organizations should balance process automation and human interaction to meet their unique security requirements.

An attack discovered on Dec. 4, 2021 forced the Ma...

Microsoft has started rolling out an out-of-band update for Windows Server 2019. The same KB5010791 patch addresses a couple of critical issues introduced with January 2021 Patch Tuesday updates....

5G is taking the world by storm for its notable speed and bandwidth strength. It supersedes 3G, which gained popularity in the early 2000s after the iPhone 3G was...

It’s important to choose the right datatypes when designing a database. Greg Larsen explains the differences between CHAR, VARCHAR and VARCHAR(MAX).… The post When to use CHAR, VARCHAR, or VARCHAR(MAX)...

This article gives an overview of DIFFERENCE and SOUNDEX SQL Server built-in system functions. It explains how to use those functions and how do they work. Introduction Soundex is...

In this article we look at using TSQL to create MACD moving average convergence/divergence indicators to predict buy and sell indicators for stocks.

In this article, you will see the different ways to calculate SQL percentage between multiple columns and rows. You will also see how to calculate SQL percentages for numeric...

Crowdsourcing is one way to get a job done. In this article, Devyani Borade describes crowdsourcing quality testing of software.

While nearly nine out of 10 organizations use Kube...