Welcome again to another BizTalk Server Best practices, Tips, and Tricks blog post! In my previous blog posts, I discussed some essential tips and tricks for BizTalk Server administrators:
Today we are going to switch and speak about another critical Best practice, Tips and Tricks, this time for BizTalk Server administrators: Delete BizTalk Database Backups files from the local drive.
#17 Delete BizTalk Database Backups files from the local driver
The BizTalk Server databases and their health are crucial for a successful BizTalk Server database messaging environment. This is nothing new, and everybody knows!
But there are two main things that we must understand and be aware of, especially the database administrators:
The execution of the BizTalk Server SQL Agent jobs is crucial for managing the BizTalk Server databases and maintaining optimal performance.
The Backup BizTalk Server job is the only supported method way to back up the BizTalk Server databases. All of the BizTalk Server databases must be configured to use the SQL Server full recovery model.
You also need to be aware that you can back up your databases and log files to:
A local or shared folder.
To an Azure blob storage account (feature available starting with BizTalk Server 2016 Feature Pack 2)
We also have another job available called DTA Purge and Archive, which also can archive data to your local or shared folder.
However, despite BizTalk Server out-of-the-box providing everything you need to maintain the well-being of your BizTalk Server platform, by default, BizTalk Server doesn’t provide functionalities for deleting backup files accumulated over time on your file system.
And most of the time, we find our BizTalk Server Backup BizTalk Server jobs to be configured to backup the databases and logs files to our local hard drive.
For performance capabilities, the Backup BizTalk Server job runs each 15 minutes, doing a:
Full backup of each database one time per day.
Every 15 minutes, a backup of the transaction log of each database.
It is fairly easy to see that if we don’t apply a good maintenance policy for these backups, we will end up with problems. Trust me, it is quite normal to forget to create a “process” or a “job” to accomplish this until it is too late. The result is… lots of times, we just remember when disks are full, and everything stops working!
What would be the easy and simple way to create a maintenance policy for these backups?
Well, you can apply different approaches depending on your tools and systems. I personally like a SQL Server Maintenance Plan to clean BizTalk Database backup files after a certain period of time.
To create the maintenance plan, you need to:
Open the SQL Server Management Studio by choosing Start > Programs > Microsoft SQL Server [edition] > SQL Server Management Studio.
Expand the server and then the Management folder.
Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard, and you can now step through and create a plan customized to meet your maintenance requirements.
On the SQL Server Maintenance Plan Wizard page, click Next.
On the Select Plan Properties page:
In the Name: enter the name of the maintenance plan you are creating.
In the Description: enter a brief describe your maintenance plan.
In the Run as: specify the credential that Microsoft SQL Server Agent uses when executing the maintenance plan – leave the default.
Select Single schedule for the entire plan or no schedule to specify the recurring schedule of the maintenance plan.
Under Schedule, click Change…
Under Frequency, on the Occurs list, select Daily and in the Recurs every box, enter how often the job schedule repeats in days: 1 Day.
Under Daily frequency, select Occurs once at and specify the time of day when the job schedule should run: 12:00:00
Leave the default values for the rest of the properties and click OK.
Back to the Select Plan Properties page, click Next.
On the Select Maintenance Tasks page, select Maintenance Cleanup Task from the list and click Next.
On the Select Maintenance Task Order page, click Next to continue.
On the Define Maintenance Cleanup Task page, specify the following properties:
Under Delete files of the following type: select Backup files
Select Search folder and delete files based on an extension to delete all files with the specified extension in the specified folder
Under Folder: specify the path and name of the folder containing the files to be deleted.
Under File extension: Provide the file extension of the files to be deleted, by default, this is bak.
Select also Include first-level subfolders option if you want to delete the files also from first-level subfolders under the folder specified in Folder.
Select Delete files based on the age of the file at task run time and specify the minimum age of the files that you want to delete under Delete files older than the following property
Specify x Day
Click Next
On the Select Report Options page, click Next.
On the Complete the Wizard page, verify the choices made in the previous pages and click Finish.
On the Maintenance Wizard Progress page, verify if every action was successfully executed and then click Close.
No matter the approach you use, the most important is: if you are backing up the databases to a local hard drive, make sure that from day zero, you implement a strategy to delete old backup files from the drive.
After they have been backed up to the local drive, you can then move these files to a different location, like backup tapes. But don’t leave the files on your local hard drive.
For simplicity or safety, if you want, and if you have enough available disk space in the hard drive, you can leave, for example, the last 3 days or maybe even more in the local hard drive before having them cleaned up.
Stay tuned for the upcoming BizTalk Server Best Practices, Tips, and Tricks.
