So, this bring up a funny discussion. How to teach your DBA the way BizTalk databases works! I’ll split up the different questions with my answers. You often end up in some sort of disagreements with DBAs, they sure know SQL and will be a good friend when you have trouble but they might not know that much about BizTalk databases, I hope these questions will help you out when you get any of the below questions from a DBA.
We already have a predefined Backup routine for BizTalk, we don’t need the BizTalk Backup job, agree?
Well first of all its good to know that you have routines for backing up SQL databases, but when it comes to Microsoft BizTalk it has to be the BizTalk Backup job, simply because the way BizTalk backs up its own database, it goes through “6 steps”,
Step 1 indicates that the BizTalk Backup job starts, by telling the databases and BizTalk that it needs to set a transaction mark, Step 2 Sets the transaction mark, Step 3 Takes a full backup Step 4Backup is completed and everyone is happy
Note Third-party backup routines are not supported by Microsoft which means if you have a meltdown of some kind Microsoft will not support you, and the biggest risk is that you might not have a valid backup of your environment. Use the BizTalk Backup Job provided with BizTalk.
The BizTalk Monitor job creates a lot of errors for us, can you resolve it?
The BizTalk Monitor job is created to mainly monitor our databases, it looks for any errors or failed messages due to missing references etc. If this job fails it means it has located some errors in our database, it is important that we get this information so the problem can be resolved. However some issues may not be resolved within a minute because some of these errors requires us to stop BizTalk completely, we need to schedule it according to our maintenance plan.
Some of your BizTalk jobs take a very long time to finish, can you resolve it?
This might happen in occasion, we might have had a huge load of messages for a short period of time making the different SQL Agent jobs take longer to finish of, however it may also be other reasons, our databases might be big so that the jobs takes a longer time to finish off, or we had a stop that built up a large amount of messages that needed processing which again helped the BizTalk databases to grow. If this doesn’t calm down after a while please inform me again so that I can continue to investigate the issue. Either way I’ll look into it.
Your database suddenly increased in size then decreased again, you need to resolve this, okay?
Well no and yes, this might happen for a numerous of reasons, a great load of messages came it, we might have had a short stop and some queues building up, The BizTalk databases processes a lot of messages.
The first is when the messages is received into BizTalk, the messages and references are created in the Messagebox, but as soon as the messages leaves BizTalk the messages and its references in the messagebox is deleted. (this is a rough image, and a lot more is really happening, but this is an easy overview and explanation).
We should set BizTalk Messagebox to auto create and update statistics to improve performance, okay?
No, that is not okay. BizTalk might not be smart but it sure is logic. All queries know approximately how many rows it will have returned, how long it will take and the type of data received. Therefor the statistics will slow down BizTalk instead of actually increase performance for it.
I don’t see the needs for BizTalk to run on its own SQL server, so why do you need it?
BizTalk connects our systems together to make integration easier between different systems, BizTalk may also be sending files to partners or other vendors outside of our company. Some of this information is extremely important and processing must be fast and run smoothly. BizTalk utilize SQL to processes and store information about messages and transactions. There is a lot of information grabbed from the database, both the management database, tracking database, sso and the Messagebox database. The resources available should be dedicated to BizTalk so we can ensure our managers, clients etc. that we delivery a product and have a steady flow. Sharing databases can create more connection by other servers, systems and services they may utilize a lot of resources, space and other elements that can significantly slow down BizTalk processing and in a worst case scenario completely stop BizTalk
The SQL servers cluster is dedicated to SQL not anything else, like the master secret key, okay?
Well, that sure is good to know, and the approach is correct however as I’ve mentioned before BizTalk SQL is not like regular SQL databases. It is strongly recommended to have the master secret key on the BizTalk SQL cluster in order to ensure high availability for our environment. The master secret key is used to get the “password/key” for the SSO database. If the master secret key is unavailable BizTalk will stop, the same goes if the SQL database is unavailable. So there is three ways to resolve this, however only one of them is recommended as best practice and the one to provide you with the highest available environment.
Scenario 1 (Best practice and recommended):
Install the master secret key on the SQL cluster together with the BizTalk SQL databases.
Scenario 2 (Not recommended)
Install the master secret key on a BizTalk server og BizTalk SQL cluster (if this is unavailable BizTalk will stop, the same if the SQL is unavailable)
Scenario 3 (Not recommended)
Install the master secret key on its own cluster (if this cluster is unavailable BizTalk will stop the same goes for the SQL)
Why do you need access to the SQL server?
Basically because the SQL database for BizTalk is important in order for it to work, I need the access in order to monitor my installation. The requirements for BizTalk at our company makes me responsible to make sure that we have enough resources and that the integration platform is running smoothly, I may need to log in to check the performance monitor, event logs and different aspects at the SQL server. We might have to run test and similar in order to make sure our platform is working accordingly to our service level agreement. This is a job that needs constant monitoring and it’s therefore important for me to have this access, however if you have other ways for me to monitor this or you have a few hours to help me out with weekly schedule tasks its okay for me, but I need at least access to the event log, SQL Agent jobs, the databases through the SQL management Studio.
If you have any other questions please let me know so I can update this post! Happy BizTalkin’ !
Updated the first question to make it more correct and easier to explain to others..