Sponsor families can be civilian or military, either way, they're great. My sponsor family (and my fellow cadets who were also sponsored by them) were fun. It was a place to go and chill. We could switch into something more comfortable (as long as we were there) and watched TV and played Battlefield 1942.
There's nothing weird about your sponsor family being members of the military. There's a time and a place for the officer life, and the sponsor families know that. They're not going to do the military thing to you. In fact, if they're Coast Guard officers, there's about a 50% chance they went to CGA too, so they'll know what you're going through and how important it is to "get away" from it all. At home they might be "Bill" or "Beth" and back outside, at CGA they'll be Lt. Cmdr. Smith or Cmdr. Brown. As a 4/c cadet it may take you a couple of visits to get comfortable with the two faces of the same person, but that's OK (and it's something you'll experience throughout your career).
My sponsor family was really special. My sponsor dad was a 1979 CGA grad who had retired and was a school teacher. His wife and three kids (and dog) were really nice to me and the other cadets. I ran into my sponsor dad two years ago at a CGA homecoming and we caught up.
Now, as I got more senior at CGA, I went there less and less, because I had more freedom and other options. Once I had a car and a girlfriend from the outside, I wasn't around New London as much during liberty. But I can't overstate how helpful the sponsor family program is.