Azure SQL Database Administration Reference

This section provides comprehensive reference information for administrating Azure SQL Database, covering a wide range of topics from monitoring and performance tuning to security and resource management.

Key Administration Areas

1. Monitoring and Performance

• Performance Metrics
• Query Performance Tuning
• Indexing Strategies
• Using Query Store
• Azure Monitor Integration

2. Security Management

• Authentication and Authorization
• Firewall Rules and Network Access
• Advanced Threat Protection
• Data Encryption (TDE, Always Encrypted)
• Auditing and Logging

3. Resource Management

• Understanding Service Tiers (DTU, vCore)
• Scaling Up/Down and Out/In
• Resource Governance
• Backup and Restore Operations

4. High Availability and Disaster Recovery

• Geo-Replication
• Failover Groups
• Active Geo-Replication

Detailed Topics

Performance Metrics

Azure SQL Database exposes a rich set of performance metrics that can be monitored via Azure Monitor and SQL Server Management Studio (SSMS). Key metrics include:

• CPU Percentage: The percentage of time the CPU is busy.
• Data IO Percentage: The percentage of data IO capacity used.
• Log IO Percentage: The percentage of log IO capacity used.
• DTU Percentage: For DTU-based models, the overall resource utilization.
• Active Queries: The number of currently executing queries.
• Deadlocks: The number of deadlocks occurring.

Use these metrics to identify performance bottlenecks and proactively address potential issues.

Query Performance Tuning

Optimizing query performance is crucial for a responsive application. Consider the following strategies:

• Analyze Execution Plans: Use SSMS or Azure Data Studio to inspect query execution plans and identify costly operations.
• Update Statistics: Ensure statistics are up-to-date for the query optimizer to generate efficient plans. Use UPDATE STATISTICS command.
• Rewrite Queries: Simplify complex queries, avoid unnecessary joins, and use appropriate functions.
• Parameterize Queries: Use parameterized queries to improve plan caching.

Indexing Strategies

Proper indexing significantly improves query performance by reducing the amount of data that needs to be scanned.

• Clustered Indexes: Define the physical order of data in a table. Every table should have one.
• Non-clustered Indexes: Provide a logical ordering of data for specific columns, speeding up lookups.
• Columnstore Indexes: Optimized for analytical workloads, providing high data compression and query performance.

Use the sys.dm_db_missing_index_details dynamic management view to identify potential missing indexes.

Using Query Store

Query Store is an invaluable feature for tracking query performance history, identifying performance regressions, and managing query execution plans.

Enable Query Store by setting its mode to READ_WRITE:

ALTER DATABASE [YourDatabaseName] SET QUERY_STORE = ON;

You can then query system catalog views like sys.query_store_runtime_stats to analyze performance.

Azure Monitor Integration

Azure Monitor provides a unified view of your Azure resources. For Azure SQL Database, you can leverage:

• Metrics: Track performance counters and resource utilization.
• Logs: Collect diagnostic logs for auditing and troubleshooting.
• Alerts: Set up alerts based on metric thresholds to be notified of potential issues.

Authentication and Authorization

Azure SQL Database supports various authentication methods:

• SQL Authentication: Username and password-based authentication.
• Azure Active Directory (Azure AD) Authentication: Centralized identity management for simplified access control.

Authorization is managed through database roles and permissions.

Firewall Rules and Network Access

Configure firewall rules at the server and database levels to control network access to your Azure SQL Database. This is a critical security measure to restrict connections to trusted IP addresses or IP ranges.

Advanced Threat Protection

Azure SQL Database offers Advanced Threat Protection, which provides security intelligence to detect and alert on anomalous activities, potential SQL injections, and brute-force attacks.

Data Encryption (TDE, Always Encrypted)

Protect your sensitive data at rest and in transit:

• Transparent Data Encryption (TDE): Encrypts your data files and transaction logs. It's enabled by default for new databases.
• Always Encrypted: Protects sensitive data in the database from unauthorized access by encrypting it on the client-side.

Auditing and Logging

Enable auditing to track database events and audit logs to Azure Storage, Azure Event Hubs, or Azure Log Analytics for analysis and compliance.

Understanding Service Tiers (DTU, vCore)

Azure SQL Database offers different service tiers to meet varying performance and scalability needs:

• DTU Model: A simpler, bundled measure of database throughput.
• vCore Model: Provides more granular control over compute and storage resources, offering better flexibility and cost optimization.

Choose the tier that best aligns with your application's workload and budget.

Scaling Up/Down and Out/In

You can dynamically scale your Azure SQL Database resources:

• Scaling Up/Down: Change the compute size (e.g., DTUs or vCores) to increase or decrease performance.
• Scaling Out/In: For elastic pools, you can scale the pool's resources. Read replicas allow scaling read workloads.

Resource Governance

Azure SQL Database provides mechanisms for resource governance, ensuring that your database consumes resources within the configured limits of its service tier.

Backup and Restore Operations

Azure SQL Database automatically backs up your data and transaction logs. You can configure retention policies and perform point-in-time restores.

Geo-Replication

Geo-replication enables you to create readable, geographically replicated copies of your database, improving disaster recovery capabilities and reducing read latency for geographically dispersed users.

Failover Groups

Failover groups provide automatic failover to a secondary region in case of a regional outage, ensuring business continuity.

Active Geo-Replication

Active geo-replication allows you to maintain multiple readable secondary databases in different regions. This is a more advanced form of geo-replication offering better read availability.