SQL Server recommendations

The database is an important part of BMC Client Management, especially in highly distributed environments. For each of the three basic types of database-Web Application (Web), Online Transaction Processing (OLTP), and Data Warehousing (DW)-the hardware component priorities are different. Client Management works like a web application with web services; it behaves like an OLTP application when integrating all information reported by devices; and it performs like a DW application for report generation, aggregating the results and displaying inventory views.

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

If you need to refer to this page offline, you can export it as a PDF by clicking .

Disk layout

When you run the Create_TS.sqlserver.sql script, which is 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	—

Recommended minimum disk layout

Required number of disks	Rpms/s	File system	Purpose
1	10 K / 15 K or SSD	NTFS	/ SYSTEM - SQL Server
1	10 K / 15 K or SSD	NTFS	/ TRANSACTION LOG
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR TABLES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR INDEXES
1	10 K / 15 K or SSD	NTFS	/ TEMPDB

Recommended best disk layout

Required number of disks	Rpms/s	File system	Purpose
1	10 K / 15 K or SSD	NTFS	/ SYSTEM - SQL Server
2	10 K / 15 K or SSD	NTFS	/ TRANSACTION LOG
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR SMALL TABLES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR LARGE TABLES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR STATIC TABLES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR SMALL INDEXES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR LARGE INDEXES
1	10 K / 15 K or SSD	NTFS	/ TABLESPACES FOR STATIC INDEXES
1	10 K / 15 K or SSD	NTFS	/ TEMPDB

Recommended RAID implementation

Required number of disks	Rpms/s	RAID level	File system	Purpose
2	10 K / 15 K or SSD	10	NTFS	/ SYSTEM + SQL Server + Backup
2	10 K / 15 K or SSD	1	NTFS	/ TRANSACTION LOG
8	10 K / 15 K or SSD	10	NTFS	/ ALL TABLESPACES
2	10 K / 15 K or SSD	10	NTFS	/ 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

Storage capacity

Database element	Space requirement	20,000 nodes	50,000 nodes	100,000 nodes	150,000 nodes	200,000 nodes
Database files	DB size	20 GB	50 GB	100 GB	150 GB	200 GB
Transaction log "Simple"	DB size * 0.2	4 GB	10 GB	20 GB	30 GB	40 GB
tempdb	DB size * 0.1	2 GB	5 GB	10 GB	15 GB	20 GB
Backup	DB size * 1.0	20 GB	50 GB	100 GB	150 GB	200 GB
Total		46 GB	115 GB	230 GB	345 GB	460 GB

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.

MS SQL configurations

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

Note

Do not run or modify any database configuration without the approval of the database administrator.

Run the following query on the BCM database :
SELECT
is_parameterization_forced as 'Parameterization Forced',
is_read_committed_snapshot_on as 'Read Committed Snapshot On' ,
snapshot_isolation_state as 'Snapshot Isolation State On'
FROM sys.databases
WHERE name LIKE '<BCM_DB_NAME>'
Replace <BCM_DB_NAME> with the BCM database name, for example, bcmdb .The preceeding query returns the current configuration of the database for the following parameters:
- Parameterization
- Read Committed Snapshot
- Snapshot Isolation State Verify the result for these parameters and ensure that they are all set to 1.
(Optional) If the value for one or more parameters is set to 0, execute the following steps to activate them:
1. Connect to the SQL server instance as a user with ALTER permission on the database.
2. Ensure that there are no active connections to the database except for the connection executing the Alter database command.
3. (Optional) Type the following command to set the Parameterization option to Forced:
  Alter database '<BCM_DB_NAME>' set PARAMETERIZATION FORCED
  Replace <BCM_DB_NAME> with the BCM database name, for example, bcmdb .
(Optional) Run the following SQL commands to turn on the Read Committed Snapshot and Snapshot Isolation State options:
ALTER DATABASE <BCM_DB_NAME> SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE <BCM_DB_NAME> SET READ_COMMITTED_SNAPSHOT ON;
Replace <BCM_DB_NAME> with the BCM database name, for example, bcmdb .
If you installed version 12.1 of BMC Client Management , these recommendations are set automatically by the setup.

SQL Server 2017 and SQL Server 2019

For SQL Server 2017 and SQL Server 2019, 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