DPM/SQL Performance

DPM is largely depending on its SQL database. All settings, jobs and recovery information is hold in the DPM database.
When the DPM database has performance issues then this will also impact the performance of the DPM console.
One of the possible performance issue are fragmented Indexes in your database.

The Report Index Physical Statistics show the status of the fragmentation

 

Rebuild indexes
When the indexes are heavily fragmented then you need to rebuild them. You can do that by hand, but that’s a ‘hell of a job’.

Therefor I use the script below which will rebuild every index existed in database with fillfactor of 80.

DECLARE @TableName VARCHAR(255) DECLARE @sql NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 80 DECLARE TableCursor CURSOR FOR SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName FROM sys.tables OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@sql) FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor GO

(Source: \http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/)

DPMbackup Invalid command -db.

The DPMbackup.exe command is a very useful tool to make a copy of your DPM database to a disk location. Using this copy you can easily recover from a corrupt database.

For our customers a use a scheduled task to make a dump of the DPM to disk on a daily bases. It has saved me from a full reinstall more then ones.

The issue I run in to is that the command DPMbackup –DB works perfectly from the command prompt, but when I use it in a script it gives me the following eror  {Error: Invalid command –db.}

In my case it worked to specify the instance name as well. Like: DpmBackup.exe -db -instanceName MSDPM2010

Full Script

@Echo off SET LOGFILE="E:\Backup_folder\SQL Daily Backup\DPMSQLBACKUP.LOG" SET backupfile="C:\Program Files\Microsoft DPM\DPM\Volumes\ShadowCopy\Database Backups\DPMDB.bak" SET backuppath="E:\Backup_folder\SQL Daily Backup\" for /f "tokens=2,3,4 " %%x in ("%date%") do set dstamp=%%z%%y%%x for /f "tokens=1,2,3 " %%x in ("%time%") do set tstamp=%%x%%y%%z ECHO ===================================================================>> %LOGFILE% ECHO %dstamp% - %tstamp% >> %LOGFILE% ECHO ===================================================================>> %LOGFILE% DpmBackup.exe -db -instanceName MSDPM2010 >> %LOGFILE% if not exist %backupfile% goto NOTEXIST IF Exist %backuppath%\DPMDB_05.* del %backuppath%\DPMDB_05.* IF Exist %backuppath%\DPMDB_04.* ren %backuppath%\DPMDB_04.* DPMDB_05.* IF Exist %backuppath%\DPMDB_03.* ren %backuppath%\DPMDB_03.* DPMDB_04.* IF Exist %backuppath%\DPMDB_02.* ren %backuppath%\DPMDB_02.* DPMDB_03.* IF Exist %backuppath%\DPMDB_01.* ren %backuppath%\DPMDB_01.* DPMDB_02.* IF Exist %backuppath%\DPMDB.* ren %backuppath%\DPMDB.* DPMDB_01.* ECHO. >> %LOGFILE% copy %backupfile% %backuppath% /Y >> %LOGFILE% GOTO END :NOTEXIST ECHO DPMSQL DB BACKUP does not exist!!! >> %LOGFILE% :END ECHO ===================================================================>> %LOGFILE% ECHO. >> %LOGFILE% ECHO. >> %LOGFILE%

Six copies will remain on this location. DPMDB.bak is the most recent DPM SQL Backup and DPMDB_05.bak is the oldest backup file.

DPM 2010 SQL (Reporting) service crashes

Checking the event log of the DPM server I found the following errors.

----------error 1------------------
Log Name:      Application
Source:        Report Server Windows Service (MSDPMV3BETA1EVAL)
Date:          5/22/2010 5:55:58 PM
Event ID:      139
Task Category: Startup/Shutdown
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      xxxxxxx.c2ict.com
Description:
The application domain WindowsService_0 failed to start. Error: Microsoft.ReportingServices.Diagnostics.Utilities.EvaluationCopyExpiredException: The evaluation period for this instance of Microsoft SQL Server Reporting Services has expired.  A license is now required.
----------error 1------------------

----------error 2------------------
Log Name:      Application
Source:        Report Server Windows Service (MSDPMV3BETA1EVAL)
Date:          5/22/2010 5:55:58 PM
Event ID:      136
Task Category: Activation
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      xxxxxx.c2ict.com
Description:
The evaluation period for this instance of Microsoft SQL Server Reporting Services has expired.  A license is now required.
----------error 2------------------

----------error 3------------------
Log Name:      Application
Source:        Report Server Windows Service (MSDPMV3BETA1EVAL)
Date:          5/22/2010 5:55:57 PM
Event ID:      107
Task Category: Management
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      xxxxxx.c2ict.com
Description:
Report Server Windows Service (MSDPMV3BETA1EVAL) cannot connect to the report server database.
----------error 3------------------

The service manager shows also that the SQL and SQL reporting services could not start and stopped

Checking the installed SQL instances shows that there is a second instance still active from the previous (beta) version of DPM 2010.

This instance can successfully be removed using the remove program wizard in the control panel.
Select remove.

 

Select the old/expired instance

