The following table describes the versions of SQL Server that are supported when you use different versions of BizTalk Server: BizTalk Server 2000 BizTalk Server 2002 BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 BizTalk Server 2009 BizTalk Server 2010 SQL Server 7.0 Yes Yes, with SQL Server 7.0 Service Pack 3 […]
Blog Post by: Sandro Pereira
The following table describes the operating systems that are supported when you use different versions of BizTalk Server: BizTalk Server 2000 BizTalk Server 2002 BizTalk Server 2004 BizTalk Server 2006 BizTalk Server 2006 R2 BizTalk Server 2009 BizTalk Server 2010 Windows 2000 Yes Yes Yes, with Windows 2000 Service Pack 4 Yes, with Windows […]
Blog Post by: Sandro Pereira
This week I had the opportunity of co-presenting a session at the SharePoint Conference with my friend and Windows Azure AppFabric product manager Tony Meleg. This year, we decided to tap on the recent release of Office365 and presented a series of real…(read more)
Blog Post by: gsusx
As part of our regular BizTalk Health Check service that we do for a client, we had an issue where the CPU usage on the BizTalk SQL Server kept increasing over the past few months. I looked at all the usual culprits for this increase in CPU usage on the SQL Server but none of those seemed to be the cause. I started looking a bit deeper into the BizTalkMsgBoxDb with the help of MsgBoxViewer and found that the tracking data from the trackingdata_1_X tables was not being moved to the DTA database. I then checked to make sure that the tracking host was running and it was, it then became a bit more puzzling, the trackingdata_0_X tables were being move to the BAMPrimaryImport, so part of the tracking was working.
After speaking some more with the client, they mentioned that they had run the BizTalk Message Box clean-up stored procedure (knowing it was unsupported in production) more than once in the past year, and the problem was getting worse in the last couple of months. I then started digging a bit deeper into exactly how the tracking data gets moved from the BizTalkMsgBoxDb to the BizTalkDTADb and found that there is a table in the BizTalkDTADb that keeps track of the last Sequence Number moved from the BizTalkMsgBoxDb tracking_1_X table to the BizTalkDTADb. I then looked at the current number in this table (TDDS_StreamStatus) and they were not even close to the Sequence Numbers in the trackingdata_1_X table in the BizTalkMsgBoxDb.
After fixing this Sequence Number mismatch the tracking data started to move as expected and over the next hour or so the CPU usage on the BizTalk SQL server dropped significantly low and back to what I would have expected on a BizTalk system with this amount of load.
The tools that can be used to fix these sort of issues it the BTSTerminator and it can be found along with other BizTalk Tools here: http://msdn.microsoft.com/en-us/biztalk/dd920317.aspx
Last year I did a post on table operations using WCF-SQL Adapter as part of Adapter Pack 2.0. This post was targeted for BizTalk 2009 and I now like to do an update targeting BizTalk Server 2010. With BizTalk Server 2010 (DVD/ISO) comes the adapter pack that bundles old (non-WCF based) Enterprise Adapter for BizTalk with new (WCF-based) adapters to support compatibility with different versions of Line Of Business systems (SAP, Siebel, Oracle, Oracle eBusiness Suite). The BizTalk Adapter Pack 2010 includes support for new versions of the following line-of-business applications and databases: SAP 7, Oracle EBS 12.1 and SQL Server 2008 R2. The Adapter Pack also provides support for Visual Studio 2010 and .NET Framework 4. For documentation see MSDN BizTalk Server Adapter Pack 2010 Documentation and interactive poster.
Installation is straight forward and made simpler through following steps in a wizard and adjusted to installing on x64 (you can read that in post “Installing BizTalk Adapter Pack 2010 on x64”). After installation you will need to add adapter manually if you want to use SQL adapter through a WCF-SQL port. The SQL adapter can be also used in BizTalk as a WCF-Custom port. If you want to use the SQL adapter through a WCF-Custom port, you do not need to add the WCF-Custom port to the BizTalk Server Administration console, because the WCF-Custom port is added to the BizTalk Server Administration console by default. When you use Consume Adapter Service Tool in BizTalk Project it will create xml schema’s based on operation contract (inbound or outbound) and WCF-Custom binding file.
To perform operations on SQL Server through messaging by using the WCF-Custom adapter, you must perform a set of design-time and run-time tasks using Visual Studio and BizTalk Server Administration console respectively. The following high level tasks are required to create a message based solution:
First, we need the XML schemas, which represent our data source. By installing the WCF LOB Adapter SDK, you will have a metadata browser built into Visual Studio. NET at your disposal. You can access this from a BizTalk project by right-clicking the project, choosing Add then Generated Items and selecting Consume Adapter Service.
This action opens a new window that provides us the interface to connect to, browse, and select database objects for service interaction. The very first thing we need to do here is choose the sqlBinding as the service binding, and then configure a connection string. The simplest working connection string consists of an Initial Catalog value for the database, and a valid Server name entry. Note that the adapter now uses a connection string in the form of a URI instead of the traditional Data Source=;Initial_Catalog=;User_ Id=;Password= style.
Once a satisfactory connection string is defined, the Connect button can be clicked to establish an active connection to the target database. If the connection is successful, one see the category browser with a set of database object categories.
On the root “/” character a range of un-typed generic functions are displayed, which can be exploited. Click on tables and you will see a number of Tables, select Products.
Click the Delete, Insert, Select and Update statement in available categories and operations. Click the Add button to add the operations. After clicking the Ok button at the bottom, schemas (and a single binding file) is added to our associated BizTalk project in Visual Studio.NET.
The binding is required later on, when solution is deployed and ports have to be configured for SQL Server (i.e. Send Port). This binding has to be edited for each operation (Select, Update, Insert, and Delete), because there is no corresponding logical ports as this a complete message based solution (if you keep original binding file and import in than you will run into error described in this post Why does the Adapter say ’Action is not understood’ even though I am using the binding file generated by the ’Consume Adapter Service’ wizard?). I choose to create four new binding files, each a copy of original, but all with a single operation in BtsActionMapping.
Next step is to generate messages from generated schema, which can be routed from Recieve location in Receive Port to a send port with WCF-Custom Adapter. Generating the first message is easy, as you can right click on the TableOperation.dbo.Products.xsd and select Generate Instance and an instance of top element is created (e.g. Select).
You will get a instance of message as depicted below:
By opening up the TableOperation.dbo.Products.xsd in file-editor you can move the next operation to top, so you can then in Visual Studio generate the next instance for an operation or you can use XML-Editor inside Visual Studio (closing schema, right click and select open with and then select XML Editor) to move elements around. In end you will have four xml instance for TableOperations, save to file as xml (type all files, encoding UTF-8!).
Only schemas are required for a messaging only scenario with WCF-SQL Adapter to perform operations on SQL Server. Therefore after assigning a strong name and application name to BizTalk project it can be build and deployed.
After solution has been deployed it is time to configure the ports. FILE ports are easy and focus here will be configuring the WCF-Custom Send Ports. Through BizTalk Management Console one can browse to BTS.SQL.Table.Operations application and right click the application and select Import Bindings and navigate to file called WcfSendPort_SqlAdapterBinding_Custom_Select.bindinginfo.xml, select and click Open. You will notice that a Send Port will be created automatically. You can repeat the import for binding:
Next steps are setting filters on WCF-Send Port and File Send Port. Configuration is as follows:
| PortName | Type | Filter |
| RPTableOperationIn | FILE | |
| SPTableOperationOut | FILE |
BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#UpdateResponse |
| WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Delete | WCF-Custom | BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Delete |
| WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Insert | WCF-Custom | BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Insert |
| WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Select | WCF-Custom | BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Select |
| WcfSendPort_SqlAdapterBinding_TableOp_dbo_Products_Update | WCF-Custom | BTS.MessageType == http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products#Update |
In the end your messaging solutions can be depicted as below. After configuration you can start BizTalk application. Schema below gives an overview what will happen once drop message in folder (e.g. In folder).
I hope with this post I was able to show all operations (CRUD) on table. The sample from MSDN shows only SELECT operation and here I have extended on that, showing all operations and how to setup WCF-Custom Adapter using sqlBinding and steps to do, before you can execute all operations on a SQL Table.
You can find sample for this post through MSDN Code Gallery: BizTalk Table Operations – Messaging
Cheers!
We are very pleased to announce the immediate availability of BizTalk 360 version 2.5. This is our biggest release since we released BizTalk360. Here are the top 6 highlights
One of the top requested feature, BizTalk360 now supports role based security in addition to existing user based security. Control "who can access what" using your NT groups.
Why do people always need to search Google/Bing to solve the problems. Build your organisations knowledge base repository seamlessly with BizTalk360. Problems once fixed should be fixed for ever.
We all know how invaluable Message Box Viewer tool is!! We love it. So we integrated it deeply with BizTalk360. You can schedule when you want to run it in each environment, the reports will be nicely formatted and available in the UI, and you can set the alert if MBV detects some serious problems
In addition to service instance details, now you can see the referenced message details along with all the the context properties and message content. Importantly! you can control who can see the message with our fine grained authorization module.
There is a big push in this area.
Alert/Notification capabilities are enhanced to include:
Whenever you install something like BizTalk360 you normally steer at the screen asking yourself what’s next!!. Not with v2.5, it tells you exactly what you need to do (ex: configure SMTP, MBV, alarm etc.) to get maximum benefit.
There are lot more to this release, see the release notice for complete set of enhancements and fixes.
Read for yourself what our customer are saying. For a personal demo please contact us via [email protected]
We wanted to make BizTalk360 a no brainier purchase, it should do more than what you have paid for.
Nandri!
Saravana Kumar,
Founder, CTO – BizTalk 360
MVP – Microsoft BizTalk Server since 2007
http://www.biztalk360.com
@biztalk360 | http://facebook.com/biztalk360 | http://getsatisfaction.com/biztalk360
While I’m waiting for my new BizTalk books that I referred earlier in this post, I decided to review an “old book”: Pro Mapping in BizTalk Server 2009 by Jim Dawson and John Wainwright. I simply love this book! It is a great book to have for mapping reference. I think that much more could […]
Blog Post by: Sandro Pereira
In the past I never got the version 1.x installed because of a complex installation guide, so now that I am looking at the toolkit again I thought that I better write done some notes for myself (and maybe other), so that you can get it up and running.
My base image is:
– Windows Server 2008 R2
– SQL Server 2008 R2
– BizTalk Server 2010
– Visual Studio 2010
– and some other stuff that I don’t think is relevant for the toolkit
First thing is to download the toolkit:
BizTalk ESB Toolkit 2.1
This also contains an installation guide, so what follows here is only the adds and special notes as I go through the installation guide.
Well you need some optional software from Microsoft before you can install it and the installation guide doesn’t contains links:
Visual Studio 2010 SDK (If you get an error which tells you that Visual Studio 2010 is missing then you might have the wrong version of the SDK. There is one for each service pack. Otherwise it is a “next next” type of installation).
Windows SDK (Well this is a bit hard to predict what is needed. I don’t want to install the full monty as this is 1,8GB. Here is what I choose. We will see later if that is enough. It is used for the portal. I would guess that it has something to do with the .Net framework as the portal is an ASP.Net web application).
If you are running in a domain and you use domain groups for the “BizTalk Server Administrators” and groups like this. Then remember to change those when you run the ESB Config toolkit.
If you get an error like this “Cannot open database…” when you run the BAM definition import. Then you need to add your SQL Server Analysis account as a datareader for the databases BAMPrimaryImport and BAMStar:
Hidden links can sometimes cause your map to behave in unexpected ways! If this occurs, always check out the compiler-generated links to make sure that he (the compiler) hasn%u00b4t misinterpreted your intentions. To see them: go to Solution Explorer, right-click over the map and select “Validate Map” option Then go to the Main menu and […]
Blog Post by: Sandro Pereira
Yesterday I have bought the BizTalk Server Unleashed 2010 Kindle Version for my iPad. Why? Well book contains a wealth of information on BizTalk Server 2010 and has 864 pages, which means is a heavy book to be carrying around. My iPad is a light device and I can have multiple books on it, so I can carry around a complete library of BizTalk and other related books (e.g. WCF) with me.
The book contains some interesting chapters like ESB, RFID, Windows Azure AppFabric and so on. Now I agree with Saravana that is not likely that you will read this book from cover to cover. I certainly have not had time yet to do so, but I have read two chapters so far. One on deployment concepts, which unfortunately does not discuss BizTalk Deployment Framework. And the chapter on ESB that gives you more background and understanding of ESB Toolkit.
Should you BUY this book? Yes you should and I will give you three reasons why:
)It will be a welcome addition to other BizTalk books you have or like Sandro states in his blog post it is one of the five books you should have.