SQL sizing for SCCM

Question

Hello,

I write this topic on SCCM part, but they redirect me here :)

Hello,

I'm trying to automate SCCM installation and for this, I try to find best practice.
I have an issue about sizing and settings on SQL. Lot of different things can be found on internet...

1)How much file for data is needed ? Sometimes I see 2 and sometimes 4. What are the benefit for 4 on the same logical volume and not 1 ?
2)How much file for tempdb data is needed ? In microsoft documentation for memCM, I can see 4 and in whitepaper microsoft sql recommandation is as much as core processor.
3) Does this excell file is still ok for sizing ? https://blog.ctglobalservices.com/configuration-manager-sccm/kea/system-center-2012-configuration-manager-sql-recommendations/
4) Does tempdb autogrowth need to be turned off if it's on dedicated disk drive ?

5)I see in youtube video (patch my pc previous Microsoft PFE) installation with 8 disk drive :

1. c: system + sql binaries
2. d: sccm binaries
3. e: sccm DB data
4. f: sccm DB log
5. g:temp DB (data + log)
6. h:wsus db
7. i:source file (if SCCM server host application, packages, osd, ...)
8. j:content library

I suppose I can use NTFS on all disk except e+f+g REfs with 64ko right ?

Finally, I often install SCCM on customer with less than 10K devices. Do I need to follow all of these advices or they will never be difference because it's not enough devices ?

Thanks :)

Answer 1

Hi matteu,

just a few thoughts from my side and probably some rule of thumb advice, so nothing carved in stone:

1. as always: it depends . If there will be a lot of Inserts and you only have one data file, then you may get a contention problem. With 2 or 4 physical data files you minimize this effect. I could go a bit more in detail regarding GAM, SGAM and PFS pages but there are plenty of articles out there (if you're interested in)
2. 2. same goes for tempdb. You find often the advice that you should go for as much files as cpu cores. But not more than eight files. I'd say: start with 2 or 4 and analyse if TempDB latch contention becomes a problem or not. (https://www.sqlshack.com/sql-server-tempdb-database-and-latch-contention/ )
3. no idea, never stumbled across this before and not the SCCM experience.
4. 4. If you have your on disk/volume for temp db, then you can presize tmepdb as well. I'd also use different drives for data and log there as well.

5) again. it depends For example: if I install SQL Server then C drive is always system only and I put sql binaries on a separate drive as well
Also 64k cluster size when you format the appropriate drives may or may not right. With Windows 2019 and SQL 2019 a lot of people go for a 2MB size. Here is good stuff by former data platform MVP Argeniz Fernandez (he was also in the SQL Server Tiger Team!)
https://www.youtube.com/watch?v=4c9v0XVvqlA

Regards
Dirk

Answer 2

@Dirk Hondong

Thank you for your complete answer.

@Tom Phillips
Thank you for this answer, but maybe you read too fast my post and specially the first line :p
I'm newbie on SQL and don't know anything but when I participated on Microsoft SCCM training, PFE says SCCM use SQL to store and use data but after SQL is installed, you can give the database to SQL DBA if you want for him to optimize like he wants it and it's totally supported. It's like any other SQL database.

Answer 3

This is a SCCM application sizing question, not really a SQL Server question. I suggest you post your question in the SCCM forum.

Answer 4

maybe someone can help me ?