SQL 2008
SQL 2008 for SCCM R2 – STEP by STEP GUIDE
by admin on Apr.16, 2009, under SQL 2008
1 SQL Server 2008 Installation & Provisioning
1.1 SQL Server 2008 Installation
1.1.1 Pre-Installation tasks
Reboot your machine prior to starting the SQL Server installation process to make sure that you do not have any pending reboot requests which will block the SQL Server installation process.
Create following Service Accounts in the Domain: SQLAgent, SQLReporting, SQLService
1.1.2 Installation Step by Step
Install Dotnet 3.5 SP1 and Windows Installer 4.5. These are the prerequisities for SQL 2008.
Start the setup from the media by running “setup.exe”.
Once prerequisites have been installed, you will see the main installation screen, as shown below.
Click the New SQL Server stand-alone installation link to launch the SQL Server installation.
Installation will execute a system configuration check. Once the check completes successfully, your screen should look similar to the following: Click “OK”
Accept the default product key Click NEXT.
Select “I accept the license terms” as show below and then click Next.
Click “Install” to “Setup Support Files”.
Verify that all the rules has status = Passed and click Next.
Select the SQL Server features and Change the Feature directories to the Disk path as shown below then click Next.
Select Default Instance as shown below then click Next.
Verify the disk space requirements and click Next.
Enter the Domain service accounts that you created earlier for the appropriate services. When complete, your screen should look similar to the following: Click NEXT
Select “Windows authentication mode” as shown below, Click “Add” and Choose “SCCM Admins” Group from the Active Directory as SQL Server Administrators.
Click the Data Directories tab and Change the settings as shown Below, Click NEXT.
Choose “Install but do not Configure the report server” as shown below, Click NEXT.
In the “Error and Usage Reporting” section, Click NEXT.
Verify that all the rules has status = Passed or Not applicable and click Next.
Click “Install” on the “Ready to Install” section
Verify that the Installation Status is = “Success” and Click NEXT
Verify the final installation report and Click “Close” to Finish the installation
1.2 SQL Server 2008 Provisioning & Optimization
POST SCCM Installation steps on the Sql server for optimization purposes.
1.2.1 SCCM Database Parameters
Calculate the initial database size based on 2 MB per client (Workstations and Servers), e.g. 50,000 client * 2MB per client = 100,000 MB or 100 GB.
Central Site
| Setting | Value |
| Name | SMS_123 |
| Data File: | 100 Gb |
| Log File: | 20 Gb |
| Autogrowth | By 10%, unrestricted growth |
| Users | 50 |
Regional Primary Sites
| Setting | Value |
| Name | SMS_<<SiteCode>> |
| Data File: | 40 Gb |
| Log File: | 8 Gb |
| Autogrowth | By 10%, unrestricted growth |
| Users | 20 |
4.2.1.1 Follow the steps defined below to do the changes on the SCCM Databases
Click Start -> Microsoft SQL Server 2008 ->
Right Click on the “SQL Server Management Studio” and choose to “Run as administrator”, as shown in the picture below;
4.2.1.2 Click “Connect” to start managing the local SQL Server, as shown in the picture below
4.2.1.3 Expand the “Databases” and Right Click “SMS_XYZ” and choose “Properties” as shown in the picture below;
4.2.1.4 Click “Files” on the “Select page” to the left and change the “initial Size” of the “SMS_XYZ” file to “40000 MB” and the “SMS_XYZ_log” file to “8000 MB” on the right pane and Click “OK”
Do Not Close the “Microsoft SQL Management Studio console”, it will be used to optimize the TempDB in the following steps.
1.2.2 TempDB Optimization
For best performance, increase or request the TempDB size as such:
Central Site
| Setting | Value |
| Name | TempDB |
| Data File: | 20Gb (= 20% of the SCCM DB) |
| Log File: | 10Gb (= 50% of the TempDB) |
| Autogrowth | By 10%, unrestricted growth |
Regional Primary Sites
| Setting | Value |
| Name | TempDB |
| Data File: | 8 Gb (= 20% of the SCCM DB) |
| Log File: | 4 Gb (= 50% of the TempDB) |
| Autogrowth | By 10%, unrestricted growth |
4.2.2.1 Expand the “Databases” and “System Databases” tree to see the “TempDB” in the console, Right Click “TempDB” and choose “Properties” as shown in the picture below;
4.2.2.2 Click “Files” on the “Select page” to the left and change the “initial Size” of the “tempdev” file to “8000 MB” and the “templog” file to “4000 MB” on the right pane and Click “OK”
Close the “Microsoft SQL Management Studio console”
1.2.3 Change the RAM settings for the SQL server
I choose to fix the RAM usage for the SCCM Sql server running on the same server as the SCCM Primary site servers,
my servers had 8 GB Ram Memory and I have allocated 5 GB for the SQL server using the OSQL commands below:
