Press "Enter" to skip to content

Month: August 2024

Transferring CLR Binaries from SQL Server to Azure SQL Managed Instance

Published 2024-08-15 by Kevin Feasel

Mihailo Joksimovic shows a use case for Azure SQL Managed Instance link:

Azure SQL Managed Instance link is a new feature enabling you to create a distributed availability group between your SQL Server and Azure SQL Managed Instance. It makes it super simple to connect on-prem. and cloud, providing near real-time replication speeds. Benefits are many and if you aren’t familiar, I’d strongly suggest you read our official blog post about it.

One great benefit for our use-case is that MI Link takes care of transferring the CLR assemblies for you! You can import your assemblies on your SQL Server instance, using all too familiar syntax (i.e. CREATE ASSEMBLY FROM ‘C:\path\to\assembly.dll’), and MI link will ensure that those same assemblies get transferred to cloud. Easy peasy and no need to deal with hex literals anymore.

Read on to learn more about this capability.

Comments closed

A Reference Architecture for Microsoft Fabric

Published 2024-08-15 by Kevin Feasel

James Serra draws boxes:

Microsoft Fabric uses a data lakehouse architecture, which means it does not use a relational data warehouse (with its relational engine and relational storage) and instead uses only a data lake to store data. Data is stored in Delta lake format so that the data lake acquires relational data warehouse-like features (check out my book that goes into much detail on this, or my video). Here is what a typical architecture looks like when using Fabric (click here for the .vsd):

Click through for the image as well as James’s explanation of the components.

Comments closed

Request: State of the Database Landscape Survey 2024

Published 2024-08-15 by Kevin Feasel

Grant Fritchey needs you:

Hello all! This post is nothing but a simple request. Please, if you have a few spare minutes, meander on over to this link and fill out the State of the Database Landscape Survey for 2024. Yeah, it’s for Redgate Software, my employer. But, really, it’s for everyone. Why is it for everyone? Because, every time we do one of these surveys, we don’t sit on the data, we share it. Here are the results from the 2023 survey, published earlier this year.

Yeah, but, I hear you opining, what does this really do for me? Well, let’s talk about it.

Check out the benefits and also fill out the survey.

Comments closed

Database Code is Code

Published 2024-08-15 by Kevin Feasel

Tom Zika speaks the truth:

This month’s invitation is from Mala Mahadevan (b) asking us how we manage the database code. Since this is my passion project, I have a few basic tips to share.

I have yet to see a perfect implementation but even a partial one benefits you. My experience is mostly with enterprise-scaled environments – large servers, minimal downtime, brownfield development and also mostly single-tenant.

But these concepts and building blocks should be generic enough.

Read on for Tom’s thoughts, as well as some pros and cons of the state-based versus migration-based approaches for database changes.

Comments closed

Backing SQL Server up to S3 Buckets

Published 2024-08-15 by Kevin Feasel

David Fowler backs up a database:

Way back in the mists of time I wrote a post on how to backup SQL server to an S3 bucket using TNTDrive, https://sqlundercover.com/2018/06/18/backup-your-on-premise-sql-server-directly-to-an-aws-s3-bucket/.

Back then, if we wanted to backup SQL to S3 we needed to use a third party tool. Since SQL 2022 things have changed and we’ve now got the option to backup directly to S3 in a similar way that we can backup to Azure BLOB store.

And, going one step further, you can also use PolyBase to read data from S3 buckets in SQL Server 2022.

Comments closed

String Concatenation in R

Published 2024-08-14 by Kevin Feasel

Steven Sanderson smooshes strings together:

String concatenation is a fundamental operation in data manipulation and cleaning. If you are working in R, mastering string concatenation will significantly enhance your data processing capabilities. This blog post will cover different ways to concatenate strings using base R, the stringr, stringi, and glue packages. Let’s go!

Read on for examples using paste(), paste0(), str_c(), stri_c(), and glue().

Comments closed

SQL ConstantCare Population Report for Summer 2024

Published 2024-08-14 by Kevin Feasel

Brent Ozar shares an update:

The short story: SQL Server 2022 finally saw some growth this quarter! Two years after the release, 1 in 10 SQL Servers is finally running the latest version.

The long story: ever wonder how fast people are adopting new versions of SQL Server, or what’s “normal” out there for SQL Server adoption rates? Let’s find out in the summer 2024 version of our SQL ConstantCare® population report.

As always, this is my reminder that we’re looking at a particular sample of the SQL Server population—users of Brent’s service—and will likely have some skew. That said, even within the context of this population, it is interesting to see these trends over time, and Brent covers that in the post.

Comments closed

Multi-Tenant Database Design Choices for SQL Server

Published 2024-08-14 by Kevin Feasel

Erik Darling finally has a blog post whose text I can quote versus a string of good videos to check out:

When you’re designing the back end of a database, people will have all sorts of ideas.

Normalization, partitioning, referential integrity, and, usually, trying to figure out what to do when you have more than one client.

If your application is user-focused (like Stack Overflow), you don’t have to struggle too much with the idea of isolation. But when your application is geared more towards supporting multiple entities that have one or more users, things change. Sort of like how Stack Overflow manages all the other Stack Network sites.

Were you to ask me which model I prefer, it would be every tenant getting their own database. Your other options are:

  • Everyone all mixed in together like gen-pop
  • Using separate schemas inside a single database

Definitely read what Erik has to say. My prior job was a hybrid multi-tenant environment: for the main transactional database, there were several dozen SQL Server instances. Each instance had anywhere from one to a few dozen copies of the transactional database, and each database hosted one or more customers’ data. There’s not a lot of tooling out there to support that kind of strategy, so we had to build a lot of it in-house. But that said, it did work out reasonably well without having hundreds or thousands of databases on a single instance.

Comments closed

Direct Query of Flat Files in Postgres via file_fdw

Published 2024-08-14 by Kevin Feasel

Semab Tariq uses a Postgres extension:

The file_fdw (Foreign Data Wrapper) is a PostgreSQL extension that lets you access data stored in flat files, like CSV files, as if they were regular tables in your PostgreSQL database. This is useful for integrating external data sources without needing to import the data directly into your database.

file_fdw is a contrib module, meaning it’s an additional feature included with PostgreSQL but not part of its core functionality. Contrib modules provide extra capabilities and enhancements beyond the core database system.

At first, I was going to write that the mechanism looks a lot like PolyBase in SQL Server. But in actuality, it’s more like a hybrid of PolyBase and OPENROWSET, as there’s no definition of external data sources or file formats, but there is the creation of an external (“foreign”) table.

Comments closed