One of the components of BizTalk360 is a SQL Server database. This database is used for all kind of configuration like user permissions and all the monitoring settings. When that data got corrupted or lost, you would have to do all the configuration all over from scratch. To prevent this from happening, you should frequently take backups of that database. Besides creating manual backups, you can also have these backups being created automatically.
There are two different approaches to making automated backups of this database, both are shown below:
- Create a SQL Server Maintenance Plan
- Extend the Backup BizTalk Server job
The difference between these 2 methods is, that with the Maintenance Plan approach you’ll have a backup which is not in sync with the backup of your BizTalk databases, while with the latter option your BizTalk360 backup will be in sync with the BizTalk backups. This could make restoring your databases in one go easier.
In a previous post, Rochelle has already explained how to create a Maintenance Plan to take care of the BizTalk360 database backups. In this article, we’ll explain how to add the BizTalk360 database to the Backup BizTalk Server job.
Adding custom databases to the BizTalk Server Backup job
What we basically are going to do is using a feature from BizTalk Server. As you probably are aware of, BizTalk Server contains multiple databases and to be able to restore them in sync, the backup needs to be created in sync. The only by Microsoft supported way to create such backups, is by using the Backup BizTalk Server job, which is a SQL Server Agent job. You can read more about that topic in the below articles:
BizTalk users can extend the backup job with other databases which are considered important to the integrations which are deployed in BizTalk Server. This is exactly what we will be doing with the BizTalk360 database.
The process exists of the following steps:
- Prepare the BizTalk360 database
- Add the BizTalk360 database to the BizTalk Server backup job
- Start making backups
Let’s take these steps one by one and have that database added to the BizTalk Server backup job!
Prepare the BizTalk360 database
In this first step, we’ll make sure a table and some Stored Procedures will be created in the BizTalk360 database. The table which becomes created is called MarkLog. You will find this table in all the databases which are being backed up via the BizTalk Server backup job.
Perform below steps to create that table and the needed Stored Procedures:
- Open SQL Server Management Studio and connect to the SQL Server instance which contains the BizTalk360 database
- Click Open File, navigate to folder C:Program Files (x86)Microsoft BizTalk Server 2016Schema and select query Backup_Setup_All_Tables.sql
- From the Databases dropdown, select the BizTalk360 database
- Click the Execute button or hit F5 to execute the script. If the database has been created successfully, you can proceed with the next step
- Click Open File, navigate to folder C:Program Files (x86)Microsoft BizTalk Server 2016Schema and select query Backup_Setup_All_Procs.sql
- If not yet selected, select the BizTalk360 database from the Databases dropdown
- Click the Execute button or hit F5 to execute the script
If both SQL scripts have been executed successfully, an important part of the configuration has been completed. The BizTalk360 database is ready and in the next step, it will be added to the BizTalk Server backup job!
Important: Ensure yourself that the BizTalk360 is in Full Recovery Model, otherwise the backup will fail! You can check this by:
- Right-click the database then select Properties
- Select Options
- Check if the Recovery Model is set to Full
Add the BizTalk360 database to the BizTalk Server backup job
In the previous step, we prepared the BizTalk360 database to be able to be backed up by the BizTalk Server backup job. In this step, we’ll make sure that that database becomes added to a table in BizTalk Server’s management database, which will make sure that the database will be picked up by the BizTalk Server backup job.
Follow below steps, to make sure that the BizTalk360 database will be picked up by the BizTalk Server backup job:
- In SQL Server Management Studio, connect to the SQL Server instance which contains the BizTalkMgmtDb
- In the Object Explorer, expand the Databases, BizTalkMgmtDb, Tables and find the dbo.adm_OtherBackupDatabases table
- Right-click that table and, from the menu that appears, select Edit Top 200 Rows. As you are in Edit mode, you can add a new row which will contain the information about the BizTalk360 database.
- In the last row, which now only shows NULL values, enter below values
- DefaultDatabase: BizTalk360
- DatabaseName: BizTalk360
- ServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
- BTSServerName: <Name of the SQL Server Instance which contains the BizTalk360 database>
- Hit Enter to save the record in the table
The BizTalk360 database is now part of the BizTalk Server backup job. The last step we need to do is forcing a full backup, to make sure that also incremental backups can be created.
Start making backups
We are almost there; we have seen how the BizTalk360 database has been prepared to accommodate the BizTalk Server backup job. In the previous step, we have added the BizTalk360 database to the BizTalk Server backup job. In this last step, we will force a full backup, to make sure that after that, also incremental backups can be created. By default, a full backup will be created once every 24 hours; Incremental backups will be created, by default, every 15 minutes.
Perform below steps to force a full backup:
- In SQL Server Management Studio, under the BizTalkMgmtDb, expand Programmability and expand Stored Procedures
- Scroll through the Stored Procedures until you have found sp_ForceFullBackup
- Right-click that Stored Procedure and, from the menu that appears, select Execute Stored Procedure… As the Stored Procedures doesn’t need any parameter values, just click OK
- If the Stored Procedure has been executed successfully, the next time the BizTalk Server backup job runs, it will perform a full backup
There are a couple of ways to check whether the backups are really being created. You can:
- Check the output of the BizTalk Server backup job (in SQL Server Management Studio)
- Check if the backup files have been created (in Windows Explorer)
Check the output of the BizTalk Server backup job
To perform this check, perform these steps:
- In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
- Next, expand SQL Server Agent
- Right-click the Backup BizTalk Server job and select View History
Check if the backup files have been created
To check the availability of the backup files, you firstly need to check where these files are located. Follow the below steps, to find that location and then check the actual location:
- In SQL Server Management Studio, you need to navigate to the SQL Server instance which contains the BizTalk Server backup job
- Next, expand SQL Server Agent
- Double-click the Backup BizTalk Server job
- In the Job Properties dialog which appears, under Select a Page, select Steps
- Now, under Job step list, double click BackupFull
- In the Job Step Properties dialog, at Command, scroll to the right to find the backup path
- Copy the backup path and close all dialog screens
- Next, open a Windows Explorer and paste the backup path in the Address bar
Now the backup files should show. Although we only checked the backup path for the full backup files, this folder might also contain the backup files of the incremental backups.
The BizTalk360 database contains valuable information about, amongst others, your monitoring configuration and the people who have access to BizTalk360. If in case of a disaster, you need to easily restore a backup of your BizTalk360 database, you need to have a recent backup of that database. The BizTalk Server backup job creates such backups. In this article, we have seen how to extend the BizTalk Server backup job to incorporate the backup of the BizTalk360 database.
The post Backup your BizTalk360 database via the BizTalk Backup job appeared first on BizTalk360.
Some clients have restricted rules regarding what to install content in the C (the default) hard drive. For some of them, C drive is just for the operating system and other component related to the operating system. All the rest should be installed or kept in different hard drives.
The goal of this post is not to say if that is the proper and best approach or not is just to document this error and know the reason and what to do. Personally, I usually install BizTalk Server in C (the default) hard drive, that is why I never encounter this error/warning before.
So, during one of my recent installations, where I have the need to install and kept all BizTalk Server installation and configuration components on a non-default (C:), I encounter the following error message while trying to configure the local of the Backup file location of the SSO Master Secret Key:
TITLE: Microsoft BizTalk Server Configuration Wizard
The backup file could not be created. (SSO)
For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2016&ProdVer=3.12.774.0&EvtSrc=SSO&EvtID
(0x80070003) The system cannot find the path specified.
For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2016&ProdVer=3.12.774.0&EvtSrc=Win32&EvtID
I don’t know if this can be considered an error or actually a bug in the BizTalk Server Configuration Wizard because, for me, the wizard should be responsible for creating the specified path.
By default, the SSO Master Secret Key Backup file location is set as C:Program FilesCommon FilesEnterprise Single Sign-On with the following name structure “SSO****.bak”:
- where **** is a randomly generated name by the BizTalk Server Configuration Wizard.
The BizTalk Server Configuration Wizard has an option for you to open an annoying Folder Browser Dialog windows that force you to iterate through a Directory Tree (you cannot manually set the path) instead of using a more fancy, elegant and practical dialog box or way to archive the same result, like, suing SaveFileDialog component instead
So, if you try to manually set the path according to your requirement, for me it was just changing the hard drive letter from C: to E: you need to be sure to first manually create the full folder path on the desired destination hard drive, otherwise, you will get this problem.
Make sure the folder path is created on the desired hard drive, if not create it before you specify the path in the BizTalk Server Configuration Wizard tool.
If you already specify the path in BizTalk Server Configuration Wizard, that is, before you have created the folder:
- Create folder path on the desired hard drive
- And refresh the Enterprise SSO Secret backup page
and the backup file could not be created. (SSO) error message will be gone.
Think about all the information that is stored in your BizTalk360 database – Alarms, Knowledge Base, various Settings. This data is very important for many reasons. Now imagine if all of the information just disappeared.
Although it is a scary thought, it is highly unlikely that your company is not backing up your database. Let’s talk about how you can setup a Database Maintenance Plan.
Continuing with the Support Series to discuss common occurring issues with our customers and how others can benefit from the knowledge gained when we helped them resolve the same I will now focus on 1 key issue – Maintenance and Backup of your BizTalk360 database.
Customer just found out that the SQL transaction logfile is taking enormous amount of space
If you check your SQL, the SQL transaction log file of our BizTalk360 database is very large (over 19GB).
The quick Support reply in this scenario would be to refer to this article and resolve the issue by Releasing unused space and forcing a backup.
Identifying the Issue
The transaction log grows to be inordinately large on a database that’s in FULL or BULK_LOGGED recovery mode. This happens after a database backup has been taken, which switches the log into a mode where it won’t truncate until it’s been backed up.
In these circumstances, if you don’t take a transaction log backup, the log will continue to grow.
Our purging policies in BizTalk360 are as follows (defaults):
BizTalk360 purging will not stop the transaction log files from growing. In order to maintain healthy BizTalk360 Database please ensure you have maintenance plans configured:
One to backup BizTalk360 Database and Log every week and a second one to delete backup files which are older than 2 weeks.
You may also face this problem when you configured quite a large number of alarms and these alarms are monitoring a large number of artifacts.
Setting up BizTalk360 Database Maintenance Plan – Backup
I am using the Microsoft SQL Server Management Studio.
- Select the Maintenance Plan Wizard.
- Select Single Schedule and then Click the Change Button.
- You can select probably a backup of your BizTalk360 database every week – on a day when you are not expecting too many transactions.
- Select the Back-up Tasks Tick boxes.
- Select the BizTalk360 Database for each Screen (Full), (Transaction) backup.
Setting up the BizTalk360 Database Maintenance Plan – Clean-up Task
Now we need to do the setup for a Clean-up Task. We suggest that you can make this run every 2 weeks, and we need to select the Clean-up Task instead of Backup in the Maintenance Wizard.
Lastly just provide the details for deleting the files (File Age) as shown below.
NOTE: Make sure your SQL Server Agent is running on the server.
You can adjust the schedule depending on a load of your environment.
So I hope this Blog has given you some helpful information to ensure that your Transaction Log size doesn’t increase when the backups and clean-up Tasks are managed and run properly with BizTalk360.
If you have any questions, contact us at firstname.lastname@example.org. Also, feel free to leave your feedback in our forum.
During my sessions about BizTalk Server Tips and Tricks, I normally ask: What RosettaNet, ESB or UDDI have in common? And the answer is: they all are BizTalk optional features that are not part of the primary installation process, you need to execute “secondary” installation processes to add theses features. These installation processes will create BizTalk custom databases for supporting all of these new optional features. But the big questions here are: do you think that these databases are being backed up? And if not, how to backup (other) BizTalk Custom Databases?
Do you think that these databases are being backed up?
To respond this first question, the answer is: No!
Because these BizTalk custom databases (we are calling “custom databases” because they are supporting optional features that are not part of the primary installation process) are not installed by default with BizTalk Server, they are not included in the default list of databases to be marked and backed up by the Backup BizTalk Server job. The default list of databases that are, normally, being backed up by the Backup BizTalk Server job are:
How to Backup (other) BizTalk Custom Databases?
If you want the Backup BizTalk Server job to back up these additional BizTalk custom databases, you must manually add the databases to the Backup BizTalk Server job.
You can achieve this by:
- Taking Windows Explorer and browse to the “Schema” directory on the BizTalk installation folder, normally:
- C:Program Files (x86)Microsoft BizTalk Server <version>Schema
- Run “Backup_Setup_All_Tables.sql” and next “Backup_Setup_All_Procs.sql” against all your BizTalk custom databases that you want to back up. This creates the necessary table, procedures, roles and assigns permissions to the stored procedures.
- After that you need need to modify the adm_OtherBackupDatabases table, in the BizTalk Management (BizTalkMgmtDb) database, to include a row for each of the new BizTalk custom databases
- Type the new server and database names in the corresponding columns, as shown in the following tab
- DefaultDatabaseName: The friendly name of your custom database.
- DatabaseName: The name of your custom database.
- ServerName: The name of the computer running SQL Server.
- BTSServerName: The name of the BizTalk Server. This value is not used, but it must contain a value nonetheless.
To complete the process, you, mandatory, need to force Backup BizTalk Server (BizTalkMgmtDb) job to perform a full backup of the databases, otherwise you will receive the following error:
- BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013)
To do that you need:
- Execute the “sp_ForceFullBackup” stored procedure present in the BizTalkMgmtDb database.
The next time you run the Backup BizTalk Server job, it will back up all your BizTalk custom databases.
Note: I will not recommend you to add any of your application support custom databases to the Backup BizTalk Server job since they may interfere with the execution times of this job. If the Backup BizTalk Server job starts to take a long time to execute, it may also affect the overall performance of the BizTalk platform.
Stay tuned for new BizTalk Server Tips and Tricks!
Check out the first blog of the series BizTalk Server Tips and Tricks: Enabling BAM Add-In for Excel 2016.