Notes on configuring SQL Server for use with SMP 8.x

Products

IT Management Suite Client Management Suite

Issue/Introduction

You are installing and/or configuring SQL Server for use with the Symantec Management Platform (SMP) and you need to know what is expected from the SQL server.

Environment

ITMS 8.x

Resolution

The Symantec Management Platform (SMP) stores much of its data in a Microsoft SQL Server database. This article describes many of the expectations about the SQL Server is configuration. The Symantec Management Platform (SMP) is the underlying core framework of the Altiris product line. This article also applies to many of Altiris solutions (in whole or in part). Please note, some solutions have additional requirements.

Supported versions of SQL server:

For current supported versions of SQL Server and additional details, please review:

Symantec Management Platform and OS Support Matrix

Where to install SQL server:

The SMP can use an SQL Server installed on the same computer or on a different computer. For additional information about recommended configuration see:

Altiris 7 Planning & Implementation Guide (v1.1) (HOWTO9811)

SQL Server, Database, and Account Credentials

The SMP stores information on one SQL server; in one database, using one schema. The default database name is Symantec_CMDB. The SMP can authenticate to the SQL Server using either a Windows account, or SQL Server login.

The SMP uses one set of credentials for most activities. It can also a second set of credentials to produce public reports. These credentials can be either:

the Altiris Application Identity (the Windows account that is used to run the Altiris services on the Notification Server), or
an SQL server login.

The name of the SQL server, the name of the database and the account credentials can be specified during installation, using Symantec Installation Manager (SIM); and can be viewed and/or modified using the Symantec Management Console (SMC). see: Settings > All Settings; then Settings > Notification Server > Database Settings; then General (tab).

Warning: changing these settings in the SMC does NOT move or copy the database, and may cause the loss of access to the Altiris database.

SQL Server Account Permissions

The account used in SMP and later to access the database needs permissions to modify the Altiris database, including:

create, alter, and drop tables
create, alter, and drop views
create, alter, and drop stored procedures
execute stored procedures
view, insert and drop records in these tables.

Additionally, this account needs to:

view information from the msdb database
execute some system stored procedures.

Additionally, when the the Symantec Installation Manager (SIM) is installing SMP; If the Altiris database does not already exist, then this account needs to be able to create the Altiris database.

For additional information See:

What SQL rights are needed for the application identity?

Database Purging The SMP, and many of the solutions can store historical information in the database. This data will remain in the database until it is explicitly purged. An Altiris Administrator can enable schedule a task to purge the database of information older than a specified amount of time. See:

How to set the Purge Maintenance for the CMDB

Database Maintenance

database backup and restore
table re-indexing
file defragmentation
update Statistics

Maintaining the database can be accomplished using the built-in capabilities of SQL Server. In some situations some 3rd party applications may be helpful. Please note, that the SMP does not include any utilities to accomplish these maintenance tasks. For additional information see:

Additionally, the Altiris database needs to be appropriately maintained. This includes (but is not limited to)

Recommended Database Settings:

We recommend that the following settings on the Symantec_CMDB database:

set Recovery Model to Simple
enable autogrowth, and set the growth increment to 10%
disable autoshrink
leave Collation at the default, which is Latin1_General_CI_AS.