SQL Server IaaS Agent extension

-> 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.

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.

Azure SQL Managed Instance, Azure database Health Check using Azure SQL Analytics

-> Azure SQL Analytics uses Azure log Analytics and provides several reports with regards to Azure SQL tasks and performance. It supports below Azure SQL resources,

  1. Azure SQL Elastic Pools
  2. Azure SQL Databases
  3. Azure SQL Managed Instance and databases

-> Let’s setup Azure SQL Analytics and check using below steps.

-> Click “Create a resource”, type “Azure SQL Analytics” and select it.

-> Once you click “Create”. You will be advised to select an existing Log Analytics Workspace or create a new one. In our case, I will be creating a new Log Analytics Workspace,

-> Azure SQL Analytics is created. Now data should be sent to Log Analytics workspace.

-> All Azure SQL resources like Azure SQL Managed Instance, Azure SQL Database or Elastic pool can be configured to send its data to Log Analytics workspace by configuring “Diagnostic settings” under “Monitoring” section.

  1. Azure SQL Managed Instance

-> Open appropriate Azure SQL Managed Instance from Azure portal. Click “Add diagnostic setting”,

1.1 Azure SQL Managed Instance Database

-> Open Azure SQL Managed Instance database from Azure portal. Click “Add diagnostic setting”,

2. SQL Elastic Pool

-> Open SQL elastic pool from Azure portal. Click “Add diagnostic setting”,

3. Azure SQL Databases

-> Open required Azure SQL database from Azure portal. Click “Add diagnostic setting”,

-> Let us try opening Azure SQL Analytics and check what details we have.

-> Open “Log Analytics Workspaces”,

-> Click on “Workspace Summary” and you will see below screen,

-> Click on “Azure SQL databases” and you will get details collected so far,

-> Click on “Resources per utilization Bucket” and you will see more details,

-> You can modify queries to get more details,

-> Now details for “Azure SQL Managed Instance”,

-> You can get more details from this solution that would give you some good information to complete a health check of Azure SQL database or managed instance

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.

Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing

AlwaysOn + Log shipping related blogs
Below blog post are all related to AlwaysOn and log shipping configured together,
-> Configuring Log Shipping with AlwaysOn
-> Removing Log shipping on a database with AlwaysOn configured

Environment

-> JBSAG1, JBSAG2 and JBSAG3 are part of a failover cluster without shared storage. Alwayson Availability group is configured between JBSAG1, JBSAG2 and JBSAG3. JBSAG1 is the current primary, JBSAG2 is the synchronous secondary and JBSAG3 is Asynchronous secondary. The database(s) that is part of Always on Availability group has log shipping configured and its secondary is on JBSAG4.

-> LSCopy and LSRestore jobs were failing.

-> LSCopy job Error,

Date 1/8/2021 1:04:00 PM
Log Job History (LSCopy_JBSAG1_JBDB)
Step ID 1
Server JBSAG4
Job Name LSCopy_JBSAG1_JBDB
Step Name Log shipping copy job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2021-01-08 13:04:00.30 *** Error: Could not retrieve copy settings for secondary ID ‘1d58dd23-142c-498f-83ab-5077791b5781’.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:04:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:04:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

2021-01-08 13:04:00.30 —– END OF TRANSACTION LOG COPY —–
Exit Status: 1 (Error)

-> LSRestore job error,

Date 1/8/2021 1:10:00 PM
Log Job History (LSRestore_JBSAG1_JBDB)
Step ID 1
Server JBSAG4
Job Name LSRestore_JBSAG1_JBDB
Step Name Log shipping restore log job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2021-01-08 13:10:00.28 *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.28 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:10:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:10:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

2021-01-08 13:10:00.30 —– END OF TRANSACTION LOG RESTORE —–
Exit Status: 1 (Error)

-> I searched through the internet and found same/similar errors in LSCopy and LSRestore jobs, if the server mentioned on these jobs are not the log shipping secondary server. Let us look what I have in my database server.

-> LSCopy job step contains below command,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

-> LSRestore job step contains below command,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

-> In our setup, LSCopy and LSRestore jobs contains the correct log shipping secondary server as above marked in green. You need to ensure that LSCopy and LSRestore jobs should contain the log shipping secondary servers only.

-> I started a profiler trace on Log shipping secondary server and started LSCopy job and found below,

-> From the above screenshot, function sys.fn_MSvalidatelogshipagentidreturns value 1 or 0 depending on below query,

return case
when ((@agent_type = 0) and
exists (select * from msdb.dbo.log_shipping_monitor_primary
where primary_id = @agent_id)
) then 1
when ((@agent_type in (1,2)) and
exists (select * from msdb.dbo.log_shipping_monitor_secondary
where secondary_id = @agent_id)
) then 1
else 0 end

-> Lets execute the below query on Log shipping secondary JBSAG4 and verify the output,

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary]
where secondary_id = '1D58DD23-142C-498F-83AB-5077791B5781'

-> We dont see any data in object msdb.dbo.log_shipping_monitor_secondary for agent_id specified in LSCopy job, so this means it is going to return always 0 and you will see below error,

Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider)

-> Lets execute below query and see what valid id we have in object log_shipping_monitor_secondary,

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary]
where primary_database = ‘JBDB’

-> Secondary_id is 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23. Lets try replacing the LSCopy and LSRestore job as below,

LSCopy job,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4

LSRestore job,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4

-> Once I have replaced the jobs using above query. The job completed fine.

-> It seems Always On failover happened from JBSAG1 to JBSAG2, once after the failover someone have opened the log shipping settings and configured secondary server on JBSAG2 as below,

-> Above action created 1 more LSCopy and LSRestore job on secondary server JBSAG4. It seems like the job created as part of JBSAG2 configuration was later removed and this left the other job with wrong secondary_id value. This is the reason for failure.

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.