As with anything, take my opinion as what it is... an opinion based on 'what we do'. We do have HA with CM and SQL, where the Site Database and the Primary Site (the inboxes location) are on the same server(s) (two servers). All of the other roles are on other servers, specifically the ones which are for client communications / IIS (FSP, DP, MP, SUP...)
That works for us... but we do keep a very tight rein on what teams and individuals have access to SQL. As you know, SQL for CM sometimes doesn't want to be managed the same as "other sql databases", so if you have DBAs that want to manage your SQL, you might have political reasons to have SQL be elsewhere than on the box as the Site Server components. Of course that is supported and works fine... as long as there is excellent connectivity between the Site Component server and the SQL db... and your DBAs are well-versed in the quirks of CM regarding SQL, so they don't do something like "make it just like every other database..." and break it.