SQL Server

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.

In this post, we’re going to walk through configuring Active Directory authentication for SQL Server on Linux. We will start by joining the Linux server to the domain, configuring SQL Server on Linux to communicate to the domain, and then use adutil to create our AD users and set up Kerberos for SQL Server login authentication.

Before getting started

First, let’s get some environment requirements set. We’ll need an Active Directory domain, a Linux host to install SQL Server on, some DNS records for that host, and the DNS client on that host configured for our environment. Here are the settings I used in this walk-through.

The intent of this post is a quick reference guide based on the recommendations made on Pure Storage Support page in the Microsoft Platform Guide. The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server on physical machines on Pure Storage.

Physical Host Configuration

Check with your hardware vendor to see if they publish a guide for SQL Server-specific configurations for their server platforms. Here are my thoughts on general guidance around CPU configuration and power management.

This post is a reference post for retrieving IO statistics for data and log files in SQL Server. We’ll look at where we can find IO statistics in SQL Server, query it to produce meaningful metrics, and discuss some key points when interpreting this data.

The Source DMF

The primary source for file latency data is the dynamic management function sys.dm_io_virtual_file_stats. The data in this DMF is per file. The query below joins with sys.master_files.

The intent of this post is a quick reference guide based on the recommendation made in “Architecting Microsoft SQL Server on VMware vSphere” April 2019 version. The target audience for this blog post is for SQL Server DBAs introducing them to the most impactful configurations and settings for running SQL Server in VMware.

For the explanations for each of these settings and how to configure the base VMware infrastructure, please read the “Architecting Microsoft SQL Server on VMware vSphere” guide and consult with your VMware administrators and experts.

This post will walk you through setting file permissions on database files copied into a container. The SQL Server process sqlservr running in containers runs as the non-privileged user mssql. The appropriate permissions on files are needed, so the SQL Server process has the proper access to any database files, log files, and backup files.

Start up a container

First up, let’s start up a container. Here’s we’re starting up SQL Server 2019 CU11 and attaching a Docker data volume for our persistent data.

In this blog post, we will walk through a few examples of configuring SQL Server in Kubernetes. First, we will create a Deployment for SQL Server, override the container’s command, and specify a Database Engine Service Startup Option. Second, we will create a Deployment for SQL Server using a ConfigMap to inject an mssql.conf configuration file.

Creating a SQL Server Deployment and Overriding the Container’s Command and Arguments

First up, let’s create a Deployment for SQL Server and override the container’s command specify a Database Engine Service Startup Option.

In this blog post, we will walk through a few examples of how to configure SQL Server in Docker Containers. First, we will configure a container at runtime by overriding the default docker command for the container and setting Database Engine Service Startup Options. Second, we’re going to inject a configuration file into our container to configure SQL Server. Let’s go!

Starting a Container with a Trace Flag

First up, let’s configure a container at runtime using Database Engine Service Startup Options. In Docker you can override the command of a container at the command line so we can start up a container with the correct executable and parameters. I’m configuring a trace flag by setting the correct parameters for the sqlservr executable. So when this container starts up it will start the sqlservr executable with the -T 3226 parameter. Let’s check out the code for this…