Q. What is it supposed to be on the show?

A. Out at sea aboard an expensive yacht. 

 
Q. Where is it supposed to be on the show?

A.  In New York Harbor.

I Q. When did we see it on the show?

A. At the very end of  Episode 10, the season finale of Season One of Dexter: Resurrection.
 
This is the final scene of the series' first season.
 

Dexter has killed billionaire Leon Prater (after Prater murdered Angel Batista, and tried to kill both Dexter and Harrison), and in this scene he has taken Prater's yacht from its dock, has sailed out to sea, and is in the process of throwing Prater's remains into the ocean (as he used to do from his own boat, the Slice of Life), while the Statue of Liberty is clearly visible in the background.

( The name of this yacht is The Getaway, which was the title of the final episode of Dexter's 4th season, which many people consider to be the peak of the original series. )   

While he it goes about this task, we hear his internal dialog, as he muses about his change of attitude. In the past, he thinks, he always wanted to leave his “dark passenger” behind and lead a normal life. But now he has accepted the fact that is never going to happen, and has embraced his true role in life: methodically eliminating other serial killers.

 
He is armed with a thick stack of files from Prater's collection containing information about a number of infamous serial killers, and is obviously planning to hunt them in the near future.
 
He accepts that perhaps he wasn't meant for a solitary life, as his father had assumed he was. And instead, that he needs people around him. He thinks that he always depended on his sister Deb, but with her gone, he can look forward to a mutually helpful relationship with his son, Harrison.
 
In the final shot, he looks into the camera and says that he is exactly what he is meant to be, and exactly what “you” want him to be.

Q. What is it actually in real life?

A. Out at sea aboard an expensive yacht.

Q. Where can I find it in real life?

A. Given that you can see the Statue of Liberty glowing in the background behind this night scene, it is obvious that it is supposed to take place in the New York Harbor.  The only question you may have is whether it is authentic, or whether it was faked in post-production.

Well, I have good news for you: This is one time when the scene is exactly what it appears to be. Dexter is out on an expensive yacht in the New York Harbor, near the Statue of Liberty.
 
And it's obvious from the scene that the yacht was out in the waters bounded roughly by Battery Park/Manhattan on the north, Liberty Island (and the Statue of Liberty) on the south, Governors Island on the east, and Ellis Island on the west.
 
For a little behind-the-scenes info: in the real world, when the yacht left to film this scene with MCH, it left from a dock on the south side of the North Cove Yacht Harbor (AKA North Cove Marina), which is just one block west of the World Trade Center, next to Brookfield Place shopping center, on the west side of lower Manhattan Island.   
 
The only inconsistency is that during the Gala, we overhear part of a conversation in which Prater reveals that he is auctioning off a charter on the yacht as part of the benefit. Prater says that he just brought the ship up from St. Bart’s [Saint Barthélemy, a French-speaking island in the Caribbean], and it’s mentioned that the ship will be at Chelsea Piers.  Dexter overhears this, which is what gives him the idea of borrowing the yacht to dispose of Prater's remains. But North Cove Yacht Harbor is actually about two and a half miles south of Chelsea Piers.
 

Here is a matching StreetView of the marina;


 
Here is an aerial photo of the location.  And here is a map link.