Backing Up DPM by Using a Secondary DPM Server

In addition to my Prepare for a DPM server disaster post that describes how to protect the DPM server database locally on disk or tape. Now a link to the TechNet article that describes how to protect the DPM server by using a second server http://technet.microsoft.com/en-us/library/bb795680.aspx

Prepare for a DPM server disaster

The DPM SQL database (DPMDB) is the most crucial part of your DPM installation. The database contains all your protection group information and tape\disk catalogs.
When this database gets corrupted (believe me this can happen), you will loss all this configuration data.

When you have a single DPM server implementation, you can protect this database in two ways.

• Make a backup\Dump to disk: dpmbackup command
• Make a backup to tape: Create a protection group to include the DPM database

I would recommend to use both techniques to protect your DPM databases.The disk solution allows you to recover the DPM quickly when it gets corrupted. The backup to tape allows you to recover the configuration when the full DPM server has become unavailable.

Make a DPM database backup to disk

• Use the command DPMbackup –db to create a dump of the database to the folder %program files%\Microsoft Data Protection manager\DPM\Volumes\ShadowCopy\Database Backups.
• When you schedule this command to run frequently,it is recommended to save multiple versions of the database. With the DPMbackup –db command you cannot specify a alternative location for the database backup. Therefore it is recommended to copy your database dump to an alternative location. This will prevent you from overwriting a good working copy with a more recent corrupt version.

Restore a DPM database from disk

Before you start:

1. Secure the current DPMDB.BAK database backup, by renaming it: %program files%\\Microsoft DPM\DPM\Volumes\ShadowCopy\Database Backups
2. Next, create a backup of the running database in DPM  with the following command: dpmbackup –DB Also rename this DPMDB.BAK file to DPBDB_before_restore.BAK

Restore database:

1. Use DPMSync to attach the database to DPM.
• DPMSync –RestoreDB –DBLoc (location and name of the database secured in step 1 of before you start) When this database is also corrupt you can use a previous saved version of the database
• DPMSync takes the DPM service offline and attaches the backed up database to SQL Server.
2. Run the command DpmSync –sync
• This will sync the database with the available recovery points
3. Start DPM 2007 Administrator Console. You now have some protection errors to resolve.
4. Secure now the working DPM DB running: DPMBACKUP –DB from the DPM server

Make a DPM database backup to disk

Enable local protection (When using the local SQL installation): 

• Starting with DPM 2007 SP1 it is possible to backup the local files of the DPM server.
• To enable it you need a powershell command (DPM Management Shell): Set-DPMGlobalProperty –AllowLocalDataProtection $true

Protect SQL database:

• Create a new protection group
• Select the local DPM server and select under SQL the DPMDB
• Use long-term protection using tape
• Specify the protection schedule

Recover a DPM database from disk
This is a not the most pleasant solution, since you have to uninstall and reinstall DPM2007. You should only use this when the recovery from disk is not possible

1. create a backup of the running database in DPM  with the following command: dpmbackup –DB ,rename this DPMDB.BAK file to DPBDB_before_restore.BAK
2. Place the tapes with the most recent DPM database backup
3. Uninstall Microsoft System Center Data Protection Manager 2007 through the Add/Remove Programs applet in Control Panel.

   Important

   Remember to select Retain disk-based recovery points.

4. Restart the computer.
5. Delete the DPM database using SQL Server Management Studio.
6. Install DPM 2007.
7. Restart the computer
8. Install the same level of Service pack and latest hotfixes
9. Restart the server again
10. Secure the now clean Database
• Run the command dpmbackup –DB
11. Open the Libraries tab on the Management tab of DPM Administrator Console.
12. Click Rescan on the Actions pane. Go to Updating Tape Library Information for more information on using the Rescan option.
13. Select the library from the list and click Inventory libraries… and perform a detailed inventory from the Actions pane.
14. Select the imported tape from the list and click Recatalog imported tape on the Actions pane.
• Do this for all tapes on which the DPM database resides
15. Open the Recovery tab from DPM Administrator Console.
16. Select the DPMDB by expanding the External Tapes node.
17. Click Recover on the Actions pane.
18. Restore the database files to a separate folder
19. All DPM and SQL services must be stopped.
20. Rename the following Database files .\DPMDB:
• a) DPMDB2007RTM.mdf to DPMDB2007RTM.mdf.old
• b) DPMDB2007RTM_log.ldf to DPMDB2007RTM_log.ldf.old
21. Copy the restored database files to the location .\DPMDB
22. Start first the SQL services, next start the DPM services
23. When all services are started again:Run the command DpmSync –sync
• This will sync the database with the available recovery points
24. Start the DPM administrative console
• If this works > DPM should be up, you now have some protection errors to resolve
• If this not work try step 15 to 23 for with the two other versions of the database

***Secure the now working database Run the command dpmbackup –DB

Upgrade DPM integrated database to SP3

Microsoft has released Service Pack 3 for SQL2005. When running the Service Pack 1 for DPM2007 you can upgrade your integrated database to SP3 for SQL2005.
More info