The process for moving the Management Reporter 2012 database is as
follows:
Note If you are using a Management Reporter 2012 provider
that uses the Dynamic Datamart (DDM) database, do not move the DDM database.
Instead, a new database will be created by using the process here.
1. Start the Management Reporter 2012 Configuration Console.
2. Click
Management Reporter Services.
3. Record the name of the database listed under the Database connection
heading. The database name will be listed after the SQL server name. For
example: SQLServer (Database)
4. Record the name of the Service Account that is used.
5. Login to SQL Server Management Studio.
6. Backup the Management Reporter 2012 database recorded earlier in this
topic.
7. Start the Management Reporter 2012 Report Designer.
8. Export each building block group. The reports can be re-imported if a
failure were to occur. You can do this by following these steps:
a. Under the
Company menu, click
Building Block
Groups.
b. Select the building block group to export.
c. Click
Export.
d. Select all building blocks to be exported on the Report Definitions, Row
Definitions, Column Definitions, Reporting Tree Definitions tabs. Also, select
all Dimension Value Sets.
e. Click
Export.
f. Choose a location to save the .tdbx export file.
Note The default path on a Windows Server 2008 server is
C:\Users\
\Documents\Management Reporter\Building Block
Groups
g. Click
Close on the Building Block Groups window.
9. Exit Management Reporter 2012 Report Designer.
10. Start the Management Reporter 2012 Configuration Console.
11. In the navigation bar, click the name of the SQL server.
12. Click
Remove to remove the ERP integration.
13. Click
Yes to the prompt “Are you sure you want to remove
the ERP Integration?”
14. In the navigation bar, click
Management Reporter
Services.
15. Click
Remove Process Service.
16. Click
Yes to the prompt “Are you sure you want to remove
‘Management Reporter 2012 Process Service’?”
17. Click
Remove Application Service.
18. Click
Yes to the prompt “Are you sure you want to remove
‘Management Reporter 2012 Application Service’?”
19. Login to SQL Server Management Studio on the new SQL server.
20. Restore the Management Reporter 2012 database, that was created in step
6, on the new SQL server.
21. Verify that the Management Reporter 2012 service account has the correct
permissions on the SQL server and to the new database. Refer to the Management
Reporter installation guides at the following link:
Microsoft Management Reporter: Installation, Migration, and Configuration
Guides
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5916
22. Using the instructions here, run the following script against the
Management Reporter 2012 database on the new SQL server:
--////////////////////////////////////////////////////////////////
--
-- Script Instructions:
--
-- 1. Update the line in the following
code, starting with 'CREATE MASTER KEY ENCRYPTION BY PASSWORD', to contain the
-- Master key you wish to use. The master key must meet the Windows password
policy
-- requirements of the computer that is running the instance of SQL
Server.
--
-- 2. Run this script against the Management Reporter 2012
database. This script
-- will output a message if it does not detect the
Management Reporter 2012
database.
--
--////////////////////////////////////////////////////////////////
IF
EXISTS (SELECT Name FROM sys.tables WHERE Name =
'ControlReportSchedule')
BEGIN
IF EXISTS (SELECT TOP(1) name FROM
sys.symmetric_keys WHERE name = 'GeneralUserSymmetricKey')
DROP SYMMETRIC KEY
GeneralUserSymmetricKey
IF EXISTS (SELECT TOP(1) name FROM
sys.certificates WHERE name = 'GeneralUserCertificate')
DROP CERTIFICATE
GeneralUserCertificate
IF EXISTS (SELECT TOP(1) name FROM
sys.symmetric_keys WHERE name = 'ConnectorServiceSymmetricKey')
DROP
SYMMETRIC KEY ConnectorServiceSymmetricKey
IF EXISTS (SELECT TOP(1) name
FROM sys.certificates WHERE name = 'ConnectorServiceCertificate')
DROP
CERTIFICATE ConnectorServiceCertificate
IF EXISTS (SELECT TOP(1) name
FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
DROP MASTER
KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Access!23'
-- NOTE
Where Access!23 is your actual password
CREATE CERTIFICATE
[ConnectorServiceCertificate]
AUTHORIZATION [dbo]
WITH SUBJECT =
N'Certificate for symmetric key encryption - for use by the connector
service.'
CREATE CERTIFICATE [GeneralUserCertificate]
AUTHORIZATION
[dbo]
WITH SUBJECT = N'Certificate for access symmetric keys - for use by
users assigned to the GeneralUser Role.'
CREATE SYMMETRIC KEY
[ConnectorServiceSymmetricKey]
AUTHORIZATION [dbo]
WITH ALGORITHM =
AES_256
ENCRYPTION BY CERTIFICATE [ConnectorServiceCertificate]
CREATE
SYMMETRIC KEY [GeneralUserSymmetricKey]
AUTHORIZATION [dbo]
WITH ALGORITHM
= AES_256
ENCRYPTION BY CERTIFICATE [GeneralUserCertificate]
IF NOT
EXISTS (SELECT TOP(1) name FROM sys.database_principals WHERE
name='GeneralUser')
BEGIN
CREATE ROLE [GeneralUser]
AUTHORIZATION
[dbo]
END
GRANT CONTROL ON CERTIFICATE::[GeneralUserCertificate] TO
[GeneralUser]
GRANT VIEW DEFINITION on SYMMETRIC
KEY::[GeneralUserSymmetricKey] TO [GeneralUser]
GRANT CONTROL ON
CERTIFICATE::[ConnectorServiceCertificate] TO [GeneralUser]
GRANT VIEW
DEFINITION on SYMMETRIC KEY::[ConnectorServiceSymmetricKey] TO
[GeneralUser]
END
ELSE
BEGIN
PRINT 'WARNING: Incorrect database
selected.'
Print 'Execute script against the Management Reporter 2012
database.'
PRINT 'This can be found in the Management Reporter 2012
Configuration Console.'
END
23. Start the Management Reporter 2012 Configuration Console.
24. Under the
File menu, click
Configure.
25. Put a check next to
Management Reporter
Application Service and
Management Reporter
Process Service.
26. Click
Next.
27. Correct any issues noted on the Prerequisite Validation screen, and then
click
Next.
28. In the Service Account section, enter the name of the service account
recorded earlier. Also enter the password for the service account.
29. In the Database Configuration section, put a check in
Connect to
an existing database.
30. Enter the name of the new SQL Server in the Database server field.
31. Select to use Windows authentication, or enter a SQL authenticated
username and password.
32. In the
Database dropdown, select the name of the newly
restored Management Reporter 2012 database.
33. In the Application Service section, enter a port number for the
application service to run on. If the Windows Firewall is enabled on the server,
put a check next to
Open this port in the Windows Firewall.
34. Click
Next.
35. Click
Configure.
36. Click
Close on the Configure Management Reporter screen
when the process is completed.
37. Under the
File menu, click
Configure.
38. Put a check next to the ERP that is correct for your environment.
39. Click
Next.
40. Follow the instructions for the ERP for your environment using the
appropriate guide at the following link:
Microsoft Management Reporter: Installation, Migration, and Configuration
Guides
http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5916
Useful Links:
Posts
