Chapter 2
Introducing database server components
In this chapter, we cover the components that make up a typical database infrastructure. This chapter is introductory; the chapters that follow provide more detail about designing, implementing, and provisioning databases.
Although Microsoft SQL Server is new to Linux, Microsoft has, as much as possible, crafted it to work the same way that it does on Windows. We highlight places where there are differences.
No matter which configurations you end up using, there are four basic parts to a database infrastructure:
Memory
Processor
Permanent storage
Network
We also touch on a couple of high availability offerings, including improvements to availability groups in SQL Server 2017. We then look at an introduction to security concepts, including ways to access instances of SQL Server on-premises with Windows and Linux, and Microsoft Azure SQL Database. Finally, we take a brief look at virtualization.
Memory
SQL Server is designed to use as much memory as it needs, and as much as you give it. By default, the upper limit of memory that SQL Server can access, is limited only by the physical Random Access Memory (RAM) available to the server, or the edition of SQL Server you’re running, whichever is lower.
Understanding the working set
The physical memory made available to SQL Server by the operating system (OS), is called the working set. This working set is broken up into several sections by the SQL Server memory manager, the two largest and most important ones being the buffer pool and the procedure cache (also known as the plan cache).
In the strictest sense, “working set” applies only to physical memory. However, as we will see shortly, the buffer pool extension blurs the lines.
We look deeper into default memory settings in Chapter 3, in the section, “Configuration settings.”
Caching data in the buffer pool
For best performance, you cache data in memory because it’s much faster to access data directly from memory than storage.
The buffer pool is an in-memory cache of 8-KB data pages that are copies of pages in the database file. Initially the copy in the buffer pool is identical, but changes to data are applied to this buffer pool copy (and the transaction log) and then asynchronously applied to the data file.
When you run a query, the Database Engine requests the data page it needs from the Buffer Manager, as depicted in Figure 2-1. If the data is not already in the buffer pool, a page fault occurs (an OS feature that informs the application that the page isn’t in memory). The Buffer Manager fetches the data from the storage subsystem and writes it to the buffer pool. When the data is in the buffer pool, the query continues.
The buffer pool is usually the largest consumer of the working set because that’s where your data is. If the amount of data requested for a query exceeds the capacity of the buffer pool, the data pages will spill to a drive, either using the buffer pool extension or a portion of TempDB.
The buffer pool extension makes use of nonvolatile storage to extend the size of the buffer pool. It effectively increases the database working set, forming a bridge between the storage layer where the data files are located and the buffer pool in physical memory.
For performance reasons, this should be solid-state storage, directly attached to the server.
To see how to turn on the buffer pool extension, read the section “Configuration settings” in Chapter 3. To learn more about TempDB, read the section “Physical database architecture,” also in Chapter 3.
Caching plans in the procedure cache
Generally speaking, the procedure cache is smaller than the buffer pool. When you run a query, the Query Optimizer compiles a query plan to explain to the Database Engine exactly how to run the query. To save time, it keeps a copy of that query plan so that it doesn’t need to compile the plan each time the query runs. It is not quite as simple as this, of course (plans can be removed, and trivial plans are not cached, for instance), but it’s enough to give you a basic understanding.
The procedure cache is split into various cache stores by the memory manager, and it’s also here where you can see if there are single-use query plans that are polluting memory.
For more information about cached execution plans, read Chapter 9 or visit https://blogs.msdn.microsoft.com/blogdoezequiel/2014/07/30/too-many-single-use-plans-now-what/.
Lock pages in memory
Turning on the Lock pages in memory (LPIM) policy means that Windows will not be able to trim (reduce) SQL Server’s working set.
Locking pages in memory ensures that Windows memory pressure cannot rob SQL Server of resources or shunt SQL Server memory into the Windows Server system page file, dramatically reducing performance. Windows doesn’t “steal” memory from SQL Server flippantly; it is done in response to memory pressure on the Windows Server. Indeed, all applications can have their memory affected by pressure from Windows.
On the other hand, without the ability to relieve pressure from other applications’ memory demands or a virtual host’s memory demands, LPIM means that Windows cannot deploy enough memory to remain stable. Because of this concern, LPIM cannot be the only method to use to protect SQL Server’s memory allocation.
The controversy of the topic is stability versus performance, in which the latter was especially apparent on systems with limited memory resources and older operating systems. On larger servers with operating systems since Windows Server 2008, and especially virtualized systems, there is a smaller but nonzero need for this policy to insulate SQL Server from memory pressure.
The prevailing wisdom is that the LPIM policy should be turned on by default for SQL Server 2017, provided the following:
The server is physical, not virtual. See the section “Sharing more memory than we have (overcommit)” later in this chapter.
Physical RAM exceeds 16 GB (the OS needs a working set of its own).
Max Server Memory has been set appropriately (SQL Server can’t use everything it sees).
The MemoryAvailable Mbytes performance counter is monitored regularly (to keep some memory free).
If you would like to read more, Jonathan Kehayias explains this thinking in a Simple Talk article (https://www.simple-talk.com/sql/database-administration/great-sql-server-debates-lock-pages-in-memory/).
Editions and memory limits
Since SQL Server 2016 Service Pack 1, many Enterprise edition features have found their way into the lower editions. Ostensibly, this was done to allow software developers to have far more code that works across all editions of the product.
Although some features are still limited by edition (high availability, for instance), features such as Columnstore and In-Memory OLTP are turned on in every edition, including Express. However, only Enterprise edition can use all available physical RAM for these features. Other editions are limited.
Central Processing Unit
The Central Processing Unit, or CPU, and often called the “brain” of a computer, is the most important part of a system. CPU speed is measured in hertz (Hz), or cycles per second. Current processor speed is measured in GHz, or billions of cycles per second.
Modern systems can have more than one CPU, and each CPU in turn can have more than one CPU core (which, in turn, might be split up into virtual cores).
For a typical SQL Server workload, single-core speed matters. It is better to have fewer cores with higher clock speeds than more cores with lower speeds, especially for non-Enterprise editions.
With systems that have more than one CPU, each CPU might be allocated its own set of memory, depending on the physical motherboard architecture.
Simultaneous multithreading
Some CPU manufacturers have split their physical cores into virtual cores to try to eke out even more performance. They do this via a feature called simultaneous multithreading (SMT). Intel calls this Hyper-Threading, so when you buy a single Intel® Xeon® CPU with 20 physical cores, the OS will see 40 virtual cores, because of SMT.
SMT becomes especially murky with virtual machines (VMs) because the guest OS might not have any insight into the physical versus logical core configuration.
SMT should be turned on for physical database servers. For virtual environments, you need to take care to ensure that the virtual CPUs are allocated correctly. See the section “Abstracting hardware away with virtualization” later in this chapter.
Non-Uniform Memory Access
CPUs are the fastest component of a system, and they spend a lot of time waiting for data to come to them. In the past, all CPUs would share one bank of RAM on a motherboard, using a shared bus. This caused performance problems as more CPUs were added because only one CPU could access the RAM at a time.
Multi-Channel Memory Architecture tries to resolve this by increasing the number of channels between CPUs and RAM, to reduce contention during concurrent access.
A more practical solution is for each CPU to have its own local physical RAM, situated close to each CPU socket. This configuration is called Non-Uniform Memory Access (NUMA). The advantages are that each CPU can access its own RAM, making processing much faster. However, if a CPU needs more RAM than it has in its local set, it must request memory from one of the other CPUs in the system (called foreign memory access), which carries a performance penalty.
SQL Server is NUMA-aware. In other words, if the OS recognizes a NUMA configuration at the hardware layer, where more than one CPU is plugged in, and each CPU has its own set of physical RAM (see Figure 2-2), SQL Server will split its internal structures and service threads across each NUMA node.
Since SQL Server 2014 Service Pack 2, the Database Engine automatically configures NUMA nodes at an instance level, using what it calls soft-NUMA. If more than eight CPU cores are detected (including SMT cores), soft-NUMA nodes are created automatically in memory.
Disable power saving everywhere
Modern systems can use power saving settings to reduce the amount of electricity used by a server. Although this is good for the environment, it is bad for query performance because the CPU core speed might be reduced to save energy.
For all operating systems running SQL Server, turn on High Performance at the OS level, and double-check that High Performance is set at the BIOS level, as well. For dedicated VM hosts, this will require downtime to make the change.
Storing your data
When data is not in memory, it is at rest, and must be saved somewhere. Storage technology has evolved rapidly over the past few years, so we no longer think of storage as a mechanical hard drive containing one or more spinning metal disks with a magnetic surface. But, old habits die hard, and colloquially we still refer to a nonvolatile storage subsystem as “the disk,” even if it might take another form. In this book, however, we refer to it as a “drive.”
In the context of SQL Server, the storage subsystem should have low latency, so that when the database engine accesses the drive to perform reads and writes, those reads and writes should complete as quickly as possible. In the following list, we present some commonly used terms with respect to storage devices.
Drive. The physical storage device. This might be a mechanical drive, a solid-state drive with the same form-factor as a mechanical drive, or a card that plugs directly into the motherboard.
Volume. A logical representation of storage, as viewed by the OS. This might be one drive, part of a drive, or a logical section of a storage array. On Microsoft Windows, a volume usually gets its own drive letter or mount point.
Latency. Measured in milliseconds, latency is how long it takes for data to be read from a drive (seconds per read), and written to a drive (seconds per write).
IOPS. Input/output operations per second, or IOPS, is the number of reads and writes per second. A storage device might have differing performance depending on whether the IOPS are sequential or random. IOPS are directly related to latency by means of the queue depth.
Queue depth. The number of outstanding read and write requests in a storage device’s request queue. The deeper the queue depth, the faster the drive.
SQL Server performance is directly related to storage performance. The move toward virtualization and shared storage arrays has placed more emphasis on random data access patterns. Low latency and high random IOPS will thus benefit the average SQL Server workload.
In the next two chapters, we go into more detail about the preferred storage configuration for SQL Server.
Types of storage
Nonvolatile storage can be split up into two main areas: mechanical and solid-state.
Mechanical hard drives
Traditional spinning disks have a built-in latency, called seek time, due to their shape and physical nature. The read/write head is mounted on an arm that must scan the surface of the disk as it spins, seeking a particular area to perform the I/O operation. If the data on the spinning disk is fragmented, it can take longer to access because the head must skip around, finding data or free space.
The standard interface for mechanical drives is Serial ATA (SATA) or Serial Attached SCSI (SAS).
As spinning disks increase in capacity, the tracks between data become narrower, which causes performance to decrease, and increases the likelihood of mechanical failure or data corruption. The limits are pushed because of the rotational energy in the disk itself, so there is a physical speed limit to the motor.
In other words, mechanical disks grow bigger but slower and more prone to failure.
Solid-state drives
Solid-state technology, which makes use of flash memory, eliminates seek time entirely because the path to each cell where the data is stored is almost instantaneous. This is what makes solid-state storage so much faster than mechanical storage.
Solid-state storage devices can take many different forms. The most common in consumer devices is a 2.5-inch enclosure with a SATA interface, which was common with mechanical laptop drives. This accommodates a drop-in replacement of mechanical storage.
In server architecture, however, flash memory can take several forms. For local storage, they make use of the Peripheral Component Interconnect Express (PCIe) interface and plug directly into the motherboard. An example of this is Non-Volatile Memory Express (NVMe).
As the technology evolves, the performance will only improve as capacity grows. Solid state is not perfect though; data can be written to a particular cell only a certain number of times before it fails. You might have experienced this yourself with thumb drives, which tend to fail after heavy usage. Algorithms to balance writes across cells, called wear-leveling, help to extend the lifespan of a solid-state device.
Another problem with flash memory is write-amplification. On a mechanical drive, if a file is overwritten, the previous file is marked for deletion, but is not actually deleted from the disk surface. When the drive needs to write to that area again, it overwrites the location without removing what was there before.
Solid-state drives must erase the location in question before writing the new data, which has a performance impact. The size of the cells might also require a larger area to be erased than the file itself (if it is a small file), which compounds the performance impact. Various techniques exist to mitigate write amplification, but this does reduce the lifespan of flash memory.
The performance problems with mechanical disks, and the lifespan problems with both mechanical and solid-state drives, can be mitigated by combining them into drive arrays, to reduce the risk of failure by balancing the load and increase performance.
Configuring the storage layer
Nonvolatile storage can stand alone, in the form of Direct-Attached Storage, or be combined in many ways to provide redundancy or consolidation, perhaps even offering different levels of performance in order to manage costs better. For example, archive data might not need to be stored on the fastest available drive if it is accessed infrequently.
Direct-Attached Storage
Direct-Attached Storage (DAS) is plugged directly into the system accessing it. Also called local storage, it can comprise independent mechanical hard drives, solid-state drives, tape drives for backups, CD and DVD-ROM drives, or even enclosures containing storage arrays.
DAS has a lower latency than a Storage-Area Network or Network-Attached Storage (more on these later in the chapter) because there is no network to traverse between the system and the storage. However, it cannot be shared with other systems, unless the local file system is shared across the network using a protocol such as Server Message Block (SMB) 3.0.
For SQL Server, DAS comprising flash storage (solid-state) is preferred for TempDB, which is also supported (and recommended) in a Failover Cluster Instance. You can also use DAS for the buffer pool extension.
To see how you should best configure TempDB, see the section “Configuration settings” in Chapter 3.
Storage arrays and RAID
Combining drives in an enclosure with a controller to access each drive, without any thought to redundancy or performance, is called JBOD (colloquially, “just a bunch of disks”). These drives might be accessed individually or combined into a single volume.
When done correctly, combining drives into an array can increase overall performance and/or lower the risk of data loss should one or more of the drives in the array fail. This is called Redundant Array of Independent Disks (RAID).
RAID offers several levels of configuration, which trade redundancy for performance. More redundancy means less raw capacity for the array, but this can reduce data loss. Faster performance can bring with it data loss.
Striping without parity (RAID 0) uses multiple drives to improve raw read/write performance, but with zero redundancy. If one drive fails, there is significant chance of catastrophic data loss across the entire array. JBOD configurations that span across drives fall under this RAID level.
Mirroring (RAID 1) uses two drives that are written to simultaneously. Although there is a slight write penalty because both drives must save their data at the same time, and one might take longer than the other, the read performance is nearly double that of a single drive because both drives can be read in parallel (with a small overhead caused by the RAID controller selecting the drive and fetching the data). Usable space is 50 percent of raw capacity, and only one drive in the array can be lost and still have all data recoverable.
Striping with parity (RAID 5) requires an odd number of three or more drives, and for every single write, one of the drives is randomly used for parity (a checksum validation). There is a larger write penalty because all drives must save their data and parity must be calculated and persisted. If a single drive is lost from the array, the other drives can rebuild the contents of the lost drive, based on the parity, but it can take some time to rebuild the array. Usable space is calculated as the number of drives minus one. If there are three drives in the array, the usable space is the sum of two of those drives, with the space from the third used for parity (which is evenly distributed over the array). Only one drive in the array can be lost and still have full data recovery.
Combinations of the base RAID configurations are used to provide more redundancy and performance, including RAID 1+0 (also known as RAID 10), RAID 0+1, and RAID 5+0 (also known as RAID 50).
In RAID 1+0, two drives are configured in a mirror (RAID 1) for redundancy, and then each mirror is striped together (RAID 0) for performance reasons.
In RAID 0+1, the drives are striped first (RAID 0), and then mirrored across the entire RAID 0 set (RAID 1). Usable space for RAID 0+1 and 1+0 is 50 percent of the raw capacity.
To ensure full recovery from failure in a RAID 1+0 or 0+1 configuration, an entire side of the mirror can be lost, or only one drive from each side of the mirror can be lost.
In RAID 5+0, a number of drives (three or more) is configured in a RAID 5 set, which is then striped (with no parity) with at least one other RAID 5 set of the same configuration. Usable space is (x – 1) / y, where x is the number of drives in each nested RAID 5 set, and y is the number of RAID 5 sets in this array. If there are nine drives, six of them are usable. Only one drive from each RAID 5 set can be lost with full recovery possible. If more than one drive in any of the RAID 5 sets is lost, the entire 5+0 array is lost.
SQL Server requires the best performance from a storage layer as possible. When looking at RAID configurations, RAID 1+0 offers the best performance and redundancy.
Centralized storage with a Storage-Area Network
A Storage-Area Network (SAN) is a network of storage arrays that can comprise tens, hundreds, or even thousands of drives (mechanical or solid-state) in a central location, with one or more RAID configurations, providing block-level access to storage. This reduces wasted space, and allows easier management across multiple systems, especially for virtualized environments.
Block-level means that the OS can read or write blocks of any size and any alignment. This offers the OS a lot of flexibility in making use of the storage.
You can carve the total storage capacity of the SAN into logical unit numbers (LUNs), and each LUN can be assigned to a physical or virtual server. You can move these LUNs around and resize them as required, which makes management much easier than attaching physical storage to a server.
The disadvantage of a SAN is that you might be at the mercy of misconfiguration or a slow network. For instance, the RAID might be set to a level that has poor write performance, or the blocks of the storage are not aligned appropriately.
Storage administrators might not understand specialized workloads like SQL Server, and choose a performance model that satisfies the rest of the organization to reduce administration overhead but which penalizes you.
Network-Attached Storage
Network-Attached Storage (NAS), is usually a specialized hardware appliance connected to the network, typically containing an array of several drives, providing file-level access to storage.
Unlike the SAN’s block-level support, NAS storage is configured on the appliance itself, and file sharing protocols (such as SMB, Common Internet File System [CIFS] and Network File System [NFS]) are used to share the storage over the network.
NAS appliances are fairly common because they provide access to shared storage at a much lower monetary cost than a SAN. You should keep in mind security considerations regarding file-sharing protocols.
Storage Spaces
Windows Server 2012 and later support Storage Spaces, which is a way to manage local storage in a more scalable and flexible way than RAID.
Instead of creating a RAID set at the storage layer, Windows Server can create a virtual drive at the OS level. It might use a combination of RAID levels, and you can decide to combine different physical drives to create performance tiers.
For example, a server might contain 16 drives. Eight of them are spinning disks, and eight are solid state. You can use Storage Spaces to create a single volume with all 16 drives, and keep the active files on the solid-state portion, increasing performance dramatically.
SMB 3.0 file share
SQL Server supports storage located on a network file share that uses the SMB 3.0 protocol or higher because it is now fast and stable enough to support the storage requirements of the Database Engine (performance and resilience). This means that you can build a Failover Cluster Instance (see the section on this later in the chapter) without shared storage such as a SAN.
Network performance is critically important, though, so we recommend a dedicated and isolated network for the SMB file share, using network interface cards that support Remote Direct Memory Access (RDMA). This allows the SMB Direct feature in Windows Server to create a low-latency, high-throughput connection using the SMB protocol.
SMB 3.0 might be a feasible option for smaller networks with limited storage capacity and a NAS, or in the case of a Failover Cluster Instance without shared storage. For more information, read Chapter 12.
Connecting to SQL Server over the network
We have covered a fair amount about networking just discussing the storage layer, but there is far more to it. In this section, we look at what is involved when accessing the Database Engine over a network, and briefly discuss Virtual Local-Area Networks.
Unless a SQL Server instance and the application accessing it is entirely self-contained, database access is performed over one or more network interfaces. This adds complexity with authentication, given that malicious actors might be scanning and modifying network packets in flight.
SQL Server 2017 requires strict rules with respect to network security, which means that older versions of the connectors or protocols used by software developers might not work as expected.
Transport Security Layer and its forerunner, Secure Sockets Layer, (together known as TLS/SSL, or just SSL), are methods that allow network traffic between two points to be encrypted. (For more information, see Chapter 7.) Where possible, you should use newer libraries that support TLS encryption. If you cannot use TLS to encrypt application traffic, you should use IPSec, which is configured at the OS level.
Protocols and ports
Connections to SQL Server are made over the Transport Control Protocol (TCP), with port 1433 as the default port for a default instance. Some of this is covered in Chapter 1, and again in Chapter 7. Any named instances are assigned random ports by the SQL Server Configuration Manager, and the SQL Browser service coordinates any connections to named instances. It is possible to assign static TCP ports to named instances by using the Configuration Manager.
There are ways to change the default port after SQL Server is installed, through the SQL Server Configuration Manager. We do not recommend changing the port, however, because it provides no security advantage to a port scanner, but some network administration policies require it.
Networking is also the foundation of cloud computing. Aside from the fact that the Azure cloud is accessed over the internet (itself a network of networks), the entire Azure infrastructure, which underlies both infrastructure-as-a-service (virtual machines with Windows or Linux running SQL Server) and platform-as-a-service (Azure SQL Database) offerings, is a virtual fabric of innumerable components tied together with networking.
Added complexity with Virtual Local-Area Networks
A Virtual Local-Area Network (VLAN) gives network administrators the ability to logically group machines together even if they are not physically connected through the same network switch. It makes it possible for servers to share their resources with one another over the same physical LAN, without interacting with other devices on the same network.
VLANs work at a very low level (the data link layer, or OSI Layer 2), and are configured on a network switch. A port on the switch might be dedicated to a particular VLAN, and all traffic to and from that port is mapped to a particular VLAN by the switch.
High availability concepts
With each new version of Windows Server, terminology and definitions tend to change or adapt according to the new features available. With SQL Server now supported on Linux, it is even more important to get our heads around what it means when we discuss high availability.
At its most basic, high availability (HA) means that a service offering of some kind (for example, SQL Server, a web server, an application, or a file share) will survive an outage of some kind, or at least fail predictably to a standby state, with minimal loss of data and minimal downtime.
Everything can fail. An outage might be caused by a failed hard drive, which could in turn be a result of excessive heat, excessive cold, excessive moisture, or a datacenter alarm that is so loud that its vibrational frequency damages the internal components and causes a head crash.
You should be aware of other things that can go wrong, as noted in the list that follows; this list is certainly not exhaustive, but it’s incredibly important to understand that assumptions about hardware, software, and network stability are a fool’s errand:
A failed network interface card
A failed RAID controller
A power surge or brownout causing a failed power supply
A broken or damaged network cable
A broken or damaged power cable
Moisture on the motherboard
Dust on the motherboard
Overheating caused by a failed fan
A faulty keyboard that misinterprets keystrokes
Failure due to bit rot
Failure due to a bug in SQL Server
Failure due to poorly written code in a file system driver that causes drive corruption
Capacitors failing on the motherboard
Insects or rodents electrocuting themselves on components (this smells really bad)
Failure caused by a fire suppression system that uses water instead of gas
Misconfiguration of a network router causing an entire geographical region to be inaccessible
Failure due to an expired SSL or TLS certificate
Running a
DELETEorUPDATEstatement without aWHEREclause (human error)
Why redundancy matters
Armed with the knowledge that everything can fail, you should build in redundancy where possible. The sad reality is that these decisions are governed by budget constraints. The amount of money available is inversely proportional to the amount of acceptable data loss and length of downtime. For business-critical systems, however, uptime is paramount, and a highly available solution will be more cost effective than being down, considering the cost-per-minute to the organization.
It is nearly impossible to guarantee zero downtime with zero data loss. There is always a trade-off. The business decides on that trade-off, based on resources (equipment, people, money), and the technical solution is in turn developed around that trade-off. The business drives this strategy using two values called the Recovery Point Objective and Recovery Time Objective, which are defined in a Service-Level Agreement (SLA).
Recovery Point Objective
A good way to think of Recovery Point Objective (RPO) is “How much data are you prepared to lose?” When a failure occurs, how much data will be lost between the last transaction log backup and the failure? This value is usually measured in seconds or minutes.
Recovery Time Objective
The Recovery Time Objective (RTO) is defined as how much time is available to bring the environment up to a known and usable state after a failure. There might be different values for HA and disaster recovery scenarios. This value is usually measured in hours.
Disaster recovery
HA is not disaster recovery (DR). They are often grouped under the same heading (HA/DR), mainly because there are shared technology solutions for both concepts, but HA is about keeping the service running, whereas DR is what happens when the infrastructure fails entirely. DR is like insurance: you don’t think you need it until it’s too late. HA costs more money, the shorter the RPO.
Clustering
Clustering is the connecting of computers (nodes) in a set of two or more nodes, that work together and present themselves to the network as one computer.
In most cluster configurations, only one node can be active in a cluster. To ensure that this happens, a quorum instructs the cluster as to which node should be active. It also steps in if there is a communication failure between the nodes.
Each node has a vote in a quorum. However, if there is an even number of nodes, to ensure a simple majority an additional witness must be included in a quorum to allow for a majority vote to take place.
Windows Server Failover Clustering
As Microsoft describes it:
“Failover clusters provide high availability and scalability to many server workloads. These include server applications such as Microsoft Exchange Server, Hyper-V, Microsoft SQL Server, and file servers. The server applications can run on physical servers or virtual machines. [Windows Server Failover Clustering] can scale to 64 physical nodes and to 8,000 virtual machines.” (https://technet.microsoft.com/library/hh831579(v=ws.11).aspx).
The terminology here matters. Windows Server Failover Clustering is the name of the technology that underpins a Failover Cluster Instance (FCI), where two or more Windows Server Failover Clustering nodes (computers) are connected together in a Windows Server Failover Clustering resource group and masquerade as a single machine behind a network endpoint called a Virtual Network Name (VNN). A SQL Server service that is installed on an FCI is cluster-aware.
Linux failover clustering with Pacemaker
Instead of relying on Windows Server Failover Clustering, SQL Server on a Linux cluster can make use of any cluster resource manager. Microsoft recommends using Pacemaker because it ships with a number of Linux distributions, including Red Hat and Ubuntu.
Resolving cluster partitioning with quorum
Most clustering technologies make use of the quorum model, to prevent a phenomenon called partitioning, or “split brain.” If there is an even number of nodes, and half of these nodes go offline from the view of the other half of the cluster, and vice versa, you end up with two halves thinking that the cluster is still up and running, and each with a primary node (split brain).
Depending on connectivity to each half of the cluster, an application continues writing to one half of the cluster while another application writes to the other half. A best-case resolution to this scenario would require rolling back to a point in time before the event occurred, which would cause loss of any data written after the event.
To prevent this, each node in a cluster shares its health with the other nodes using a periodic heartbeat. If more than half do not respond in a timely fashion, the cluster is considered to have failed. Quorum works by having a simple majority vote on what constitutes “enough nodes.”
In Windows Server Failover Clustering, there are four types of majority vote: Node, Node and File Share, Node and Disk, and Disk Only. In the latter three types, a separate witness is used, which does not participate in the cluster directly. This witness is given voting rights when there is an even number of nodes in a cluster, and therefore a simple majority (more than half) would not be possible.
Always On FCIs
You can think of a SQL Server FCI as two or more nodes with shared storage (usually a SAN because it is most likely to be accessed over the network).
On Windows Server, SQL Server can take advantage of Windows Server Failover Clustering to provide HA (the idea being minimal downtime) at the server-instance level, by creating an FCI of two or more nodes. From the network’s perspective (application, end users, and so on), the FCI is presented as a single instance of SQL Server running on a single computer, and all connections point at the VNN.
When the FCI starts, one of the nodes assumes ownership and brings its SQL Server instance online. If a failure occurs on the first node (or there is a planned failover due to maintenance), there are at least a few seconds of downtime, during which the first node cleans up as best it can, and then the second node brings its SQL Server instance online. Client connections are redirected to the new node after the services are up and running.
On Linux, the principle is very similar. A cluster resource manager such as Pacemaker manages the cluster, and when a failover occurs, the same process is followed from SQL Server’s perspective, in which the first node is brought down and the second node is brought up to take its place as the owner. The cluster has a virtual IP address, just as on Windows. You must add the virtual network name manually to the DNS server.
You can read more about setting up a Linux cluster in Chapter 11.
FCIs are supported on SQL Server Standard edition, but are limited to two nodes.
The versatility of Log Shipping
SQL Server Transaction Log Shipping is an extremely flexible technology to provide a relatively inexpensive and easily managed HA and DR solution.
The principle is as follows: a primary database is in either the Full or Bulk Logged recovery model, with transaction log backups being taken regularly every few minutes. These transaction log backup files are transferred to a shared network location, where one or more secondary servers restore the transaction log backups to a standby database.
If you use the built-in Log Shipping Wizard in SQL Server Management Studio, on the Restore tab, click Database State When Restoring Backups, and then choose the No Recovery Mode or Standby Mode option (https://docs.microsoft.com/sql/database-engine/log-shipping/configure-log-shipping-sql-server).
If you are building your own log shipping solution, remember to use the RESTORE feature with NORECOVERY, or RESTORE with STANDBY.
If a failover occurs, the tail of the log on the primary server is backed up the same way (if available—this guarantees zero data loss of committed transactions), transferred to the shared location, and restored after the latest regular transaction logs. The database is then put into RECOVERY mode (which is where crash recovery takes place, rolling back incomplete transactions and rolling forward complete transactions).
As soon as the application is pointed to the new server, the environment is back up again with zero data loss (tail of the log was copied across) or minimal data loss (only the latest shipped transaction log was restored).
Log Shipping is a feature that works on all editions of SQL Server, on Windows and Linux. However, because Express edition does not include the SQL Server Agent, Express can be only a witness, and you would need to manage the process through a separate scheduling mechanism. You can even create your own solution for any edition of SQL Server, using Azure Blob Storage and AzCopy.exe, for instance.
Always On availability groups
As alluded to previously, this is generally what people mean when they incorrectly say “Always On.” However, it’s official name is Always On availability groups. In shorthand, you can refer simply to these as availability groups (or AGs).
What is an availability group, anyway? In the past, SQL Server offered database mirroring and failover clustering as two distinct HA offerings. However, with database mirroring officially deprecated since SQL Server 2012, coinciding with the introduction of availability groups, it is easier to think of availability groups as a consolidation of these two offerings as well as Log Shipping thrown in for good measure.
Availability groups provide us with the ability to keep a discrete set of databases highly available across one or more nodes in a cluster. They work at the database level, as opposed to an entire server-instance level, like FCIs do.
Unlike the cluster-aware version of SQL Server, when it installed as part of an FCI, SQL Server on an availability group is installed as a standalone instance.
An availability group (on Windows Server through Windows Server Failover Clustering, and on Linux through a cluster resource manager like Pacemaker) operates at the database level only. As depicted in Figure 2-3, it is a set of one or more databases in a group (an availability replica) that are replicated (using Log Shipping) from a primary replica (there can be only one primary replica), to a maximum of eight secondary replicas, using synchronous or asynchronous data synchronization. Let’s take a closer look at each of these:
Synchronous data synchronization. The log is hardened (the transactions are committed to the transaction log) on every secondary replica before the transaction is committed on the primary replica. This guarantees zero data loss, but with a potentially significant performance impact. It can be costly to reduce network latency to a point at which this is practical for highly transactional workloads.
Asynchronous data synchronization. The transaction is considered committed as soon as it is hardened in the transaction log on the primary replica. If something were to happen before the logs are hardened on all of the secondary replicas, there is a chance of data loss, and the recovery point would be the most recently committed transaction that made it successfully to all of the secondary replicas. With delayed durability turned on, this can result in faster performance, but higher risk of data loss.
You can use read-only secondary replicas for running reports and other operations that reduce the load on the primary replica. This also includes backups and database consistency checks, but you must also perform these on the primary replica when there is a low-usage period or planned maintenance window.
If the primary replica fails, one of the secondary replicas is promoted to the primary, with a few seconds of downtime while the databases run through crash recovery, and minimal data loss.
Read-scale availability groups
SQL Server 2017 introduces a new architecture that allows for multiple read-only secondary replicas, but does not offer HA. The major difference is that a read-scale availability group does not have a cluster resource manager.
What this allows is reduced contention on a business-critical workload by using read-only routing or connecting directly to a readable secondary replica, without relying on a clustering infrastructure on Windows or Linux.
For more information, go to Microsoft Docs at https://docs.microsoft.com/sql/database-engine/availability-groups/windows/read-scale-availability-groups.
Distributed availability groups
Instead of having an availability group on one cluster, a distributed availability group can span two separate availability groups, on two separate clusters (Windows Server Failover Clustering or Linux, each cluster can run on a different OS) that are geographically separated. Provided that these two availability groups can communicate with each other, you can configure them in a distributed availability group. This allows a more flexible DR scenario, plus it makes possible multisite replicas in geographically diverse areas.
The main difference from a normal availability group, is that the configuration is stored in SQL Server, not the underlying cluster. With a distributed availability group, only one availability group can perform data modification at any time, even though both availability groups have a primary replica. To allow another availability group to write to its primary replica database requires a manual failover, using FORCE_FAILOVER_ALLOW_DATA_LOSS.
Basic availability groups
SQL Server Standard edition supports a single-database HA solution, with a limit of two replicas. The secondary replica does not allow backups or read access. Although these limits can be frustrating, they do make it possible to offer another kind of HA offering with Standard edition.
For more information, go to Microsoft Docs at https://docs.microsoft.com/sql/database-engine/availability-groups/windows/basic-availability-groups-always-on-availability-groups.
Improve redundancy and performance with NIC teaming
NIC teaming, also known as link aggregation, uses two or more network interfaces to improve redundancy (failover), or increase the available bandwidth (bandwidth aggregation). In the Microsoft space, this is also called load balancing and failover support (LBFO). NIC teaming can work at the network-card level, where two or more NICs are combined into a virtual NIC on a server, or on a network switch level, where two or more network ports are aggregated.
When traffic encounters the aggregated network ports, the switch will know which port is the least busy at that time, and direct the packet to one of the other ports. This is how network load balancing works. There might be one or more servers behind each port, where the load balancing is distributed to multiple servers. Otherwise they might just be connected to a single server with multiple NICs, used just for redundancy, so that if one network interface card on the server fails, the server remains available.
Securing SQL Server
Security is covered in more depth in Chapter 6, and Chapter 7, so what follows is a basic overview of server access security, not a discussion about permissions within SQL Server.
When connecting to SQL Server on Windows or Linux, or SQL Database in Azure, security is required to keep everyone out except the people who need access to the database.
Active Directory, using Integrated Authentication, is the primary method for connecting to SQL Server on a Windows domain. When you sign in to an Active Directory domain, you are provided a token that contains your privileges and permissions.
This is different from SQL Server Authentication, however, which is managed directly on the SQL Server instance and requires a user name and password to travel over the network.
Integrated authentication and Active Directory
Active Directory covers a number of different identity services, but the most important is Active Directory Domain Services, which manages your network credentials (your user account) and what you can do on the network (access rights). Having a network-wide directory of users and permissions facilitates easier management of accounts, computers, servers, services, devices, file sharing, and so on.
In this type of environment, SQL Server would be managed as just another service on the network, and the Active Directory Domain Service would control who has access to that SQL Server instance. This is much easier than having to manage per-server security, which is time consuming, difficult to troubleshoot, and prone to human error.
Authenticating with Kerberos
Kerberos is the default authentication protocol used in a Windows Active Directory domain; it is the replacement of NT LAN Manager (NTLM).
Kerberos ensures that the authentication takes place in a secure manner, even if the network itself might not be secure, because passwords and weak hashes are not being transferred over the wire. Kerberos works by exchanging encrypted tickets verified by a Ticket Granting Server (TGS; usually the domain controller).
A service account that runs SQL Server on a particular server, under an Active Directory service account, must register its name with the TGS, so that client computers are able to make a connection to that service over the network. This is called a Service Principal Name.
Understanding the Service Principal Name
As shown in Figure 2-4, when a client logs into a Windows domain, it is issued a ticket by the TGS. This ticket is called a ticket-granting ticket (TGT), but it’s easier to think of it as the client’s credentials. When the client wants to communicate with another node on the network (for example, SQL Server), this node (or “principal”) must have a Service Principal Name (SPN) registered with the TGS.
It is this SPN that the client uses to request access. After a verification step, a ticket and session key is sent from the TGS, to both the SQL Server and the client, respectively. When the client uses the ticket and session key on the SQL Server, the connection is authenticated by the SQL Server using its own copy of the session key.
For SQL Server to use Kerberos authentication instead of the older and less-secure NTLM, the Windows domain account that runs the SQL Server service, must register the SPN with the domain controller. Otherwise, the authentication will fall back to NTLM, which is far less secure. The easiest way to achieve this is to give the service account Write ServicePrincipalName permission in Active Directory Domain Service. To configure an SPN manually, you must use the Setspn.exe tool (built in to Windows).
Accessing other servers and services with delegation
Kerberos delegation allows an application (such as SQL Server, or Internet Information Services) to reuse end-user credentials to access a different server. This is intended to solve the so-called “double-hop issue,” in which the TGS verifies only the first hop, namely the connection between the client and the registered server. In normal circumstances, any additional connections (the second hop) would require reauthentication.
Delegation impersonates the client by sending the client’s TGT on the client’s behalf. This in turn causes the TGS to send tickets and session keys to the original server and the new server, allowing authentication. Because the original connection is still authenticated using the same TGT, the client now has access to the second server.
For delegation to work, the service account for the first server must be trusted for delegation, and the second server must be in the same Active Directory forest or between forests with the appropriate trust relationship.
Azure Active Directory
Azure Active Directory (Azure AD) is concerned with identity management for internet-based (and on-premises) services, which use HTTP and HTTPS to access websites and web services, without the hierarchy associated with on-premises Active Directory.
You can use Azure AD for user and application authentication; for example, to connect to Azure SQL Database or Microsoft Office 365. There are no Organizational Units or Group Policy Objects. You cannot join a machine to an Azure AD domain, and there is no NTLM or Kerberos authentication. Instead, protocols like OAuth, OpenID Connect (based on OAuth 2.0), SAML, and WS-Federation are used.
You can authenticate (prove who you are), which then provides authorization (permission, or claims) to access certain services, and these services might not even be controlled by the service that authenticated you. Think back to network credentials. On an on-premises Active Directory, your user credentials know who you are (authentication), and what you can do (authorization).
Protocols like OpenID Connect blur these lines, by extending an authorization protocol (what you can do) into an authentication protocol, as well (who you are). Although this works in a similar manner to Kerberos, whereby an authorization server allows access to certain internet services and applications, permissions are granted with claims.
Asserting your identity by using claims
Claims are a set of “assertions of information about the subject that has been authenticated” (https://docs.microsoft.com/azure/active-directory/develop/active-directory-authentication-scenarios#claims-in-azure-ad-security-tokens).
Think of your user credentials as a security token that indicates who you are based on how you were authenticated. This depends on the service you originally connected to (i.e., Facebook, LinkedIn, Google, Office 365, or Twitter).
Inside that user object is a series of properties, or attributes, usually in the form of key–value pairs. Each set of attributes, or claims, is dependent on the authentication service used.
Authentication services like Azure AD might restrict the amount of information permissible in a user object, to provide the service or application just enough information about you to prove who you are, and give you access to the service you’re requesting, without sharing too much about you or the originating authentication service.
Federation and single sign-on
Federation is a fancy word that means an independent collection of websites or services that can share information between them using claims. An authentication service allows you to sign in on one place (LinkedIn, Facebook, or Microsoft) and then use that identity for other services controlled by other entities.
This is what makes claims extremely useful. If you use a third-party authentication service, that third party will make certain information available in the form of claims (key–value pairs in your security token) that another service to which you’re connecting can access, without needing to sign in again, and without that service having access into the third-party service.
For example, suppose that you use LinkedIn to sign in to a blogging service so that you can leave a comment on a post. The blogging service does not have any access to your LinkedIn profile, but the claims it provides might include a URL to your profile image, a string containing your full name, and a second URL back to your profile.
This way, the blogging service does not know anything about your LinkedIn account, including your employment history, because that information is not in the claims necessary to leave a blog post comment.
Logging in to Azure SQL Database
Azure SQL Database uses three levels of security to allow access to a database. First is the firewall, which is a set of rules based on origin IP address or ranges and allows connections to only TCP port 1433.
The second level is authentication (proving who you are). You can either connect by using SQL Authentication, with a username and password (like connecting to a contained database on an on-premises SQL Server instance), or you can use Azure AD Authentication.
Microsoft recommends using Azure AD whenever possible, because it does the following (according to https://docs.microsoft.com/azure/sql-database/sql-database-aad-authentication):
Centralizes user identities and offers password rotation in a single place
Eliminates storing passwords by enabling integrated Windows authentication and other forms of authentication supported by Azure AD
Offers token (claims-based) authentication for applications connecting to Azure SQL Database
The third level is authorization (what you can do). This is managed inside the Azure SQL database, using role memberships and object-level permissions, and works exactly the same way as it would with an on-premises SQL Server instance.
You can read more about SQL Server security in Chapters 6 and 7.
Abstracting hardware with virtualization
Hardware abstraction has been around for many years, and, in fact, Windows NT was designed to be hardware independent. Taking this concept even further, virtualization abstracts the entire physical layer behind what’s called a hypervisor, or Virtual Machine Manager (VMM) so that physical hardware on a host system can be logically shared between different VMs, or guests, running their own operating systems.
To a guest OS, the VM looks like normal hardware and is accessed in the same way.
As of this writing, there are two main players in the virtualization market: Microsoft Hyper-V and VMware.
The move to virtualization has come about because physical hardware in many organizations is not being used to its full potential, and systems might spend hundreds of hours per year sitting idle. By consolidating an infrastructure, namely putting more than one guest VM on the same physical host, you can share resources between these guests, reducing the amount of waste and increasing the usefulness of hardware.
Certain workloads and applications are not designed to share resources, and misconfiguration of the shared resources by system administrators might not take these specialized workloads into account. SQL Server is an excellent example of this, given that it is designed to make use of all the physical RAM in a server by default.
If the resources are allocated incorrectly from the host level, contention between the guests takes place. This phenomenon is known as the noisy neighbor, in which one guest monopolizes resources on the host, and the other guests are negatively affected. With some effort on the part of the network administrators, this problem can be alleviated.
The benefits far outweigh the downsides, of course. You can move VMs from one host to another in the case of resource contention or hardware failure, and some hypervisors can orchestrate this without even shutting down the VM.
It is also much easier to take snapshots of virtualized file systems, which you can use to clone VMs. This can reduce deployment costs and time when deploying new servers, by “spinning up” a VM template, and configuring the OS and the application software that was already installed on that virtual hard drive.
Over time, the cost benefits become more apparent. New processors with low core counts are becoming more difficult to find. Virtualization makes it possible for you to move physical workloads to VMs (now or later) that have the appropriate virtual core count, and gives you the freedom to use existing licenses, thereby reducing cost.
David Klee writes more on this in his article “Point Counterpoint: Why Virtualize a SQL Server?” available at http://www.davidklee.net/2017/07/12/point-counterpoint-why-virtualize-a-sql-server.
Resource provisioning for VMs
Setting up VMs requires understanding their anticipated workloads. Fortunately, as long as resources are allocated appropriately, a VM can run almost as fast as a physical server on the same hardware, but with all of the benefits that virtualization offers.
It makes sense, then, to overprovision resources for many general workloads.
Sharing more memory than you have (overcommit)
You might have 10 VMs running various tasks such as Active Directory Domain Controllers, DNS servers, file servers, and print servers (the plumbing of a Windows-based network, with a low RAM footprint), all running on a single host with 16 GB of physical RAM.
Each VM might require 4 GB of RAM to perform properly, but in practice, you have determined that 90 percent of the time, each VM can function with 1 to 2 GB RAM each, leaving 2 to 3 GB of RAM unused per VM. You could thus overcommit each VM with 4 GB of RAM (for a total of 40 GB), but still see acceptable performance, without having a particular guest swapping memory to the drive as a result of low RAM, 90 percent of the time.
For the remaining 10 percent of the time, for which paging unavoidably takes place, you might decide that the performance impact is not sufficient to warrant increasing the physical RAM on the host. You are therefore able to run 10 virtualized servers on far less hardware than they would have required as physical servers.
Provisioning virtual storage
In the same way that you can overcommit memory, so too can you overcommit storage. This is called thin provisioning, in which the VM and guest OS are configured to assume that there is a lot more space available than is physically on the host. When a VM begins writing to a drive, the actual space used is increased on the host, until it reaches the provisioned limit.
This practice is common with general workloads, for which the space requirements grow predictably. An OS like Windows Server might be installed on a guest with 127 GB of visible space, but there might be only 250 GB of actual space on the drive, shared across 10 VMs.
For specialized workloads like SQL Server and Microsoft SharePoint (which is underpinned by SQL Server anyway), thin provisioning is not a good idea. Depending on the performance of the storage layer and the data access patterns of the workload, it is possible that the guest will be slow due to drive fragmentation or even run out of storage space (for any number of reasons, including long-running transactions, infrequent transaction log backups, or a growing TempDB).
It is therefore a better idea to use thick provisioning of storage for specialized workloads. That way the guest is guaranteed the storage it is promised by the hypervisor, and is one less thing to worry about when SQL Server runs out of space at 3 AM on a Sunday morning.
When processors are no longer processors
Virtualizing CPUs is challenging because the CPU works by having a certain number of clock cycles per second (which we looked at earlier in this chapter). For logical processors (this refers to the physical CPU core, plus any logical cores if SMT is turned on), every core shares time slices, or time slots, with each VM. Every time the CPU clock ticks over, that time slot might be used by the hypervisor or any one of the guests.
Just as it is not recommended to overprovision RAM and storage for SQL Server, you should not overprovision CPU cores either. If there are four quad-core CPUs in the host (four CPU sockets populated with a quad-core CPU in each socket), this means that there are 16 cores available for use by the VMs (32 when accounting for SMT).
Virtual CPU
A virtual CPU (vCPU) maps to a logical core, but in practice, the time slots are shared evenly over each core in the physical CPU. A vCPU will be more powerful than a single core because the load is parallelized across each core.
One of the risks of mixing different types of workloads on a single host is that a business-critical workload like SQL Server might require all the vCPUs to run a large parallelized query. If there are other guests that are using those vCPUs during that specific time slot and the CPU is overcommitted, SQL Server’s guest will need to wait.
There are certain algorithms in hypervisors that allow vCPUs to cut in line and take over a time slot, which results in a lag for the other guests, causing performance issues. Assume that a file server has two logical processors assigned to it. Further assume that on the same host, a SQL Server has eight logical processors assigned to it. It is possible for the VM with fewer logical processors to “steal” time slots because it has a lower number of logical processors allocated to it.
There are several ways to deal with this, but the easiest solution is to keep like with like. Any guests on the same host should have the same number of virtual processors assigned to them, running similar workloads. That way, the time slots are more evenly distributed, and it becomes easier to troubleshoot processor performance. It might also be practical to reduce the number of vCPUs allocated to a SQL Server instance so that the time slots are better distributed.
The network is virtual, too
Whereas before, certain hardware devices might be used to perform discrete tasks, such as network interface cards, routers, firewalls, and switches, these tasks can be accomplished exclusively through a software layer, using virtual network devices.
Several VMs might share one or more physical NICs on a physical host, but because it’s all virtualized, a VM might have several virtual NICs mapped to that one physical NIC.
This allows a number of things that previously might have been cumbersome and costly to set up. Software developers can now test against myriad configurations for their applications without having to build a physical lab environment using all the different combinations.
With the general trend of consolidating VMs, virtual networking facilitates combining and consolidating network devices and services into the same environment as the guest VMs, lowering the cost of administration and reducing the need to purchase separate hardware. You can replace a virtualized network device almost immediately if something goes wrong, and downtime is vastly reduced.
Summary
SQL Server now runs on Linux, but for all intents and purposes, it’s the same as the Windows version, and many of the same rules apply.
Whether running on physical or virtual hardware, databases perform better when they can be cached in memory as much as possible and are backed by persistent storage that is redundant, and has low latency and high random IOPS.
As data theft becomes more prevalent, consider the security of the database itself, the underlying OS and hardware (physical or virtual), the network, and the database backups, too.
When considering strategies for SQL Server HA and DR, design according to the organization’s business requirements, in terms of the RPO and RTO. Chapter 11 and Chapter 12 cover this in depth.