Configuring SCRM 2006 Data Sources

  • Article

SCRM 2006 can utilize data from MOM 2005 SP1, SMS 2003 SP2, and business context data. However, data from all three sources is not required. When you configure a data source for MOM or SMS, the data source properties are stored in the SystemCenterConfiguration database.

Data Source Directories and Files

The following directories and files are created during data source installation. The default install path is %SYSTEMDRIVE%\Program Files\Microsoft System Center Reporting Manager.

  • Bin

    • The X86 subdirectory contains the .dll and .exe files used by SCRM.

  • Extraction

    • The Configuration subdirectory contains DTS, BAT, and EXE files used to extract data from the SMS database.

    • The DATA subdirectory contains the data extracted from the SMS database.

    • The Status subdirectory contains SCRM_Message.log, which is used to send status information to the SCRM server.

  • Log

    • The Log directory contains log files for DTS jobs, SQL Agent Jobs, and SCRM Client Service.

  • Scripts

    • The Scripts directory contains scripts to install SQL objects.

Data Transfer Security

Data transfer from the data source to the SCRM server is not encrypted by default. You can configure the SQL server to encrypt the MOM data by using the Server Network Utility. You can configure SMS data encryption before it is transferred to the SCRM server by using Internet Protocol Security (IPSec).

For more information about security best practices in SCRM 2006, see SCRM 2006 Security Risks and Best Practices.

MOM 2005

When configured, SCRM 2006 retrieves MOM 2005 data at the schedule you choose. When creating a new MOM data source, no additional configuration is required that affects security changes on the SCRM server.

The account that executes the MOM DTS job must have read access to the MOM Database tables. The account used to install SCRM 2006 is granted necessary privileges during SCRM 2006 installation. However, you can manually add an account to the SC DW DTS group on the MOM data source server.

The user who adds the MOM data source in the SCRM Administrator console must be a member of the SC DW DTS group on the MOM server. Additionally, the hotfix from Microsoft Help and Support article 912787 must be installed on the MOM server before members of the SC DW DTS group can add or remove a MOM data source in the SCRM console. This hotfix gives the SC DW DTS group on the MOM server execute permissions on the DeleteReportSetting and WriteReportSetting stored procedures.

Note

To locate and download the required update, search for article 912787 on the Microsoft Help and Support Web site https://go.microsoft.com/fwlink/?linkid=756).

SMS 2003

First you need to install ReportingDataSource.msi on the SMS database server that will be used as an SCRM data source. Then the following actions occur when you install an SMS data source:

  • SrcSetup_<DataSourceName>.bat is created on the SCRM server when an SMS data source is added in the SCRM Administrator console. This batch file contains the necessary parameters for data source set up. This data is collected when you add the SMS data source in the SCRM Administrator console.

    • You manually copy the BAT file and ReportingDataSource.msi to the SMS database server.

    • The BAT file is run to launch ReportingDataSource.msi with the parameters necessary for the data source.

The following objects are added when you install an SMS data source:

  • Directories

    • The directory structure is created in %SystemDrive%\Program Files\Microsoft System Center Reporting Manager.

    • A subdirectory is created for the data that is extracted from the SMS site database. The default location on the SMS server is <InstallationDirectory>\Extraction\DATA. You can change the default location by modifying the ExtractionDataFilePath entry in the SCRM_MTD_Configuration table in the SMS site database.

    • A directory for each SMS data source is created in the SCRM_Staging shared folder when you add the data source using the Add Data Source Wizard. The account specified when adding the data source is granted permission to the folder.

  • Local Groups

    • SC DW SMS DTS local group is created during Setup.

    • The user account that Setup runs under is added to the group.

  • Registry Keys

    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\SCRM Client

  • SCRM SMS Site Service (SCRMClientSrv)

    • Copies data to the SCRM server.

  • SQL Objects

    • SCRM tables, views, and data extraction components.

    • SyncProcessAgentJob SQL Agent job, which launches BCP.exe tasks to extract data from SMS site database.

Importing Custom SMS Hardware Information

By default, SCRM imports only hardware inventory data that is collected in the default SMS_def.mof file that ships with SMS. Inventory data that is collected by additions to SMS_def.mof must be manually extracted from the SMS database and imported into SCRM. To get the data imported into the SystemCenterPresentation database, you need to do the following:

  • Manually extend the CustomTable tables in SCRM.

  • Design a process (such as SQL DTS or BCP) that exports the data from SMS and loads it into the SystemCenterStaging database.

  • Run a full or delta sync to move the newly added data to the SystemCenterPresentation database so that it can be accessed by report users.

For information about importing custom SMS Hardware information, see Importing Custom SMS Hardware Information to SCRM 2006.

Business Context Data

If you have Microsoft Excel installed, you can use the Excel spreadsheet included in SCRM 2006 to add additional information about users, computers, SMS sites, files, and installed software to the SCRM 2006 SystemCenterPresentation database. This data is used in many reports that are included with SCRM 2006. You can import data from Active Directory or other external sources into this Excel spreadsheet.

The data in the spreadsheet is then loaded into USR_<dimension name>_DW tables in the SystemCenterStaging database during the ETL process of an SMS full or delta synchronization job. From there, the data is loaded into the appropriate dimension tables in the SystemCenterPresentation database.

As an alternative to using the spreadsheet template to load business context data, you can develop custom solutions to load the data directly into the staging tables. Sample scripts are provided with SCRM 2006 that can help you load user and computer information from Active Directory directly into the staging tables, bypassing the spreadsheet.

For more information about adding business context data, see Loading Business Context Data into SCRM 2006.