MEMCM: Move Data Warehouse

If you ever need to move your MEMCM data warehouse, it’s actually fairly easy. This blog post will walk you through the general process, and provide a method for making sure your new data warehouse point is syncing all your tables.

I completed this post using MEMCM 2002 and migrated SQL Server from 2016 to 2019.

Moving the Database

If you’re going to upgrade SQL versions, I would do it on the old server. That way your new installation is clean. Don’t forget to upgrade the database version, not just SQL Server.

Moving the database is fairly straightforward. Make sure that the SQL Server version matches on the old and new server.

1. Remove the data warehouse service point from the old server using the MEMCM console.
2. Launch SQL Server Management Studio (SSMS) on the old server, or connect to the old server using SSMS from another member server.
3. Backup the data warehouse database.
   1. Right-click on the data warehouse database.
   2. Select Tasks, then Back Up….
   3. Specify Full for Backup type, and Disk for Back up to.
   4. Provide a backup location by clicking Add.
   5. Click OK to start the backup.
4. Launch SSMS on the new server, or connect to the new server using SSMS from another member server.
5. On the new server, restore the data warehouse database.
   1. Right-click on Databases and select Restore Database….
   2. Change the Source to Device and click the … button.

1. 3. Click Add, and browse to the backup location.
   4. Select the backup file and click OK.
   5. Click OK to restore the database.
2. Install the data warehouse role on the new server from the MEMCM console.

Identify Extra Tables

MEMCM doesn’t save tables that were manually specified for syncing, so if you didn’t document the extra tables you selected, this is a way to go back and do those now.

First, we need to figure out which tables weren’t default. To do that, kick off a manual sync from the MEMCM console. This can be done by viewing the role on the site server where it’s installed. Wait for the sync to complete. You can follow along in the Microsoft.ConfigMgrDataWarehouse.log file. This file is in the SMS folder on the server running the data warehouse service point. Once the sync is complete, run this SQL query from SSMS, replacing the timestamp in the WHERE statement with a timestamp after your manual sync.

SELECT TableName,LastSyncTime
FROM Version_DW
WHERE LastSyncTime < '2020-07-26 07:04:000.000'

Any tables returned need to be selected on the database warehouse service point role MEMCM console, under the Synchronization Settings tab. Once you think you have them all, you can rerun the query (with a new timestamp) until it produces no results.

Disclaimer

All content provided on this blog is for information purposes only. Windows Management Experts, Inc makes no representation as to accuracy or completeness of any information on this site. Windows Management Experts, Inc will not be liable for any errors or omission in this information nor for the availability of this information. It is highly recommended that you consult one of our technical consultants, should you need any further assistance.