This guide is intended to assist administration in properly configuring SQL Server prior to installing Configuration Manager.
Too many make the mistake of miss-configuring SQL Server, then need to spend a LOT of time later, correcting it.
Article Contents
Installing and configuring SQL Server for Configuration Manager.
Planning.
ConfigMgr 2012 R2 Database Sizing Estimator.
How are we going to configure SQL.
Cap log files.
Other databases.
Conclusion.
SQL Server Installation.
Install SQL Server
Feature selection.
Instance Configuration.
Server Configuration Collation.
Install SQL Server Service Pack and latest Cumulative Update.
Install SQL Server 2012 Sp2.
Install SQL Server 2012 Sp2 CU4 (latest cumulative update).
Configure SQL Server.
Max memory setting.
Change location of TempDB.
Pre-create the ConfigMgr 2012 R2 database.
Post ConfigMgr 2012 R2 Installation Tasks.
Planning
You will want to install SQL Server on the same server that you plan on installing ConfigMgr 2012. If you need some background on why installing ConfigMgr 2012 on a remote SQL Server is a really bad idea, please read the following.
For a smaller ConfigMgr 2012 site, with perhaps less than 5,000 clients, the following server Physical or Virtual platform specifications will perform well.
ConfigMgr Site Server Specifications
Ø Windows Server 2012 R2
Ø SQL Server 2014
Ø 4 x CPU Cores
Ø 32GB memory (minimum)
Site Server Drive configuration guidelines:
Logical Drive | Minimum Size
(GB) |
Purpose |
C: | 100 | OS + *Page file |
E: | 100 | Applications + Database |
F: | 50 | Log Files |
G: | 500 | SCCM Content OS images, app packages, etc. |
*P: | 50 | Page File |
*T: | 50 | Temp DB |
*U: | 50 | Backups |
*Note: Optional configuration for higher performance and/or higher client counts
It is recommended that a separate server be installed to provide the Distribution Point and Management Point roles. The following server Physical or Virtual platform specifications are indicated.
ConfigMgr Distribution Point Specifications
Ø Windows Server 2012 R2
Ø 2 x CPU Cores
Ø 8GB memory (minimum)
Distribution Point Drive configuration guidelines:
Logical Drive | Minimum Size
(GB) |
Purpose |
C: | 100 | OS + Page File + Binaries |
E: | 500 | SCCM Content |
ConfigMgr 2012 R2 Database Sizing Estimator
Prior to installing ConfigMgr 2012, It is recommended to pre-create the ConfigMgr 2012 database and pre-size the TempDB databases. One challenge, determining the correct size for the databases.
Shown is an example of the Database sizing worksheet, based on estimated client counts:
Anthony has documented this approach as follows:
http://configmgr.com/configmgr-sql-server-sizing-and-optimization/
Using the estimator to calculate the number and size of the data files, begin the SQL Server Installation process.
SQL Server Installation
Prior to SQL Server installation:
Drives dedicated for SQL Server data files or log files, such as TempDB should be formatted to NTFS 64KB block size.
Reference: https://stevethompsonmvp.wordpress.com/2014/07/25/sql-server-ntfs-cluster-size/
Server 2012 platform – Install File Services & .NET 3.5 framework (required for SQL install)
Install SQL Server 2012 or SQL Server 2014
Note: example is from SQL Server 2012, however, SQL Server 2014 is very similar.
Browse to SQL Server 2012 Sp1 install media, right click setup.exe, run as administrator. Only key dialogs will be shown for this installation.
Feature selection – choose the above options.
Instance Configuration
Instance Configuration – take the Default Instance option.
Change the Instance Root directory location (assuming you’ve followed the drive suggestions) to:
E:\Microsoft SQL Server
Server Configuration Collation
Collation – default should be correct, however, verify that ‘SQL_Latin1_General_CP1_CI_AS’ is selected.
Default security – Windows Authentication, add <SERVER Name>\Administrators & <DOMAIN Name>\Domain Admins for Sysadmin permissions. Note: there may be some variation for groups based on client requirements.
Set SQL Server Agent service startup type to Automatic.
Install SQL Server Service Pack and latest Cumulative Update
Note: example, use the latest supported SP and CU!
Install SQL Server 2012 Sp2
Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to SQL Server 2012 Service Pack, right click setup.exe, run as administrator
Click Next>
Click “I accept the license terms” and Click Next >
Accept defaults, and Click Next >
When checking files is complete, and Click Next >
Ready to Update, and click Update
Completion Dialog, all features should show a status of Succeeded
To verify installation results, review SQL Server log directory, navigate to:
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
Within that root folder, locate the Summary.txt file, review this file for component success status and proper SQL version.
Exit code = 0
SQL Server 2012 Sp2 build level = 11.2.5058.0
Install SQL Server 2012 Sp2 CU4 (latest cumulative update)
Note: Install latest SQL CU (reference link at end of this chapter)
Recommend downloading SQL Service pack to a folder on the server to be updated. Browse to the folde containing the hotfix, Right click setup.exe, run as administrator.
Setup Support Rules; and Click OK
And, Click Next >
Click “I accept the license terms” and Click Next >
Accept defaults, and click Next >
When checking files is complete, and Click Next >
Ready to Update, and click Update
(Note: highlight below – shows current patch level of SP2)
Completion Dialog, all features should show a status of Succeeded
Review Summary.txt
Exit code should read 0
Restart Server.
Verify that MSSQLServer service and SQL Server Agent services are running.
Summary
Always check for the latest updates!
Reference: https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx
Configure SQL Server
Now that SQL Server is installed, it is time to configure SQL for optimal performance. In this section we will:
Ø Cap maximum memory
Ø Increased default size of TempDB and modified default file growth
Ø Give server administrators and domain admins Sysadmin equivalence
Ø Optionally, Install and configure SSRS (SQL Server Reporting Services)
o Increase default size of ReportingServer database and modified default file growth
o Change recovery mode to simple
Max memory setting
SQL Server Properties – Memory
Server properties > set min memory usage (8GB total)
Server properties > set max memory usage(32GB total – 8 GB = 26 GB for SQL)
Note: 26 GB * 1024 = 26624 MB
SQL Server Properties – Database Settings
SET Database Default Data, Log & Backup locations (as appropriate)
Change location of TempDB
From SSMS (SQL Server Management Studio), Start a New Query.
1. To get current location and names of tempdb files
Copy and Paste the following:
— To get current location and names of tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO
2. Change the location of each file by using ALTER DATABASE. Modify file paths as appropriate.
Copy and Paste the following:
USE Master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = ‘E:\SQLData\tempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ‘F:\MSSQL\LOGS\templog.ldf’);
GO
Rerun query from step 1 to validate modified location:
— To get current location and names of tempdb files
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id = DB_ID(N’tempdb’);
GO
3. Stopping and restarting MSSQLServer will recreate the TempDB files in their new locations.
4. To set initial size of TempDB from 8MB to the estimated size of tempdb, from prior step. From SSMS, expand System Databases node, right click TempDB database and select Properties
Files Option: Set TEMPDB initial size (based on estimated size – this should be approximately 25-30% of the size of the estimated ConfigMgr database) and autogrowth file setting. Autogrowth should be set to either 1024MB, or a minimum of 512MB.
TempDB Data files: optionally, create a minimum of 4 to a maximum of 8 data files (see references)
Reference: https://stevethompsonmvp.wordpress.com/2014/12/09/why-default-database-autogrowth-settings-are-bad/
https://stevethompsonmvp.wordpress.com/2016/02/05/proper-tempdb-creation-for-configuration-manager/
Pre-create the ConfigMgr 2012 R2 database
Based on the previous size estimation, create a database to the total estimated size.
From SSMS, right click databases > New Database…
New Database properties
Database name: CM_<Site Code>
Owner: sa
Database Files: For both Data & Log files – Enter initial size, autogrowth, Path (as appropriate) & file name
New Database Properties
Select Options (on left)
Change recovery model to ‘Simple’
Then, click OK
Firewall ports
Windows server – if you are using a firewall, be aware that the following ports are required:
- Intersite communications use:
- SQL Server Service Broker, which defaults to port TCP 4022.
- SQL Server Service, which defaults to port TCP 1433
- Intrasite communication between the SQL Server database engine and various Configuration Manager site system roles default to port TCP 1433.
https://technet.microsoft.com/en-us/library/hh427328.aspx
Install Configuration Manager
Now that all of the preliminary work is in place, now install Configuration Manager.
When installing Configuration Manager, use the option of installing to an existing SQL Server instance and database. Choose the pre-created database during the installation phase.
Post ConfigMgr 2012 R2 Installation Tasks
1. Setup database backup plan – backs up to <drive>:\BACKUP with a 1 week retention
Pingback: SCCM: Proper SQL Installation prior SCCM Setup | IT Consultant Everyday Notes
About TempDB Autogrowth setting. You write 1025 or 215 kilobytes. Should it be megabytes?
* I meant you did write 1024kb or 512kb
I should hire you to proof read my work. Thank you
Thanks for this! Answers a lot of questions I had.
I do want to ask one thing though; Why are the paths in Feature Select not changed (The instance Root Directory)?
Cheers
Sorry, ignore that, I’ve seen you change it in the next step. one useful part to show in here is a screenshot of the Database Engine Configuration > Data Directories section, also indicating which of those locations should be formatted in 64k. Thanks