Moving the ACS Database to new Drives on the Same System

Is the ACS Database a little larger than you were expecting? You may decide you want to do move this database to another drive on the same server if you have a second disk drive with more space, or want to move to another spindle for better performance.

In our case we relocated an existing ACS installation from the default location on the C: drive to new drives which were added for the data and logs. In the example below, we move the database from the C: drive to the D: drive (data) and the E: drive (logs). The database file names are the default names of dbAuditData.mdf and dbAuditLog.ldf.

  1. To determine the location of the files for the OperationsManagerAC Database, log into SQL Server Management Studio, connect to the server running the OperationsManagerAC Database, click on the OperationsManagerAC database and right-click New Query. Enter: sp_helpfile. The response will show the location (and names) of the OperationsManagerAC database files.
  2. Stop the ACS Collector service (Operations Manager Audit Collection Service) on any management server(s) running the ACS Collector.
  3. Backup the OperationsManagerAC and the master database as well, just to be safe. This can be done using the SQL Server Management Studio; select the OperationsManagerAC database in the left pane, right-click, select Tasks, select Back Up… and follow the instructions. Be sure to do a fullbackup. Do the same for the master database.
  4. Detach the OperationsManagerAC database. In a SQL query select the master database and type: sp_detach_db ‘OperationsManagerAC’ (Don’t highlight the OperationsManagerAC database on the left pane or it will not detach because it is in use! Also the database will not detach because it is in use if any management servers are running the ACS Collector Service (if you have not stopped the service).
  5. Using Windows Explorer, copy the data and log files from the current location to the new drive/location. We are assuming that the location is D:\Sqldata and E:\Sqllogs.
  6. Re-attach the database. In a SQL Query window select the master database and type: sp_attach_db ‘OperationsManagerAC’, ‘E:\Sqldata\OperationsManagerAC.mdf’, ‘E:\Sqllogs\OperationsManagerAC.ldf’
  7. Verify it worked, using sp_helpfile. Select the OperationsManagerAC database and in the query window, type: sp_helpfile. The filename column returned in the response from sp_helpfile should reflect the new locations.
  8. Restart the collector service on any management servers where it had been stopped and validate functionality of the new database. This can be done well through using the performance monitor (perfmon) utility and monitoring the Connected Clients counter as part of the ACS Collector object.

This entry was designed to provide specifics as to how this is done for the ACS database, see http://support.microsoft.com/kb/224071 for general processes regarding database moves.

You can also use this process to move the underlying files for the other Operations Manager databases. For example, see https://opsmgrunleashed.wordpress.com/2007/06/15/moving-the-operationsmanager-database-to-a-new-drive-on-the-same-system/ for information on moving the OperationsManager database  to another drive on the same server.

Moving to another server takes additional work, as documented in our previous article on moving the Operations Manager database (https://opsmgrunleashed.wordpress.com/2007/06/12/moving-the-operations-database/). We are currently testing the process to move the Data Warehouse database to another server and hope to have a blog entry on that shortly.

Advertisements
This entry was posted in Operations Manager 2007. Bookmark the permalink.

One Response to Moving the ACS Database to new Drives on the Same System

  1. Jake Stevens says:

    Worked like a charm for me!

    slight correction on instruction 6

    6.Re-attach the database. In a SQL Query window select the master database and type: sp_attach_db ‘OperationsManagerAC’, ‘E:\Sqldata\dbauditdata.mdf’, ‘E:\Sqllogs\dbauditlog.ldf’

    Thanks for your help

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s