For virtual machines in Azure environment on which SQL Server is running, a new management mode has been introduced that makes the maintenance activities necessary to increase security easier, obtain cost benefits and optimize deployments. This article describes how to enable this new feature and explores the benefits that can be achieved.
Azure provides a wide range of fully managed SQL database services, modern and secure that can support different scenarios, like re-hosting, modernizing existing SQL Server workloads and developing new cloud applications.
Figure 1 - SQL Service family
Although the adoption of managed services leads to high benefits in terms of costs, management and scalability, IaaS virtual machines with SQL Server installed are often still required in the Azure environment. This scenario is also common when dealing with "lift and shift" migrations of virtual machines from the on-premises environment.
Figure 2 – Administration effort in Cloud migration scenarios
In order to optimize and automate management and administration tasks, it is possible to activate the new extension SQL Server IaaS Agent (SqlIaasExtension) on Azure virtual machines with SQL Server installed. By registering this extension from the Azure portal it will be possible to access the "SQL virtual machines" group of resources as well as the classic "Virtual machines".
The adoption of this extension is completely free and the data collection carried out is aimed exclusively at offering new features from the Azure portal. Collected data will not be used by Microsoft to perform license checks without the customer's prior consent.
How to activate this new management method?
The first step required to use the extension SQL Server IaaS Agent is to register the resource provider Microsoft.SqlVirtualMachine on the specific subscription. This provider offers to the extension the ability to create resources within that specific subscription.
Figure 3 – Resource provider registration
Once this operation has been completed, it is advisable to choose the management method to be adopted, among the following:
- Lightweight mode: in this mode, the extension binary files are copied to the virtual machine, but no agent is installed and the SQL Server service running on the VM is not restarted. By adopting this mode, you can only change the type of license and the edition of SQL Server, in addition to having a limited set of management options. This is the default management mode when using the automatic registration feature which can be activated from the Azure portal or through manual registration.
Figure 4 – Auto-enrollment from the Azure portal
Figure 5 – Select the subscription during the automatic registration phase from the Azure portal
The adoption of this mode has no impact on the use of virtual machine resources in terms of memory and CPU and it is recommended to activate this mode before the full management mode (full mode).
- Full mode: in this mode, it is planned to install the SQL IaaS Agent aboard the virtual machine and a complete management experience is provided. Activating this mode involves restarting the SQL Server service. Full mode specifically installs two Windows services that, from direct experience, can have an impact on memory and CPU usage that is not always negligible.
- NoAgent Mode: this is the mode dedicated to installations of SQL Server 2008 and SQL Server 2008 R2 on board Windows Server 2008. For this mode there is no impact on the use of memory or CPU and it is not necessary to restart SQL Server.
Virtual machines with SQL Server that have registered the extension in "lightweight" mode can upgrade to "full" mode via the Azure portal, Azure command line or Azure PowerShell. There is no downgrade procedure, but to switch from "full" mode to "lightweight" mode it is necessary to unregister on the VM the extension SQL IaaS Agent.
When you activate a virtual machine with SQL Server by using the images available in the Azure Marketplace, the extension SQL Server IaaS Agent is automatically registered if the specific resource provider is active on the subscription.
For more details on the registration process and the commands that you can use, please refer to this Microsoft's document.
Features offered
The extension SQL Server IaaS Agent allows you to take advantage, direct from the Azure Portal, of the benefits listed below for virtual machines hosting SQL Server:
- Management from the Azure portal: you can view and manage specific SQL-related features of all virtual machines with SQL Server on board, at a single centralized point in the Azure portal.
Figure 6 – SQL Server management using the SQL Server IaaS Agent extension
- Backup management: it will be possible to schedule backups for databases by selecting various options such as backup encryption, the setting of the retention period, the backup of system databases and the configuration of a manual or automatic schedule. This feature is useful for SQL Server protection when you do not want to adopt a specific backup solution, but it is sufficient to back up the databases on the instance to a storage account.
Figure 7 – Manage SQL Server backups by using the SQL Server IaaS Agent extension
- Patching management: you will be allowed to configure a maintenance window during which can be installed security updates, coming from Windows Update and classified as critical or important, of Windows and SQL Server.
Figure 8 – Patching by using the SQL Server IaaS Agent extension
- Security aspects and Azure Key Vault integration: it will be possible to manage the port to connect to the SQL Server instance. Furthermore, you will be allowed to enable SQL authentication, specifying a particular login. If the SQL Server SKU supports it, it is also possible to install and configure integration with Azure Key Vault, to use data encryption features such as Transparent Database Encryption, Column Level Encryption and Always Encrypted.
Figure 9 – Manage security aspects and integration with Azure Key Vault by using the SQL Server IaaS Agent extension
- Licensing management: it will be possible to easily change the way SQL Server is licensed, thus being able to obtain direct cost savings.
Figure 10 – Manage SQL Server licensing by using the SQL Server IaaS Agent extension
- Flexible management of the version and of the edition: in case there is a need to change the version or edition of SQL Server, you can update the metadata within the Azure portal without having to redeploy the entire SQL Server VM.
Figure 11 – Manage the SQL Server edition by using the SQL Server IaaS Agent extension
- Enabling R Services (Advanced analytics): if the system is used in Machine Learning, the possibility of installing this feature is provided, during SQL Server setup, to allow the execution of R scripts on the SQL Server virtual machine.
Figure 12 – Enable R Services by using the SQL Server IaaS Agent extension
- Configure Always On availability group functionality: directly from the Azure portal it is possible to activate high availability and disaster recovery mechanisms by configuring the Always On availability group.
Figure 13 - Activation of the Always On availability group functionality through the SQL Server IaaS Agent extension
Conclusions
Thanks to the adoption of this recent extension SQL Server IaaS Agent, running SQL Server on board an Azure virtual machine allows you to take advantage of various additional features and to have an optimal management experience, similar to SQL Server managed service. All these features also allow for greater ease of use and important advantages in SQL Server management compared to implementations on on-premise virtual machines.
