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 2014 in highly distributed environments.
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
Disk | Rmp/s | File system | Purpose |
---|---|---|---|
1 | 10 K / 15 K | NTFS | / SYSTEM - SQL Server |
1 | 10 K / 15 K | NTFS | / TRANSACTION LOG |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR TABLES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR INDEXES |
1 | 10 K / 15 K | NTFS | / TEMPDB |
Recommended best disk layout
Disk | Rmp/s | File system | Purpose |
---|---|---|---|
1 | 10 K / 15 K | NTFS | / SYSTEM - SQL Server |
2 | 10 K / 15 K | NTFS | / TRANSACTION LOG |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR SMALL TABLES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR LARGE TABLES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR STATIC TABLES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR SMALL INDEXES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR LARGE INDEXES |
1 | 10 K / 15 K | NTFS | / TABLESPACES FOR STATIC INDEXES |
1 | 10 K / 15 K | NTFS | / TEMPDB |
Recommended RAID implementation
Disk | Rmp/s | RAID level | File system | Purpose |
---|---|---|---|---|
2 | 10 K / 15 K | 10 | NTFS | / SYSTEM + SQL Server + Backup |
2 | 10 K / 15 K | 1 | NTFS | / TRANSACTION LOG |
8 | 10 K / 15 K | 10 | NTFS | / ALL TABLESPACES |
2 | 10 K / 15 K | 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.
Important
- Do not run or modify any database configuration without the approval of the database administrator.
- In the queries specified below, replace <BCM_DB_NAME> with the BCM database name, for example, bcmdb.
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>'
The above query returns the current configuration of the database for the following parameters:
- 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:
- Connect to the SQL server instance as a user with ALTER permission on the database.
- Ensure that there are no active connections to the database except for the connection executing the
Alter
database command. (Optional) Type the following command to set the Parameterization option to Forced:
Alter database '<BCM_DB_NAME>' set PARAMETERIZATION FORCED
(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;
If you installed version 12.1 of BMC Client Management, these recommendations are set automatically by the setup.
SQL Server database maintenance recommendations
When you are creating a maintenance plan in SQL Server by using SQL Server Management Studio, you need to create the Reorganize Index, Rebuild Index, and Update statistics maintenance tasks. Refer to the following table for the configuration of the SQL Server Management Plan Wizard options.
Options | Configuration |
---|---|
Maintenance tasks | Reorganize Index, Rebuild Index, and Update statistics |
Reorganize Index | |
Specific databases | BCM database (bcmdb) |
Define reorganize index tasks | Compact large objects: select Scan type: Fast Fragmentation: 30% Page count: 1000 Used in last: 7 days |
Daily frequency | Occurs once at 1:00:00 AM |
Rebuild Index | |
Databases | BCM database (bcmdb) |
Free space options | Default free space per page |
Advanced options | Sort results in tempdb: select Keep index online: select Low priority used: select |
Index stats options | Fast |
Optimize index only if | Fragmentation: >30% Page count: >1000 Used in last: 7 days |
Daily frequency | Occurs once at 1:30:00 AM |
Update statistics | |
Databases | BCM database (bcmdb) |
Update | Column statistics only |
Scan type | Full scan |
Daily frequency | 2:00:00 AM |
Comments
Log in or register to comment.