SQL server Installed in Azure
VM
1.
Introduction
When planning for a new SQL database One of the primary
decisions that one need to make is where it’s going to reside. In the past,
only option was an on premise installation of sql server, but with the
capabilities of Azure we now have additional options to be considered. One such
option is SQL server installed on Azure VM.
This option is described as infrastructure as Service or IaaS.
SQL Server IaaS helps FAST migration of the existing
applications to the cloud with minimal changes. With this method we use cloud
platform to provide computing resources in the form of a VM. With IaaS
Microsoft provides the hardware and OS and SQL server installation (if
selected). We will have full control of OS and SQL Server installations
2.
Sizing of SQL Server VM
Sizing SQL Server Azure IaaS VMs is similar to the
traditional way we size on-premises systems. However, there are a couple of
concepts we need to understand about storage and how to approach cloud systems to
ensure you select the right size to host your workloads.
Azure Storage There are multiple types of Azure storage
available from Standard to Premium disks, both managed and un-managed. The type
of storage selected for SQL VMs will have a bearing not only on performance but
also on whether we will be able to meet the requirements covered by the SLAs
The size and class of VM selected will also dictate the
number of data disks you can use and the performance profile for them regarding
IOPS and throughput. Monitoring the VM and Database server will help in
optimizing the performance and cost
3.
Monitoring of Database in Azure VM
We have to make sure we have great insight into our
database, and insight comes from monitoring. If there are undetected database
problems, then problems will be exhibited throughout the entire application,
and it will be very hard to diagnose.
Database monitoring
should cover
·
Performance
·
SLA adherence and Service Health
·
Spikes and unexpected errors
·
Capacity –CPU, Memory, IOPS etc.
4.
Types of Monitoring Data in Azure
These are different types of monitoring data in Azure
4.1
Metrics
o
Virtual Machine Host metrics like CPU
percentage, IO, Disk space allocated etc. can be captured. These are available
by default
Note: SQL server Metrics are not available for SQL Server
IaaS Version
4.2
Logs
Logs are broadly of two types
4.2.1
Activity logs
It shows information about what's happened at the fabric
level to a resource, a resource was created, it was modified, it had a new key
generated to it etc.
4.2.2
Diagnostic logs
These come from inside the resource, and can send those to
various targets.
Ø
Enable Guest Level monitoring as shown below
Ø
Once Enabled, it provides several options to
capture diagnostic logs
5.
Database Metrics and Monitoring
5.1
Azure SQL databases and Monitoring tools
The below tables has the tools available in Azure for
monitoring database performance metrics
|
Azure SQL Server types |
Purpose |
Monitoring Tools |
Comments |
|
Azure SQL Database |
Managed, intelligent SQL in the cloud |
Azure Monitor |
Azure SQL Database includes database advisors that provide
performance tuning recommendations for single and pooled databases. |
|
Azure SQL Managed Instance |
Managed, always up-to-date SQL instance in the cloud |
Azure Monitor |
|
|
SQL Server on Virtual Machines |
Migrate your SQL workloads to Azure while maintaining complete SQL
Server compatibility and operating system-level access |
Custom Logging via Log Analytics |
Commercial tools are also
available like SolarWinds SQL sentry, Dynatrace, AppDynamics etc which installs
the agent in SQL server machine for monitoring |
5.2
Analytical Tools
Analytical tools are meant to derive meaning insights from
the metrics captured using monitoring tools
6. Custom Logging via Log Analytics
There are no built in solution to monitor performance of SQL
Server hosted in Azure VM but it is just a normal SQL Server database, and so
many of the metrics and alerts that one need for monitoring SQL performance are
the same as those need for any other SQL Server instance.
In case of Azure SQL Database, then we have the option of
using the built-in monitoring that is supplied in the Azure Portal to obtain
the overview of your entire Azure SQL Server estate, and to see it within the
context of our overall Azure resources
In SQL Server VM, Dynamic management views can be used to
monitor the health of a server instance, diagnose problems, and tune
performance. These views can be
triggered via AZURE functions at period intervals to get performance and
diagnostic information
- Enable VIEW SERVER STATE permission on the SQL server.
- Connect to SQL SERVER via AZURE Timer Trigger Function and custom log output of the SQL DMV queries to Azure Log Analytics Workspace
- Configure sql connection string, Loganalytics workspaceid and sharedkey in the keyvault. Details of workspaceid and key can be found in the the azure portal. Please refer the below screenshot
- Make sure all the sensitive information is stored on the keyvault and access is provided to the required function resource through the service principal
- Custom logs are stored in Log analytics Workspace as shown below
- Data from logs can be queried and saved to a dashboard as shown below
- These DMV queries can be configured in json file stored in a blob storage and can be customized by adding/deleting new ones as needed
- Dashboard with such metrics can be collated and displayed as below
7.
Conclusion
There are several commercial tools available in the market
which can also be deployed in the Database server to get performance metrics
and diagnostic details for debugging purposes. However, the solution approach
provided in the document is easy and simple and can be deployed with limited
overhead on the servers without extra license cost (Excluding storage cost and
Azure function cost)
Storage Cost
Ø
Data injection – 5 GB free per month - $2.76 per
GB
Ø
Data Retention – 31 days free - $0.12 per
GB per month
Azure Function Cost
Consumption plan pricing includes a monthly free grant of 1
million requests and 4,00,000 GB-s of resource consumption per month per
subscription in pay-as-you-go pricing across all function apps in that
subscription. Details refer below
https://azure.microsoft.com/en-in/pricing/details/functions/
