search

marcingminski / sqlwatch

SQL Server Performance Monitor
https://docs.sqlwatch.io
Other
429 stars 168 forks source link

Agentless setup for Azure? #84

Open dmarlow opened 5 years ago

dmarlow commented 5 years ago

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?

marcingminski commented 5 years ago

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!

dmarlow commented 5 years ago

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?

marcingminski commented 5 years ago

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.

marcingminski commented 5 years ago

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.

tvanderv92 commented 4 years ago

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.

marcingminski commented 4 years ago

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.

marcingminski commented 3 years ago

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

PowerDBAKlaas commented 2 years ago

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

MrWright77 commented 1 year ago

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