Which media would be more reliable for long-term storage of my photo, SD card, or external HDD?
What time frame is "long-term"? 1 yr, 5 yrs, 10 yrs, 20 yrs?
I would say 5 to 10 yrs would be my target, in between media refreshes/rotations.
I wouldn't rely on SD cards for long term storage at all, largely because that really isn't what they are designed for. From the little I've read, you could expect 5-20 years depending upon the exactly type of memory used and how much you had previously used the card.
I only "trust" a hard drive for 4-5 years. It's not so much about the magnetic 1s and 0s on the platter, but rather it's about all the mechanics/electronics that it takes to read the disk. While many last longer than 5 years, some do not and you just don't every want your main backup drive to be one that could fail when you need it the most. There are a variety of issues that can strike with age. Seals can dry out. Bearings can lose some lubrication, actuators can wear out, insulation on motor windings can deteriorate, capacitors can leak - just to name a few.
So, if you really want to be confident, then you'd replace your hard drive media every 5 years or so, copying the content from the old drive to the new drive. This is exactly what I do for my photo library. Fortunately for me, storage is growing faster than my library so each time I replace my drives, I get more space for less money.
Apparently you've sworn off online storage, but one thing you get when you pay for that is you get a group of professionals that is managing the aging of hard drives for you. They replace older drives with new ones and copy the data over to the new drives, all without you ever lifting a finger. They are also typically managing redundancy so they have more than one copy of your data in case of their own drive failure. If you worry about privacy, there are services (like BackBlaze) that ask you to create an encryption password locally and they only upload and backup encrypted data such that the BackBlaze service never has the key and can never unlock your data on their own.
My backup strategy actually consists of both internal and internet solutions. I have an internal backup drive (not external) that gets a nightly backup from my main data drive. Then, I have the BackBlaze backup service ($50/yr for unlimited backup storage) that maintains an online backup copy of everything. This gives me instant recovery of anything from my local backup drive or disaster recovery from Backblaze in case of fire, flood, theft, etc... I probably don't need the local backup drive, but replacing that drive every 5 years costs an average of about $25/yr and declining each time I do it and I find it worth it for the double backup peace of mind (I did lose 2 years of kid photos once with a drive failure).
--
John