Microsoft is rapidly expanding the services and features available from the Azure Arc SQL Extension. As this moves into GA and more visibility features get added and importantly, features that allow actions EG controlling backup and even patching using Azure to create a management layer for your SQL Server estate outside of Azure becomes more viable.

Let’s take a brief look at grabbing SQL-specific performance counters to Azure Monitor Metrics.

We need to create a Data Collection Rule (DCR). I am not using Data Collection Endpoints (DCE), but you may want to consider this in your environment.

Select the Arc Resource you want to add

now we need to add some Performance counter

it will add a bunch by default; select None, then Custom to clear all.

Unfortunately, currently, you can not see the SQL counters from this view, so you’ll have to go collect what you want from another source.

You can use Perfmon, unfortunately you cant copy from perfmon.

this query will provide a list

SELECT COUNT(*) FROM sys.dm_os_performance_counters;

Here is a list I have extracted for this DCR

\SQLServer:General Statistics\User Connections
\SQLServer:General Statistics\Logins/sec
\SQLServer:General Statistics\Transactions
\SQLServer:Databases(_Total)\Active Transactions
\SQLServer:SQL Statistics\Batch Requests/sec
\SQLServer:SQL Statistics\Logical Connections
\SQLServer:SQL Statistics\SQL Compilations/sec
\SQLServer:SQL Statistics\SQL Re-Compilations/sec

Paste it in line by line, Add, tick, Delete line, next counter. Adjust the Sample rate as needed. I have used the default 60 seconds. For large SQL servers estates, you might want to consider increasing this depending on your needs.

It’s worth considering building a template for this if you want to do this repeatedly. This resource type has some challenging layers to work through for deployment. Microsoft reference material can be found here.

we pipe these into Azure Monitor Metrics Which is still in preview and for this exercise I will also push the data into a log analytics instance.

as of this writing the metrics for hybrid compute fail to be added to scope of Azure Monitor. For the remainder of this example we will use Log Analytics Workspace for metrics.

Heading over to the Log Analytics Workspace. You can create various Kusto queries to integrate these SQL Metrics

Perf
| where ObjectName == 'SQLServer:General Statistics' and CounterName == 'User Connections'
| project TimeGenerated, Computer, ObjectName, CounterName, CounterValue
| summarize avg(CounterValue) by bin(TimeGenerated,60m) , Computer
| render timechart

You can send this to a Workbook or Dashboard

We can now see the data on a chart in a workbook. By saving the you can give the workbook a name and save it to a resource group

Alternatively, you can publish this to an Azure Dashboard. I am going to use the dashboard I created here Arc SQL Extension - Best Practices Assessment — Crying Cloud

This displays a tile here. You can continue to add tiles and edit the queries. You build workbooks and dashboards to target the specific metrics about your Arc-enabled SQL Servers.