BAM Alerts and cross-database ownership chaining issue

BizTalk offers up many surprises to me even after about 8 years of constant experience with it.  Today’s lesson to me was a permission error we were getting in the BAMAlertsApplication stating that our SQL Notification Service user no longer had permissions to SELECT data from some of the tables.  I had never run into this before … ever … not in 8 years.

After much digging, we determined that starting the BAM notification server was calling a stored procedure in the BAMAlertsNSMain database: NSGetApplicationActivationStatus.  This stored procedure was, in turn, querying off tables in BAMAlertsApplication such as NSProcessActivationStates.  So why was this previously working but no longer?  We looked at permissions granted to this service account and it never had SELECT permissions on these tables, only EXEC rights to the stored proc.  From past experience, I knew there was some sort of “loop-hole” in which a stored procedure could return results from a table the user did not have permissions to.  This is, apparently, called ownership chaining and to make matters worse this was doing a cross-database join.  The setting allows this only if the table, stored procedure and databases are all owned by the same user.

After poking around awhile in the database and server options we found an option called: “Cross-database ownership chaining” which was turned off for the server as well as for these databases. Consulting another BizTalk setup that was working I noticed that this setting was turned on for these two databases but no others.  As luck would have it, the DBA had just detached and re-attached the database files to move them to a different location and a side-effect of this is that this one particular setting as noted here.  Seriously, this is the ONLY side-effect of re-attaching database files.  Crazy.

To fix this you can either grant the service account correct access to all the tables, etc., in the database or restore this database option.  Of course, it would have been better if Microsoft properly setup permissions on these databases in the first place.  Oh … and make sure you don’t change the database owner to an account that does not also own all the objects in these databases.



WCF Web Service Latency on BizTalk 2010

I was recently working with a client to roll-out some WCF web services to BizTalk 2010 which access an Oracle database to facilitate the query (also using the WCF-OracleDb adapter).  As expected, we were getting some pretty serious latency issues (1-2 seconds per request) as BizTalk is tuned for throughput and not low-latency.  This, however, is not really an acceptable solution for web services.

To fix this, we needed to adjust some BizTalk tuning parameters to improve the latency.  Specifically, we looked at the polling interval for messages on a given host.  By default, it is set a 500 ms which means we’ll most likely have a MINIMUM of .5 seconds to process the request and most likely more as the message is sent to the orchestration, to the WCF-OracleDb adapter, and then back to the orchestration.

We decided to create a new BizTalk low latency in-process host to accomodate our web services.  Fortunately for us, BizTalk 2010 makes it even easier to set some of the previously registry-based on inaccessible tuning parameters.  After creating our host and host instances, it was pretty straight-forward:

  1. Open the Admin console, right-click on the BizTalk group and select settings
  2. Under the Hosts node, we selected our low latency host from the drop-down
  3. Now, under the Polling intervals we set both Messaging and Orchestration to 50 ms instead of 500 ms
    1. A better practice would be to have split receive, process, and send hosts and set the polling more specifically.   This was not required in our case.
  4. After selecting the host in the bindings (orchestration and send ports, not needed for the isolated receive port) and restarting we got the average response time down to 300 ms which puts us at the mercy of the Oracle databases response time. 

We also adjusted the internal message queue size for the host to ensure more messages can be kept in memory for even lower latency.

Take a look at this article for more specific details.


Getting off the ground with the WCF-OracleDB adapter in BizTalk

Time and time again I walk into a client which uses Oracle and I need to connect BizTalk to it.  And … time and time again I run into issues getting the correct Oracle client installed, then getting Visual Studio to pick that up and so forth.  Finally, I think I have arrived at a formula for getting things working.

Step 1: Install the WCF LOB Adapter pack

Ensure that you have installed at least the 32-bit version on a development workstation as well as the 64-bit version for any 64-bit environments.  The BizTalk 2010 installation program does a nice job of walking you through installing the SDK and then the LOB packs so I won’t go into any more details.

Step 2: Obtain and install the ODAC client

Technically, the adapter pack is compatible with an older 11g client but this will be taken care of via a slew of Publisher Policy’s that will “redirect” your client to the installed version.  To get the client you can search for ODAC1110720 or go to Oracle’s Windows download page and locate the ODAC client links and then the specific version listed above.  I honestly have not tested with the newer 11g clients but perhaps the same/similar process will work with them.

Step 3: Update the adapter pack’s assmbly binding

Locate the Microsoft.Adapters.OracleDB.Config file in the Adapter Pack’s install directory (c:\Program Files\Microsoft BizTalk Adapter Pack\bin) and add the following XML snippet in the <assemblyBinding…> element:

<assemblyIdentity name="Oracle.DataAccess" publicKeyToken="89b483f429c47342" culture="neutral" />
<bindingRedirect oldVersion="" newVersion=""/>

Now this will redirect all 11g calls to th 11.7.0 client version to the installed version.

At this point, the Add Generated Items->Consume Adapter Service wizard should work to connect to your Oracle server.  Depending on your standards, you may need to get a tnsnames.ora file in the correct location OR skip TNS resolution and use the server’s direct settings in the binding configuration.

Please leave a comment if this does (or does not) work for you!

BizTalk Schema Inheritance Practices / Examples

Frequently I find myself at clients explaining BizTalk best practices and one of the items I always try and push forward is proper schema inheritance practices.  Much like object-oriented design, schema inheritance can help create a nice canonical domain model.  However, there are always questions as to which type of inheritance to use.  While these practices go beyond just BizTalk, I wanted to focus this on implementation within the BizTalk tools.

