I have a windows server VM, with a combination of HDD and SSD storage, the SSD storage capacity is limited to 50 GB, but HDD storage capacity is high.
What is the best way to share these storages to run MSSQL in the most stable state and the highest performance possible?
Does it need to install SQL server engine in the SSD drive?
50 GB is not a lot of space, but if you have a tiny database you could configure the whole database to live on just the SSD. I couldn't say for sure without knowing your database size and projected growth rate.
Assuming you do need to use the HDD for holding your user database, I would then assess if 50 GB is big enough for the tempdb database possibly. tempdb is a system database where certain operations occur during query processing, so it's generally recommended to provision it on it's own fast disk. Also, when your query runs, if the data spills to disk because of a cardinality misestimation or otherwise, the tempdb is where that spill to disk occurs. Having that spill occur on an SSD as opposed to an HDD will make a meaningful difference for performance.
Generally you should provision your tempdb to 9 equal sized files (8 data files and 1 log file) to maximize it's performance as well.
In a similar vein, you should pre-grow the data and log files of your user databases as well, especially with them being on the HDD. Database growth operations can be performance costly, so by completely growing out the files pro-actively once, you don't have to worry about incurring that cost multiple times later as the amount of data increases.
The only other advice I'd recommend is if you have the luxury of using two different physical HDDs for your database, then you should put the database's data file on one HDD, and the log file on the other HDD since maximizing I/O will be helpful.
Does it need to install SQL server engine in the SSD drive?
It's usually recommended (more so for management reasons) to install the SQL Server Engine to it's own drive separate from any databases. So based on my recommendations above, in addition to 1x SSD and 2x HDD, you would need a 3rd HDD to follow this advice.
If that's not possible, then I suppose you should install it to the SSD so it's at least isolated from your user database files, and since the SSD is going to have the highest disk performance, so it shouldn't be an issue supporting the Engine files and tempdb.
I have worked with SQL Server instances that co-mingled the installation path of the Engine and their user database files and never ran into issues myself, but it's been a long time since I worked with an instance dependent on HDDs too, so sticking it on the SSD will be probably be the best choice if you don't have an additional dedicated HDD you could use instead.
Finally, make sure you're following SQL Server best setup practices which will help you maximize performance too. (This is slightly dated of a resource but still very valid.) You may find more updated information also in DBA Training Plan 21: Building a New SQL Server.
