Building Microsoft System Center Cloud – SCSM 2012 R2 – Service Manager 2012 R2 Data Warehouse Management Server installation

Management Server is installed so we can continue with installation of the Service Manager Data Warehouse Management Server.

Modifications on the SQL Server where will be stored Data Warehouse Management Server DB

Prepare the Microsoft SQL Server Reporting Services (SSRS) server

We will access the Microsoft SQL Server Reporting Services (SSRS) remotely because it is installed on a different server than the SCSM Data Warehouse Management server. Therefore we have to do some preparation that ensure that the remote server will not refuse our requests. This preparation do not have to be done in the case you installed Data Warehouse on the same server where is SQL Server with Reporting feature.

DLLs

On the SQL Server navigate to the Reporting Services\ReportServer\bin.
- The default path is: C:\Program Files\Microsoft SQL Server\MSRS11.NameOfTheInstance\Reporting Services\ReportServer\bin
Paste here a file Microsoft.EnterpriseManagement.Reporting.Code.dll that is stored on the in the Prerequisites directory in the root of the Service Manager installation media.

rssrvpolicy.config

Open file rssrvpolicy.config in XML or plain text editor (Notepad) and modify it.
- Default path is: C:\Program Files\Microsoft SQL Server\MSRS11.SCSM0\Reporting Services\ReportServer\rssrvpolicy.config
You can see a lot of <CodeGroup> segments:

<CodeGroup>
...
</CodeGroup>
<CodeGroup>
...
</CodeGroup>
<CodeGroup>
...
</CodeGroup>

You need to add this exact another segment (original code is published on Manual Steps to Configure the Remote SQL Server Reporting Services on TechNet):

<CodeGroup
   class="UnionCodeGroup"
   version="1"
   PermissionSetName="FullTrust"
   Name="Microsoft System Center Service Manager Reporting Code Assembly"
   Description="Grants the SCSM Reporting Code assembly full trust permission.">
   <IMembershipCondition
      class="StrongNameMembershipCondition"
      version="1"
      PublicKeyBlob="0024000004800000940000000602000000240000525341310004000001000100B5FC90E7027F67871E773A8FDE8938C81DD402BA65B9201D60593E96C492651E889CC13F1415EBB53FAC1131AE0BD333C5EE6021672D9718EA31A8AEBD0DA0072F25D87DBA6FC90FFD598ED4DA35E44C398C454307E8E33B8426143DAEC9F596836F97C8F74750E5975C64E2189F45DEF46B2A2B1247ADC3652BF5C308055DA9"
/>
</CodeGroup>

Results:

rsreportserver.config

Open file rsreportserver.config in XML or plain text editor (Notepad) and modify it.
- Default path is: C:\Program Files\Microsoft SQL Server\MSRS11.SCSM0\Reporting Services\ReportServer\rsreportserver.config
Find

<Data> ... </Data>

and add following:

<Extension Name="SCDWMultiMartDataProcessor" Type="Microsoft.EnterpriseManagement.Reporting.MultiMartConnection, Microsoft.EnterpriseManagement.Reporting.Code" />

Results:

Installation on the VM for Data Warehouse Management Server

Install prerequisites

Install .NET Framework 3.5
Install Microsoft SQL Server 2012 Analysis Management Objects
Install Microsoft SQL Server 2012 Native Client
- Direct links to download are listed in the previou article.

Install Service Manager Data Warehouse Management Server

Login as member of SCSM Administrators.
Install – Service Manager Data Warehouse Management Server
SQL Server Cluster with AlwaysOn Failover Cluster Instances
- Staging and Configuration – Choose SQL Cluster.
- Data Mart – Choose SQL Cluster.
Standalone highly-available VM with standalone SQL Server
- Staging and Configuration – Choose SQL DB server
  - - contscsmdb0.ad.contoso.com\SCSM0
- Data Mart – Choose SQL DB server
  - - contscsmdb0.ad.contoso.com\SCSM0
Do not worry about the collation SQL_Latin1_General_CP1_CI_AS warning. That is normal.
And sometime the installer failed. That is also normal.
Do not install another DBs.
It is possible to scale out the Service Manager across multiple SQL DB servers for the best performance but in our case we will use a single DB server.
Provide a Data Warehouse Management Group name. It is a best practice to use same name as we used for SCSM Management Group with the DW_ prefix.
Choose our SCSM Administrators group.
- - DW_contososervice
  - scsmadmins0
SQL Server Cluster with AlwaysOn Failover Cluster Instances
- Reporting Services on SQL Failover Cluster have to be manually pre-configured.
- Reporting server – Choose node of the SQL cluster where the Reporting Services are installed.
  - - Database server: contsqlfcic0n0.ad.contoso.com
Standalone highly-available VM with standalone SQL Server
- Reporting server – Choose SQL DB server.
- Sometime you have to choose it several times before validation pass. That is normal.
  - - contscsmdb0.ad.contoso.com\SCSM0
Choose SCSM services account.
- - serscsms0
Choose SCSM reporting and analysis account.
- - serscsmr0
Reporting server – Choose SQL DB server.
- - contscsmdb0.ad.contoso.com\SCSM0
Choose SCSM reporting and analysis account.
- - serscsmr0
Install

Backup the encryption key for the new management group

Same process as we did when we install SCSM Management Server in the previous article.

Register the Data Warehouse and verify the installation

Sign in SCSM trough console on our management server as member of the SCSM Administrators.
Go to Administration – Register the Service Manager Data Warehouse.
Input Data Warehouse server.
- - contscsmdw0
Use default Run As account.
Input credentials for the service account.
Create
Wait several hours and regularly checks Datawarehouse Jobs.
At the end you will see following 5 jobs:
1. Extract_ServiceManagerManagementGroupName
2. Extract_DW_DataWarehouseManagementGroupName
3. Load.Common
4. Transform.Common
5. MPSyncJob
Result

Technology Stronghold