Migrating the reporting database component

Applies To: Forefront Client Security

Checklist

The following is a checklist of the high-level tasks required for you to successfully migrate the Client Security reporting database server component. This checklist is included to help you perform the migration procedures. Detailed steps follow the checklist.

Before migrating the reporting database component, you must first prepare the target computer.

Preparing the target computer

On the target reporting database server, install the Client Security prerequisites for the reporting database server component, as well as the Client Security reporting database server component.

To install SQL Server 2005

• Follow the instructions in "Install SQL Server 2005 with SP2 or SP1" in Installing the software prerequisites on the collection database server.

To install the Client Security reporting database server component

1. Follow the instructions about installing the Client Security reporting database server component in "To install Client Security on the reporting database server" in Installing Client Security on a six-server topology.

2. During the installation of the Client Security reporting database server component, when prompted for locations of the other Client Security components, enter the planned locations based on the target topology.

Preparing the management server for the move of the reporting database

The management server connects to the reporting database to display information in the console. To prepare the management server for the move of the reporting database, you must close consoles and stop the Client Security Management service.

To prepare the management server

1. To ensure the management server does not have an open connection to the reporting database, close all Client Security consoles and any MOM consoles that are open.

2. In Administrative Tools, start the Services console and stop the Microsoft Forefront Client Security Management service.

Preparing the collection server

To prepare the collection server for the reporting database transfer, perform the following step.

To prepare the collection server

• Close any MOM consoles that are open.

Preparing the source reporting database server

The reporting database server has two automated DTS tasks that transfer data from the collection database to the reporting database. To move the reporting database, you must disable both of these DTS tasks.

To disable the DTS tasks on the source reporting database server

1. Click Start, click Control Panel, and then open Scheduled Tasks.

2. Right-click SystemCenterDTSPackageTask, clear the Enabled check box, and then click OK.

3. Click Start, click All Programs, click SQL Server 2005, and then select SQL Server Management Studio.

4. In the Connect to Server dialog box, verify the name of the SQL Server, and then click Connect.

5. In the Object Explorer, expand SQL Server Agent, and then expand Jobs.

6. Right-click Microsoft Forefront Client Security, and then click Disable.

7. Right-click FCS - Update SystemCenterReporting Partition Function, and then click Disable.

Migrating the reporting role databases

The SQL Server Reporting Service databases (ReportServer and ReportServerTemp) reside on the reporting database server. To migrate the reporting database server role, you must transfer the SQL Server Reporting Service reporting databases to the new reporting database server.

To prepare to transfer the SQL Server Reporting Service databases

1. On the reporting server, in Administrative Tools, click Services.

2. In the right pane, right-click SQL Server Reporting Services, and then click Stop.

3. In Administrative Tools, click Internet Information Services Manager.

4. In the tree pane, expand Application Pools.

5. Right-click ReportServer, and then click Stop.

Transferring the reporting server databases involves detaching the database files from the original SQL Server instance and reattaching the databases to the new SQL Server instance.

To transfer the reporting server databases

1. Start SQL Server Management Studio, and then expand Databases.

2. Right-click ReportServer, point to Tasks, and then click Detach.

3. Click OK. The database is detached and ready for the .mdf and .ldf files to be copied to the target server.

4. Repeat steps 1-3 for the ReportServerTempDB database.

5. Copy the database files for both the ReportServer and ReportServerTempDB databases to the target reporting database server.

1. On the target computer, start SQL Server Management Studio.

2. Right-click Databases, and then click Attach.

3. Click Add and browse to the location of the .mdf files for the source reporting server ReportServer database. The .ldf, or log file, will be populated automatically.

4. Click OK, and click OK in the Attach Databases dialog box.

5. Repeat this procedure for the source reporting server ReportServerTempDB database.

After attaching the databases, you must verify that the RSExecRole database role has been created on the new reporting database server. This role is required for the ReportServer and ReportServerTempDB databases.

To verify the RSExecRole database role

