What DBA’s need to Know about BizTalk Server

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.

1. Make sure that all the Jobs configured and enabled and running properly.
2. Do not forget to enable the jobs after any sort of maintenance.
3. Make sure sufficient database storage is available for the backups.
4. 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.

How much do you invest on a BizTalk developer for hardware and software?

Recently, I had to analyze and provide per yearly cost incurred by the company to setup the Microsoft BizTalk Server development environment for its developers.

Assuming that each developer would be provided with individual virtual machine to remote in and work, below table depicts the costs that would typically incurred for “per developer per server”. All numbers below are approximation.

Software Cost – MSDN License for Visual Studio Professional. Includes Operating system, SQL Server and BizTalk Server	$1200 for 1st year. $799 recurring.
Hardware cost	$2000 / Server
Recurring Server maintenance cost	$600 / Year
VM maintenance cost	$480 /Year
Total	Appr. $4280 / Developer per year

 

MSDN Subscription chart is found below or in the following link : http://msdn.microsoft.com/en-us/subscriptions/buy.aspx

 

Note:-

TFS – Team Foundation Server is included in the MSDN subscription, so you would not have to buy a separate license for the developers, who are already subscribed for MSDN.

For Non-developers, such as PMs, Bas, you need to purchase a CAL license individually or on volume licensing, which may typically cost around $300-400

 

Microsoft licensing white paper regarding TFS states as below.

“Visual Studio Team Foundation Server 2012 can be obtained using MSDN subscriptions. Visual Studio Ultimate with MSDN, Visual Studio Premium with MSDN, Visual Studio Professional with MSDN, and Visual Studio Test Professional with MSDN subscribers can download and deploy one instance of Team Foundation Server 2012. These same MSDN subscribers are granted a Team Foundation Server 2012 CAL to be used within their organization (it is not valid for use of Team Foundation Server acquired by a different organization). ”