Improving Performance of Microsoft CRM 3.0 by Using a Dedicated Report Server

(1)

Downloaded from http://www.sonomapartners.com/ - 1 -

Improving Performance of Microsoft

CRM 3.0 by Using a Dedicated Report

Server

January 2006

(2)

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted in examples herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo, person, place, or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Microsoft, MS-DOS, Windows, Windows Server, Windows Vista, Microsoft Dynamics, the Microsoft Dynamics logo, and Active Directory are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

All other trademarks are property of their respective owners.

(3)

Overview

To improve performance in high-volume Microsoft® Dynamics™ CRM 3.0 systems, you can run reports on a dedicated report server. This frees up the resources of servers that are running Microsoft SQL Server™ and Microsoft CRM Server, and better services the

transaction processing requests generated by users adding, viewing, and updating Microsoft CRM records. In this configuration, the online analytical processing requests required for reporting are serviced by the dedicated report server.

In order to use a dedicated report server, you use SQL replication to replicate the data from the SQL Server that is running on the same server as Microsoft CRM to the dedicated report server. The reports are generated from the replicated database.

This article describes the steps that you must follow to:

● Decide on a replication strategy

● Set up the dedicated report server

● Set up replication for the Microsoft CRM databases on the server that is running Microsoft SQL Server and Microsoft CRM

● Configure Microsoft CRM to use the dedicated report server

● Test the configuration

Decide on Replication Strategy

Replication can be set up between two servers running SQL Server 2000, SQL Server 2005, or one of each. If you have servers that are running different versions, replication is limited to the functionality available in SQL Server 2000.

For information about SQL replication options, see:

