You can move the Configuration Manager site database and associated Forefront Endpoint Protection (FEP) databases after setup has completed to a different SQL Server computer system by:
Detailed steps follow.
Configuration Manager 2007 does support moving the site database from a remote SQL Server to the local site server computer if the site server computer is running a supported version of Microsoft SQL Server. For a list of supported SQL Server versions, see Configuration Manager Supported Configurations.
FEP hosts two databases, the FEP database (FEPDB_sitecode) and the FEP data warehouse (FEPDW_sitecode). The FEP database serves as a proxy database for extracting data from the Configuration Manager site database. It does not need to be backed up or moved, and will be recreated when the FEP Reporting component is reinstalled.
Important: You will require access to the FEP 2010 installation media in order to successfully complete these steps.
Ensure that the database access permissions are the same on the new databases as they are on the original databases.
I recently ran into this issue when migrating the FEP database and it wasn't acceptable to loose the historical data.
I isolated and identified the root cause of the problem and have managed to successfully migrate the FEP database and all of my FEP reports are successfully working.
I wanted to share here the solution for others that run into this issue:
The problem is that some of the views in the FEPDW database reference the local SQL server by a linkedserver instead of the actual servername, this is by design to provide the felxibility of changing servers etc...
The problem is that when running the ServerSetup.exe and using an existing database (the migrated one) it doesn't create the linkedserver references and as such the views in the FEPDW database do not work.
You can use the following SQL query to view the linkedserver entries:
The two entries that are required by the FEP database are:
on the new SQL Server that you are migrating to you must create these linkedserver entries, the following queries can be used to do this:
@server = 'SelfLinkedServer',
@srvproduct = '',
@provider = 'SQLNCLI',
@datasrc = 'SQLSERVERNAME\INSTANCE'
NOTE: - SQLSERVERNAME\INSTANCE is the servername and instancename of your sql server
@server = 'FEPDW_ORG_FEPDW_ORG_OLAPProvider_FEP',
@provider = 'MSOLAP',
@datasrc = 'SQLSERVERNAME\INSTANCE',
@catalog = 'FEPDW_SITECODE'
NOTE: - FEPDW_SITECODE is the name of your FEPDW database
After performing these actions you should be able to successfully migrate the FEP database using the FEP ServerSetup.exe