The course is part of this learning path
This course focuses on maximizing the performance of hardware and infrastructure relating to database servers. You will learn the best ways to improve an SQL server's performance and that of its databases through infrastructure choice and configuration settings.
Learning Objectives
- Learn how to set up disks for maximum performance
- Understand how to boost file performance and how to use instant file initialization
- Understand how to optimize TempDB and choose the right VM for your workloads
- Learn how to manage an instance's resources
- Learn how to configure your database as well as your SQL Server system accounts
- Understand how to use Intelligent Query Processing to optimize database performance
- Understand the benefits of using Azure SQL Serverless
Intended Audience
- Database administrators
- Solutions architects
- Anyone interesting in improving the performance of their database
Prerequisites
To the most out of this course, you will need to be familiar with basic Azure concepts, have some knowledge of computer hard drives and networking, and be familiar with databases and their servers.
You are definitely not constrained by the maximum performance of any one hard drive. Writing and reading data simultaneously across multiple hard drives can reduce the I/O bottleneck and increase overall disc performance. This practice is called RAID 0 in an on-premise environment. RAID stands for Redundant Array of Inexpensive Disks. But in the cloud, referred to as disc striping.
The concept is pretty simple, instead of a read or write operation carried out by one disc controller on one hard drive, the I/O operation is split amongst multiple hard drives so it can be carried out simultaneously. This means that a file will be split across multiple hard drives. This divide and conquer strategy is managed by the operating system and is invisible to other applications, as a pool of striped disks appears as one volume.
Because disk access is so slow relative to CPU and RAM, the benefits of multiple hard drives working simultaneously in unison far outweighs the cost of managing disk striping. Higher RAID levels that offer redundancy are unnecessary in the Azure environment as multiple synchronous disk replication protects against disk failure. To create a striped disk set or pool, use the Storage Spaces utility within Windows to add disks connected to your VM to the pool.
Azure's documentation talks about disk striping with Premium SSDs, but you can stripe with Standard SSDs and HDDs, so it may be possible to achieve better IO performance using cheaper media, but that would be something you would have to test for yourself. Disk striping is not available with Ultra disks.
Hallam is a software architect with over 20 years experience across a wide range of industries. He began his software career as a Delphi/Interbase disciple but changed his allegiance to Microsoft with its deep and broad ecosystem. While Hallam has designed and crafted custom software utilizing web, mobile and desktop technologies, good quality reliable data is the key to a successful solution. The challenge of quickly turning data into useful information for digestion by humans and machines has led Hallam to specialize in database design and process automation. Showing customers how leverage new technology to change and improve their business processes is one of the key drivers keeping Hallam coming back to the keyboard.