Manage Azure SQL Database

Introduction

This tutorial guides you through essential management tasks for your Azure SQL Database. Effective management is crucial for ensuring performance, security, and availability of your data.

Monitoring Performance

Azure SQL Database offers robust monitoring tools to track performance metrics, identify bottlenecks, and optimize query execution. Key metrics include CPU usage, IOPS, data throughput, and storage space.

You can access performance insights through the Azure portal:

• Navigate to your SQL Database resource.
• Select "Performance insights" from the left-hand menu.
• Explore the "Query performance insights" and "Performance recommendations" sections.

Consider setting up Azure Monitor alerts for critical performance thresholds. For example, to be notified when CPU utilization exceeds 80% for a sustained period:

-- Example of monitoring query in Azure Portal (not actual code for alerts)
SELECT TOP 10
    total_elapsed_time / execution_count AS avg_elapsed_time,
    SUBSTRING(qt.text, (dat.statement_start_offset/2)/2,
        ((CASE dat.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE dat.statement_end_offset
         END - dat.statement_start_offset)/2)
    ) AS statement_text,
    dbid,
    OBJECTID
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as dp
JOIN sys.dm_exec_requests as dr ON qs.sql_handle = dr.sql_handle AND qs.statement_start_offset = dr.statement_start_offset AND qs.statement_end_offset = dr.statement_end_offset
WHERE database_id = DB_ID()
ORDER BY total_elapsed_time / execution_count DESC;
            

Using Performance Insights

Performance Insights provides a visual representation of your database's performance, allowing you to drill down into specific queries that are consuming the most resources. This tool is invaluable for tuning and optimization.

Security Management

Securing your Azure SQL Database is paramount. Azure provides multiple layers of security, including network access control, authentication, authorization, and data encryption.

• Firewall Rules: Configure server-level and database-level firewall rules to restrict access to authorized IP addresses.
• Authentication: Utilize Azure Active Directory (Azure AD) authentication for centralized identity management and enhanced security.
• Authorization: Implement role-based access control (RBAC) to grant appropriate permissions to users and applications.
• Data Encryption: Azure SQL Database encrypts data at rest (Transparent Data Encryption - TDE) and in transit (SSL/TLS).

Configuring Azure AD Authentication

To enable Azure AD authentication:

1. Set the Azure AD admin for your Azure SQL server.
2. Connect to your database using Azure AD credentials.
3. Create database users and map them to Azure AD identities.

Backup and Restore

Azure SQL Database automatically backs up your data, ensuring business continuity. You can leverage these backups for disaster recovery or to restore to a previous point in time.

• Automated Backups: Full backups, differential backups, and transaction log backups are taken automatically.
• Point-in-Time Restore (PITR): Restore your database to any point in time within your defined backup retention period (typically 7 to 35 days).
• Geo-restore: Restore your database to a different Azure region to protect against regional outages.
• Long-Term Retention (LTR): Configure LTR for compliance or archival purposes, storing backups for extended periods.

To perform a point-in-time restore via the Azure portal:

1. Go to your SQL Database resource.
2. Select "Overview".
3. Click "Restore".
4. Choose the desired restore point.

Scaling Your Database

As your application's demands grow, you can easily scale your Azure SQL Database's compute and storage resources up or down without significant downtime.

• Scaling Compute: Adjust the service tier (Basic, Standard, Premium, Business Critical, General Purpose) and compute size (DTUs or vCores) to match your workload's performance needs.
• Scaling Storage: Increase the storage size as your data volume grows.

Scaling operations can be performed through the Azure portal, Azure CLI, or PowerShell.

# Example: Scaling database using Azure CLI
az sql db update --resource-group myResourceGroup --server myserver --name mydatabase --edition Premium --capacity 200
            

Automation with Scripts

Automating routine management tasks can save time and reduce the potential for human error. Azure SQL Database can be managed programmatically using various tools.

• Azure CLI: A powerful command-line tool for managing Azure resources, including SQL databases.
• Azure PowerShell: A scripting environment for managing Azure resources.
• REST API: For integration with custom applications and workflows.
• SQL Server Management Studio (SSMS) / Azure Data Studio: For interactive management and script execution.

Consider creating scripts for common tasks such as creating firewall rules, setting up Azure AD users, or performing routine maintenance queries.