# SQL

{% hint style="info" %}

#### WHAT IS SQL?

Structured Query Language (**SQL**) lets you access and manipulate databases. It became a standard of the American National Standards Institute (**ANSI**) in 1986, and of the International Organization for Standardization (**ISO**) in 1987.

Read more about SQL [here](https://en.wikipedia.org/wiki/SQL)
{% endhint %}

## SQL SERVER 2019 INSTALLATION

After installing and configuring the required prerequisites for Microsoft Endpoint Configuration Manager, it's time to install and configure the SQL Server 2019, so log into to the CM server, and we'll get started.

{% hint style="info" %}
SQL Server 2019 has some known issues, for more information about that or supported SQL versions click [here](https://docs.microsoft.com/en-us/configmgr/core/plan-design/configs/support-for-sql-server-versions)
{% endhint %}

**Step 1.** Mount the "**SQL Server 2019**" media to the CM server. Execute the "**Setup.exe**" from the media and click "**New SQL Server stand-alone installation or add features to an existing installation**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TsqkZ3pt2htE8FgFX%2F-M4TurIxN_PoyEZ9_rmx%2FCM_SQL_01.JPG?alt=media\&token=1191233b-5c12-4a73-9468-406c5a2b2ffe)

**Step 2.** Enter a product key or select the "**Evaluation**" edition which will expire after 180 days. Click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TsqkZ3pt2htE8FgFX%2F-M4TvHalEizDytQKrhjl%2FCM_SQL_02.JPG?alt=media\&token=e7ab44a9-98b7-4426-951c-cd2f51c65b00)

**Step 3.** Check the "**I accept the license terms and Privacy Statement**" box and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4Twl5yZ3o-mytGCuQY%2FCM_SQL_03.JPG?alt=media\&token=0b8a4556-2b47-44ba-a46d-63c33efd6d42)

**Step 4.** Check the "**Use Microsoft Update to check for updates (recommended)**" box and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TwsTKujBtvXSfNVcH%2FCM_SQL_04.JPG?alt=media\&token=23c3b2a2-0cfd-40a6-89ba-37e374caac25)

**Step 5.** The below "**Windows Firewall**" warning is OK! But if you are getting any errors or warnings at this step, I would always advise you to read the details before proceeding with the installation.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TwwJ7D8S11stelTb3%2FCM_SQL_05.JPG?alt=media\&token=1b53220c-dbfc-4a8b-ac96-7004e60b83d0)

**Step 6.** Select "**Database Engine Services**" and change the directory drive letter to "**E:\\**" which is the ConfigMgr drive.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4Tx0PdqkWFh3O4V7qq%2FCM_SQL_06.JPG?alt=media\&token=cfd26b7b-a649-43c1-ac0a-179cb0a5e991)

**Step 7.** Leave everything default and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4Tx53JSvVGCGt3hyJy%2FCM_SQL_07.JPG?alt=media\&token=1ed60491-5cca-40e8-b612-1e8c8cd5767d)

**Step 8.** Set the "**SQL Server Agent**" and "**SQL Server Database Engine**" services to run with the SQL service account that we created earlier and set the startup type to "**Automatic**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxA2FvAqGnIl7Jdqn%2FCM_SQL_08.JPG?alt=media\&token=552ae6c1-14bb-45a2-8448-36d2f7059cb9)

**Step 9.** Click on the "**Collation**" tab. Set "**Database Engine:**" to use "**SQL\_Latin1\_General\_CP1\_CI\_AS**" and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxGNy_yBB-Wg9DwwG%2FCM_SQL_09.JPG?alt=media\&token=e3046307-216e-436b-95e5-a71ff2345ad1)

**Step 10.** Select "**Windows authentication mode**". Add the "**Current User**" and "**CM Administrators**" group as SQL Server administrators.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxKtorfiABR31Gud7%2FCM_SQL_10.JPG?alt=media\&token=0b66a53d-67b6-4f73-87b7-ca80a38d30de)

**Step 11.** Click on the "**Data Directories**" tab and change the path for "**User database**" and "**User database log**" directory. Also make sure that the "**Data root**" and "**Backup**" directory is set to use the drive letter "**E:\\**" which is the ConfigMgr drive.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxP2s3aJbDlELNIyu%2FCM_SQL_11-1.JPG?alt=media\&token=b938e5d6-f5f2-4e65-9c1c-f1e6f04d625f)

