The pain of getting a septum piercing varies with everyone, but it will no doubt cause you to tear up. That doesn't mean you're crying - it's a natural response of your nervous system to involuntarily secrete tears when your nose is pinched or pierced, even if it's not particularly painful.
Needless to say, the nose is a dirty place. The constant presence of bacteria can make it difficult to heal a septum piercing, so keeping your nose clean (literally) is essential. If you have seasonal allergies or are prone to colds certain times of the year, you may want to avoid getting your septum pierced near or during that time. If you have severe allergies or are easily prone to sinus infections, you might want to avoid this piercing altogether.
One advantage of the septum piercing is that with the right jewelry (usually a circular barbell) it can easily be hidden by simply turning the jewelry and allowing the ends to rest inside the nose. The size of the jewelry and the balls on the ends, however, could affect your ability to breathe through your nose when you do that, so it's something to keep in mind. A septum retainer bypasses that particular problem, but it's not quite as attractive when visible.
Starter jewelry can be as small as 18 gauge, but 16 and 14 gauge suit a fuller nose much better. The piercing can be stretched to a larger size if desired.