The Complete Guide for SCCM Server Migration Part 1 – SQL 2017

18
The Complete Guide SCCM Server Migration

Recently received a lot of inquiries from admins regarding the upgrade of their ConfigMgr(SCCM) hierarchy. Most of their organizations want to upgrade/migrate their hierarchy to ConfigMgr Current Branch latest version. Since most of the existing ConfigMgr 2012 environment is running on Windows Server 2008 R2, the upgrade needs to be planned properly.

As of writing this post, SQL 2017 is the latest version and ConfigMgr Current Branch 1702 is the baseline version and Windows Server 2008 R2 is not supported for site servers or most site system roles but does remain supported for the distribution point site system role (including pull-distribution points, and for PXE and multicast). You can check more on the depreciated OS for site system roles here and Current Branch support lifecycle here.

In next few weeks, I will be covering the SCCM role migration from one server to another in my next series of posts. Topics that will be covered are as below.

  1. The Complete Guide for SCCM Server Migration Part 1 – SQL 2017  -SCCM SQL Server 2017 Installation and best practices
  2. The Complete Guide for SCCM Server Migration Part 2 – Database Migration
  3. The Complete Guide for SCCM Server Migration Part 3 – WSUS Server Migration
  4. The Complete Guide for SCCM Server Migration Part 4 – Primary Server Migration – SCCM Site Restore without SCCM Backup

Following are the topics covered in the first part of this series. My years of experience with different customers helped to derive the best approach from SCCM world.

Tips on SCCM SQL Server Best Practices
How to Install SQL Server 2017 for SCCM
How to Install Reporting Service for SCCM
How to Configure Report Server for SCCM

Tips on SCCM SQL Server Best Practices

Below are some of the best practices followed

64K file allocation unit size

By default, the NTFS file format uses 4K file allocation units. Since the SQL stores data in pages and extents which is of size 8K and 64K respectively. It’s recommended to use 64K file allocation size to format the drives which will hold the SQL Databases including tempdb

 

 

 

 

 

ConfigMgr Database Sizing

Below is a rough estimate for configuring the ConfigMgr database size based on the initial sizing and number of clients. Make sure you forecast the asset count increase for next 5 years and include it in the total number of clients

250 MB (initial DB size) + (x * 6MB) – Where x is the number of clients the site server manages

For Example, if an Organization has 10K workstations including the growth for next 5 years the ConfigMgr DB size will be as below:

Data File = 250 + 10,000*6 = 60250 MB
Log File = 30% of Data File =  18075 MB

Its always recommended to pre-create the DB file

TempDB best practice

Tempdb files should be placed on a dedicated drive. If your ConfigMgr hierarchy is huge with Central Administration Site and multiple primaries with SQL replication playing an important role then its recommended to place the tempdb on a drive with high IOPS.

Tempdb should be sized at 25-30% of ConfigMgr DB

The latest version of SQL Setup adds multiple equally sized tempdb data files during a new instance installation. By default, setup adds as many tempdb data files as the logical processor count or eight, whichever is lower.

Pag File Sizing

Pagefiles along with server RAM are used as virtual memory and it’s recommended to set a MIN and MAX page file size

MIN = 1 X RAM on Server
MAX = 3 X RAM on Server

In my lab server with 2 GB RAM, below is the configuration.

Memory Configuration

By default, SQL will consume 100% of the memory available on the server. If ConfigMgr DB resides on Primary site then restrict max memory to 75% of total RAM and if ConfigMgr DB is on remote SQL server then restrict max memory to 80-90% of total RAM

AV Exclusions

AV exclusions play an important role in the SQL performance. Its recommended excluding the SQL process and DB files from Real-time monitoring for better performance. However, if these files get infected then it won’t be identified by your antivirus. Below are the exclusions for SQL Server 2017.

%ProgramFiles%\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\SQLServr.exe
%ProgramFiles%\Microsoft SQL Server\MSRS14.MSSQLSERVER\Reporting Services\ReportServer\bin\ReportingServicesService.exe
SQL Server data files (.mdf, .ndf, .ldf files)
SQL Server backup files (.bak, .trn files)
Trace files (.trc files )

SQL Service Account

As a SQL Security best practice, it is recommended to use a service account for SQL and Reporting Service. Use SQL Server configuration manager to manage the passwords of the SQL Service account.

How to Install SQL Server 2017 for SCCM

Begining with SQL Server 2017, SSRS installation moved out of the standard SQL server installation to a lightweight installer. The major advantage I see here is that now Reporting services can be upgraded without any impact to the SQL Server database engine. Below are the steps involved in installing a stand-alone SQL 2017 instance.

  1. As soon as you start the SQL 2017 setup, just click on the installation tab on the SQL Server Installation Center and then on New SQL Server stand-alone Installation.

