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.
It is best practice to make any changes to SQL Server related accounts through SQL Server Configuration manager and not by other means like the Services applet. SQL Server service does not require an account with local administrative privileges. You should run SQL Services using the principle of least privileges. Do not use the Local System or Network Services builtin accounts. Both of these accounts have access to extensive privileges on the local computer and network resources, either directly or by association.
Use a specifically setup up domain account with only the necessary permissions when you want to connect to other network resources like linked servers or file shares. Any domain account used to run the SQL Server service should not be a member of the Windows Administrator group. This is preferable over other mechanisms such as user impersonation because it is much easier to maintain and manage.
Just as a point of interest, most SQL service accounts, that is, the server, agent, analysis, integration, and reporting services, all logged on by default with the network service account prior to SQL Server 2008 R2. From 2008 R2, the default installation behavior is to create managed local accounts known as virtual accounts.
These virtual accounts follow the naming convention of NT Service, followed by the service name. For the default server instance, this would be NT Service\MSSQLSERVER, and for a named instance, it might be NT Service\MSSQL$WEBSALES. The same principle applies to the SQL Agent service account. In a domain environment, these virtual accounts do have access to the network.
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.