SCSM: Install Fresh Data Warehouse on Post UR Updated Environment
These instructions were made using SCSM 2012 R2 with UR9 and then re-installing a Data Warehouse Management Server again since the previous was corrupted beyond repair and no recent backups were good.
Note: If you have custom reports on your DW Report Server that weren't made from SCSM or a third party Management Pack, make backups of the files by using IE in compatibility mode on the Report Server URL where you can download reports
On Management Server à SCSM Console à Administration à Unregister DW if one exists.
If you have an existing SCSM Data Warehouse:
Go to DW Management Server à Uninstall SCSM 2012 if it is present
1) Ensure the following registry keys are deleted on the DWMS
a. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center
b. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager
2) Using SSMS, connect to the DW SQL Server via DataBase Engine
a. Expand Databases and remove the DW Databases:
i. CMDWDataMart
ii. DWDataMart
iii. DWRepository
iv. DWStagingAndConfig
v. OMDWDataMart
vi. ReportServer
vii. ReportServerTempDB
3) Using SSMS, connect to the DW SQL Server via Analysis Services
a. Expand Databases and remove DWASDataBase
4) Restart the DW Server and the DW SQL Server
5) Remote to the DW Reports Server (which is most likely the DW SQL Server)
a. Run program: "Reporting Services Configuration Manager"
b. Once it loads, click on "Database" section
c. Click Change Database
d. Ensure "Create a new report server database" is selected
e. Ensure Server Name is where you want to store the database (DW SQL Server)
f. Test Connection and then click next
g. Ensure DataBase Name is "ReportServer"
h. For credentials, use the Service Account for SQL Reporting (if you look under the "Service Account" section you should see which account in case you're unsure)
i. Verify, then click Next, then close when completed
6) Now click the "Report Manager URL" section
a. Click on the URL
b. Ensure the SQL Server Reporting Services Home loads correctly
c. Click Folder Settings
d. Add your SCSM Administrators group with All Access in the form DOMAIN\GROUP
e. Add your SCSM Report Users group with Browse Access in the form DOMAIN\GROUP
f. Ensure the folder "Reporting Services" that was installed has permissions of the service account with Full Control. Example - C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services
g. Optionally, use the "Encryption Keys" section to make a backup of the report server credentials.
Now that we're ready to install a New Data Warehouse using the Setup.exe for SCSM 2012 R2 on the DW Server:
1) Select under Install - Service Manager data warehouse management server
2) Enter your license, accept the agreement and click next a few times until you get to Configure DW Databases
3) For each database you'll need to install the new one by clicking on each one
4) Enter the DW SQL Server as your Database Server, choose instance to default, and ensure Create a new database, and ensure data and log folders are selected properly
5) Same goes for the additional DWDataMarts, set them up the same way
6) Set your Management Group Name for your DW and set your SCSM Administrator Group
7) On the Reporting Server, type the name of the DW Reports Server (which is most likely the DW SQL Server), then set Report server instance: Default, checkmark I have taken the steps and click next
8) Configure the Server Manager Services Domain Account
9) Configure the Reporting Account Domain Account
10) Configure Analysis Services for OLAP cubes:
a. "Create a new database" is selected
b. Database Server will be your DW SQL Server
c. SQL Server instance is set to default
d. Database name == DWASDataBase
11) Configure the Server Manager Services Analysis Services Account
12) For Help improve ….. Select your preference and click Next
13) Click Install
14) Once finished, make a backup of the newly generated Encryption Key
IMPORTANT! Make backups of your DW databases (DataBase Engine and Analysis Services) so you don't have to repeat this in case you fail to follow the next steps successfully
After you've made a backup, on the DW Server, open powershell and ensure the following command is executed successfully:
Get-SCDWJob | foreach-object { Disable-SCDWJobSchedule -JobName $_.Name }
Then on Management Server à SCSM Console à Administration à Register DW Server
Start MPSyncJob if it is not already running so it will sync the management packs.
Now in the Console go to Data Warehouse - Security - User Roles and add the group for Report Users
Confirm with Analysis Services Connection on DW SQL Server that
About an hour later when it creates other jobs like EXTRACT, run the command to disable all the job schedules again:
Get-SCDWJob | foreach-object { Disable-SCDWJobSchedule -JobName $_.Name }
Once the MPSyncJob finishes and the all jobs are set to not started and the first dw synced mps are done, then apply the UR. This can take a few hours to a day depending on the amount of Management Packs. For me I waited until the next day. Run the disable job schedules command again for any other jobs created. Now on the DW Server apply UR9. After UR9 is applied, run MPSyncJob one more time as this will sync a newer MP of "Microsoft.SystemCenter.Warehouse.System" with a higher batch Id. Once MPSyncJob has finished, proceed.
Now in the SCSM Console - Administration - Unregister the DW Server. Wait a few minutes on the DW Server then enable the the job schedules with PowerShell commands:
Get-SCDWJob | ForEach-Object { Enable-SCDWJobSchedule -JobName $_.Name }
Get-SCDWJob | ForEach-Object { Stop-SCDWJob -JobName $_.Name }
Get-SCDWJobModule
After running the Get-SCDWJobModule command you'll notice there will probably be some job modules running, if so, using SSMS, Database Engine Connect to DW SQL and run the following query on DWStagingAndConfig database after replacing 'Extract_DW_<MANAGEMENT GROUP>' with the SCSM Management Group Name of your DW Server in order to 'reset' them so the jobs will be cleaned correctly over the next few days:
UPDATE infra.workitem SET StatusId = 3,TakenTime = NULL,UpdateTime = NULL,CustomInfo = NULL
WHERE WorkItemId IN (select WorkItemId from infra.workitem(nolock)
WHERE BatchId IN (select batchid from infra.batch
WHERE processId IN (select processId from infra.process
WHERE processname IN ('Extract_DW_<MANAGEMENT GROUP>','Transform.Common','Load.CMDWDataMart','Load.OMDWDataMart','Load.Common','DWMaintenance','MPSyncJob')) AND StatusId != 6))
** **
Restart the DW Server
** **
The reason you should wait for at least 2 to 3 days is so it will release old MPSyncJob references and unmake a few jobs.
After waiting at least 2 - 3 days, run the command:
Get-SCDWJob - Ensure no jobs are running
Get-SCDWJobModule - Ensure no modules are running
If any are running, you will have to repeat stopping the jobs and executing the above query and waiting another day.
Using Get-SCDWJob make note of the jobs listed, there should be no 'Process.*' cube jobs, once you confirm this, using SSMS, Analysis Services connect to the DWASDatabase and then under Cubes, you must delete each Cube so they will be created correctly with their corresponding job also being created.
On Management Server à SCSM Console à Administration à Register DW Server
Now you should be MPSyncJob resyncing all the management packs and re-creating the missing jobs… Congrats, you've got a Data Warehouse with UR9 registered and running again. Let it create the cube jobs.
After the cube jobs are created, open SSMS, using Analysis Services connect to the DWASDatabase, under roles for SCDW_Report_Readers you must ensure the sections DataSources make Read Access and in Cubes ensure Read Access. Alternatively, you could checkmark the "Full control" under general, but at that point, you're better off including them in the admin group.
Important: To Fix The Side-Effect that Jobs won't run their modules when the job schedule is disabled, you need to:
- Disable the Jobs using PowerShell: Get-SCDWJob | ForEach-Object { Disable-SCDWJob -JobName $_.Name }
- Disable the Job Schedules using PowerShell: Get-SCDWJob | ForEach-Object { Disable-SCDWJobSchedule -JobName $_.Name }
- Using the SCSM Console, Unregister the DW Server, close the SCSM Console
- Enable the Jobs using PowerShell: Get-SCDWJob | ForEach-Object { Enable-SCDWJob -JobName $_.Name }
- Open the SCSM Console, using the SCSM Console, Register the DW Server
- If any jobs are running, wait for them to finish
- Enable the Job Schedules using PowerShell: Get-SCDWJob | ForEach-Object { Enable-SCDWJobSchedule -JobName $_.Name }
This is also the time to make any additional modifications to your Data Warehouse such as ensuring the Retention Settings for the DWRepository matches the DWDataMart time if you don't want relationships being marked as deleted prematurely. The DWRepository does affect the retention of the DWDataMart Relationships being marked as deleted. I've found altering both RetentionTimes (DWRepository and DWDataMart) to the same amount (and ensuring it's equal to or greater than any Ticket Retention Time in SCSM Settings) will prevent DWDataMart relationships being marked as deleted when they shouldn't be in the first place. Obviously there is more data being stored in the DWRepository, but accuracy over efficiency in this case. In my case, I never wanted data to be removed from the archive, so I purposefully set the time in minutes to about ~4000 years. This was accomplished by altering the [etl].[Configuration] table which contains a variable called "RetentionPeriodInMinutes.Default" for the following:
DWRepository == 129600 (90 days by default) => changed to 2102400000 (~4000 years by default)
DWDataMart == 1576800 (~3 years by default) => changed to 2102400000 (~4000 years by default)