Install SQL Monitor without needing to provision a (large) SQL Server database
CONTEXT: One challenge with installing SQL Monitor is that we need to provide a connection to a SQL Server database.
PROBLEM: This can be costly or complicated. It would be better if SQL Monitor had an alternative backend.
• Offer a hosted data store
• Automatically deploy a free backend alternative such as mongoDB or PostgreSQL
• Reduce data collection so that SQL Express can be used for small deployments
• Provide greater granularity over data retention policies
What do you think about these solutions? Got another idea? Let us know in the comments!
We’ve made some improvements in this area but will leave it open so you can continue to give us your suggestions
Greg Smulko (Redgate) commented
Hi @Phil, thanks for your comment. It is just to let you know that we are actively working on an alternative to MSSQL. Stay tuned. :)
Please consider Postgresql as an alternative repository database. With licensing rules changing at every version - it's increasingly becoming extremely expensive to use MSSQL - especially on-prem. Most recently, I believe the rules are a minimum of 4 cores - which is around $8000.
If I were starting from scratch now, I would simply use multiple Postgresql instances and look for an alternative to Redgate Monitor that did not require MSSQL.
I do not know if the babelfish project is yet at the point that it might help with this (https://babelfishpg.org/docs/usage/migration/) and I do appreciate that full on support for a completely different engine is a big ask - but I think things are going in that direction anyway.
You should consider open source database.
Mike George commented
How about multiple databases for separate collections to achieve these requirements? This would allow for granular control and smaller databases
Steven John Cuthill commented
Support for MySQL backend would be bloody brilliant, with the option to setup and install out of the box.
Adam Mullin commented
Awesome news, thank you Daniel! :)
Set either a customer/user defined field for purging datatypes (Machine/SQL/Alerts), or increase options in the 1-3 month range, for example 6 weeks, 10weeks, 14 weeks. Each site will have different drivers that affect how long their reasonable data retention should be. If they only need 6 weeks, and not two months, why pay for the storage space?
Our Production SQL Monitor DB is north of 90GB, skinnied down to near minimal data retention, (making the tool less useful for historical analysis). Please consider this as more of the norm for larger customers.
We, too, had to either use Azure, incurring the SQL Server license bump, or hunt for a spare license, which wasn't available without hurting a paying customer. Open source DB would be preferable to the 8K for Standard, likely to go up in price with Microsoft's mooted core licensing for Standard edition...
The underlying problem here seems to be that we run into the 10GB limit in order to not having to provision a non-express SQL Server version. I'll merge this suggestion into the corresponding idea.
Andrej Zevnik commented
I also agree it would be less costly solution, to use opensource database. Since SQL Monitor database is usualy not huge in size and does not require a lot of maintenance, Postgres or MySQL would be ideal solution for backend database.
Stephen Bovill commented
The Data purging option currently is very basic on the number of options it has, the main issue is the Custom Metric Data is all headed under one setting, which means if I have custom metrics that I want to keep for all of time, all custom metrics must be kept for all of time, ideally there are three metrics I would like keeping till the end of time so I can do comparisons on them for year on year data, and there are some metrics which after a week are completely irrelevant.
I would like the option to be able to select purging times for each of the custom metrics so the main data can be kept while data that is mainly for alerts is purged in a weeks (or months) time
Thanks for your time and concideration
Thomas Franz commented
If they would consider to use a separate database per monitored server (plus a DB that gathers all the metrics from all DBs in views) instead only a single, big DB it would solve the problem too.
In this case you would only need to set up a free SQL Server Express on an old server / VM and would be able to full monitor a server for ~80 days (150 MB per Server and day) within the 10 GB limit (more if you aggregate the datas more often or reduces the sampling rate).
eric twilegar commented
Sounds weird to use a non sql server product, but it adds a lot of cost to sql monitor to have to license sql web or sql standard in addition. Do you really want to use the server you are monitoring to store the data about it being monitored. Basically the base agent could just install mongo ( works on windows ) and use it for no additional costs. Or maybe get MS to not be dumb and give a free copy of SQL Server given you are monitoring a paid one likely.
How about allow usage of SQL Express by using 1 Database per server as Repository?
John Stinchcomb commented
I definitely agree with this, however, I'd be just as okay with MySQL.
Rich James commented
I'm in two minds about using this. On the one hand, it's another DB architecture to look after and know how to recover, etc.
On the other, it saves a LOT of money. Given the current per core licensing in enterprise agreements, dedicating a whole server to this is a big pot of cash.
Whichever way it's implemented, it'll cost, as I have trouble running this on a regular server; with 18 DBs monitored, the DB is currently running over 150GB in size (and that's with a lot of purge options cut right back).
Adam Mullin commented
Would really appreciate this feature, if it were possible. For a lot of shops, SQL Server cost is prohibitively expensive.
Thanks for the comment. We specifically decided to store the data repository in SQL Server, because it's a technology that our customers are familiar with, and they can manage it using their existing maintenance processes. It will be interested to see if others would prefer we support another backend database platform.
afroz ahmed commented
SQL Monitor only supports SQL Server as backend database to store SQL Monitor specific data. As a result we are using one standard edition license and SQL Server express edition doesn't cut it. A SQL license adds to the total cost of ownership of SQL Monitor. It will be awesome if Postgres can be used. If the code is written in a database agnostic way, it won't be hard to accommodate this request.
Koen Wuyts commented
Before we installed SQL Monitor we had our own system, which stored detailed data for 1 month, but aggregated the data older then one month, creating averages per day. This allowed us to show trends over long periodes of time, (more then 5 years of data) which helps us to predict future needs.
I would like to see 3 months, 6 months and 12 months added to the statisitics screen, but this will only be possible with data agraggation. Were are only monitoring 1 of our servers for 14 days, and have already gathered +11GB of data. Monitoring our top 5 servers for +12 months would require 1.43 TB of space :-) a bit to much.
SQL Monitor is a good tool to manage the servers on a daily basis. But currently not so to help with long term decisions.