I have the following challange:
I have a staging database which holds a few big tables (on SSD) but I start to run out of space on the SSDs. I use SQL server 2014 BI-Edition (= Standard Edition for the relational engine) so table partitioning out of the box is not available. My approach would be the splitting of the data into separate tables and joining them together in a view.
The data itself is (mostly) written once and then only read from.
What would be a better solution of the two suggested ones below? Why?
Solution A: Use Filegroups to move data to different disks:
- create a two new filegroup on a separate disks (non-SSD, RAID5)
- create several tables (e.g. one per Quarter Per Year) and move the old data to those tables on some rules (e.g. move data older thant 6 months)
- one filegroup is readonly and the other one is read write. More current data resides in the read write filegroup and the older data is on the readonly filegroup
- create the same index (same columns) for all tables
- use a view to have transparent access to all data
Solution B:Use a database to hold the historic data
- Implemented in more or less the same way but addng a database (in the same instance) as an additional layer
Thanks for sharing your ideas and advice.