re: How to move the FEP Databases and the CM Site Database

Kriss Milne — Fri, 18 Jan 2013 02:15:58 GMT

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:

select *

from sys.servers

The two entries that are required by the FEP database are:

SelfLinkedServer

FEPDW_ORG_FEPDW_ORG_OLAPProvider_FEP

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:

for SelfLinkedServer:

Exec sp_addlinkedserver

@server = 'SelfLinkedServer',

@srvproduct = '',

@provider = 'SQLNCLI',

@datasrc = 'SQLSERVERNAME\INSTANCE'

NOTE: - SQLSERVERNAME\INSTANCE is the servername and instancename of your sql server

for FEPDW_ORG_FEPDW_ORG_OLAPProvider_FEP:

Exec sp_addlinkedserver

@server = 'FEPDW_ORG_FEPDW_ORG_OLAPProvider_FEP',

@srvproduct = '',

@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