**Step 12.** Click on the "**TempDB**" tab and change the path for "**Data**" and "**Log**" directory. During this step I made an mistake with the database sizing and I will show you how to correct it in the step "**TempDB Correction**" further down in this post. 

Here's the correct approach during the setup wizard.

{% hint style="info" %}
For estimating the database size, I am using a Excel file created by [Kent Agerlund](https://twitter.com/agerlund) which can be downloaded [here](https://blog.ctglobalservices.com/configuration-manager-sccm/kea/system-center-2012-configuration-manager-sql-recommendations/)
{% endhint %}

I will create an database for 2000 Clients, 1 Processor, 8 Cores and 32 GB of Memory.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4UFQj2sXzcj1AWOJZ_%2F-M4UIPu3_vsiOOqZgb5X%2FCM_SQL_35.JPG?alt=media\&token=0b2ac889-6ce9-4f5d-b7a1-7c77674af7ab)

According to the Excel calculations, the TempDB values should be as shown in the table below. So, as already mentioned, change the path for "**Data**" and "**Log**" directory, add the below values and click "**Next**"

| **TempDB Data files**  | **Value** |
| ---------------------- | --------- |
| **Number of files:**   | 8         |
| **Initial size (MB):** | 567       |
| **Autogrowth (MB):**   | 256       |

| **TempDB Log file**    | **Value** |
| ---------------------- | --------- |
| **Initial size (MB):** | 2268      |
| **Autogrowth (MB):**   | 512       |

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxYMHXjVM6Zy7-SL-%2FCM_SQL_13.JPG?alt=media\&token=c65a66f9-5373-4f0a-90c7-99a82c0cb494)

**Step 13.** Verify that the SQL Server 2019 features to be installed is correct and click "**Install**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxcG4phOFWJht93Fn%2FCM_SQL_14.JPG?alt=media\&token=04f1fcb3-4ee4-441d-82fe-e05d7aaa5278)

**Step 15.** Everything should be completed successfully. Click "**Close**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4TvzBu4TscSmoQIHNS%2F-M4TxhYrc4-imCphX0gQ%2FCM_SQL_15.JPG?alt=media\&token=b173ec31-2e91-4747-8114-2608fc4420b0)

### SQL Management Studio

SQL Server Management Studio (**SSMS**) can be downloaded from [here](https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver15)

**Step 1.** Download and execute "**SSMS-Setup-ENU.exe**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4Xr95rZOwARW2kUNUQ%2FCM_SQL_16.JPG?alt=media\&token=9f45d772-9389-496b-8517-27158407fcec)

**Step 2.** Change the drive letter of the location to "**E:\\**" which is the ConfigMgr drive. Click "**Install**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XrDJi7DEYCQkvfhL_%2FCM_SQL_17.JPG?alt=media\&token=9ea3d886-0f8a-44ed-9c91-c1890aeda7ab)

**Step 3.** Click "**Restart**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XrHIcq4ZuFaVI6Dth%2FCM_SQL_18.JPG?alt=media\&token=1c1e6f0c-cba5-4bf5-be57-2eede9c95c32)

### SQL Reporting Services

SQL Server 2019 Reporting Services (**SSRS**) can be downloaded from [here](https://www.microsoft.com/en-us/download/details.aspx?id=100122)

**Step 1.** Log into to the CM server. Download and execute "**SQLServerReportingServices.exe**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4Xr95rZOwARW2kUNUQ%2FCM_SQL_16.JPG?alt=media\&token=9f45d772-9389-496b-8517-27158407fcec)

**Step 2.** Click "**Install Reporting Services**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4Xu6vws4pA5tyqW83f%2FCM_SQL_19.JPG?alt=media\&token=5042253d-345b-41bb-bdea-f91c001c175d)

**Step 3.** Enter a product key or select the "**Evaluation**" edition which will expire after 180 days. Click "**Next**"

{% hint style="info" %}

#### BONUS INFO

If you didn't note down the product key during SQL installation, you can actually find it in the "**.\x64\DefaultSetup.ini**" file on the "**SQL Server 2019**" media.
{% endhint %}

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XuBr8vkp--kVkCBQC%2FCM_SQL_20.JPG?alt=media\&token=e0dc2e91-358a-4805-9d9d-92c91964b958)

**Step 4.** Check the "**I accept the license terms**" box and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XuFTuvUJ2IerygnC7%2FCM_SQL_21.JPG?alt=media\&token=4c407f88-3120-4d50-bdea-2a5f24615115)

**Step 5.** Click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XuSwIHZL9fnH8jtTz%2FCM_SQL_22.JPG?alt=media\&token=a16f4c33-171c-4344-ac4a-10fad74fe8c5)

**Step 6.** Change the drive letter of the location to "**E:\\**" which is the ConfigMgr drive. Click "**Install**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XuYAzS4QhqDtDVChg%2FCM_SQL_23.JPG?alt=media\&token=081a0926-cf39-44b6-af69-35b51cda3f77)

**Step 7.** Click "**Close**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4XuhfJKyRYiu13pYOE%2FCM_SQL_24.JPG?alt=media\&token=af543b1d-d1f4-470f-929c-7acfa02f9740)

### SQL Server 2019 Update

Latest Microsoft SQL Server 2019 Updates can be downloaded from [here](https://sqlserverbuilds.blogspot.com/#sql2019x)

**Step 1.** Download and execute "**SQLServer2019-KBXXXXXXX-x64.exe**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xpy2kEnIhA4oRBTTy%2F-M4Xr95rZOwARW2kUNUQ%2FCM_SQL_16.JPG?alt=media\&token=9f45d772-9389-496b-8517-27158407fcec)

**Step 2.** Check the "**I accept the license terms and Privacy Statement**" box and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xzn6qKkVoQOzxxeiy%2F-M4Y2ckjjYfmCs2LFHMT%2FCM_SQL_25.JPG?alt=media\&token=afcf2c92-c8e6-4505-b4f1-553a8c6735d4)

**Step 3.** Leave everything default and click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xzn6qKkVoQOzxxeiy%2F-M4Y2gEDDzzqKa49ZM5D%2FCM_SQL_26.JPG?alt=media\&token=73edb1ad-7a88-42b6-a24e-ea782fa45573)

**Step 4.** Click "**Next**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xzn6qKkVoQOzxxeiy%2F-M4Y2kM5IwS-j-dyJ5nW%2FCM_SQL_27.JPG?alt=media\&token=0b7aac95-c92c-4ee7-90ea-20157a89b5c4)

**Step 5.** Click "**Update**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xzn6qKkVoQOzxxeiy%2F-M4Y2nhVx8ztSN3kbbOE%2FCM_SQL_28.JPG?alt=media\&token=0e337c28-14f0-4bc0-b314-d41ca9c41ba4)

**Step 6.** Click "**Close**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Xzn6qKkVoQOzxxeiy%2F-M4Y2rZJa3qZCvcW3eX6%2FCM_SQL_29.JPG?alt=media\&token=74798c0a-53ba-44a4-b892-4fb132f3b41e)

### Service Principal Names

When configuring the SQL server to use a domain account instead of the local system account, you must manually register the Service Principal Name (**SPN**) for the domain account.

**Step 1.** Log into the CM server with an Domain Administrator account. Type "**CMD**" in the search line next to the start button, and click "**Command Prompt**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Y3kxZyYhLRMYSFq--%2F-M4Y5XI5D-RrlZ7f08QJ%2FCM_SQL_30.JPG?alt=media\&token=df4ed00d-e15b-4f5a-848e-145e0a29b0d9)

**Step 2.** Run the below commands to create the SPN.

```
# Change the HOST, DOMAIN and ACCOUNT name for each command
# Run the following commands to create the SPN

setspn -A MSSQLSVc/HOST:1433 DOMAIN\ACCOUNT
setspn -A MSSQLSVc/HOST.DOMAIN.LOCAL:1433 DOMAIN\ACCOUNT

# Run this command to verify the SPN creation

setspn –L DOMAIN\ACCOUNT
```

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Y3kxZyYhLRMYSFq--%2F-M4Y5a1DoyRqnaOQRT_c%2FCM_SQL_31.JPG?alt=media\&token=15f95bd9-6db4-43a1-865e-413bc07eaf2d)

## SQL Server Configuration

Now that we are done with the SQL server installation, it's time to configure it:sunglasses:

**Step 1.** Type "**Microsoft SQL Server**" in the search line next to the start button, and click "**Microsoft SQL Server Management Studio 18**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YBZFRn5KJpdIOU-vG%2F-M4YCtCCmYtGikynDwsL%2FCM_SQL_32.JPG?alt=media\&token=f618d33a-90d9-4a9c-82af-8b9411075ab8)

**Step 2.** Right-click on the "**CM (SQL Server...)**" node and select "**Properties**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YBZFRn5KJpdIOU-vG%2F-M4YD3qnyzypfcct9_wv%2FCM_SQL_33.JPG?alt=media\&token=edf0d648-1ea5-45a0-a260-7eb26b065a1d)

**Step 3.** Select the "**Memory**" page and set "**Minimum server memory**" to 8192 MB and the "**Maximum server memory**" to 25600 MB which is 80% of the total system memory.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YBZFRn5KJpdIOU-vG%2F-M4YDDfuyNSpoJhlescM%2FCM_SQL_34.JPG?alt=media\&token=49614615-c926-4c76-a4fa-f13b546bf511)

### CM Database Creation

It´s not required, but I recommend creating the CM database before installing CM. As mentioned during the SQL server installation, I'm using a Excel file to estimate the database size.

{% hint style="info" %}
For estimating the database size, I am using a Excel file created by Kent Agerlund which can be downloaded [here](https://blog.ctglobalservices.com/configuration-manager-sccm/kea/system-center-2012-configuration-manager-sql-recommendations/)
{% endhint %}

I will create an database for 2000 Clients, 1 Processor, 8 Cores and 32 GB of Memory.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4UFQj2sXzcj1AWOJZ_%2F-M4UIPu3_vsiOOqZgb5X%2FCM_SQL_35.JPG?alt=media\&token=0b2ac889-6ce9-4f5d-b7a1-7c77674af7ab)

According to the Excel calculations, the ConfigMgr DB values should be as shown in the table below.

| **ConfigMgr DB**    | **Value** |
| ------------------- | --------- |
| **Number of files** | 8         |
| **Size per file**   | 1890      |
| **Autogrowth**      | 624       |

| **ConfigMgr Log** | **Value** |
| ----------------- | --------- |
| **Size per file** | 4990      |
| **Autogrowth**    | 512       |

With that in mind I've modified a SQL query created by [SystemCenterDudes](https://twitter.com/scdudes) to fit my needs.

I'm using "**PS1**" as my site code, but if you are going to use another site code or path for the database and log file, then please remember to change it in the SQL query below before executing it.

{% hint style="info" %}
I've found my inspiration to this SQL query on a [blog post](https://www.systemcenterdudes.com/complete-sccm-installation-guide-and-configuration/) created by [SystemCenterDudes](https://twitter.com/scdudes) but please keep in mind that I'm no SQL query guru and it ain't perfect but it'll do:nerd:
{% endhint %}

```
USE master
GO
CREATE DATABASE CM_PS1 ON ( NAME = CM_PS1_1, FILENAME = 'F:\Database\CM_PS1_1.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
LOG ON ( NAME = PS1_log, FILENAME = 'G:\Logs\CM_PS1.ldf', SIZE = 4990, MAXSIZE = 25600, FILEGROWTH = 512)
GO
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_2, FILENAME = 'F:\Database\CM_PS1_2.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_3, FILENAME = 'F:\Database\CM_PS1_3.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_4, FILENAME = 'F:\Database\CM_PS1_4.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_5, FILENAME = 'F:\Database\CM_PS1_5.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_6, FILENAME = 'F:\Database\CM_PS1_6.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_7, FILENAME = 'F:\Database\CM_PS1_7.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
ALTER DATABASE CM_PS1 ADD FILE ( NAME = CM_PS1_8, FILENAME = 'F:\Database\CM_PS1_8.mdf', SIZE = 1890, MAXSIZE = Unlimited, FILEGROWTH = 624)
```

**Step 1.** Open "**Microsoft SQL Server Management Studio 18**" and click on "**New Query**" from the toolbar. Copy / Paste my SQL query and make your changes before executing it.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4mkwEUV19Bj2cXLEFO%2F-M4mmdvfggUlWOfpzboL%2FCM_SQL_36.JPG?alt=media\&token=6d6ab1ea-41ff-481b-b873-e71ebe9455f6)

**Step 2.** Verify that the database and log file has been created.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YiaIUx79rWxGWTbMN%2F-M4YrqxyA0ea0XK6-Kx3%2FCM_SQL_37.JPG?alt=media\&token=6b243649-7db5-4818-9041-84af166ec6bd)

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YiaIUx79rWxGWTbMN%2F-M4Ys5ii9-KTmj9JrLLm%2FCM_SQL_38.JPG?alt=media\&token=6e3335a3-f88e-4ee6-b158-e49c3ecd3899)

**Step 3.** From "**Microsoft SQL Server Management Studio 18**" right-click on the database "**CM\_PS1**" and select "**Properties**". Verify that the collation is set to "**SQL\_Latin1\_General\_CP1\_CI\_AS**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YiaIUx79rWxGWTbMN%2F-M4YsEI-77-lwhnipuSx%2FCM_SQL_39.JPG?alt=media\&token=436db470-4967-4041-ada9-f392cc646888)

**Step 4.** Click on the "**Files**" page and change the owner to "**SA**". Verify that the Size, Autogrowth / Maxsize and Path is correct.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4YiaIUx79rWxGWTbMN%2F-M4YtQdiiZC99eDxjdnF%2FCM_SQL_40.JPG?alt=media\&token=bc36e701-62d8-4f54-94d8-ce76de47d18b)

**Step 5.** Click on the "**Options**" page, change "**Recovery model**" to use "**Simple**" and click "**OK**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Yue3nAJG6iziRfk-j%2F-M4YvtDEP1HKlV9wlp8z%2FCM_SQL_41.JPG?alt=media\&token=0a54b005-96ef-4b51-9385-c346d5122bb6)

### TempDB Correction

{% hint style="warning" %}

#### You can skip this step, if you didn't make the same mistake as i did:laughing:

{% endhint %}

Okay, as I mentioned during the SQL server installation, I made a mistake with the TempDB creation, so here's my way to correct this afterwards.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Z69kycsP8lqy6Hrui%2F-M4Z8qtllFG39cOSkAcg%2FCM_SQL_35.JPG?alt=media\&token=5f7e8dd6-77d8-4814-b87c-610308a5005b)

According to the Excel calculations, the TempDB values should be as shown in the table below. Take these values an add them to the below SQL query.

| **TempDB DB**     | **Value** |
| ----------------- | --------- |
| **Size per file** | 567       |
| **Autogrowth**    | 256       |

| **TempDB Log**    | **Value** |
| ----------------- | --------- |
| **Size per file** | 2268      |
| **Autogrowth**    | 512       |

```
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'H:\Database\tempdb.mdf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp2, FILENAME = 'H:\Database\tempdb_mssql_2.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp3, FILENAME = 'H:\Database\tempdb_mssql_3.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp4, FILENAME = 'H:\Database\tempdb_mssql_4.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp5, FILENAME = 'H:\Database\tempdb_mssql_5.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp6, FILENAME = 'H:\Database\tempdb_mssql_6.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp7, FILENAME = 'H:\Database\tempdb_mssql_7.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
ALTER DATABASE tempdb MODIFY FILE (NAME = temp8, FILENAME = 'H:\Database\tempdb_mssql_8.ndf', SIZE = 567, MAXSIZE = Unlimited, FILEGROWTH = 256)
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'G:\Logs\templog.ldf', SIZE = 2268, MAXSIZE = Unlimited, FILEGROWTH = 512)
```

**Step 1.** Open "**Microsoft SQL Server Management Studio 18**" and click on "**New Query**" from the toolbar. Copy / Paste my SQL query and make your changes before executing it.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4mcTPCgsMiFC4p15jD%2F-M4mgew7E70eS1F2icgs%2FCM_SQL_42.JPG?alt=media\&token=05fc5430-76dd-4159-bd07-7daf98a729c9)

**Step 2.** Right-click on the database "**tempdb**" and select "**Properties**". Click on the "**Files**" page and make sure that the owner is set to "**SA**". Verify that the Size, Autogrowth / Maxsize and Path is correct.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Z69kycsP8lqy6Hrui%2F-M4ZAu-TqUcvcr5TOPcr%2FCM_SQL_43.JPG?alt=media\&token=4cefc3d7-0a5a-4ed2-b991-016122712b2e)

**Step 3.** Verify that the database and log file has been changed.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Z69kycsP8lqy6Hrui%2F-M4ZDcaLpwGKSQoNuny7%2FCM_SQL_44.JPG?alt=media\&token=b4a0fe94-41fc-42da-8bc5-19f9bc87c9d4)

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4Z69kycsP8lqy6Hrui%2F-M4ZDnCpBsD2sJIJHUbz%2FCM_SQL_45.JPG?alt=media\&token=6eaa9e33-3b7e-4033-b4fd-a650e9e8620d)

### SQL Network Configuration

We can make a few changes to the SQL Server Network Configuration to ensure a proper SQL communication.

**Step 1.** Type "**SQL Server 2019**" in the search line next to the start button, and click "**SQL Server 2019 Configuration Manager**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4ZE-4SC5rJ287iejOH%2F-M4ZFjXNhB3U5zOIR257%2FCM_SQL_46.JPG?alt=media\&token=fe5eb541-29df-49d5-9e17-f4bd941f87b6)

**Step 2.** Expand the "**SQL Server Network Configuration**", right-click on "**Protocols for MSSQLSERVER**" and select "**Properties**". Ensure that "**Enabled**" is set to "**Yes**" and "**Listen All**" is set to "**No**"

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4ZE-4SC5rJ287iejOH%2F-M4ZGBEZx2TOfi8xAsW0%2FCM_SQL_47.JPG?alt=media\&token=d0c0f605-ad9d-4356-aff2-218f2fdb623c)

**Step 3.** Click on the "**IP Addresses**" tab and find the IP setting containing the IP address of your CM server and make sure "**Enabled**" is set to "**Yes**". Set "**Enabled**" to "**No**" for the remaining IP settings.

![Configuration Manager - SQL](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4ZE-4SC5rJ287iejOH%2F-M4ZGEp0YiDISp3jzL4Z%2FCM_SQL_48.JPG?alt=media\&token=be02f04f-ce8b-43cd-ad07-8251b5995ed8)

## WSUS SERVER ROLE

This blog post won't be covering the Software Update Point (**SUP)** installation, but I'll prepare the WSUS server role and database for an upcoming blog post covering this part.

**Step 1.** Open the "**Server Manager**" and click "**Add Roles and Features**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cAUlzEHFVJqZN_kgn%2FCM_SQL_49.JPG?alt=media\&token=79796cd8-d209-4007-b95f-32a3b993294d)

**Step 2.** Click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cAncLWp8XRxYW-gVe%2FCM_SQL_50.JPG?alt=media\&token=a5d56412-49f8-4995-bd5c-ebd4c257ac0a)

**Step 3.** Leave everything default and click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cAtoDJRvX0w-G1YbC%2FCM_SQL_51.JPG?alt=media\&token=e0cbc6cd-b2a1-4d87-9fa5-62b3879cbba2)

**Step 4.** Leave everything default and click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cB-jutMQW-K_aWjoH%2FCM_SQL_52.JPG?alt=media\&token=161c2867-e249-446f-a0ca-37ee7d5e235e)

**Step 5.** Select "**Windows Server Update Services**". Leave everything default and click "**Add Features**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBBuwjVpN1T1KuJW6%2FCM_SQL_53.JPG?alt=media\&token=af483d33-2015-4e17-adfc-c39b9be421e1)

**Step 6.** Leave everything default and click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBHW6Wb4U3g9ZoCVq%2FCM_SQL_54.JPG?alt=media\&token=47b9572d-5367-40ba-8b57-4aff3d117e12)

**Step 7.** Click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBNa1jgHJdAnONG0x%2FCM_SQL_55.JPG?alt=media\&token=e1a2cf8c-4bd9-44b0-b213-dd00b1cf27a6)

**Step 8.** Select "**WSUS Services**" and "**SQL Server Connectivity**" on the role services list. Click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBX9wyXcK7LDcB6as%2FCM_SQL_56.JPG?alt=media\&token=a9b53fc6-2560-4652-9fc8-299f5659d160)

**Step 9.** Navigate to the ConfigMgr drive (**E:\\**) and create a WSUS folder in the root.

Return to the "**Add Role and Features Wizard**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBdsjN4uKdy89bawl%2FCM_SQL_57.JPG?alt=media\&token=6d6e4c18-7b64-4ae2-a034-bfcb8970fc20)