Microsoft (and others) do put out documentation on this very subject but it doesn’t really go into any good examples of each type so I plan to plug that gap here.  The first few sections go into some background and other practices … if you are just looking for examples and practices for the given types feel free to skip ahead.

Types of Schema Inheritance

To summarize, there are three ways to re-use schemas within your BizTalk solution:

  • XSD Import – Importing types from another schema/namespace
  • XSD Include – Importing types from another schema within the SAME namespace
  • XSD Redefine – Importing types from another schema with the intent on extending/overriding the types

These all sound very similar in definition but there are some key usage scenarios for each in practice.

Organization of Schemas

Before going into the re-use of schemas it is important to think of re-use of your schemas beyond just your solution and extend into the greater collection of BizTalk applications at a given organization.  This becomes even MORE crucial in the realm of BizTalk when dealing with dependency, deployment and maintenance issues.  Without getting into too much detail, here are some quick pointers:

  • When creating canonical schemas to be shared with multiple BizTalk applications they MUST be isolated in their own project/assembly.
  • Treat yourself like a 3rd party vendor when consuming these schemas: add a reference to a well-known, strongly versioned DLL of these schemas
  • Remember that BizTalk only allows a given schema message type to be defined once in the entire BizTalk group so sharing these at the source code level won’t work
  • Track these central schemas as a separate project/lifecycle so that there is a clean separation between your consuming application and these soon-to-be highly shared schemas
  • Enforce schema versioning (especially in the namespace) from the beginning.  When you roll out an update you’ll not only have to version the .NET DLL but also the schema namespace
Creating ComplexTypes

The first step in re-using other schemas is to define the reusable schema itself (obviously) but then to create actual schema types (ComplexType) out of the bits to re-use.  This is the simple part: Simply select the “record” node which you want to re-use/share and set the DataStructureType property to the name you wish to use for the type.  The following screen shot shows the data structure type property.

BizTalk - Schema Data Structure Type

BizTalk - Schema Data Structure Type

It would be a good idea to define a naming standard/practice for these types … I generally use something like the node name plus the word type.  For example if I were creating an Order schema I would create a complex type named simply: OrderType.

The following sections define a typical usage scenario for each type of schema inheritance.

XSD Import – Composition

Frequently, you have the same type of information repeated throughout your projects and solutions.  A classic example is the Address.  When defining a schema like Order, you can have several addresses all containing the same fields but representing different locations.  For example, you may want to have a shipping and billing address in your order and ensure the addresses are consistent. This is very simple to accomplish:

  1. Create a schema which represents your address, and create a ComplexType out of it as in the above example.
  2. Create a schema called Order and ensure it has a different target namespace than the Address schema
  3. Click on the <Schema> node, then open the Imports collection in the properties window
  4. In this windows you’ll now be able to “XSD Import” the address schema into the Order schema

The following screenshots depict the XSD Import of the Address schema:

BizTalk - XSD Import

XSD Import

Next, create a couple of Child Records nodes for holding a Billing and a Shipping address. In the properties window for each record, set the Base Data Type to the complex type created above.

BizTalk - Base Data Type

Base Data Type

Now as the address type is changed, it will be reflected in your Order schema as well.

Another, perhaps more useful, example is adding additional context details to a schema.  Take, for example, the Order Schema created above.  If you wanted to add additional process context items you could wrap the order schema into another schema (call it: MyProcessOrder) where a child record node represents the Order and additional items are added as sibling nodes.  Using this method isolates your Order schema as the “Order entity” without poluting it with non-Order related attributes.

XSD Include – Batch Schemas

XSD Includes are slightly different from Imports in that the target namespaces of the schema and the included schema must match.  The primary use case I have used here is when creating envelope schemas used for debatching.  Continuing the above Order example, convert the Order schema to a ComplexType (using the Data Structure Type node) so it can be included in another schema.

Use the following steps to create an Orders schema containing one or more Order records:

  1. Create a new schema called Orders
  2. Ensure the target namespace of the Order and Orders schema are IDENTICAL
  3. Open the Imports collection (same as above) and now you can “XSD Include” the Order schema
  4. Now, add a child record and set it’s type to the OrderType created above

The following screen shots show including the Order schema into the Orders envelope schema:

XSD Include

XSD Include

And then creating the Order record …

Order Record

Order Record

Since this will be used for debatching, a few more properties need set at:

  • Envelope: Yes
  • Root Reference: Orders
  • On the Orders node, set the Body Xpath property to point to the Order node
  • On the Order node, set the maxOccurs to unbounded
XSD Redefine – Embrace and Extend

The XSD Redefine is probalby the least uses type of schema reuse for me as I genreally find myself using it to “hack” around another schema or trying to over-simplify something … and actually making it more cumbersome in the end.

An easy example of this is to take the AddressType above and create a new type from it to include some phone numbers:

  1. Create a new schema called AddressPhoneType
  2. Ensure the namespace on AddressPhoneType matches that of AddressType above
  3. As above, use the Imports property to “XSD Redefine” the Address schema
  4. Add a new child record and set its Data Structure Type property to AddressType (this is different from earlier where the Base Data Type property was used)
  5. Now, add a new Sequence Group to the new child record and customize!

The following screen shot depicts the AddressPhoneType schema:

XSD Redefine

XSD Redefine

Now you can change the Address schema without affecting other “consumers” of it.  Very useful if you want to embrace the re-usability of the Address type but not break aa bunch of other schemas which inherit it as well.

Now, with the above examples BizTalk developers can embrace re-usability in their BizTalk schemas along with other best practices!