It depends on the university. At most of the universities in the UK I know, a position is usually called research fellow if it either is a temporary position or a position with less teaching obligations.
However, there is no general rule about what one calls a research fellow. It may even be different from department to department (Oxford is an interesting example, there it can mean pretty much everything).
If it is a suitable academic career option depends therefore more on the university itself and the conditions of the position (how much teaching, is it a permanent position?), than on the name of the position.
About the difference between the countries: There are large differences between countries, not only in the names (which differ of course, due to different languages). For example in Germany most positions are only temporary positions without tenure track.