Drywall – How to replicate this drywall texture

That is in fact a "sand finish", your intuition was right.

Now depending on the age of the house it could be sand added to the plaster, added to the mud, or a "sand finish" paint. The last is your best hope of matching, as you would drywall it all smooth then simply use brushstrokes to recreate the pattern. There are premixed sand paints amazingly enough.

When I've seen this done with wallboard mud, they have a mixer on site to stir every so often (the sand settles slower than blueberries). A quick google search reveals a number of pros complaining about how hard sand finishes are to match. Practice practice practice, on some scrap drywall.

Using a piece you cut off the ceiling for repair, try to figure out what layer the sand was added at (plaster, top coating on drywall, paint).

I have used hot mud in a texture gun. Make sure to run it wet or it may plug the gun and cause problems. If you notice it setting up before the hopper is empty dump it and wash the gun or your gun may be toast. hot mud sets harder than regular mud. Both mixes will work fine and when you have your orifices set you can get the thickness you desire. Just a note never use warm or hot water only cold. warm / hot water will cause the mud to set much faster that's how I know about the gun being toast. I could not clean it out.
Added after not using a sprayer comment: It will be difficult to get a good orange peal with a roller. If using a roller I would strongly advise not to use hot mud because the working time is short it will need to be made up in small batches and it is really tough to sand.