When setting up a BizTalk 2010 Development Environment on a Workstation or VM, sometimes BizTalk 2010 Developers forget to configure the BizTalk 2010 SQL Agents.
There are two SQL Agents Jobs which need to be configured and running.
- Backup BizTalk Server (BizTalkMgmtDb)
- DTA Purge and Archive Job
- You could eventually run out of disk space
- Overall Performance will be degraded
- Timeouts when using the BizTalk Administration Console
- Timeouts when testing your application
- Deploying from Visual Studio 2010 could timeout
Recommendation:
Create folders to store the Backup and Archives. Preferably this should be on a secondary drive.
- Assuming you has a secondary drive D:, create a new folder named “D:\SQLBackUp”.
- Add three new folders; “Data”, “Logs”, and “Tracking”, under D:\SQLBackUp
| SQLBackUp |
| SQLBackUp\Data |
| SQLBackUp\Logs |
| SQLBackUp\Tracking |
Configuring the Agents
Edit the SQL Server Agent job Backup BizTalk Server (BizTalkMgmtDb).
1. In Microsoft SQL Server Management Studio, double-click SQL Server Agent, and then expand Jobs.
2. In the Object Explorer pane, right-click Backup BizTalk Server (BizTalkMgmtDb), and then click Properties.
3. In the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box, under Select a page, click Steps.
4. To configure backup compression:
a. In the Job step list, click Set Compression Option, and then click Edit. Set the Command as shown below:
exec [dbo].[sp_SetBackupCompression] @bCompression = 1 - Use Compression */
b. Click OK.5. In the Job step list, click BackupFull, and then click Edit.
exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'D:\ SQLBackUp\Data' /* location of backup files */, 0 (default) or 1 /* ForceFullBackupAfterPartialSetFailure
6. Click OK.
7. In the Job step list, double-click MarkAndBackupLog to open the Job Step Properties – MarkAndBackupLog screen.
8. On the General page, in the Command box, replace '<destination path>' with the full path (including single quotes) to the computer and folder where you want to store the BizTalk Server database logs and then click OK.
a. Set the destination path to D:\ SQLBackUp\Logs.9. In the Job step list, click Clear Backup History, and then click Edit.
10. On the General page, in the Command box, change DaysToKeep=<number> to the number of days you want to keep the backup history, and then click OK twice to close the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box.
a. Set the <number> to 311. Click OK
Change the Backup Schedule
1. On the General page, in the Command box, change DaysToKeep=<number> to the number of days you want to keep the backup history, and then click OK twice to close the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box.2. In the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box, under Select a page, click Steps.
3. In the Job step list, click BackupFull, and then click Edit.
4. In the Job Step Properties - BackupFull dialog box, in the Command box, edit the command by changing the frequency to the desired interval at which to perform a full backup: 'h' (hourly), 'd' (daily), 'w' (weekly), 'm' (monthly), 'y' (yearly), and then click OK.
a. Use 'd' (daily)5. In the Job Properties - Backup BizTalk Server (BizTalkMgmtDb) dialog box, under Select a page, click Schedules.
6. In the Schedule list, click MarkAndBackupLogSched, and then click Edit.
7. In the Job Schedule Properties - MarkAndBackupLogSched dialog box, in Schedule type, select Recurring from the drop-down list box (if it is not already selected).
a. Set the job to run every 60 minutes8. Click OK.
Edit the SQL Server Agent job DTA purge and archive job.
1. In the Object Explorer Details pane, right-click DTA Purge and Archive (BizTalkDTADb), and then click Properties. 2. In the Job Properties - DTA Purge and Archive (BizTalkDTADb) dialog box, under Select a page, click Steps.
3. In the Job step list, click Archive and Purge, and then click Edit.
4. On the General page, in the Command box, edit the following parameters as appropriate, and then click OK.
exec dtasp_BackupAndPurgeTrackingDatabase 1, 0, 1, ' D:\ SQLBackUp\Tracking', null, 0
5. On the Job Properties - DTA Purge and Archive (BizTalkDTADb) dialog box, under Select a page, click General, select the Enabled check box, and then click OK.
Deleting the backups
Since this environment is used for Development and local testing, restoring the backups is unnecessary. The recommendation is to create a Windows Command File to delete the backup and log files. You would then create a new Windows Task Event to run the Command File.Creating the Windows Command File
1. Open either Notepad or your Text Editor and create a new file. 2. Enter the following:
DEL /Q /F “D:\SQLBackup\Data\*.*”
DEL /Q /F “D:\SQLBackup\Logs\*.*”
DEL /Q /F “D:\SQLBackup\Tracking\*.*”
3. Save the file as “D:\DeleteBTSBackups.cmd”
Creating the Windows Task
1. Open “Task Scheduler”.
2. In the Action Menu, Select “Create Basic Task”.
3. Enter the Name and Description as shown below 
4. Click Next.
5 For the Trigger select “Daily”.
6 Click Next.
7 Set the Start Date to Today
8. Set the Start Time to 12:00.00 PM
9. Set Recur every 3 days.
10. Click Next.
11. Set the Action to “Start a Program”.
12. Browse to the “D:\DeleteBTSBackups.cmd”
13. Click Next.
14. Check the “Open Properties” as shown below: 
15. On the General Tab, set the Security Options
16. Change the User\Group to run under the Administrator credentials.
17. Select Run whether user is logged on.
18. Check “Run with highest Privileges
19. Select Configure for: Windows 7, Windows Server 2008 R2 as shown below.
20. On the Settings Tab, configure the settings as shown below. 
21. On the Conditions Tab, accept the default settings.
22. Click OK.
It’s hard to find knowledgeable people on this topic, but you sound like you know what you’re talking about! Thanks
ReplyDelete