Install FIM Data Warehouse Support Scripts when the Data Warehouse SQL database resides on a Remote SQL Server.

Issue:

Currently the FIM Reporting documentation for the installation of Service Manager Data Warehouse server with its SQL database on a remote SQL server does not describe how to install the FIM post installation scripts. The installation of such configuration requires additional steps.

Scope if this document:

The scope of this document is the step-by-step workaround to install the FIM Reporting Post installation scripts when the Data warehouse database is on a remote SQL server. Keep in mind that another "most do" procedure required by the Service Manager Data Warehouse installation to use SSRS is needed. This procedure is outside the scope of the document. See details below if you need help with SSRS.

---

Manual Steps to Configure the Remote SQL Server Reporting Services(SSRS)

Describes how to manually configure SQL Server Reporting Services (SSRS) in situations where SSRS is not on the same server as the data warehouse management server.

http://technet.microsoft.com/en-us/library/ff460955.aspx

---

Overview:

The FIM Reporting post installation scripts required to execute .\FIMPostInstallScriptsForDataWarehouse.ps1 scripts in the Data Warehouse server. However, this script requires access to the "SQLCmd" tools and the "SMCmdletSnapIn" snapins. These two components are both present when the SLQ server resides in the Data Warehouse server. In this scenario that condition is not true. In this procedure, we will run the FIM post installation powershell script in the SQL server. We will create a PSSessionConfiguration in the Data Warehouse server that will get called from the remote SQL server to execute the "SMCmdletSnapIn".

Steps:

1. You most use an account that is a member of the DW-ManagementGroup group.
2. The group must have sysadmin permissions to the Service Manager Data Warehouse SQL Server instance
3. DBOwner permissions required to perform these tasks. This permissions are granted by the setup process when installing the Data Warehouse server.
4. Go to the Service Manager Data Warehouse Server to prepare the server to run the FIM post installation scripts remotely.
   1. Log on to the computer that hosts Service Manager Data Warehouse Management Server using an account that is a member of the DW-ManagementGroup group.
   2. Launch Windows PowerShell in elevated mode (Run As Administrator).
   3. From the Windows PowerShell console execute the following commands to perform each action below:
      1. Create a Scripts directory at the root of the server:
         
         md Scripts
         
      2. Create a directory Data Warehouse Support Scripts directory under the Scripts directory or copy the folder from the FIM installation bits:
         
         md Scripts\Data Warehouse Support Scripts"
         
      3. Change directories to the %\Scripts\Data Warehouse Support Scripts directory:
         
         cd "Scripts\Data Warehouse Support Scripts"
      4. Create a script to load the Service Manager PowerShell Snap-ins.
         1. Type:
            
            "Add-PSSnapIn SMCmdletSnapIn" > Add-SvcMgrSnapIns.ps1
            
      5. Enable PowerShell Remoting:
         
         Enable-PSRemoting –Confirm:$false
         
      6. Register remote session configuration for running the Service Manager PowerShell snap-ins from a remote computer (this is a single command, wrapped for readability):
         
         Register-PSSessionConfiguration -Name Service.Manager -StartupScript 'Scripts\Data Warehouse Support Scripts\Add-SvcMgrSnapIns.ps1' -Confirm:$False
         
5. Go to the SQL Server hosting the data warehouse databases to prepare the SQL Server to install the FIM Data Warehouse Support Scripts:
   1. Log on to the computer using an account that is a member of the DW-ManagementGroup group.
   2. Launch Windows Explorer and create a new directory called Scripts at the root of the server.
   3. Copy the contents of the Data Warehouse Support Scripts directory from the DW server to the \Scripts directory in the SQL server. (The resulting directory in the SQL server should be \Scripts\Data Warehouse Support Scripts.)
   4. Launch Windows PowerShell in elevated mode (Run As Administrator).
   5. From the Windows PowerShell console:
      1. Navigate to the Data Warehouse Support Scripts directory created in step (3) above.
         
         cd 'Scripts\Data Warehouse Support Scripts'
         
   6. Type the following command to set the PowerShell Execution Policy to allow local unsigned scripts to be run.
      
      Set-ExecutionPolicy -ExecutionPolicy RemoteSigned
      
      Type Y when prompted to change the Execution Policy.
   7. Type the following to establish a remote PowerShell Session with the Service Manager Data Warehouse server:
      
      $ServiceManager=New-PSSession -Computer enter_here_your_dw_host_netbios_name -Name Service.Manager -ConfigurationName Service.Manager
      
   8. Type the following to import the remote PowerShell Session Service Manager cmdlets to the local PowerShell session:
      
      $SvcMgrCmd=Import-PSSession $ServiceManager -CommandName *-SC* -AllowClobber
      
   9. Type the following to run the FIMPostInstallScriptsForDataWarehouse.ps1 script.
      
      .\FIMPostInstallScriptsForDataWarehouse.ps1
      
      1. When prompted for the DataWarehouseServerInstance instance, type the Data Warehouse server name, and then press Enter. Example: your_dw_host_netbios_name
         
         
      2. When prompted for the DataWarehouseDatabaseServerInstance instance, type the data warehouse database SQL Instance, and then press Enter. Example: your_sql_host_netbios_name\sql_instance. Do not include the instance name if using the default SQL instance.
      3. When prompted for the FIMServiceAccountName instance, type:
         
         domain\FIM service account, and then press Enter.
      4. Type Y and then Enter when prompted to continue.
6. Done!

Close Windows PowerShell.