Linguistically speaking, there is an, albeit small, difference between the two different forms of the minuscule S. The long S, 'ſ' is an allographic variant of the grapheme <s>, which can also be represented as 'S', 's' and so on, depending on language, position and the type of the word. Practically, this makes little difference since they can or could be used pretty interchangeably, depending on language and stylistic considerations. In German, for example, 'ſ' was used for a long time to represent <s> unless at the end of a word or a syllable. So it was pretty easy to differentiate Wachſtube from Wachstube (Wach-Stube, guard's office vs. Wachs-tube, wax-tube). Incidentally, this is where the German alphabet got one of its unique glyphs, the 'ß' from, as a ligature of ſs or ſz. In English, it was used unless at the end of a word, and sometimes in ligatures such as aſseſs, but the opera score you linked doesn't use the 's' inside of words but only at the end. This is not a 100% rule, as in the beginning the 'ſ' was sometimes used at the end of the word, and by italian printers even later sometimes, but those exceptions are rare.

Such an allographic difference dependent on the position of the glyph is not unheard of, and any educated European of the time would have known that the minuscule Greek alphabet also makes a distinction between the allographs 'σ' and 'ς' for sigma, the former used word-internally while the latter is used exclusively at the end of a word.

So where did it come from? Most of our modern alphabets go directly back to the caroligian minuscule, a script that developed out of the earlier Roman cursive forms of handwriting. They gradually developed some elongated forms of the letters with ascenders and descenders. One letter that got elongated in that way was the 'S', which gradually came to resemble a simple, elongated and rather flat angle. Here is an example showing idealized versions of the older (top) and younger (bottom) forms of Roman cursive handwriting, where you can see pretty well how the S became an elongated and barely recognizable version of itself. This development continued in the so-called half-uncial script, which originated in North Africa and was the direct precursor to the carolingian minuscule. Here is an example of blind text comparing the uncial to the half-uncial script; you can see how the uncial, much more oriented on the Roman capital script, keeps the traditional S, while the half-uncial approaches the form used in the carolingian minuscule, the 'ſ'. This is another practical example of the half-uncial script.

Then, you have the carolingian minuscule, which uses the long-s. This example actually has the first two lines of the IIII chapter written in an uncial script, so you can see that the practical application left a lot of freedom to the scribe; you can see the long-s in the third line of that chapter 'dum dioceses visitata agebamus', here still used at the end of the word. This is a good example because it also shows where the 's' survived in its more traditional form alongside the carolingian minuscule, and the two were used according to the formula 's' at the end, 'ſ' inside the word from late carolingian and early gothic scripts on, until the 19th and in some cases 20th and even 21st century (especially German 'gothic' Fraktur typefaces, which is where it still survives, as well as in the German 'ß').

The letter you are referring to is called a Long S, which was derived from the old Roman cursive medial s (an s in the middle of a word).

In many alphabets, some letters will take different forms depending on their position within the word. English for example has capital letters which only appear in certain situations (beginning of sentences, proper nouns, abbreviations, etc), and "lowercase" letters that appear everywhere else. H and h, A and a, B and b, etc. Hebrew and Arabic (among others) have final forms of certain letters, which only are used at the end of a word (sort of a "reverse capital letter). For example, the Hebrew letter "pe" (פ) appears normally like פ, but if it appears at the end of a word, it changes to look like ף. The Greek letter sigma has three forms, capital Σ (appears at the beginning of certain words), medial σ (appears in the middle of words), and final ς (appears at the end of words). The long s that you observe in older English documents is the equivalent of the medial sigma, that is, it is the form of the letter s that appears in the middle of words unless certain conditions are met.

The long s was used for a very long time, and can be seen in many ancient manuscripts. It eventually fell out of use in the 19th century, first being abandoned by printers and typewriters, and then falling out of handwriting shortly afterwards.

The long s originated as a medial s (in the middle of a word). So you could consider it to be a "middle form" of the letter s. It occurred at the beginning and in the middle of words, but rarely/never at the end of words. So, for example, "sasquatches" would be written "ſaſquatches", because the "normal s" was actually the final form of the letter. In words with double s, like Mississippi, the long s would precede the short s, thus "Miſsiſsippi".

Basically, it was the exact same letter, just a typographical form specifically for use within the word, not at the end. In the same way we use capital letters at the beginning of sentences, people used the long s in the middle of words.