Several years ago Kent A. and someone else put out a couple spreadsheets on database sizing and server sizing for Configmgr. I used them off and on for a few years and over time I have revised them based on the newer SQL server best practices (SQL 2012 R2+).
In the days of SQL 2008 and earlier, the common db practice was to have multiple files for the configmgr database files (MDF & NDF files), while the TempDB was configured to use a single file (MDF file). And all databse transaction log files (LDF Files) were configured to use single files alongside each database.
TempDB or tempdb database: Is a globally used file available and is used for holding temporary objects such as local and global tables, stored procedures, table variables, row versions, and query results. The tempdb is temporary, it gets recreated at each reboot or SQL service start. It is literally empty at each start, all data is purged and the db cannot be backed up. The tempdb.mdf is the data file, templog.ldf is the log file for the tempdb. The tempdb autogrowth setting can cause serious performance issues if the size of the db is too small and it is constantly growing the db. A common practice when running a SQL server in an Azure VM is to put the temdb files on the D drive since the drive is non-persistent during reboots.
The best practice for SQL 2012 R2 and beyond is to use a single database file for the Configmgr database and multiple files for the TempDB files (MDF & NDF files). The transaction log files (LDF files) are still configured for a single file as before. My buddy Steve Thompson points this out in a recent post where he discusses the proper tempdb creation practices. Steve is also a former SQL MVP, now a Configmgr MVP so he knows both well.
The topic of using a CAS or multiple primaries for a customer came up on a discussion list today and when I replied I had screen shots of my sql sizing spreadsheet and my site sizing spreadsheet and a few people asked me for copies of them. They can be downloaded now and as they get updated I will do my best to make the new versions available.
Any questions please let me know.
P.S. SQL should always be local.