Select Page

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.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

PostgreSQL vs. Oracle: Let’s Compare

Discover some key differences between PostgreSQL vs Oracle that can help you make an informed decision when deciding on a database management system.

Vijay Muthu | March 30, 2021

Oracle RMAN Backup and Recovery with Restore Points

Oracle restore points are useful for benchmark testing. Find out how you can use Oracle’s Recovery Manager (RMAN) tool to create and use restore points.

Cindy Putnam | May 3, 2019

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS