Recommendations for MS SQL Server

This topic provides recommendations for the appropriate database configuration for MS SQL Server in highly distributed environments.

Important

You can enable secure communication between master and SQL Server.

For example: DatabaseName=Driver={ODBC Driver 17 for SQL Server};Server=ServerName;Database=bcm_master;Encrypt=yes;TrustServerCertificate=yes

Disk layout

When you run the Create_TS.sqlserver.sql script provided for tablespace creation on your database, BMC recommends that you segregate the tablespaces on different disks, as shown in the following table.

Tablespaces for tables

Small	Large	Static
BCM_DATA	BCM_EL	BCM_VMCKB
BCM_VM	BCM_INV	BCM_ESIDKB
BCM_WQ	BCM_DELTAINV	BCM_PATCH
—	BCM_VMINV	—
—	BCM_ESIDDATA	—
—	BCM_SCAP	—

Tablespaces for indexes

Small	Large	Static
BCM_INDEX	BCM_ELINDEX	BCM_VMCKBINDEX
BCM_VMINDEX	BCM_INVINDEX	BCM_ESIDKBINDEX
BCM_WQINDEX	BCM_DELTAINDEX	BCM_PATCHINDEX
—	BCM_VMINVINDEX	—
—	BCM_ESIDDATAINDEX	—
—	BCM_SCAPINDEX	—

Minimum requirement disk layout

Disk	Purpose
Disk 1	/ SYSTEM - SQL Server
Disk 2	/ TRANSACTION LOG
Disk 3	/ TABLESPACES FOR TABLES
Disk 4	/ TABLESPACES FOR INDEXES
Disk 5	/ TEMPDB

Recommended best disk layout

Disk	Purpose
Disk 1	/ SYSTEM - SQL Server
Disk 2	/ TRANSACTION LOG
Disk 3	/ TABLESPACES FOR SMALL TABLES
Disk 4	/ TABLESPACES FOR LARGE TABLES
Disk 5	/ TABLESPACES FOR STATIC TABLES
Disk 6	/ TABLESPACES FOR SMALL INDEXES
Disk 7	/ TABLESPACES FOR LARGE INDEXES
Disk 8	/ TABLESPACES FOR STATIC INDEXES
Disk 9	/ TEMPDB

Database volume creation

BMC recommends that you use a basic disk storage type for all volumes. Also, use the default disk alignment provided by Windows 2012 or later and use the NTFS file system with a 64-KB allocation unit for SQL database and log partitions.

For tempdb, Microsoft recommends up to a 1:1 mapping between the number of files and logical CPUs. A more reasonable approach is to have a 1:1 mapping between files and logical CPUs up to eight files. If, for example, you have a server with 4 logical CPUs that manages 20,000 nodes, it creates 4 files of 500 MB for tempdb.

Storage capacity sizing

When sizing storage capacity for an SQL Server system, you need to consider the following SQL Server database components:

Database files
Transaction log files
Tempdb and backup

Database element	5,000 nodes	10,000 nodes	20,000 nodes	50,000 nodes	100,000 nodes	150,000 nodes	200,000 nodes
Database files (1)	5 GB	10 GB	20 GB	50 GB	100 GB	150 GB	200 GB
Tablespaces for tables DB size * 0.4	2 GB	4 GB	8 GB	20 GB	40 GB	60 GB	80 GB
Tablespaces for index DB size * 0.6	3 GB	6 GB	12 GB	30 GB	60 GB	90 GB	120 GB
Transaction log DB size * 0.2	1 GB	2 GB	4 GB	10 GB	20 GB	30 GB	40 GB
Tempdb DB size * 0.1	500 MB	1 GB	2 GB	5 GB	10 GB	15 GB	20 GB
Backup DB size * 1.0	5 GB	10 GB	20 GB	50 GB	100 GB	150 GB	200 GB
Total (GB)	11,500	23	46	115	230	345	460
As a rule of thumb, the size of the database is: number of nodes * 1 MB

MS SQL Server configurations

To ensure that the SQL Server delivers optimum performance when working with BMC Client Management, you need to specifically configure some parameters.

For SQL Server 2017, SQL Server 2019, and SQL Server 2022, you need to specifically configure some parameters. To check these parameters, run the following query on the BCM database.

Select Convert(VARCHAR(60), name) AS 'Name'
, value
FROM sys.configurations
where (name = 'max degree of parallelism' or name = 'cost threshold for parallelism')

The above query returns the current configuration of the database for the following parameters:

Name	Value
max degree of parallelism	0
cost threshold for parallelism	50

To modify these values, you have to execute the following queries:

USE '<BCM_DB_NAME>';
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0'
GO
RECONFIGURE WITH OVERRIDE;
GO
USE '<BCM_DB_NAME>';
GO
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50'
GO
RECONFIGURE WITH OVERRIDE;
GO

SQL Server Database Maintenance

For information about SQL Server's maintenance plan, see Create a Maintenance Plan and Use the Maintenance Plan Wizard.