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. Click Add, and browse to the backup location.
    2. Select the backup file and click OK.
    3. Click OK to restore the database.
  1. 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.


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.



Contact Us

On Key

More Posts

WME Cybersecurity Briefings No. 014
Cyber Security

WME Security Briefing 14 June 2024

LightSpy Spyware’s macOS Variant Detected with Advanced Surveillance Capabilities Overview Findings reveal a previously undocumented macOS variant of the LightSpy spyware. It was initially thought to target only iOS users. This spyware utilizes a plugin-based system

Read More »
WME Cybersecurity Briefings No. 013
Cyber Security

WME Security Briefing 10 June 2024

CISA Urges Patching of Actively Exploited Linux Kernel Vulnerability Overview CISA just issued an urgent advisory concerning a newly discovered security flaw in the Linux kernel. The flaw is being actively exploited to affect the netfilter component of

Read More »
3 Things to Consider Before You Enable Copilot for Microsoft 365
Microsoft Copilot

3 Things to Consider Before You Enable Copilot for Microsoft 365

In today’s digital landscape, any productivity tool that streamlines workflow and boosts performance is a pleasant addition. With its AI-powered productivity-enhancing capabilities, Microsoft Copilot has emerged as a game-changer for employees, particularly for organizations using Microsoft

Read More »
WME Cybersecurity Briefings No. 012
Cyber Security

WME Security Briefing 03 June 2024

Moroccan Cybercrime Group Exploits Gift Card Systems for Major Financial Gains Overview: Storm-0539, also called Atlas Lion, is a Moroccan cybercrime group that executes advanced email and SMS phishing attacks. They are committing fraud by utilizing

Read More »
Be assured of everything

Get WME Services

Stay ahead of the competition with our Professional IT offerings.