SQL Server

SQL Server 2022 introduces a new feature to enable application-consistent snapshot backups. TSQL Snapshot Backups enable the SQL Server to control the database quiesce without external tools. Using TSQL Snapshot backups enables instantaneous restores, independent of the size of data, for a database, group, or server backups, including point-in-time recovery.

When you use this feature, it freezes I/O. You’ll see a record like this in your error log when you execute the command ALTER DATABASE TestDB1 SET SUSPEND_FOR_SNAPSHOT_BACKUP = ON. This blog post will show you that the I/O freeze is just for write operations. You can continue to read from the database during this frozen state.

In this blog post, I will show you how to build a hello world container-based web application in the go programming language. The reason I want to do this is because I need a very small container image to do some testing in Kubernetes. I’ll also highlight some of the pitfalls I ran into to hopefully have you some time in your learnings.

Let’s build and test it locally first

Before you build a container-based application, you need an application. So let’s go ahead and build a simple hello world app in go, but running on our local system as a traditionally compiled program. I want to make sure my application works before I move onto the container build process. You’ll need to install the go programming language development tools. On a Mac, you can do that with brew install go. For other operating systems, check out the install page.

Introduction

Purity is the operating environment that runs Pure Storage products like FlashArray and Cloud Block Store. Starting in Purity 6.0, you can assign tags to objects. This post shows you how to perform some basic tagging operations for volumes.

What’s a Tag and Why Do I Care?

A tag is a key/value pair that can be attached to an object in FlashArray, like a volume or a snapshot. Using tags enables you to attach additional metadata to objects for classification, sorting, and searching. For example, you can assign a tag to a collection of volumes and then come along later and retrieve a listing of volumes that match a particular key or value. You can use tags to add application context to resources inside FlashArray. Specifically, in the examples in this blog post, I want to tag volumes with the names SQL Server Instances.

Last week I purchased a shiny new MacBook Air with an M2 processor. After I got all the standard stuff up and running, I set out to learn how to run SQL Server containers on this new hardware. This post shows you how to run SQL Server on Apple Silicon using colima.

Colima is a container runtime that runs a Linux VM on your Mac. This Linux VM runs using the Virtualization framework hypervisor native in MacOS. Your containers will run inside this virtual machine.

I’m pleased to announce that I will be presenting at this year’s PASS Summit. PASS Data Community Summit 2022 is the year’s largest gathering of data platform professionals in the US. This year I have a pre-conference workshop, a regular session, and a Learning Path session. Let’s dive into each.

Pre-Conference Workshop: Become a Hybrid Architect

My good friend Ben E. Weissman and I will teach a pre-conference workshop called “Become a Hybrid Architect” in this workshop we will cover the foundations needed to design and build a hybrid cloud strategy for your organization.

Introduction

In this post, I will walk you through how to set up MinIO, so you can use it to work with SQL Server 2022’s s3 object integrations. Working with s3 and SQL Server requires a valid and trusted TLS certificate. This can be a pain for some users and environments. So I’m writing this post so you can get off the ground running with this new feature set in SQL Server 2022. The certificate we’re working with here is self-signed. You could get a real certificate for your environment, and that’s encouraged. But this walk-through intends to get you up and running fast so that you can test out SQL Server’s s3 object integrations. We’re using MinIO’s free GNU AGPL v3 edition and running it in a docker container for our s3 compatible object storage and SQL Server 2022 CTP 2.0, which is also running in a container.

Introducing S3 in SQL Server 2022

S3 compatible object storage integration is a new feature introduced in SQL Server 2022. There are two significant areas where SQL Server leverages this: backup and restore and data virtualization. This article will focus on getting started with using S3 compatible object storage for backups. Now let’s unpack that phrase ‘S3 compatible object storage’ a bit. AWS Simple Cloud Storage Service (S3) is a storage service AWS provides in their cloud. That platform’s REST API is available for others, including my company, Pure Storage, to build their own s3 compatible object storage platforms. And on at Pure Storage we have s3 available on our scale-out File and Object Platform FlashBlade. This means you can take advantage of s3 object storage anywhere you like, outside AWS’s cloud infrastructure.

Background

If you’ve been using Availability Groups, you’re familiar with the replica seeding (sometimes called initializing, preparing or data sychronization) process. Seeding is a size of data operation, copying data from a primary replica to one or more secondary replicas. This is required before joining a database to an Availability Group. You can seed a replica with backup and restore or automatic seeding, each with its own challenges. Regardless of which method you use, the seeding operation can take a long amount of time. The time it takes to seed a replica is based on the size of the database, the speed of the network, and storage. If you have multiple replicas seeding all of them is N times the fun!

I’m proud to announce that I will be speaking at SQLBits! I had the absolute pleasure of speaking at SQLBits in the past, both in person and virtual, and experienced first hand how great this event is and cannot wait to get back and speak again (in person)! And this year, I have several sessions with some of my best friends on our data community!!! One on building and deploying container based applications in Kubernetes and the other on deploying SQL Server in Kubernetes

This blog post shows you how NTFS stores data, what the NTFS Allocation Unit means, and how SQL Server performs IOs of variable size.

How NTFS Stores Data on Disk

A Master File Table (MFT) is the data structure that describes files and directories on NTFS. In Figure 1, you can see an MTF record has several sections describing the metadata about the file and pointers to blocks that make up the file. A block, also referred to as a cluster in Windows, is an abstraction over one or more physical structures (sectors or pages depending on the media) presented by the underlying disk. A block/cluster is also the atomic allocation unit from a file system and has a configurable size. On NTFS, this is referred to as the NTFS Allocation Unit Size and is a configurable attribute of the file system. By default, it is 4KB and can be as large as 2MB. Since a block is a unit of allocation, if a file is between 1 byte and the file system’s allocation unit size, it will take up exactly one block/cluster on the file system. As the file grows, more blocks/clusters are allocated to represent the file. The MFT data structure tracks which blocks make up a file. The block allocator of the file system will try to ensure blocks are physically adjacent on the disk and groups them together in runs.