Are you daysailing or looking for easy places to go overnight? NY Harbor is an awesome place to daysail. True, there are currents and traffic, but there is plenty of room, and needless to say, the scenery is nothing short of spectacular. Also, there is wind, just about all the time.
On the other hand, if you want a home berth where you have easy options for overnights, weekends, etc., then NY Harbor and the south side of Long Island generally is not so hot. Just not that may places to go.
Long Island Sound has the virtue of being a very nice body of water that is well protected, and there are numerous good harbors with lots to do. The wind is lighter in the western sound, but one benefit to global warming is that the wind is becoming more consistent (at least it has the last two years). It's not SF Bay by any stretch, but most days you will see in excess of 10 knots. Don't underestimate the value of having a variety of good, fun, nice places to go for weekends, particularly if you have kids or a significant other that would want to do something other than go sailing for the day and then return home.
If you live in Brooklyn, I'd suggest looking for a place on the Long Island side, rather than City Island or the north shore of the Sound. You've got Manhasset Bay (Port Washington), Oyster Bay, Glen Cove, Huntington or Northport, all within a reasonable driving distance, all things considered.