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

Mastering Azure AD Connect - A Comprehensive Guide by WME
Active Directory

Mastering Azure AD Connect – A Comprehensive Guide

Modern businesses are fast moving toward cloud-based infrastructure. In fact, cloud-based business is not just a trend anymore but a strategic necessity. Microsoft’s Azure Active Directory (Azure AD) has become a frontrunner in this domain. It

Read More »
Security Best Practices in SharePoint
Office 365

Security Best Practices in SharePoint

Microsoft SharePoint is an online collaboration platform that integrates with Microsoft Office. You can use it to store, organize, share, and access information online. SharePoint enables collaboration and content management and ultimately allows your teams to

Read More »
The Ultimate Guide to Microsoft Intune - Article by WME
Active Directory

The Ultimate Guide to Microsoft Intune

The corporate world is evolving fast. And with that, mobile devices are spreading everywhere. As we venture into the year 2024, they have already claimed a substantial 55% share of the total corporate device ecosystem. You

Read More »
Protecting Microsoft 365 from on-Premises Attacks
Cloud Security

How to Protect Microsoft 365 from On-Premises Attacks?

Microsoft 365 is diverse enough to enrich the capabilities of many types of private businesses. It complements users, applications, networks, devices, and whatnot. However, Microsoft 365 cybersecurity is often compromised and there are countless ways that

Read More »
Be assured of everything

Get WME Services

Stay ahead of the competition with our Professional IT offerings.