As a BizTalk Architect, you would always come across a situation, where you need to convince the client’s DBA team about “BizTalk server database architecture and its nature in use of in-built jobs”.
Traditionally DBAs’ presume certain roles and responsibilities on any product (which has “database in the backend”) acquired by the company, while it is very good to do so. There are products like BizTalk server that gets shipped with the pre-scripted SQL jobs, which if configured properly, would save administration/DBA team from lots of fuss in terms of maintenance and optimization.
Before diving into, where DBA’s would need to get involved in-terms of BizTalk administration, let me explain, How BizTalk is different from other traditional applications
|Feature||BizTalk Server||Other Applications( with database backend)|
|Has Multiple Databases||Yes, Typically around 5, but may depend on BizTalk Configuration||Yes( Depends on application)|
|DB Tuning Required?||No (if configured correctly)||Yes, Depends on the transactions|
|Transactional Data||Transactional data in the Msgbox can be viewed more like a cache.Once the message completes Workflow, data has no significance||Transactional data may grow and may have to be retained for long term( Depends on the company policy)|
|Indexing Columns||Not needed, If the jobs are configured properly, the data is backed up correctly and the transactional database( MsgBox) is maintained to the minimum||Yes, as the table size increases, appropriate DB tuning needs to be applied.|
|Backup DBs||BizTalk Jobs, take care of it. These jobs do multiple things along with backing up the database.||Usually achieved through custom / SQL server backing up tools.|
|Can the backup be achieved without BizTalk Jobs?||Yes, But not worth the risk. These jobs are optimized to backup necessary DB and do other things.||DBA’s have full control; they can adapt the best for the solution.|
The differences being depicted in the table above, Let see, where the DBAs needs to get involved in terms of BizTalk administration.
- Make sure that all the Jobs configured and enabled and running properly.
- Do not forget to enable the jobs after any sort of maintenance.
- Make sure sufficient database storage is available for the backups.
- Maintain the overall health of the SQL server (if standalone) or SQL cluster (if clustered).
In conclusion, Though BizTalk uses SQL server backend, it is deemed to see the entire as a product setup and avoid digging deep and modifying the pre-scripted BizTalk jobs to include custom solutions.