Recent years have seen many organizations migrate part or all of their SQL Server environments to public cloud providers. Moving databases to the cloud addresses financial, capacity, and resource issues that may have been difficult to resolve with an on-premises implementation. SQL Server DBAs need to be prepared to administer databases that might be physically located in multiple locations.
The selection of a public cloud provider is usually made by company decision-makers after considering the pros and cons of various vendors. The choice is often made based on multiple factors including the ability of a provider to handle the specific workload that is to be migrated. Input from DBAs may be elicited to help make the final decision on the best home for enterprise SQL Server databases.
In many cases, Microsoft Azure ends up being the logical choice for housing SQL Server databases in the cloud. As the original developer of SQL Server, Microsoft has introduced cloud offerings that are specifically designed to address the needs of SQL Server customers. Clients can simply move their databases to Azure and manage them in essentially the same way as before the migration using Azure SQL Managed database instances.
Azure SQL Databases
Another option is to take advantage of Azure SQL Databases. These are fully managed database-as-a-service instances that are tightly integrated into the Azure infrastructure. There are some methods of improving database performance that can specifically address issues with Azure SQL Database queries. DBAs working in Azure are encouraged to take a look at an IDERA Geek Sync Webcast which provides a comprehensive overview of improving query performance in Azure SQL Database. Following are some areas of interest that are covered in the webcast.
Choosing the correct region
Large cloud vendors such as Microsoft usually have more than one data center from which they provide services to their customers. Azure is logically and physically broken up into geographies and regions. For instance, the United States is considered a geography with multiple regions located throughout the country. Selecting the right region for Azure SQL Databases can have a dramatic impact on database performance.
Latency is the delay caused by the physical limitations of transmitting data over a network. Signals take longer to travel greater distances. The ability to choose the regions where your Azure SQL Databases will be housed allows them to be close to your web servers and minimize latency. Moving to a new region in Azure can save a small amount of time on each transaction which can add up to serious overall performance improvement.
Both the virtual core (vCore) and database transaction unit (DTU) purchasing models are available for Azure SQL Databases. The vCore model is more customizable and enables clients to choose between a provisioned or serverless compute tier. In the DTU model, compute and storage packages are bundled to address the needs of common workloads.
Within each of these purchasing models, three service tiers provide increasingly more power. This power is not furnished for free, and moving to a higher tier will entail additional costs. Customers have the ability to move freely between the General Purpose, Hyperscale, and Business Critical tiers as their requirements change. It is common practice for a company to move to a higher tier to address seasonal workload spikes and scale back to save money when the additional power and capacity are no longer needed.
Automatic tuning
Another appealing feature of Azure SQL Databases is the ability of the system to conduct automatic query tuning to address performance issues. This feature uses a combination of query analytics and machine learning to suggest tuning options. Three actions can be taken by automatic tuning:
- Creating an index to address a query issue;
- Dropping an index that is not needed;
- Forcing the execution of the last good plan.
By default, index creation and index deletion are turned off. Settings can be modified to handle query problems in the most efficient way that conforms to your business needs.
When You Need Additional Diagnostic Capabilities
At times, the native functionality of Azure SQL Database may not be enough to completely resolve query performance issues with your systems. Third-party tools like IDERA’s SQL Diagnostic Manager for SQL Server can help fill the gaps and enable your team to attain the desired level of performance across the SQL Server environment. It provides a real-time view of the health, availability, and performance of your systems.
The tool is designed to allow teams to monitor, diagnose, alert, and report on their complete SQL Server environment from a single interface. It is compatible with on-premises or cloud-based virtual and physical database instances. Alerts are customizable and can be sent to different audiences based on the specific alert and when it was generated. Alert response actions configured to handle Azure-specific alerts can trigger actions such as switching tiers when conditions dictate an increase or decrease in resources is desired.
With a combination of the power of Azure SQL Database and SQL Diagnostic Manager for SQL Server, database teams can raise the level of performance of enterprise database instances and keep end-users and management happy.