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.
This course has focused almost exclusively on SQL Server running on a virtual machine, which shouldn't be too surprising as Azure SQL platform as a service, and SQL managed instance have limited configuration options, which is one of their key attributes. SQL Server on a VM is a close facsimile to running on-premise without the hardware headache, and while upscaling is relatively easy, it is not seamless.
Azure SQL serverless is the most recent addition to Azure SQL database offerings, and is a complete contrast to a VM setup in terms of scalability. In AZURE SQL Serverless setup is defined by minimum and maximum hardware boundaries for CPU vCores and storage. The database then has access to hardware resources up to the maximum on an as-needed basis. Serverless SQL is billed by the amount of vCore and memory utilization per second.
During periods of inactivity, the database can go to sleep after a specified period to reduce costs. Azure SQL serverless is ideal for situations where the workload is erratic and extreme, so going from low to no activity to high workloads. The serverless database model's biggest downside is the startup latency, where the workload ramps up quickly after an extended period of inactivity.
Unlike the VM scenario where the resources are already at hand, the serverless model needs to recruit resources from a shared pool and essentially provision them before spinning them up. This warm-up lag can be mitigated by disabling auto-pause and setting higher minimum hardware values, but this negates some of the cost reduction benefits offered by serverless.
A serverless database is useful for new deployments where usage patterns are unknown, and you want to get a sense of workload distribution before migrating to a more consistent and predictable pricing model.
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.