Re: so how many write cycles is it good for?
The answer to how many write cycles an HDD is good for is clearly not infinite for the very good reason that they are mechanical devices and will, sooner or later, wear out. Hammer them with random I/Os with a very high duty cycle rate will accelerate that and can only be mitigated if you buy very expensive enterprise HDDs with reduced capacity. People should also not mistake MTBFs as expected lifetimes for HDDs - they are statistical failure rates during a devices expected lifetime, which is typically quite a lot shorter than the MTBF figure, especially if used intensively.
As with tapes, HDDs will gradually move to support the type of workloads where their performance characteristics and cost profiles make sense. The high latency and related limited access rates will gradually make them less viable for many workloads where those characteristics are important.
It's also worth noting that the controllers in modern SSDs are much more capable than earlier ones in the manner in which they deal with bad cells and write levelling.
No storage device, whether HDD, SSD or tape is forever. Personally, not wasting hours a week for the cumulative effect of poor HDD performance (on personal devices and back end systems) is a considerable cost in itself and, increasingly, large businesses are appreciating that too due to productivity issues even before customer service considerations are taken into account.
Flash storage has reached the point where it is "good enough" and "cheap enough" that it makes sense for a lot of individuals and companies, at least for I/O intensive parts of workloads if not backup or archival.