In this article, we explain how to optimize and manage Azure SQL Database across the three areas of excellence for cloud management: cloud operations, cloud financial management, and cloud security and compliance.
Azure SQL Database is one of the most popular cloud-based, fully managed, relational database services. Relational databases have an important set of properties for database transactions called ACID (Atomicity, Consistency, Isolation, and Durability).
These characteristics ensure that any transaction that is written to the database is processed appropriately, even if it involves multiple tables. This is a critical feature in various industries, such as banking and e-commerce.
In this article, we’ll explore how you can optimize and manage Azure SQL Database across the three areas of excellence for cloud management: cloud operations, cloud financial management, and cloud security and compliance.
Optimize Azure SQL Database for cloud operations
First, let’s look at a sample Golang code for connecting to Azure SQL Database:
package main
import (
"context"
"database/sql"
"fmt"
"log"
"github.com/denisenkom/go-mssqldb"
)
var azureSQLDB *sql.DB
// Name of the Database Server - Each server can manage one or more databases
var server = "db-optimize-server.database.windows.net"
var port = 1433
var user = "admin"
var password = "mypassword"
// Name of the database instance
var database = "db-optimize-sql"
func main( ) {
// Build connection string
connectionString := fmt.Sprintf("server=%s;userid=%s;password=%s;port=%d;database=%s;",
server, user, password, port, database)
var err error
// Create connection
azureSQLDB, err = sql.Open("sqlserver", connectionString)
if err != nil {
log.Fatal("Error Connecting to Azure AQL DB: ", err.Error())
}
ctx := context.Background()
// Use ping to verify if the Connection was successful
err = azureSQLDB.PingContext(ctx)
if err !=nil {
log.Fatal(err.Error())
}
fmt.Printf("Connected to Azure SQL !")
}
You must ensure that the VM/Functions that this code is executed from, is allowed under Firewall rules for Server-IP and Database-IP firewall rules. More on this later in the sections below.
Resolve Server Index Fragmentation with automatic tuning
Keeping fast query performance is paramount for any application using relational databases. One of the common causes of degraded query performance is “Index Fragmentation.” Indexes play an extremely critical role in query performance. With the help of indices, the SQL server can quickly locate the row with the data that the user is requesting. Without them, the SQL database will have to scan the entire table.
Index Fragmentation is caused by repeated use of UPDATE, INSERT and DELETE operations. The first step in resolving this is to identify the degree of fragmentation. This can be done by running the following query within your DB.
sys.dm_db_index_physical_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | NULL | 0 | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
, { mode | NULL | DEFAULT }
)
You can read more about this here.
The next step is to resolve the fragmentation. The benefit of using a PaaS database like Azure SQL is that it offers features like Automatic Tuning. With this enabled, Azure leverages AI to detect any degraded query performance and fixes it whenever the database is processing low amounts of data.
There are three primary options for Automatic Tuning with Azure SQL Database:
- CREATE INDEX: Creates new indices that can improve the performance
- DROP INDEX: Drops redundant and unused indices (>90 days)
- FORCE LAST GOOD PLAN: Identifies queries using the last known good execution plan
These options can be controlled individually for all databases or can be applied at the server level, in which case they are inherited by all the databases.
Optimize Azure SQL Database for cloud financial management
Rightsizing Azure SQL Database
Rightsizing is the process that involves analyzing the usage and performance of your infrastructure resources to determine how efficiently they’re running, and then making changes as needed.
Rightsizing Azure SQL Database will help you reduce cloud costs and improve performance. Rightsizing can be achieved in three ways:
- Downsizing: Recommended for underutilized databases that achieve the same core performance, even with a downsized instance
- Terminating: Recommended for “zombie” databases. Zombie resources are assets that are running within your account but are not currently used. Terminating these resources results in immediate cost savings.
- Upgrading: Recommended for database instances that have consistently high utilization of Memory or CPU. The performance metrics must be analyzed over a period to come to this conclusion.
This analysis becomes exponentially complex to track and manage if you’re running more than one database instance. The CloudHealth Platform will help you with these use cases across your Azure Subscriptions. CloudHealth gathers DTU (database transaction units) and storage metrics for every database instance. Each metric is then compared against an efficiency target, that you can customize, to determine whether the database is being optimally used. CloudHealth will then generate up to three different recommendations that you can choose from.
In addition to this, the efficiency targets within CloudHealth can be targeted towards a specific team, organization, or environment, based on the selected Perspective. You can then use CloudHealth to generate a report that summarizes the findings and recommendations for Azure SQL Databases.
Leverage Azure SQL Database serverless
Azure SQL Database serverless is a relatively new compute tier that scales compute automatically based on query/connection activity and transactions to the database. The billing is per second for the compute used (storage costs are additional). Thus, making it suitable for applications or workloads that require a single database, and the usage is intermittent.
For example, if you’re building a cart service in an e-commerce app, Azure SQL Database serverless will be a perfect fit because the cart data needs to be persistent and needs to be retrieved/updated occasionally compared to database used for authentication services.
Optimize Azure SQL Database for cloud security and compliance
Encrypt data at rest and in transit
By default, Azure encrypts all the data in transit and at rest with Azure SQL Database by using “service-managed keys.” You can also “bring your own key” for encryption to comply with regulatory requirements. This can be done by navigating to SQL Server > Transparent data encryption > Customer-managed key.
You will have to store the key in “Azure Key Vault.” In addition to this, the client application accessing this database should have access to the encryption key.
Update firewall settings
On Azure, there are two types of firewall settings that can be used to protect network access to the SQL Database:
- Database-level IP firewall rules: Database-level IP firewall rules enable clients to access certain secure databases. You create the rules for each database (including the primary database), and they’re stored in the individual database. You can have a max of 128 database-level IP firewall rules.
- Server-level IP firewall rules: Server-level IP firewall rules enable clients to access the entire server, including all the databases managed by the server. Like database-level IP firewall rules, you can have a maximum of 128 server-level IP firewall rules for a server.
A best practice is to restrict the database access to servers that host applications (a private network) instead of exposing the database connection to a public network.
Further, a database-level IP firewall should be used whenever possible to enhance security. A server-level IP firewall is more suitable for admins who want to manage a fleet of databases.
Avoid misconfigurations with CloudHealth Secure State
Incorrect configurations can result in databases exposed to the internet and in violation of compliance and auditing standards. When you have several databases within your environment, continuous monitoring is needed to ensure all the different configuration parameters are set and consistently adhere to best practices and compliance frameworks. It’s a very time-consuming job and nearly impossible to perform in real-time.
To ease this process for SecOps and DevOps engineers, CloudHealth has a dedicated solution named CloudHealth Secure State. CloudHealth Secure State is an intelligent cloud security solution that provides real-time alerting, auto-remediation, and compliance monitoring across a broad range of frameworks including PCI, HIPAA, GDPR, and many more. It also allows you to build custom rules and frameworks across AWS, Microsoft Azure, and Google Cloud. See our technical report to learn more: Mitigating Cloud Security and Compliance Risks with CloudHealth Secure State
Optimizing Azure SQL Database is just one part of a holistic cloud management practice. You can see more information and best practices for optimizing your Azure cloud environment across operations, costs, and security in our in-depth guide: How to Gain Control of Your Azure Environment
code {
padding: 16px;
overflow: auto;
background-color: #f6f8fa;
border-radius: 3px;
display: block
}
pre {
display: block
}
