sqlwatch Agentless setup for Azure?

I'd like to know how to run this against an Azure SQL database. What would need to be setup to replace the agent jobs that this requires?

Mar 20 '19 17:03 dmarlow

Hi, as you may know Azure SQL is not supported because of lack of Agent and differences in DMVs (and lack of my time to make it all work) List of supported systems is here: https://sqlwatch.io/docs/requirements/

However, if you want to experiment you could try Azure run books. These links may give you enough information to get started: https://gist.github.com/msdotnetclr/98e62732e729351d6b39d26aab4b251e https://docs.microsoft.com/en-us/azure/automation/automation-schedules https://stackoverflow.com/questions/35690699/how-to-run-stored-procedure-using-azure-automation

If you are successful please do come back and share your findings and we may make it work!

Mar 20 '19 23:03 marcingminski

As far as I understand it's these stored procedures that need to be invoked, correct?

https://github.com/marcingminski/sqlwatch/blob/beta4/SQLWATCHDB/Script.PostDeployment1.sql#L797-L801

I'm less concerned about running the SPs from outside of SQL than I am trying to figure out what all won't work (like what perf counters or other stats that aren't available in Azure SQL). I'm not a DBA, but if you could provide some guidance here, I can take this back to my team and we can look into it. Without looking throughout all of the SQL in the repo, I wouldn't know if things would even sort of work in Azure without major changes. What do you think?

Mar 21 '19 03:03 dmarlow

Hi, the collection is covered in docs: https://sqlwatch.io/docs/execution/ I would focus on getting performance working first: https://sqlwatch.io/docs/execution/performance-data-collection/

In short, yes there is a set of SQL Agent jobs that trigger stored procedures with the exception of disk utilisation collector which triggers PS script that calls Windows WMI to get OS disks and usage. This will definitely not work but not a big deal.

Performance counters should be ok but I'm expecting some logic where it uses the master database to fail. In fact, if you look at line 76 of the PostDeployment script (from your link) you may notice it was based on Azure SQL perf counter collection as recommended by MS: /* based on https://blogs.msdn.microsoft.com/dfurman/2015/04/02/collecting-performance-counter-values-from-a-sql-azure-database/ */

I do not know what else will not work. If you run the procedure exec [dbo].[sp_sql_perf_mon_logger] and post any errors back here I will have a look at what we need to do to make it work.

Mar 21 '19 08:03 marcingminski

Hi, I have created a test instance of Azure SQL and I'm looking into this. I will come back to you with some findings soon.

Mar 21 '19 23:03 marcingminski

Hi,

is this issue still open? In the documentation it's stated that this has not been tested yet for Azure Managed Instances. I'd like to test this tool in a development environment.

Mar 16 '20 12:03 tvanderv92

Hi, Yes still open. The managed instance should more or less work out of the box as it's meant to be 100% compatible but I have not tested it at all. Feel free to contribute.

Mar 16 '20 13:03 marcingminski

In case you are still interested, the invocation can now be done via service broker so it will work out of the box.

May 02 '21 20:05 marcingminski

@marcingminski deploy of the dacpac to a Managed Instance fails:

with dbatools: "WARNING: [14:54:11][Install-DbaSqlWatch] DACPAC failed to publish to SqlWatch on myserver.database.windows.net. | Error connecting to [myserver.database.windows.net]: DacInstance with the specified instance_id does not exist.
with SSMS: " a valid URL beginning with 'https://' is required as value for any filepath specified" on "CREATE EVENT SESSION [SQLWATCH_blockers] ..."

Mar 08 '22 14:03 PowerDBAKlaas

So unless I'm misreading it should like this will likely work under a azure managed issuance?

Jun 30 '23 19:06 MrWright77