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:
http://blogs.technet.com/b/servicemanager/archive/2012/06/28/moving-the-scsm-data-warehouse-dw-databases.aspx
Hi ! - An information we all needed for various purposes. As the links are pointing to a 2010 part inside the 2012 doc i guess the steps can be used in a clean 2010 environement as well ?
Yes, you can use these same steps for the ServiceManager database in 2010.
Can this be used to refresh a database to another environment? For example, we have a production environment and a STG environment. Could this be used to refresh STG with production data?
@Jesse - yes, that was the original intent of this documentation on the TechNet Library.
Hello Travis,
First of all, thanks a lot for all the great articles!
Do you have any update about moving the DW databases?
Kind Regards,
taking a dev/test environment (db) into prod is a big customer ask...looking forward to the documentation to do this...or, even better, a wizard to walk them through this ;-)
I have a Service Manager with Data Warehouse registered. When I moved the Service Manager database correctly but the jobs of the Data Warehouse continue pointing to the old database. What should I change in the Data Warehouse for migration to be successful?.
I changed the following tables in the DW Database working perfectly.
dbo. MT_Microsoft$SystemCenter$ResourceAccessLayer$SqlResourceStore and
dbo. MT_Microsoft$SystemCenter$DataWarehouse$CMDBSource
Hi Travis,
I have a small question.
In Step 4 "Configure the Service Manager database", do I have to do all the steps or only the steps concerning the service account and the tables?
Because I only want to move my productive database to a new SQL server.
Thank you for your help.
Kind regards
Hi All,
Thanks for this great write up of the procedure. I'm just wondering whether there is a SQL script available to add the custom error messages to the master database, rather than installing another management server?
Kind regards,
Mark
Of course, not official, but you can open the build_sm_db.sql from the installation files and extract from line 83306 to 83390.
You can also find the extracted SQL here: docs.google.com/.../edit
Thank you,
Jose