ConfigMgr and Remote SQL - Dogfight
It seems that this discussion comes up quite often on whether to install the ConfigMgr database on the same server or on a separate server. While there are camps that side one way or the other, I thought I would throw my hat in on this topic.
Generally speaking, both installation are OK. (GASP :o) Before you throw me off a cliff, hear me out.
Installing the site database on the computer on which the primary site is installed generally results in good site performance if adequate hardware is used. However, because Configuration Manager is more database processing intensive than previous versions of SMS, if there is a high speed connection between the site server computer and a remote SQL Server computer used to host the site database, site performance might be better than when the database in hosted on the same primary site server.
This is because in a single server installation performing large amounts of data processing, it can be expected that SQL processes will normally utilize about two-thirds of the processing of the system, leaving the rest of the resources to be divvied up between SMS Executive and the rest of the OS and software. This can result in degraded performance and slow responses.
Using a remote SQL Server to host the site database, with a fast network connection to the site server, allows two computers to more effectively process site information by working in parallel. For data processing intensive sites, this configuration results in less resource contention than using a single computer to host both the site and site database server installations. With that said, there will undoubtedly be trade-offs in network traffic.
In a case were there are about 10,000 clients and the data process is frequent, if there is a fast network connection between the SQL server and the SCCM server, I would recommend that using a remote SQL server. For more information, please view the articles below:
Performance Considerations When Designing Configuration Manager Sites
http://technet.microsoft.com/en-us/library/bb932210(TechNet.10).aspx
Configuring Configuration Manager Sites for Best Performance
http://technet.microsoft.com/en-us/library/bb892809(TechNet.10).aspx
Performance Configuration Recommendations
http://technet.microsoft.com/en-us/library/bb932186(TechNet.10).aspx
Best Practices for Central and Primary Site Hardware and Software Configuration
http://technet.microsoft.com/en-us/library/bb932180(TechNet.10).aspx