Introduction
Overview
Intelligent Insights
SQL Server Management Studio
Wait Statistics
Index and Statistics
Storage
Alerts
Summary
The course is part of this learning path
Information is at the heart of most software systems and the lifeblood of many organizations, so you want the database that stores this information to be efficient and reliable. But as we know, things happen; sometimes bad things. One of the ways that we can prevent bad things from happening is to know about them in advance like the old saying says, "To be forewarned is to be forearmed."
Azure SQL in its many forms has an abundance of features that help you to monitor the state of your databases and database server. Ranging from prebuilt automated monitoring that is augmented with artificial intelligence through to dynamic management views, SQL Server monitors and logs all aspects of the database engine’s operation and configuration. Intelligent Insights and Azure SQL analytics enable you to easily access the wealth of diagnostic and performance data in an easily digestible format.
This course introduces you to the different database monitoring and notification technologies available, how they work, and how to use them. If you have any feedback relating to this course, feel free to contact us at support@cloudacademy.com.
Azure SQL and SQL Server Query Performance Tuning
Learning Objectives
- Understand the key elements of database monitoring
- Learn about the features of Intelligent Insights, Azure's AI-based database monitoring service
- Create graphical reports using SQL Server Management Studio
- Understand how wait statistics can show you where threads have to wait and how this can be used to monitor performance
- View and fix index fragmentation
- Monitor database storage
- Implement notification alerts on various database platforms
Intended Audience
This course is aimed at database administrators or anyone who wants to learn how to implement systems that can find potential issues that may disrupt the delivery of their database services.
Prerequisites
To get the most out of this course, you should have experience with SQL Server Management Studio, be familiar with reading and writing SQL, and have an understanding of basic database architecture and administration tasks, like indexes and backups.
Course Related SQL Scripts
https://github.com/cloudacademy/azure-sql-database-monitoring-dp-300
Now I want to turn my attention on how you might be notified of server-level changes within an SQL Server environment, whether that is on premise or on a VM.
First thing we need to do is set up Database Mail. This can be done through SQL Server Management Studio and Database Mail under the management node. I'll set up a connection to a Gmail account that can be used to send emails, I'll just use the Database Mail configuration wizard to do this.
First, I will set up a profile called Gmail that will use a new Database Mail account. This is all reasonably straightforward, server name is smtp.gmail.com using Port 25, and the server requires a secure connection. The authentication is basic, that is username and password. You'll need to allow less secure apps to access your Gmail account. This is done through the Google security settings.
Now that's done, I'll make my profile public and also make it the default profile. This now set up the trigger that will notify us of the database being created. This involves creating a trigger and server objects within SQL Management Studio.
First off, I will just create the trigger and see what the output looks like. When I create the database we get the SQL that was executed for its creation, specifying the name, files, and respective file sizes. What I'll do now is drop the trigger and recreate it using the sp_send_dbmail procedure to send a notification of the database creation. Recipients can be multiple email addresses separated by semi colons.
You can specify the body of the email to be formatted as HTML, which will allow you to have pretty emails with tables and headings. I've set the importance to high which will mark the email as such. But if the email is for informational purposes, you could use the word normal.
If you wanted to use a Database Mail profile other than the default, that can be specified with the parameter email profile, or recreate the trigger and then recreate the database. We can see the message has been queued to send, triggered by the database creation. And if I go to Outlook, I can see the message telling me all the new database has arrived.
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.