Judging by how SIM works, and how pervasive SIM is... I assume the most common practice for QA and Prod, is the Database is local/on the same machine as the website.
Is this indeed the suggested/common approach for live/Prod sites?
No this is not a suggested or common approach... at least I hope it is not. SIM is for local development environments only, it is not to be used for installing Sitecore in QA/Production environments.
As for a recommended Production environment, that will entirely depend on your requirements. What level of availability do you need, what is your traffic volume etc...
But if you are not using PaaS for your environments, then it would be standard to at least have a separate servers for SQL, CM, CD - that would be the bare minimum for a Production worthy instance.
One thing to keep in mind is that SIM is mostly useful to speed up local developers environment and I don'think you should use it to setup a prod instance. That said, the answer to this question vary in a few aspects so you won't find a single and right answer but things you need to consider:
a) Are you hosting in IaaS? PaaS?
b) What is your Sitecore licensing model? Do you have a limited number of licenses you can run your instance?
c) Azure SQL cloud is not an option?
d) What's the Sitecore version you are using?
e) Do you need different geolocation targets?
These are a few factors that need to be considered. If you are looking for documentation on how to install Sitecore in a scaled topology (separate CM, CD, SQL, etc) you can access this link.
It is not common nor recommended to setup a production environment with a standalone setup (all running in the same server). There are a number of reasons why you wouldn't go with this approach, for example, you will have challenges with deployments, performance, scalability among others.
As Richard said, If you are using IaaS it's common to split each role in it's own server (again do you have licenses, budget for it?). In this scenario, you would have something like 1 server for Content Management, 1 or more servers for Content delivery (at least 2 is recommended), 1 server for SQL server, 1 processing and so on.