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.

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.

Share:

Facebook
Twitter
LinkedIn
Picture of Matt Tinney

Matt Tinney

Professional IT executive & business leader having decades of experience with Microsoft technologies delivering modern-day cloud & security solutions.

Contact Us

=
On Key

More Posts

WME Cybersecurity Briefings No. 032
Cyber Security

WME Security Briefing 30 October 2024

Chinese Nation-State Hackers APT41 Target Gambling Industry for Financial Gain Overview The Gambling and Poker industry experienced a sophisticated cyber attack last month, orchestrated by the notorious Chinese nation-state group APT41 ( AKA Brass Typhoon, Earth Baku, Wicked

Click Here to Read Full Article »
WME Cybersecurity Briefings No. 031
Cyber Security

WME Security Briefing 24 October 2024

Hackers Exploit EDRSilencer to Evade Security Detection Overview Threat actors have been observed abusing the EDRSilencer tool as part of their bypass techniques against endpoint detection and response (EDR) solutions to carry out attacks against targeted organizations successfully.

Click Here to Read Full Article »
Be assured of everything

Get WME Services

Stay ahead of the competition with our Professional IT offerings.

=