Archive for November, 2009
- 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:
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
- 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.
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!
To continue my tradition of trying BizTalk in unsupported (or not fully supported) environments, I have been working with BizTalk 2009 on Windows Server 2008 R2 on 64-bit. This OS is being used at my current client and I have been able to configure everything in BizTalk to work except for the BAM Tools / BAM Alerts functionality.
I had posted on setting up BAM with SQL 2008 a few months ago and once the 4 hotfixes were released, I was able to get everything working on a single machine after applying the hotfixes and reconfiguring BizTalk. The current release of the BizTalk install/setup guides only go so far as supporting W2K8 R1 but do not officially announce support for R2. This is due to the way the dates lined up around the BizTalk 2009 release, but I always wonder if there are any issues, especially breaking ones.
This post walks you through a hurdle I encountered configuring BAM Tools in a multi-server environment.
Here is the configuration I am trying now:
- W2K8 R2
- SQL 2008 on separate server
- VS 2008 w/ SP1
- 4 Hotfixes for Notification Services, etc. (64-bit version)
- BizTalk 2009
After applying the 4 hotfixes I try to configure BAM Tools. I am getting the following error:
Microsoft SQL Server 2008 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine. Please install Microsoft SQL Server 2008 Integration Services. (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper)For help, click: http://go.microsoft.com/fwlink/events.asp?ProdName=Microsoft+BizTalk+Server+2009&ProdVer=3.8.368.0&EvtSrc=Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper&EvtID=error_DTS2008Archive——————————
ADDITIONAL INFORMATION:Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified. (Microsoft.BizTalk.Bam.CfgExtHelper)
This error looks similar to the one I had back on October 30, 2008 in this post: http://msinnovations.spaces.live.com/blog/cns!62E68922E47BC425!400.entry. With the changes to SQL 2008, the workstation tools were moved into a different package component known as the Management Tools. The similar error was resolved with SQL 2005 by installing the Workstation Tools on the BizTalk server. I was able to resolve the above error by checking the Management Tools (Basic & Complete) SQL 2008 features as shown in the screenshot:
As software products change, their names and subsystems also change. But the documentation for the products usually changes slower so there is often a small gap to watch out for. I found one when configuring BAM Tools for BizTalk 2009 with SQL 2008 on Windows Server 2008 R2. This post can also serve as some of the scant evidence that BizTalk 2009 runs successully on Windows Server 2008 R2, but the issue described above might be one of the supportability gaps.
- Use a clean VPC image
- Setup pre-requisites: W2K8, SQL 2008
- Install VS 2010 Beta 2
- Install VS 2008, and VS 2008 SP1
- Install BizTalk 2009