Data Engineer and Nerd
This month, Josephine has asked how we achieve good enough without burning out in the pursuit of perfect. When designing data solutions, aiming for perfection can lead to endless iterations, moving targets, delays, and – as Josephine points out – burnout. A quote which encapsulates the essence of this for me: Perfection is the enemy of progress Winston Churchill If […]
Last week I shared a script to identify foreign keys which weren’t indexed. What we didn’t discuss in detail was why it can be important to index these columns. Here we’ll demonstrate the two areas we can see great benefits by introducing the indexes. Query optimisation We’ll start with the typical benefit of indexing – query optimisation. The index […]
Foreign keys provide us with confidence in the integrity of the code we develop. In this post we identify indexing opportunities around foreign keys to increase performance of their usage. Creating an index on a foreign key column (or columns) can provide additional performance in two key situations: That’s all well and good if we’re […]
SQL Agent jobs allow us to schedule and automate tasks on a SQL Server instance. Crucially, when things go wrong we need to know about them. That’s why we use notifications. Setting up Operators and job Notifications is as expected on a Managed Instance. However, when it comes to sending the notifications we may have […]
We may associate consuming data from Azure Storage with tools like Data Factory or even SSIS as we saw recently. We don’t always need the middle man though. Here we’ll demonstrate how to use an External Data Source to perform the ingestion directly into SQL Server. Firstly we need the credential to authenticate against the storage. […]
Bad code smells can run through a system, and one instance can quickly spread when code is recycled. Simon recently looked at a non-sargable example and was able to fix it by using an index and refactoring the query. I wanted to consider an alternative approach if we saw the same issue repeated consistently. We don’t want to […]
Last week we looked at using the Azure Feature Pack for SSIS to interface with Azure Storage. Today we’re going to solve a couple of the common issues I’ve seen when using this pack. Bad or timed out requests You can set up a connection to the storage and it tests fine, but when you come to […]
Migrating to the cloud can be disruptive to existing processes. Moving storage to Azure isn’t a simple configuration change for SSIS packages. SSIS doesn’t have native connections for Azure. That doesn’t mean we need to completely re-engineer the process or change technology though. How can we take the simple package below and move to using […]
For this week’s short post I’ve been inspired by Reddit. There’s something a little sus with this snippet of SQL: The result? ඞ A very simplistic way to produce an illustration. Not quite as complex as other ways to create imagery with SQL Server…
One method for transferring a database to a different environment is using a Data-Tier Application – in the form of a DACPAC (for schema) or BACPAC (for schema and data). Trying to use this approach with multi-database solutions is a challenge though as Data-Tier Applications don’t play nicely with cross-database objects. Let’s look at how we can […]
Andy Brownsword 