Sheppard's consciousness is destroyed, when that of an Ancient is placed into his body. There's no way to reverse the process, to remove the Ancient, and even if there was, John's consciousness has been destroyed. The Ancient formerly known as John Sheppard is just trying to fit in, not take John's place, find his own in Atlantis, but McKay is making things difficult for him though. Rodney Loved John, and he thinks that John loved him too, but they never spoke about it, almost never had to, because they were that close. Rodney hates this constant reminder of what he's lost, and he hates himself more, but he and the Ancient start a Relationship. At first it's because it's John's body, and Rodney can pretend that it's just sex, that it's sex with John, but it's not John, and Rodney finds himself in a Relationship with another man. He genuinely likes the Ancient, and together they're able to work things out, but the Ancient is on an uncontrollable path to Ascension. The Ancient ascends, but is able to return John once he does.
Rodney McKay is newly Divorced, and extremely wealthy, despite having had to force his now Ex Wife to take a settlement. The Marriage hasn't exactly ended badly, he doesn't quite know how he managed to snare Samantha Carter in the first place, it must just have been circumstance, but they're not close, and he's not sure that they ever were. Rodney is very high profile, having long ago abandoned the environment of a Lab, for an Office, and a portfolio of Government Contracts. That profile also puts him in a lot of danger, and he's been advised to hire a Bodyguard. He actually interviews several people, but what seemed like a good idea in his head, namely getting a hot, tall, blonde, female bodyguard, really doesn't sit too well with him in person. He never thought he'd say this, but his "type", just doesn't work for him anymore it's been spoiled for him, because it's as bad as if Sam were in the room, and what he actually ends up with is an Ex Air Force Major, called John Sheppard.