2. Enter the product key, accept the agreement, install the setup files and run the pre-requisite rules. Proceed to the next window once you get all rules passed

3. In Feature Selection, you need to select Database Engine Services and Client Tools Connectivity. From SQL 2017 onwards, Reporting Services moved out of the SQL Setup, you need to download from Microsoft Download Center. More details on Reporting Service found here

4. Select Default Instance as a part of Instance Configuration. I won’t prefer installing ConfigMgr on SQL with multiple instances as it’s really hard to troubleshoot the performance bottlenecks. ConfigMgr license comes with standard SQL included, so nothing to worry about the SQL licensing.5. Provide the SQL Service account details(recommended) or else leave it on default SYSTEM Account.

6. Provide the Collation SQL_Latin1_General_CP1_CI_AS

7. Select Windows Authentication and provide SQL Service account and SQL DBA security group Administrator access.

8. Select the Data directories, based on your drive configurations. Recommended placing on different drives.

9. Place tempdb on a separate drive with high IOPS and the setup will detect the number of files based on the logical processor on the server or 8 whichever is lower. Since my test server is having one logical processor, it has taken the number of files as 1. Also, make sure you do the tempdb sizing as I mentioned earlier for better performance.

10. Once the setup is complete, you will get an installation successful window.The complete guide for sccm server migration

11. Reboot the server and Install SQL Server Management Studio separately by downloading the installer from here

How to Install Reporting Service for SCCM

Download the SQL Server 2017 Reporting services installer from here

  1. Run the SQL Server Reporting Services setup to land on the welcome page.

The complete guide for sccm server migration

2. Choose the appropriate licensing option and proceed with the license terms agreement. It will be the same key used for SQL Server 2017 installation. More info on Reporting Services licensing can be found hereThe complete guide for sccm server migration

3. Select Install Reporting Services only and complete the setup.The complete guide for sccm server migration

You may need to reboot the server after configuring the Report Server.

How to Configure Report Server for SCCM

  1. Open the SSRS Configuration page and connect to the Report Server Instance

The complete guide for sccm server migration

2. Select the Service Account tab and click on use another account if you are using a service account for SSRS as well. Provide the Service account and password and click on apply.The complete guide for sccm server migration

3. Select the Web Service tab and verify the default provided values. If you wish to make any changes to the Report Server URL then modify it according to your requirements and then click on Apply so that the settings will be saved.

4. Select the Database tab on the left and then click on Change Database on right for creating a new Report Server Database in SSRS configuration window.

a. Click on test connection tab to check the connectivity to SQL database engine and proceed to next step on success

The complete guide for sccm server migration

b. Provide the Report Server database name if you want custom naming. Else, leave it default and the name will be ReportServer 

The complete guide for sccm server migration

c. Service Credential will be showing up on the credentials page since I have used the service account. You don’t need to provide the password here again if you have already configured the service account initially.

d. Finally, you will see the summary page where you can verify the details which were provided earlier before the SSRS database is created.

e. On proceeding further the SSRS DB is created and appropriate permissions are set.

The complete guide for sccm server migration

5. Backup the SSRS encryption key (.snk file and password) in a safe location to restore the SSRS in case of any failure.The complete guide for sccm server migration

18 COMMENTS

  1. So what you mean is install a new instance of SQL 2017 and then move the DB to SQL 2017?

    Any possibility to directly upgrade SQL 2016 SP1 to SQL 2017? I am aware RS is separate in SQL 2017.

    Thanks

    Ram

    • I have not tested the in-place upgrade of SQL 2016 SP1 to SQL 2017 with ConfigMgr DB. Logically it should work and the only difference would be to upgrade the SSRS separately from DB engine upgrade.

  2. Hello, Rajul!
    Is it to possible to the in-place upgrade OS Windows 2012 R2 -> Windows 2016 on Site Server?
    Or needed to make a new site and migration?

    • It is possible, just make sure you do a full site backup from within SCCM, then do a complete site stop.

      Once the OS is upgraded, I had to run ConfigMgr setup and run “Perform Maintenance or reset this site” so that all the components were re-initialised – not doing this causes many headaches..

      I had some issues afterwards where several windows services failed to autostart but this could be my environment

      • Yes, it is possible. But from my past experience, I would say to restore the site on a fresh machine with fresh OS installation. By doing this, you are not carrying the rot from the old server. Obviously, you will have to make some extra effort but it will be worth and will have a problem-free environment.

  3. hi,
    I have primary server with remote sql server with Sccm cb 1710,now I want to migrate the sql server from physical to virtual.what are the best practices for p2v?what are the precautions need to be taken?

    • P2V is ok. I used P2V in the environment where I work. In new world P2V is so normal and you don’t need to worry about that at all.

      If you would have asked this question 6-7 years before then, the answer could be different

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.