How to migrate Resource Analyzer after the server name has been changed


Technique

Product: Reporting Server
Release: 8205
Primary OS: Linux
Solution Date:

Problem

How to migrate Resource Analyzer after the server name has been changed. 



The original server name hostname was 8121 and it was later reconfigured as hostname 8121b. A migration of the Repository is needed and only the 8121b is listed as an option for migration. How do you obtain the 8121 data as well?

Solution

The server name is held in the following two tables:



    
	
  • SMSERVERS_nnnn 
    • 
	
  • SMSESSIONS_nnnn.
    • 




Since there are two server names, the data is stored as if they are two separate entities. If you are using SQL against these tables, it is possible to rename the values so they are uniform. Here are the steps:



    
	
  1. Backup all repository tables.
    2. 
	
  2. Verify archive jobs are not running or will run during the following steps. 
    3. 
	
  3. Run the Repository Statistics report to retrieve counts for the two server entries.
    4. 
	
  4. Change OLD_SERVER_NAME:8121 to NEW_SERVER_NAME:8121b. This can be done by running the following code from any tool that will allow you to run it against your Repository tables:
    5. 




UPDATE SMSESSIONS_nnnn SET  SMSERVER=’NEW_SERVER_NAME:8121b’ WHERE SMSERVER=’OLD_SERVER_NAME:8121’



 This should attach all the OLD session records to the NEW server record.



    
	
  1. Run the Repository Statistics report again and verify the new counts for NEW_SERVER_NAME:8121b. They should match the combined counts for both server records from the first run.
    2. 
	
  2. If all looks good, run the following SQL code to clean up any old server references:
    3. 




DELETE SMSERVERS_nnnn WHERE SMSRVNAME=’OLD_SERVER_NAME:8121’