1. In SQL Server Management Studio expand Databases.

2. Expand ReportServer, expand Security, expand Roles, and then click on Database Roles.

3. Verify that RSExecRole is listed on the Summary page.

4. In Object Explorer, right-click RSExecRole, and then click Properties.

5. Under Select a page, click Securables.

6. In the right pane, click Add.

7. In Add Objects, select All objects of the types, and then click OK.

8. Under Object Type, click to select Stored procedure and Tables, and then click OK.

9. In the right pane, under Securables, click on the first listed item that has Table in the Type column.

10. Under Explicit permissions, in the Grant column, verify that Delete, Insert, References, Select, Update and permissions are selected.

11. In the right pane, under Securables, click on the first listed item that has Stored procedure in the Type column.

12. Under Explicit permissions, in the Grant column, verify that Execute is selected.

After verifying the RSExecRole information, you must configure SQL Server Reporting Services to connect to the new location of the ReportServer and ReportServerTempDB databases.

To configure SQL Server Reporting Services

1. On the Client Security reporting server, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click Reporting Services Configuration.

2. Select the current server in the Machine Name box, and then click Connect.

3. Click Database Setup.

4. In the Server Name box, enter the name of computer to which the ReportServer and ReportServerTempDB databases were moved, and then click Connect.

5. In the SQL Server Connection Dialog box, click OK.

6. Next to Database Name, select ReportServer, and then click Apply.

7. Click Start, point to Administrative Tools, and select Services.

8. In the details pane, right-click SQL Server Reporting Services, and then click Restart.

9. In the details pane, right-click World Wide Web Publishing Service, and then click Restart.

Transferring the reporting database

The next step is transferring the Client Security reporting database. This involves backing up the database on the source server, recording information about the database owner accounts for the SystemCenterReporting database and the ReportServer database, restoring the source SystemCenterReporting database to the target computer, and then configuring the target reporting database server with the account information

The first step is to backup the source reporting database, listed in the SQL Server Management Studio as the SystemCenterReporting database.

To back up the reporting database

1. On the source reporting database server, open the Microsoft SQL Server Management Studio, and then in Object Explorer, expand Databases.

2. Right-click SystemCenterReporting, point to Tasks, and then click Back Up.

3. On the General page of the Back Up Database - SystemCenterReporting, under Destination, for Back up to, select the appropriate destination media.

4. In the text box below Destination, click a backup path. Or click Add and type a destination for the database backup, if it does not exist. This location needs to be accessible to the target computer.

5. Click the Options page, and under Reliability, select the check boxes for Verify backup when finished and Perform checksum before writing to media, and click OK.

6. The database is immediately backed up. Click OK when it is complete.

You must record the name of the accounts in SQL Server that have the db_owner mapping for both the SystemCenterReporting database and the ReportServer database.

To find db_owner for the databases

1. On the source reporting database server, start Microsoft SQL Server Management Studio.

2. On the toolbar, click the New Query button.

3. In the right pane, type  **sp_helplogins ** and click the Execute button. Two results panes are presented at the bottom of the SQL Server Management Studio window.

4. In the bottom result pane, use the scroll bar to find the record that has SystemCenterReporting in the DBName column and db_owner in the UserName column. The entry in the LoginName column is the user account that has db_owner privileges.

5. Repeat the previous steps for the ReportServer database.

On the target reporting database server, restore the database back up from the source reporting database server.

To restore the reporting database back up

1. On the target reporting database server, open the Microsoft SQL Server Management Studio, and then in Object Explorer, expand Databases.

2. Right-click SystemCenterReporting, point to Tasks, point to Restore, and then click Database.

3. On the General page of the Restore Database - SystemCenterReporting dialog box, under Source for restore, select From device, and then click the ellipsis (…) button.

4. In the Specify Backup dialog box, in the Backup media list, verify that File is selected, and then click Add.

5. In the Locate Backup File dialog box, browse to the location of the collection database backup, select it, click OK, and then click OK again.

