You can keep it simple and inexpensive. A spinning hard drive connected to your Roon server will work just fine. It is important, however, to have backup copies of your music someplace else. Any hard drive, be it spinning or SDD can fail.
I rip and/or download new music onto a Mac computer and then copy the music files over my wireless network to a USB hard drive that is attached to my Roon server.. That way I have a backup of my music on the MAC, which is also backed up on the Cloud.