People have been asking for a long time how they can move the Service Manager and data warehouse databases. It's actually a non-trivial process so we needed to do some testing and get an official procedure documneted. We have finally released that procedure in the SCSM 2012 RTM documentation - at least for the ServiceManager database. It's a little tricky to find because it is buried in the upgrade guide and mixed in with the steps required to create a test environment from a production environment.
These are the steps required to move the Service Manager database:
1) Stop the System Center services on all the management servers
2) Backup the ServiceManager database
3) Restore the Service Manager database on the target SQL Server
4) Configure the Service Manage database (except don't do step #17 at the end)
5) Configure the registry on all the management servers to point to the new SQL Server:
Open the registry editor (Start -> run, type regedit)
Navigate to HKEY_LOCAL_MACHINE\Software\Microsoft\System Center\2010\Common\Database
There are two keys that can be configured here - one for the server name (DatabaseServerName) and one for the database name (DatabaseName)
Set those values to the new SQL Server name and database name (if different than originally).
6) Start the System Center services on all the management servers
7) Install another Service Manager (with a different name) database on the same SQL Server by installing another Service Manager management server and choosing to create a new database. This will populate the master database with error message text so that if there is an error in the future the error message can display the specific problem text and not just a generic message. Once the DB is installed you can drop it from the SQL Server and uninstall the additional, temporary management server.
If you have recently installed SCSM it might be easier to just unregister the data warehouse, install a new datawarehouse, and register it again. As long as the data hasn't been groomed from the SCSM database yet, there will be no data loss in the DW database because it will all be sync'd back over again. By default the grooming interval for work items is 90 days from the last time a work item was modified. So - if you installed SCSM anytime within the last 90 days and you haven't changed the grooming interval you can just unregister the DW, install a new DW, and register it with SCSM. That's much easier than the DW DB move documentation we are going to put out.
A special thanks to Manoj Parvathaneni (Senior Support Escalation Engineer), Ranganathan Srikanth (Senior Program Manager), and John Downing (Senior Technical Writer) for going to extra effort to get this documented.
Update: the DW move documentation is now here:
I have same problem: "The encryption keys in the registry were either not valid or not present. The System Center Data Access service will not start."
I am planning to move of standalone Service Manager DB to a SQL Cluster . Off course , I will do some testing . But wanted to know if any one has done this movement following these steps or any precaution or tips.
Hi, I have same problem: We have a Production SCSM 4 server Installation on one AD and Dev SCSM 4 server Installation on another AD. I was asked to try these instructions to overwrite Dev DBs with Prod copies. The Computer names are different between the two domains. Now the Data Acess Service fails to start on both the Server Manager and DW apps servers with "The encryption keys in the registry were either not valid or not present. The System Center Data Access service will not start." Restoration of the keys do nothing to resolve it.
I'm having the same problem now as James Bird. Prior to trying these steps to refresh our test environment with production data I was having a problem with the DW server Data Access Service failing to start. I believe that was an effect of us rebuilding our data warehouse server that housed the SCSM DW databases.We attempted to refresh the test systems with the production data in hopes to address this, but after performing the steps above to restore the ServiceManager database from production into test, the Data Access Service on the SM management server fails to start now.Furthermore, one of our DBAs gave me a script to run that will scour all tables in the ServiceManager database looking for a keyword, and I found a handful of other tables in the database that still had pointers to the production server(s). I attempted to go through and change all those to point to the correct test servers but it still fails to start.Any other suggestions to try before opening up a case with Microsoft? Do we need to do anything with encryption keys?
We have scrapped this copy. I do not think that encryption keys created in one domain\computer are usable with a database from another domain\computer. Unfortuneately uninstalling does happen cleanly and reinstalling the data warehouse server fails without cleaning up the registry from the previous install. A new issue now is the data warehouse or reportserver does not appear in the console. I am really not impressed with this product at all.
The author of these instructions should have included the environmental conditions under which this does and does not work.
To have the data warehouse appear in the console it is neccessary to register in the console...http://scug.be/mike/2009/12/11/system-center-service-manager-2010-register-the-datawarehouse-with-your-management-server/
Since following this guide to move the database, my reports and olap cubes only show data up until the move, however all the jobs are running fine without issue, any idea?