I am new here, and I am also a beginner when we talk about SQL server.
Let me describe my problem:
I am having a bunch of IoT devices which stores data to the database (Temperatures, Preasures etc.)
As number of devices grow, database grows too. It is now around 20 GB (I know this is not really big database, but it will be some day - maybe).
What is the problem here? My database have only 3 tables (Settings, DeviceList, Measurements).
(Maybe this is not the best design, but let's leave the aside for now), and only 1 data file and 1 log file (all in primary filegroup).
When I backup my database I always backup the whole database even if I know that no data will be changed (I only do insert and select on this database - no deletes or updates). I could try to do differential backup, but then I have to do full backup first and then few differential.... it seems to complex to me (and still I create a backup of unchanged data)
I read about secondary filegroup which is set as read only. (I didn't completely understand that)
What would really be a solution (at list I think so) is.
Store data in datafile (which is in primary filegroup). At the end of each year, I should "move data" to readonly filegroup, do a FINAL backup and that's it. So each year will be store in its own datafile. (.ndf).
All data is still available for user to query data (SELECT). We can do FULL/DIFFERENTIAL backup on current data (FULL backup monthly/weekly, DIFFERENTIAL backup daily).
Using Maintentance plan to automate this is really a bonus.
So this is what I have in mind:
[READONLY FILEGROUP]
Data_2018 [Readonly] -> (.ndf) backup once at the end of the year, mark as read only
Data_2018 [Readonly] -> (.ndf) backup once at the end of the year, mark as read only
Data_2019 [Readonly] -> (.ndf) backup once at the end of the year
Data_2020 [Readonly] -> (.ndf) backup once at the end of the year
[PRIMARY FILEGROUP]
Data_2021 -> (.mdf) daily backup only this file
Database recovery is also simple. You can recover single file.
Can someone please direct me to the right track. Is this even possible?
Can data be archived based on age, or some other criteria? (e.g. devices are removed/replaced and their associated measurements can be deleted or removed from normal querying)
I don't recommend using read-only filegroups, they won't help you if you're going to stick with the tables you have now.
Weekly or monthly full backups, with daily differential backups, will probably be fine. You only need to restore the last full backup, then the last differential backup, plus any log backups made after the last differential.
Unless you absolutely need to be able to restore to an exact point-in-time (less than 1 hour), you can probably set the database to Simple recovery and just do more differential backups. You could even have a rotating set of differential backup files (e.g. Backup_00_00.diff, Backup_01_00.diff) that you overwrite hourly with that time slot's differential backup, if you need to save disk space. You'd only use this for disaster recovery purposes, not for archiving data.
@yosiaz:
Yes simple solutions are usually the best one.
What I would like to achieve is to avoid making 20GB+ FULL backup every week/month.
However since I only need last FULL backup + latest differential + Trans LOG in case of disaster recovery, this is acceptable solution.
Is it possible to automate the process FULL backup every month, differential backup every day?
Remove backups that are not needed any more to save space (previous FULL/DIFF backup).
Unfortunately yes.
User can do data comparison over the years (compare temperature data for specific interval for selected years.
All data stays in database even if device/sensor/measurement is removed from the system.
But I see what you mean. Now I am curious, how would you archive this?
Offtopic: can you explain in a few words.
Your solution is quite the same as yosiasz suggested, but I will mark your answer as solution (no hard feelings yosiasz )
)