● SQL Server 2005 – Replication Overview (http://go.microsoft.com/fwlink/?LinkId=57770)

● SQL Server 2000 Replication Overview (http://go.microsoft.com/fwlink/?LinkId=57771)

Consider how the type of replication you choose handles schema changes. For example, transactional replication automatically handles adding, removing, or modifying fields, but if you add or remove entities, you must set up replication again.

Set Up a Dedicated Report Server

There are three basic steps required to set up a dedicated report server:

1. Install Microsoft SQL Server and Microsoft SQL Server Reporting Services on different computers. You can use Microsoft SQL Server 2000 or 2005. Follow the instructions in the Microsoft SQL Server documentation.

2. Because Reporting Services is on a separate server than Microsoft SQL Server and Microsoft CRM Server, additional setup is required in order to use double-hop Kerberos authentication so that users are able to list and view reports. This means that trust for delegation must be set up on both the Microsoft CRM server and the Reporting Services server. For information about how to set this up, see Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server (http://go.microsoft.com/fwlink/?LinkId=57772).

3. Publish the Microsoft CRM reports to the dedicated server. This requires three steps, detailed in the following procedures:

(4)

● Downloading all the reports from Microsoft CRM

● Publishing them on the new dedicated report server

● Verifying that the SQL Server Reporting Services role assignments for the report folder are set up correctly

To download all the reports from the Microsoft CRM server and publish them on the new dedicated report server, the user performing this action must have the SQL Server Reporting Services Report Manager System Administrator and the Content Manager security role on the root folder of Report Manager. By default, members of the local Administrators group on the report server have the System Administrator security role.

► Download all reports

When you run the DownloadReports.exe command, a file will be created for each report, together with a configuration file named publish.config that includes the metadata for the report. If you specify a folder, all report files and the publish.config file will be downloaded to that folder. Use a separate folder because it makes it easier to publish these reports. If report files or a publish.config file exists in the folder, the files will be overwritten.

1. On the Microsoft CRM server, in a Command Prompt window, in the C:\Program Files\Microsoft CRM\Reports folder, run:

DownloadReports.exe organization_name <destination_folder>

Parameter Description

Organization_name The organization name used when Microsoft CRM was set up. For example, “Adventure Works Cycle”.

Destination_folder The folder in which to store the downloaded reports. This folder will include a file for each report, plus a publish.config file that can be used when publishing the same group of reports to a new server. If this parameter is not specified, the current folder is used.

► Publish a group of reports to the new dedicated report server

1. A registry key value must be changed so that Microsoft CRM uses the new default report server.

a. On the Microsoft CRM server, click Start, click Run, and then type regedit.

b. Expand HKEY_LOCAL_MACHINE, expand Software, expand Microsoft, and expand MSCRM.

c. Double-click SQLRSServerURL.

d. In the Value data box, enter the URL for the dedicated report server. For example, if the dedicated report server is named crmreports, and you used the default value for Report Server when you installed Reporting Services, enter http://crmreports/ReportServer.

e. Click OK and close Registry Editor.

2. In a Command Prompt window, from the folder that has the downloaded reports, run the PublishReports.exe command. You will have to specify the path to the

command relative to your current folder. For example, if your downloaded reports are in a folder named C:\downloaded_reports, in a Command Prompt window, you would change directories to the downloaded_reports folder, and then run the

PublishReports.exe command.

The syntax of PublishReports.exe is as follows:

Path_to_PublishReports.exe\PublishReports.exe Organization_Name

(5)

Parameter Description

Organization_Name The organization name used when Microsoft CRM was set up. For example, “Adventure Works”.

ReportingGroup The name and GUID of the ReportingGroup Active Directory® group, in the format:

<domain name>\ReportingGroup {GUID for this group}

For example:

CRMDOM\ReportingGroup {d4b85e97-1a6f-4f8b-8fbe-e8c3b2df5bd6}

Publisher The Service account name for the Microsoft CRM Web application, in the format:

If the Service account name is Network Service or Local System, in this parameter you use the computer name followed by a dollar sign ($). For example, “CRMDOM\CRM01$” or “CRMDOM\user_name”.

To determine the Service account name, see the “Identify the security account names for Microsoft CRM” procedure in the article Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server

(http://go.microsoft.com/fwlink/?LinkId=57772) For example:

cd c:\downloaded_reports

c:\Program Files\Microsoft CRM\Reports\PublishReports.exe

”Adventure Works” “CRMDOM\ReportingGroup {d4b85e97-1a6f-4f8b-8fbe- e8c3b2df5bd6}” “CRMDOM\CRM01$”

► Verify that the role assignments for the Microsoft CRM reports are set up correctly 1. In your browser, locate the page

http://<dedicated_report_server>/<Report_Manager>. (For example, http://dedicatedreportserver/reports.)

2. On the toolbar, click Show Details.

3. Click the Edit button next to <organization>_MSCRM line.

4. Click Security.

5. In the list, verify that the account name that Microsoft CRM runs under is associated with the Publisher for Microsoft CRM role.

The Group or user column should include the account. For example, if Microsoft CRM runs under Network Service or Local System, if the domain is CRMDOM, and the computer name of the Microsoft CRM server is crm01, you should see

CRMDOM\crm01$. If Microsoft CRM runs under a user account CRMUser, you should see CRMDOM\CRMUser. In the Role column, you should see Publisher for Microsoft CRM.

6. In the list, verify that the ReportingGroup is associated with the Browser for Microsoft CRM role.

Set Up Replication for Microsoft CRM

Databases

When you configure replication, use the Microsoft CRM server as the Publisher and Distributor, the dedicated report server as the Subscriber, and set up a push subscription.

(6)

Downloaded from http://www.sonomapartners.com/ - 6 - Microsoft CRM uses two databases, <organization>_MSCRM, and

<organization>_METABASE. Both must be replicated to the report server. All tables, views, and user-defined functions in each database should be replicated. You do not have to replicate the stored procedures.

To set up replication, on the server that is running SQL Server and Microsoft CRM, the service start up account for SQL Server Agent must be LocalSystem.

If you have a large database (several gigabytes), creating the initial snapshot may take several hours.

When you are setting up replication, exclude the following views:

● Activity

● CompetitorContact

● CompetitorContactAddress

● CompetitorRevenueReport

● MailingList

● TrackedItemHistory

To configure replication, you use the following Microsoft SQL Server wizards on the server that is running SQL Server and Microsoft CRM. For information about replication, and procedures for starting and using these wizards, see SQL Server Books Online.

Purpose Name on SQL

Server 2000

Name on SQL Server 2005 Use this wizard to specify using the Microsoft CRM

server as a Distributor and Publisher

Configure Publishing and Distribution Wizard

Configure Distribution Wizard Use this wizard to select the replication type, specify the

databases to replicate, and specify push subscriptions.

Important: This wizard must be run two times: once for the <organization>_MSCRM database and once for the

<organization>_METABASE database.

Create Publication Wizard

New Publication Wizard

Use this wizard create a push subscription to a

publication that will be distributed to the dedicated report server.

Important: This wizard must be run two times: once for the <organization>_MSCRM database and once for the

<organization>_METABASE database.

Push Subscription

Wizard New Subscription

Wizard

Configure the Dedicated Report Server to

Retrieve Data from a Replicated Database

A dedicated report server must be configured to retrieve data from a replicated database.

► Configure dedicate report server

1. Open the SQL Server Reporting Services Report Manager on the dedicated report server.

2. Open the Microsoft CRM folder.

3. Click Show Detail, and then click Properties for MSCRM datasource.

4. Change the connection string from datasource=main CRM SQL to datasource=replicated SQL server.

5. Close the Report Manager.

(7)

Test the Configuration

To test that the dedicated report server is working correctly:

1. Log on as a regular user who does not have Report Manager privileges. From the Microsoft CRM server, open Microsoft CRM and verify that you can view the list of reports in the Reports area, and that you can run a report.

Problems at this level might be caused by mistakes in configuring the registry key that points to the report server, by problems setting up trust for delegation on the Microsoft CRM server or the Reporting Services server, or by specifying an incorrect

ReportingGroup parameter to PublishReports.exe.

2. Log on as a user who has the Report Manager privilege, such as user with the System Administrator or System Customizer security role. From the Microsoft CRM server, open Microsoft CRM and verify that in the Reports area, on the More Actions menu, you see Edit Report. Click Edit Report, change the description, and click Save and Close.

Problems at this step may be caused by selecting an incorrect ReportingGroup or Publisher options in PublishReports.exe.

3. From a client computer, open Microsoft CRM and verify that you can view the list of reports in the Reports area, and that you can run a report.

If you were successful with step 1, and not successful with this step, the problem is likely because of an error in setting up trust for delegation on the Microsoft CRM server or the Reporting Services server.

4. Test your replication setup:

a. Run a report.

b. Change some data that appears in the report using Microsoft CRM.

c. Allow some time for the data to be replicated.

d. Run the report again. Verify that the changed data appears in the report.

If you have problems at this step, check your replication settings to make sure data is being replicated correctly.