6. Under Select the backup sets to restore, in the Restore column for the backup you are restoring, select the check box.

7. Click the Options page, and under Restore options, select the Overwrite the existing database check box, and then click OK.

8. The restore of the collection database begins immediately. Click OK when it is complete.

After the restoration of the reporting database, you must ensure that the accounts that were mapped to db_owner on the source reporting database server have the db_owner mapping on the target reporting database server.

To ensure login mappings to db_owner

1. On the target reporting database server, in Microsoft SQL Server Management Studio, in Object Explorer, expand Security, and then select Logins.

2. Right-click the each user account identified as db_owner for the SystemCenterReporting database and choose Properties.

3. In the Login Properties dialog box, under Select a page, click User Mapping.

4. In Users mapped to this login, click SystemCenterReporting and ensure that the db_owner check box is selected.

5. Click OK, and repeat steps 1-4 for the ReportServer database and the ReportServerTempDB database.

Configuring the management server

After the restoration of the reporting database, you must restart the Microsoft Forefront Client Security Management Service.

Additionally, the management server must be informed of the location of the new reporting database server. This is done by re-running the Client Security Configuration wizard.

Configuring the management server

1. On the management server, in Administrative Tools, click Services.

2. Right-click Microsoft Forefront Client Security Management Service, and then click Start.

3. In the Client Security console, click Action and choose Configure.

4. On the Before You Begin page, click Next.

5. On the Collection Server and Database page, in the Collection database text box, verify the name of the collection database server (or servername\instancename if not using a default SQL Server instance), and then click Next.

6. On the Reporting Database page, do the following:

   1. In the Reporting database box, type the name of the new reporting database server. If using a non-default SQL Server instance, type the name of the new reporting database server and the SQL Server instance name in the format servername\instancename

   2. In the Reporting account box, enter the user name and password for the reporting account.

   3. Click Next.

7. On the Reporting Server page, do the following:

   1. In the Reporting server box, ensure that the name of the reporting server is correct.

   2. In the URL for Report Server and URL for Report Manager boxes, ensure the default values are entered.

   3. Click Next.

8. On the Verifying Settings and Requirements page, verify that your system requirements, and then click Next. If you receive an error, you cannot continue configuring Client Security. If you receive a warning or error, see the following resources for more information:

   • Configuration log file. (To view, click View Log.) For more information about the configuration log file, see Overview of log files in the Troubleshooting Guide (https://go.microsoft.com/fwlink/?LinkId=82466).

   • Setup issues in the Troubleshooting Guide (https://go.microsoft.com/fwlink/?LinkID=86102) .

9. On the Completing the Configuration Wizard page, verify that you have successfully configured Client Security, and then click Close. If you receive an error, you cannot continue configuring Client Security. If you receive a warning or error, see the following resources for more information:

   • Configuration log file. (To view, click View Log.) For more information about the configuration log file, see Overview of log files in the Troubleshooting Guide (https://go.microsoft.com/fwlink/?LinkId=82466).

   • Setup issues in the Client Security (https://go.microsoft.com/fwlink/?LinkID=86102).

10. In Administrative Tools, click Services.

11. In the service listing, right-click Microsoft Forefront Client Security Management Service, and then click Start.

To verify that the management server can communicate with the new reporting database server, perform the following steps.

To verify communication between the management server and the reporting database server

1. On the management server, in Control Panel, open Scheduled Tasks.

2. Right-click SystemCenterDTSPackageTask, and then click Run.

3. Verify that the task completes successfully by observing the Last Result column. It will show 0x0 for a successful completion.

If any of the verifying communication steps fail, verify you have performed all previous steps. If communications continue to fail, contact Microsoft product support.

The final step in the migration of the Client Security reporting database component is to uninstall the reporting database component from the source server. This should be done only after successful verification of communication.

To uninstall the reporting database component

• Follow the instructions in Removing an existing installation of Client Security.