WCF-SQL Adapter’s Database Schema Gotcha

 
Introduction
 
On a recent project I was working with BizTalk 2009 and SQL 2008 and the customer was interested in moving some database objects to a new SQL database schema (for example, from dbo to MySchema). In this post, I refer to types of schemas: BizTalk schemas (XSDs) and database schemas (SQL security mechanism).
 
I had been interacting with these database objects using the WCF-SQL adapter from the BizTalk Adapter pack. Typically, BizTalk’s port abstraction will take out all description of the physical connectivity details and provide you with a connection independent message type. Connectivity details are stored in the port properties and persisted as binding information. Through the use of the Adapter pack wizard, BizTalk schemas are created for interacting with various adapter data sources such as with the WCF-SQL adapter and SQL databases.
 
Initial Observations
 
So initially I expected the database schema change would not be a problem because the database object details were all specified as properties of the port. I modified the following port properties to point to the new schema, expecting this to work:
 
  • BizTalk WCF-Custom SOAP Action Headers such as TypedProcedure/dbo/MyProcCall to TypedProcedure/MySchema/MyProcCall
  • BizTalk binding properties with SQL information such as the properties notificationStatement, pollingStatement, or polledDataAvailableStatement, etc.

I restarted my BizTalk application and hosts and I started getting errors that referred to my old database schema. At this point I realized that the database schema is actually hard coded during the BizTalk schema generation process. This is a significant gotcha; that the changing of the database schema for custom objects that BizTalk uses requires a regeneration of the BizTalk schemas. The next section describes the steps I went through to correct the old database schema references after moving my database objects to the new database schema:

Resoluton

In addition to the deployment steps done above, I had to update several of my BizTalk solution artifacts. For my BizTalk solution, I was working with 5 different database objects and I did not want to delete all of my BizTalk schema files (approx. 15 different files) and go through the adapter wizard so many times. I decided to opt for a find and replace solution instead. Here are the steps I did to replace the old database schema references:

  • Open up the base and referenced BizTalk schemas using the Open With…, XML Editor. If you are interacting with rows of data then you may have separate referenced schema files that may also have the old schema in them.
  • Do find/replace and search for "/dbo" or whatever old database schema you are replacing. The "/" comes from the SOAP Action Header.
  • Save the schema changes.
  • Once you replace one database schema reference, your BizTalk schema will be in an inconsistent state until all of the database schema references have been updated. Try opening the BizTalk schema in the BizTalk schema designer and if you get a warning when opening you will know that there are still old database schema references to migrate. When all of the old database schema references are replaced you will no longer get a warning
  • Recompile
  • Then investigate the BizTalk map and orchestration files because these also contain hard coded references to the old database schema.
  • It would seem that a recompile of your BizTalk solution would regenerate the .cs files for the BizTalk maps and orchestrations, but I found this to not be the case. An approach I found that worked was to open any BizTalk maps and make an insignificant change (add a functoid and then remove it) just to change the file status to edited and then to save the map. This would refresh the generated map code.
  • Also, I opened all of my BizTalk orchestrations and double-clicked all of the Transform shapes and then checked the button "Open the map when I click the OK button" to change the file status to edited. This refreshed the generated orchestration code. Even though no actual changes were made the generated code was refreshed.
  • You could alternately remove all of the generated files such as those named like *.odx.cs or *.btm.cs
  • One disadvantage of this find & replace technique is that your old generated binding files will still refer to the old database schema so be sure to discard those.

Conclusion

This post has shown that a change to the database schema for SQL objects can have a major ripple effect on a BizTalk solution that uses the WCF-SQL adapter. I do not expect that changes to database schemas occurs very often so you may never encounter this but if you do, be prepared for a little bit of work to get the updated database schema names back into your solution. Thanks for reading!

Advertisements

, ,

  1. #1 by Unknown on March 15, 2010 - 4:17 am

    Thanks, I have found the same annoyance – which is very…annoying. Why could they not simply come up with an ‘refresh’ mechanism?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: