Installing and configuring SQL Server for Configuration Manager

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

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

2. Database Optimization and Tuning