You should consider open source database.

How about multiple databases for separate collections to achieve these requirements? This would allow for granular control and smaller databases

Support for MySQL backend would be bloody brilliant, with the option to setup and install out of the box.

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.

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.

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
Ste

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).

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?

I definitely agree with this, however, I'd be just as okay with MySQL.

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).

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.

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.

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.

Hi Barry,

Thanks for your suggestion. We are thinking a lot about data retention (for example, we've recently done some research to pinpoint recommended purge windows), and data aggregation is the holy grail in our considerations. However, different types of data require different aggregation tactics, and making this work across the app is a significant chunk of work. We are interested in doing more on this, but since most users are more interested in their recent data rather than their historic data, we often prioritise other work over this problem.

It is possible to extract pruned data (ie. an arbirary subset of representative data points for a given duration) for graphable metrics relatively easily from our data repository. For example, the query...

SELECT * FROM [Cluster_Machine_LogicalDisk_Capacity_UnstableSamples_DateRange]([utils].[DateTimeToTicks]('2013-03-25'), [utils].[DateTimeToTicks]('2013-03-26'), 500)

(object names quoted from memory, please double check)

...would retrieve 500 data points each for disk utilization of every disk on every machine you are monitoring for the specified time range. You could set up a weekly job to extract a week's worth of data from data sources you are interested in and put them into corresponding tables in a different database and derive SSRS reports from this. If you are looking for a workaround, this is probably the best route for you.

Daniel

The data stored within the RedGateMonitor database is extremely detailed and the database can grow very large.
What I would like to do is to copy the data stored in the database to a new database, and to aggregate the data, so that I can retain data for a far longer period of time.
I would like to have the data aggregated into Daily, Weekly and Monthly tables.
Once this has been done, I can then create SSRS reports that will be able to show trends over longer periods of time.

Is there any guidance on how this can be achieved?