This is part of a series of new job blog posts. You can find them all here.
Last week was the first week of the rest of my life. But really it was the first week of my new job and I installed a lot of tools. You can learn more about those in this blog post. I also started thinking about how I would analyze existing databases. My focus is on Azure SQL right now. This post doesn’t include details about Azure PostgreSQL. I will hit those highlights in future blog posts. This post will outline the checklist I came up with in week two of my new job.
Checklist of Items to Analyze
- Baselining – Do we know what is normal and abnormal for our database servers? Here‘s a helpful resource on baselining.
- Monitoring – Is everything monitored properly?
- Alerting – Are we getting alerts for things we need to know? Some essential ones are:
- DTU %
- Disk usage %
- Data retention and archiving – Do we have partitioning in place for large tables or tables that could grow larger?
- Data types – Are we using appropriate data types based on the data being stored?
- Indexing – Do we have proper indexing? Are queries using the indexes properly?
- Index maintenance – Do we have index maintenance scheduled and running properly?
- Auditing – Do we audit to ensure we know when users make changes that could impact uptime or performance?
- Finding Invalid/Broken Items – For example, what views or stored procedures reference missing objects?
- Perms review – Who has access to what? And should they have this level of access?
- Vulnerability assessments – Are there any settings on your database/server that put you at risk?
I only have Azure-managed databases. The list of stuff I need to know is shorter than if I used SQL Server on a VM, for example. In Azure SQL, there’s a lot less to manage because you don’t have to maintain the VM and all the server settings. On SQL Server, I would check all kinds of other stuff, like:
- Backups – You don’t need agent jobs in Azure to do this like on SQL Server. You will need to decide how redundant you want the backups to be, but they will automatically take them for you. Click here for more info.
- Integrity checks – You don’t need to do this in Azure. Click here for more info.
- Server settings – Far fewer server settings in Azure – look this up
- Agent settings and jobs – There’s no agent in Azure SQL
Highest and Lowest Priority Items
Today’s post is about what you would look for when you arrive. This is the point at which I would create a checklist of what I want to resolve over time.
For me monitoring, alerting, and auditing are at the top of the list. We need to know when something is going or has gone wrong. Next up, is indexing and index maintenance. Then I would tackle more long-term projects such as poorly chosen data types and partitioning.
Your priorities may vary depending on what challenges you face. Maybe you have a VERY large table and the data needs to be deleted ASAP. In this case, you may need to partition it before you work on other items. Maybe your users are experiencing bad performance. In this case, you may need to look at indexing and queries first.
Monitoring and alerting being one of your first orders of business can’t serve you wrong, though. You need to know when the database or server is experiencing issues. This way you aren’t finding out from someone else first.
Skills Required?
What I realized after managing a lot of Azure databases is I need more PowerShell skills. I need to check a bunch of settings across all these databases. I want to loop through them with PowerShell and have the results displayed cleanly for all of them at once. As it is now, I can cobble scripts together, but I want the ability to start typing and come up with a script that works.
On top of all that, I also need to learn more Terraform. We manage our infrastructure with Terraform. Let’s say I want to create a Log Analytics Workspace to store my audit data or create an alert for DTU. I need to use Terraform for this.
Additionally, I need to learn Flyway and Azure DevOps because we use them to deploy DDL code. I need to install some stored procs for baselining and they need to be put into a new schema. Flyway can manage that.
You don’t need PowerShell, Terraform, or Flyway to accomplish this checklist. However, learning and implementing them is well worth the time it will take. It will make your life simpler in the long run by automating manual tasks.
Tools & Scripts I Would Use
I’m going to use a few different things:
- PowerShell – I will use this to loop through my databases to check settings. I can also use this to run scripts against multiple databases. I won’t apply settings to anything in Azure Portal with PowerShell because I will use Terraform/Flyway for that. I can also use these PowerShell modules to check settings and get recommendations for improvements:
- Terraform – I will set up infrastructure such as Log Analytics workspaces or monitoring/alerting/auditing settings for databases. Learn more about Terraform here.
- SQL with Flyway – Naturally with SQL databases we will use SQL. I will implement the SQL on multiple Azure SQL databases with Flyway. Learn more about Flyway here.
- Ola – The stored procs I will need for index maintenance.
- sp_whoisactive – This is a stored proc for checking active queries.
- Glenn Berry – He has a set of database diagnostic information queries. There are different types of queries for SQL Server, Azure SQL, and Azure SQL MI. This can be helpful for baselining.
- Ozar – He has a set of stored procedures to capture tons of info about your SQL Server or Azure SQL database in the SQL Server First Responder Kit. This could also be helpful for baselining. It’s hit and miss whether it works on Azure SQL or not, though.
- Partitioning – This a fairly large topic unto itself, but here’s some Microsoft guidance. My plan is to partition any rapidly growing tables before they become gigantic.
- Invalid/broken objects – This stored proc helps you find objects referencing other objects that are gone.
- Query Store – Using Query Store, you can also get performance baselines.
- Azure portal or PowerShell/Terraform
- Auditing – I have some presentations on how to set this up in Azure SQL, but I don’t use PowerShell or Terraform in them. This will give you a clear idea of how auditing works, though. Shorter or longer versions are available.
- Index maintenance – This is a mishmash of SQL executed in a runbook. I don’t have the code handy, but I use Ola stored procs and Runbooks mixed together to accomplish this feat.
- Baselining – You can use the Azure portal for Intelligent Insights and Query Performance Insights.
- Vulnerability assessment – There is a built-in assessment in the portal that you can enable to ensure you know about vulnerabilities in your database. These can include things such as excessive permissions or excessive firewall access.
My needs may vary over time, but this is a great place to start..