Azure SQL Managed Instance Best Practices
Author: Rajesh Rajesh Varma | 5 min read | June 5, 2024
When organizations move into the cloud, the key goals are high availability, better performance, strong security, and lower operational costs. In order to achieve these goals, the cloud architect has to properly configure, monitor, and maintain the environment.
By default, many cloud parameters and features are disabled, and the cloud architect may not have a deep knowledge of the database domain when working with SQL Server instances.
Typically, problems with the configuration occur in two scenarios:
- Initial on-premises SQL Server migration to Azure SQL Managed Instance
- Database size grows and the user traffic increases, leading to database performance degradation
By implementing Azure SQL Managed Instance best practices, organizations can achieve their cloud migration goals, as well as reducing CPU utilization and improving query execution time. Use this checklist to see how your SQL Server instances compare.
Server Provisioning
- Service Tier: Choose the “Business Critical – Memory Optimized Premium Series” service tier for high-performance and availability
- Zone Redundancy: Enable zone redundancy for better availability
- Failover Group: Yes – Enable for business continuity and quick recovery
- Geo Restore: Yes – Enable for business continuity and quick recovery
- Time Zone: Ensure the correct time zone is configured
- Microsoft Defender for SQL: Enable for advanced threat protection
- Authentication: Microsoft Entra authentication for centralized identity management
- Transparent Data Encryption (TDE): Enable TDE with customer-managed keys for enhanced data protection
- Labels: Use tags to categorize and manage resources effectively
- Maintenance Window: Configure to manage patches and maintenance tasks
- Connectivity: Allow access from public endpoints, enabling access to Azure services
Backup and Storage
- Point-in-Time Restore (PITR): Configure with 35 days of retention
- Long term retention: W=12,M=0, Y=0 – Each weekly backup retention for 12 Weeks
- Geo-Replicated Backup: Yes – Enable for data redundancy
SQL Server Level Configuration
- Optimize Adhoc Workload: Enable
- Cost Threshold of Parallelism: Configure the value which suits for the application query performance
- Max Degree of Parallelism: Configure the value which suits for the application query performance
- Backup Compression: Configure the value which suits for the application query performance
- Database Mail: Enable and configure the Database mail
- Backup checksum: Enable, Configure to check for errors for individual backup or restore operation
- Backup Compression: Enable, Configure to compress the backup
- Login Auditing: Both failed and successful logins
Database Level Configuration
- Configure appropriate User database growth pattern in MB
- Configure Tempdb database growth pattern in MB
- Enable Auto Update Statistics
- Enable Auto Update Statistics asynchronous
- Database Owner to SA
- Compatibility level: SQL Server 2022(160)
- Query Store Operation mode: Read-Write
- Query Store retention: Appropriately configure as per the requirement
Database Administration
- Deploy ola hallengren script
- Deploy Firstresponder kit
Database Maintenance
- Configure Database integrity check job
- Configure Index Optimize job
- Configure Update statistics job
- Configure Blocking monitoring alert job
- Configure Long running queries alert job
- Configure Purge backup history job
- Configure Purge job history job
SQL Server Agent
- Configure the Operator
- Configure the job failure notification to the Operator
Monitoring
- Service Health Alerts: Enable for Azure maintenance and outage notifications
- Metric Alerts: Set up alerts for high CPU, Disk usage and administrative alerts
- Azure SQL analytics: Enable Advance Graphical monitoring tool
Conclusion
Implementing best practices for Azure SQL Managed Instance is crucial for optimizing performance, ensuring high availability, and enhancing security. By following the recommended server provisioning, backup, storage, and database configurations, organizations can significantly reduce operational costs and improve the efficiency of their cloud-based database environments. Regular maintenance and monitoring further support these goals, ensuring that the Azure SQL Managed Instance remains robust and responsive to the evolving needs of the business.
Need help migrating and managing your on-premises SQL Server databases to Azure SQL Managed Instance? Contact our SQL Server and Azure experts today.
