-> The SQL Server IaaS agent extension runs on Azure virtual machines that has SQL Server installed on it to automate management and administration tasks.
-> You can use below 2 methods to check if SQL Server IaaS extension is enabled on your Azure Virtual machine.
Method 1
-> Type “sql virtual machine” in search box of Azure portal and select “SQL Virtual machines”,
-> If you see your Azure virtual machine with SQL server within “SQL Virtual machines”, then it means that SQL Server IaaS Agent extension is enabled.
-> Next question would be how did this Azure virtual machine with SQL Server got the IaaS extension enabled and how come other Azure virtual machine with SQL Server not in the list? The answer to this is that SQL Server IaaS extension gets enabled automatically if you provision an Azure VM with SQL Server from Azure Marketplace. If you have provisioned an Azure VM alone and then installed SQL Server manually onto the Azure VM, then SQL Server IaaS extension will not get enabled automatically if “Automatic SQL Server VM registration” is not enabled on that subscription.
Method 2
-> Type “virtual machine” in search box of Azure portal and select “Virtual machines”,
-> Navigate to an appropriate Azure Virtual machine that has SQL server installed. Click on extensions and If you see “SqlIaasExtension” as shown in below screenshot visible, this means that SQL Server IaaS extension is enabled.
Turn on “Automatic SQL Server VM registration”
The automatic registration of a subscription will register all currently available SQL VMs with the SQL VM resource provider in lightweight mode as well as any SQL VMs deployed to the subscription in the future. This process does not restart the SQL Server service. Manually upgrading to full manageability mode is recommended to take advantage of the full feature set and that will require SQL Service restart.
-> Type “sql virtual machine” in search box of Azure portal and select “SQL Virtual machines”,
-> Click on “Automatic SQL Server VM registration”,
-> Click on “Register”. Once registered, Discovery process will kick in and with in 48 hours all Azure Virtual machines with SQL Server installed on that Subscription will be showing up under “SQL Virtual machine”. Please note that this is per subscription only.
Register Azure SQL VM with SQL IaaS extension manually
Lightweight Mode
-> You can use below method to register an Azure SQL VM manually in Lightweight mode.
-> On Azure portal, click on “Cloud Shell”,
-> Execute below command. You have to change the values marked in red as appropriate,
az sql vm create --name jb-db-sql2 --resource-group jb_sql --location eastus --license-type PAYG
Full Mode
-> You can use below method to register an Azure SQL VM manually in Full mode. This will require a SQL Service restart.
-> On Azure portal, click on “Cloud Shell”,
-> Execute below command. You have to change the values marked in red as appropriate,
New-AzSqlVM -Name jb-db-sql2 -ResourceGroupName jb_sql -SqlManagementType Full
-> Below is the view of “SQL Virtual Machine” blade with “Management Mode”,
Management modes
Below management modes can be used to register SQL IaaS extension,
Lightweight
This mode does not install SQL IaaS Agent on Azure VM and will not require a SQL Service restart. It supports only changing the license type, edition of SQL server and provides limited portal management. This option is used for Azure SQL Server VM’s with multiple instances, or those participating in a failover cluster instance (FCI). Lightweight mode is the default management mode when using the automatic registration feature. There is no impact to memory or CPU when using the lightweight mode, and there is no associated cost. It is recommended to register your SQL Server VM in lightweight mode first, and then upgrade to Full mode during a scheduled maintenance window.
-> You will see below message when an Azure SQL VM is registered with Lightweight management mode and you will see several blades under “settings” disabled as shown in below screenshot,
Full
This mode installs the SQL IaaS Agent to the Azure VM to deliver all functionality, but requires a restart of the SQL Server service. Use it for managing a SQL Server VM with a single instance. Full mode installs two windows services that have a minimal impact to memory and CPU – these can be monitored through task manager. There is no cost associated with using the full manageability mode. All options under “Settings” in Azure SQL VM will be available only in this mode.
-> Below are the 2 Windows services that gets installed when Full mode is used,
NoAgent
This mode is dedicated to SQL Server 2008 and SQL Server 2008 R2 installed on Windows Server 2008. There is no impact to memory or CPU when using the NoAgent mode. There is no cost associated with using the NoAgent manageability mode, the SQL Server is not restarted, and an agent is not installed to the VM.
This article only discusses on Lightweight and Full management modes.
Upgrade from Lightweight to Full mode
Using Azure portal
-> You can change the management mode from LightWeight to Full using below steps. Please note that this will require a SQL Server restart.
-> Click on below message to proceed,
-> When you check the Azure SQL VM again, you will see everything under “Settings” enabled,
Using Azure Cloud
-> On Azure portal, click on “Cloud Shell”,
-> Execute below command. You have to change the values marked in red as appropriate,
az sql vm update --name jb-db-sql2 --resource-group jb_sql --sql-mgmt-type full
-> You may receive below error when changing the management mode of Azure SQL VM from Lightweight to Full.
Deployment to resource group ‘jbresearch’ failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.
Found multiple SQL Server named instances in the Virtual Machine. (Code: Ext_SQLMultipleNamedInsatncesFound)
-> It is to be noted that Lightweight mode management mode should be used for Azure SQL Server VM’s with multiple instances, or those participating in a failover cluster instance (FCI). In this case, Azure SQL VM “jbresearch” contains 2 SQL Server instances and hence its management mode cannot be changed from Lightweight to full mode.
-> Let’s compare the “Virtual Machine” and “SQL Virtual Machine” screen in Azure portal side by side for Azure SQL VM “jb-db-sql1”. It is clear that the details in “Virtual Machine” and “SQL Virtual Machine” screen are mostly different to one another.
-> Let us check each options under “Settings” for “SQL Virtual Machine” in Azure Portal.
-> Below are the options available,
1) Configure
-> SQL Server License can be Pay As You Go , Azure Hybrid Benefit or HA/DR.
-> Edition will be the SQL Server edition installed on the Azure VM.
2) Security
-> SQL connectivity can Private (within Virtual Network), Local (Inside VM Only) and Public (Internet).
-> Port can be whatever port that should be used to connect to SQL Server instance.
3) Patching
-> Automated patching can be enabled. In my case I have a daily maintenance schedule at 11:00 PM with a maintenance window of 2 hours. Please note that Only Windows and SQL Server updates marked Important are installed. Other SQL Server updates, such as Cumulative Updates, must be installed manually.
-> This option doesn’t fit into most of the production SQL Server. I personally dont use this as I prefer performing manual updates on need basis.
4) Backups
-> Backups can be enabled for all databases in Azure SQL VM using this option. All SQL Server databases in Azure SQL VM will be backed up automatically per the settings you choose. If you decide to change settings via SQL Server Managed Backup in the future, the new settings will override the Automated Backup settings.
5) Additional features
-> R services (Advanced analytics) can be enabled using this option. Install this feature during SQL Server setup to enable secure execution of R scripts on the SQL Server virtual machine.
6) High Availability (preview)
-> Always On availability group will be utilized for this feature. Lets check how we implement this.
-> Azure SQL VM jb-db-sql1 and jb-db-sql2 which has SQL Server standard edition will be used for this activity.
-> Type “sql virtual machine” in search box of Azure portal and select “SQL Virtual machines”,
-> Select the SQL Virtual machine for which High Availability should be configured. In my case it is jb-db-sql1.
-> Click on “High Availability (Preview)” under Settings in Azure portal and then Click on “+ New Windows Server Failover Cluster”,
-> You can only attach enterprise SQL Server VMs that are registered in full manageability mode, in the same resource group, location, domain, and in the same virtual network as the SQL Server VM: jb-db-sql1. Jb-db-sql1 and jb-db-sql2 Azure SQL VM’s have SQL Server standard edition installed. Let’s check what happens when we try setting up with SQL Server Standard edition,
-> As expected it failed with below error,
Deployment to resource group ‘jb_sql’ failed.
Additional details from the underlying API that might be helpful: At least one resource deployment operation failed. Please list deployment operations for details. Please see https://aka.ms/DeployOperations for usage details.
-> It is pretty clear that we need enterprise Azure SQL Server VMs to use this feature.
-> Azure SQL VM jb-db-sql03 and jb-db-sql04 which has SQL Server enterprise edition will be used for below activity.
-> Type “sql virtual machine” in search box of Azure portal and select “SQL Virtual machines”,
-> Select the SQL Virtual machine for which High Availability should be configured. In my case it is jb-db-sql03.
-> Click on “High Availability (Preview)” under Settings in Azure portal and then Click on “+ New Windows Server Failover Cluster”,
-> Provide the Cluster name, in my case it is JBCLUSTER.
-> Use an existing storage for “Witness Storage Account” or create a new one. In my case, I am creating a new storage account called “JBCLUSTER_Witness”.
-> Provide “SQL Service account” and “Password” as requested.
-> As I am creating this for demo purpose, I am using “Cluster bootstrap credentials” which is used for creating cluster (at minimum needs permissions to ‘Create Computer Objects’ in domain) and “Cluster operator credentials” which is used for operating cluster that will be part of administrators group on all the participating SQL Server VMs in the cluster as “Same as SQL service account“. This can be changed as appropriate in your case.
-> Jb-db-sql03 and jb-db-sql04 will be part of the high availability. Hence, I will select jb-db-sql04.
-> Open “SQL Virtual Machine” jb-db-sql04 from Azure portal, click on “High Availability (Preview)” and click on “Configure Windows Server Failover Cluster”,
-> Open “SQL Virtual Machine” jb-db-sql03 from Azure portal, click on “High Availability (Preview)” and click on “Configure Windows Server Failover Cluster”,
-> I received below error,
Error code : SqlVmGroupUpdateFailVmAttached
Message : SQL virtual machine group cannot be updated as it has attached a SQL virtual machine.
SQL virtual machine group cannot be updated as it has attached a SQL virtual machine. (SqlVmGroupUpdateFailVmAttached).
-> I restarted Virtual machine jb-db-sql03 and then performed the same operation and it completed fine.
-> Logged into jb-db-sql03 and executed below powershell command to validate the cluster,
Test-Cluster -Node ("jb-db-sql03","jb-db-sql04") -Include "Inventory", "Network", "System Configuration"
-> Type “sql virtual machine” in search box of Azure portal and select “SQL Virtual machines”,
-> Select the SQL Virtual machine jb-db-sql03 and Click on “High Availability (Preview)” under Settings in Azure portal and then Click on “+ New Always On availability group”,
-> Click “Apply”. Click on “+Add Replica”,
-> Add required replica. Option “Automatic failover”, Sync commit” and “Readable secondary” should be selected as required.
-> It completed without any errors,
-> On Azure SQL VM jb-db-sql03, click on “High Availability (Preview) under settings,
-> Synchronization Health is “Not Healthy”. Availability group’s synchronization status will remain unhealthy until a database is added to the availability group.
-> Let us add a database to availability group using SQL Server management studio.
-> Lets check the Azure portal for “High Availability (Preview)” under setting for Azure SQL VM SQL-DB-SQL03,
-> Tried connecting to listener JBAPP after performing a failover and failback using Always On Availability group wizard and it worked fine.
-> You can add a replica, configure listener and delete availability group from the portal if required.
-> There is a possibility where Always On deployment fails with below error,
All virtual machine specified should contain only standard public IP, when providing load balancer standard sku. (Code: OnlyStandardPublicIp)
-> This error happens when SKU used for JB-DB-SQL03 and JB-DB-SQL04 Public IP Address is set as “Basic“. It should be “Standard” and this will also make the IP Address static. This will resolve this issue. This is because of below reason as advised in this article.
7) Properties
This is just a FYI section that provide details of you Azure SQL VM.
More articles on IaaS Agent Extension
-> SQL SERVER IAAS AGENT EXTENSION – “Security” tab not populated correctly
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.