Incredibly the song was merely one which was selected by the video and communication coordinator for the Red Sox during her tenure in that position from 1998-2004. The song itself has no intrinsic connection to baseball, neither does Neil Diamond have such a connection to Boston.
According to the the Boston Globe the communications coordinator, "had noticed ‘Sweet Caroline’ was used at other sporting events, and she decided to send the sweetness over the Fenway speakers.” It seems that she simply thought it was a nice tune.
It became iconic with Red Sox games mainly due to the fact that the spectators began to associate its turn in the play cycle (during the middle of the 8th inning) as a quasi-tradition.
Recent events in Boston have of course brought light onto the song and it has been played as a sign of support for the city of Boston following the tragic events of April 15th. In so doing, the once quasi-tradition has been rightfully elevated to a real Red Sox tradition which will likely endure for decades to come.