**Step 10.** Add the WSUS folder path and click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBkRccrrFI0PnCFm8%2FCM_SQL_58.JPG?alt=media\&token=0550ed27-3c1a-42d1-8e0f-645377301867)

**Step 11.** Enter the fully qualified domain name (**FQDN**) of your SQL server and click "**Next**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cBrXtRtu5U8l8AYJF%2FCM_SQL_59.JPG?alt=media\&token=5dec0ce8-5d64-4b38-ab4e-588e8ef7dea0)

**Step 12.** Check the "**Restart the destination server automatically if required**" box and click "**Install**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cC3ZmVoqLAhbHq6YX%2FCM_SQL_60.JPG?alt=media\&token=1245906e-b1ee-48bf-8e29-f7f71c2534db)

**Step 13.** Click "**Close**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cC7lkPCsyFMSBF4YN%2FCM_SQL_61.JPG?alt=media\&token=9395e024-ce37-44b7-880e-cf5afa5c7051)

**Step 14.** When the WSUS server role installation is done, we should see a yellow triangle with an exclamation mark in the "**Server Manager**", click on it and then click "**Launch Post-Installation tasks**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCDnONYdn1amyEZrd%2FCM_SQL_62.JPG?alt=media\&token=412deed8-eab8-47e9-8373-ed87f89c50d6)

### SQL Database Configuration

The WSUS server role are now installed and we'll make a few changes to the WSUS database.

**Step 1.** Type "**Microsoft SQL Server**" in the search line next to the start button, and click "**Microsoft SQL Server Management Studio 18**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCUj9St9UWVi61JgP%2FCM_SQL_32.JPG?alt=media\&token=b867536e-c53d-4fe5-9f1b-7c3292daefc0)

**Step 2.** Right-click on the database "**SUSDB**" and select "**Properties**". Click on the "**Files**" page and change the owner to "**SA**". Change the "**SUSDB**" autogrowth to 512 MB and limit the "**SUSDB\_log**" to 10240 MB.

Click "**OK**" when done.

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCYLJx_yVfUISRqw5%2FCM_SQL_63.JPG?alt=media\&token=b1b44312-f269-4a7e-b986-a0f27c5b141c)

### IIS Configuration

As the final step, we'll make a few important changes to the WSUS Application Pool in the Internet information Services (**IIS**) Manager.

{% hint style="info" %}
The computer that hosts a software update point requires the following configurations for IIS application pools.

* Increase the **WsusPool Queue Length** to **2000**.
* Increase the **WsusPool Private Memory limit** by four times, or set it to **0** (unlimited).

**Source**: [Microsoft Docs](https://docs.microsoft.com/en-us/configmgr/core/plan-design/configs/recommended-hardware#bkmk_RemoteSiteSystem)
{% endhint %}

**Step 1.** Type "**IIS**" in the search line next to the start button, and click "**Internet information Services (IIS) Manager**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCl1TnJJybR1dkeYY%2FCM_SQL_64.JPG?alt=media\&token=6ea1c9fa-d7e2-4194-85c7-33d2557a99f0)

**Step 2.** Navigate to the "**Application Pools**". Right-click on "**WsusPool**" and select "**Advanced Settings...**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCvscBhrCnP-Vt47Y%2FCM_SQL_65.JPG?alt=media\&token=ea8252d9-279a-470b-859a-4e7b6030ae88)

**Step 3.** Change the "**Queue Length**" to 2000.

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cCzwN4Zfpsn3wrBNK%2FCM_SQL_66.JPG?alt=media\&token=334bd09a-8e12-416b-a56a-d1c36693c447)

**Step 4.** Change the "**Private Memory Limit (KB)**" to 0 and click "**OK**"

![Configuration Manager - WSUS](https://3347085443-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-L_qpqqoWaR3gQAWE4Lk%2F-M4c7Qt1RRmScjmbk3Yc%2F-M4cD5-k6uj4gN0B6W6g%2FCM_SQL_67.JPG?alt=media\&token=d89c721c-1804-4fd5-a0a1-ac7bc19b1fd5)

This concludes the SQL installation and configuration. Go back to the main section by clicking on the link below or click "**Part 4 - Setting up Microsoft Endpoint Configuration Manager**" in the menu to the left.

{% content-ref url="" %}
[](https://www.osdsune.com/home/archive/microsoft-configuration-manager/configmgr-lab/part-4)
{% endcontent-ref %}