Move the site database in SCCM 2007 R2
Usually the process of moving the site database is well documented in the link http://technet.microsoft.com/en-us/library/bb680707.aspx
What it might be missing is in case you have integrated with SQL reporting services how do we make sure that the configuration manager also uses same servers reporting services.
In such a case you need to make sure that the Sql reporting services also moved you can use the following links for the same
Once you have moved and configured the same make sure that the reporting services point installation properties are set to point to the new server
· Go to Reporting Services using ConfigMgr Console and click in properties as shown below:
· Click on “Data Source Settings” and make sure you have specified Server name and database name of your ConfigMgr server as shown below:
· Under “Database Source Authentication”, make sure “Windows integrated security” is checked. Once completed then refresh the ConfigMgr console and try running the reports again.
Few other things you need to make sure before you move the database.
1) Once you take the backup of the database to be resorted in the new server make sure that the SMS_EXECUTIVE and SMS_COMPONENT_ MANAGER is stopped. The same will automatically get started once the site reset has been performed.
2) Make sure that the site reset is completed successfully. You can check the site comp.log
3) Ensure that the database access permissions are the same on the new site database as they are on the original site database. You can add the computer account (or service account) of the SCCM provider server in the Administrators group which will make sure that the necessary permission are there.
4) Make sure that the SSRS is move successfully to the other machines
Hope that this information is helpful