Improving SQL Monitor automation for installs and upgrades with Cloud deployment focus

sheldonhull

I'd like to propose a couple things be considered here related to the installation and maintenance of base/web monitoring components. 

This is coming from someone invested in the AWS platform and looking to improve RDS + EC2 Sql server monitoring. I also have experience with configuration/automation setup automation using Amazon's Systems Manager.

1. Make a chocolatey feed for your installer. This would greatly simplify running upgrades and installs, instead of needing others to rewrap these installs into another self-managed package. See for example: https://chocolatey.org/packages/SqlToolbelt which is a good start. 

A dedicated one for sql monitor agents would be great, OR the logic added with a switch to only pull MSI for the appropriate installer with a specific version.

2. Consider alternatively PowerShell DSC defined resource for installation. This would allow usage of choco or MSI from S3 directly. This has the added benefit of in AWS being directly integrated with AWS SSM (Systems Manager). This means you could have a SSM Execution command that runs against all base monitors and automatically pulls credentials for service accounts and other critical info to pass into the DSC doc. This also reports back compliance automatically in AWS SSM Compliance tab to show success/failure. 

3. Lastly, and personally my favorite option.... ECS Fargate. If the base monitor is in .NET core then a terraform plan to deploy a ECS fargate container could be almost plug and play and have zero infra to manage. If it's windows only then ECS Cluster + ECS Task definition would be required. There are modules that let this all be defined in one stack. I plan on trying this on my own eventually, but having it as a provided and supported option would be fantastic in easier adoption and deployment across many accounts. 

Would be epic to have a definition spin up ECS container for monitoring and optionally RDS instance for the data repository for example.

To be honest the lack of having all of this is something that's made me heavily consider just using cloudwatch/datadog in the future rather than SQL Monitor, because the administration is more. Long term, I see the maintenance/updates not being provided so easily as a reason many in my team would just opt for using the less robust Datadog or telegraf collected metrics.

jeremiahpeschka

Howdy sheldon! Thanks for taking the time to share these ideas. We are currently looking at supplying MSIs for the web and base monitor installers to make upgrades and automation easier.

Right now, we have some support for automation, which I suspect you've found. Obviously, we don't want to keep track of your passwords so, apart from password, are there any parameters that you would expect to be supplying on an update? Or would you expect that you could just run and update and only pass passwords to the MSI?

Also thinking about MSIs, the first step is just going to be for us to get them out there. Would having MSIs available at something like download.red-gate.com/latest/sql-monitor-web.msi be good enough?

And, finally, for chocolatey, would you expect all install customization options to immediately be available?