How to move your Management Reporter 2012 database to a new server
Greg Byer
2,586
Like
Share
ReportThe steps below will guide you through moving your Management Reporter database to a new server. If part of the server move involves a new domain, you'll need to incorporate the steps in KB 3163587.
Note If you are using a Management Reporter 2012 provider that uses the Data Mart (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\<username>\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. Under ERP Integrations, click the name of the SQL server.
12. Click Disable Integration and then click Remove to remove the ERP integration.
13. Click Yes to the prompt “Are you sure you want to remove the ERP Integration?”.
Note If you did not remove the integration prior to backing up the MR database, you can follow the steps in the article below to remove the integration records from the database.
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.
20. Backup the Management Reporter 2012 database recorded earlier in this topic.
21. Login to SQL Server Management Studio on the new SQL server.
22. Restore the Management Reporter 2012 database on the new SQL server.
23. 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
24. 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]
UPDATE Connector.Adapter
SET Settings.modify('declare namespace x="http://www.microsoft.com/2009/Dynamics/Integration";
replace value of
(/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text())[1]
with ""')
UPDATE Connector.MapCategoryAdapterSettings
SET Settings.modify('declare namespace x="http://www.microsoft.com/2009/Dynamics/Integration";
replace value of
(/SettingsCollection/x:ArrayOfSettingsValue/x:SettingsValue[x:Attributes="Password"]/x:Value/text())[1]
with ""')
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
Note If using Management Reporter 2012 with Dynamics AX 2012, also run the following SQL statements to reset the organization hierarchies in the Management Reporter 2012 Report Designer. This information will be retrieved from Microsoft Dynamics AX once the new integration is enabled:
delete ControlTreeMaster where FolderID in (select FolderID from ControlFolderIntegration)
delete ControlFolder where ID in (select FolderID from ControlFolderIntegration)
delete ControlFolderIntegration
25. Start the Management Reporter 2012 Configuration Console.
25. Under the File menu, click Configure.
27. Put a check next to Management Reporter Application Service and Management Reporter Process Service.
28. Click Next.
29. Correct any issues noted on the Prerequisite Validation screen, and then click Next.
30. In the Service Account section, enter the name of the service account recorded earlier. Also enter the password for the service account.
31. In the Database Configuration section, put a check in Connect to an existing database.
32. Enter the name of the new SQL Server in the Database server field.
33. Select to use Windows authentication, or enter a SQL authenticated username and password.
34. In the Database dropdown, select the name of the newly restored Management Reporter 2012 database.
35. 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.
36. Click Next.
37. Click Configure.
38. Click Close on the Configure Management Reporter screen when the process is completed.
39. Under the File menu, click Configure.
40. Put a check next to the ERP that is correct for your environment.
41. Click Next.
42. 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
Note If you use a Management Reporter 2012 provider that uses the Dynamic Data mart (DDM) database, do not move the DDM database. Instead, create a new data mart database.
43. After the integration has successfully created, click Close. You must complete the steps below before you click Enable Integration.
44. In the Management Reporter 2012 Configuration Console, click the name of the data mart integration.
45. Click Update Dynamics XX credentials, where XX is either AX, GP, SL or NAV (depending on your Dynamics ERP).
46. Enter the appropriate credentials to connect to the Dynamics ERP database, and then click OK.
47. Click Update data mart credentials.
48. Enter the appropriate credentials to be used to update the data mart database, and then click OK.
49. In the Management Reporter 2012 Configuration Console, stop the Management Reporter 2012 Application Service and the Management Reporter 2012 Process Service.
50. In the Management Reporter 2012 Configuration Console, start the Management Reporter 2012 Application Service and the Management Reporter 2012 Process Service.
51. In the Management Reporter 2012 Configuration Console, click the name of the data mart integration.
52. Click Enable Integration.
Comments
-
-
-
-
-
@Rob
The recommended process to update the Legacy companies after a database move is to:
1. Remove the old Legacy provider
2. Create the new Legacy provider and point it to the new server
3. And then re-import the companies for the new Legacy provider
When re-importing the companies, the Configuration Console will notify you that the connection information for the company has changed and will ask you if you want to update it. This process will correctly update the re-imported companies to point to the proper server, as well as making sure to write any other values needed and prevent unsuccessful/incorrect SQL edits.
-
Oh FYI, it did not correctly copy the whole XML string into my response, but I got it from this blog post here, which is a completely unrelated issue of users not being able to login, but that is how we stumbled across the answer:
community.dynamics.com/.../management-reporter-is-unable-to-connect-to-the-company
-
Hi Greg,
I emailed Erik Johnson just now from your support team because he was recently working on a case with us regarding management reporter not displaying changes to data inside of GP, even when using the legacy adapter instead of the data mart.
These instructions you have posted are correct for users on the data mart. However, I have discovered that if they are using the legacy adapter, the connection information to SQL is hard coded into the ControlCompany table, even when you remove the integration prior to migrating to a new server, it still contains a reference, as an XML value, to the old server name:
In the case of an incorrect SQL Server name, there is an entry in the ControlCompany table in the GLEntityConnectionInformationcolumn. The contents of this column is stored as XML. The SQL Server node contains the DNS Alias or machine name:
SQL1\GP
You must change this to your new server name, if using legacy, or the reports will continue pointing to the old server. When using legacy, the companies are hard coded into the specification set for your reports, and so simply removing them from the console does not impact this table.
I would recommend adding these instructions somehow to this document so users who are on legacy don't spent countless hours troubleshooting why it can't connect to their new server or why it's pulling data from the old server still.
*This post is locked for comments