First Stab at Using New Tool to Migrate SQL Server Adapter Solutions

If you saw the recent announcement about the Adapter Pack 2.0 release, you may have seen the short reference to a tool that migrates “classic” SQL Adapter solutions to the new WCF SQL Adapter.  This tool claims to:

  • Works on the source project files
  • Generates equivalent schema definitions for the operations used in the existing project
  • Generates new maps to convert messages from older formats to the new format
  • Modifies any existing maps to work with the new schemas
  • A new project is created upon completion, which uses the SQL adapters from the BizTalk Adapter Pack v2

Given how much “migration pain” can be a big reason that old projects never get upgraded, I thought I’d run a quick test and see what happens.

The SQL Server part of my solution consists of a pair of tables and pair of stored procedures.  In my solution, I poll for new customer complaint records, and receive that data into an orchestration where I take the ID of the customer and query a different database for the full record of that customer.

In my BizTalk Server 2006 R2 environment, I walked through the “Add Generated Items” wizard in Visual Studio.NET and pointed at the classic SQL Adapter in order to generate the schemas necessary to receive and query data.  As you would expect, the message arriving from the SQL Adapter polling port has a single node representing the customer complaint.

The schema generated by the wizard for the patient record query has nodes for both the stored procedure request and result.

My orchestration is very straightforward as it receives the polled message, constructs the patient query using a map, executes its query, and broadcasts the result.

Great.  After deploying this solution, I still need the messaging ports required to absorb and transmit the necessary data.  My classic SQL Adapter receive location has the necessary settings to poll my database.

After adding two send ports (one using the classic SQL adapter to send my patient query, and another to drop my result to a FILE location), I started everything up and it worked perfectly.  Now the fun part, migrating this bad boy.

Because this SQL adapter migration tool claims to work on the “project files” and not configuration bindings, I figured that I could package up the working Visual Studio.NET project and perform the migration in my BizTalk Server 2009 environment (which also had the Adapter Pack 2.0 beta installed).

When you install the Adapter Pack 2.0 beta, you get the SQL Migration tool.   My SQL Server instance uses integrated authentication, so while I had to specify a “username” and “password” in the command line entry, I left them blank.

MigrationTool Sqlsource=”Blog.BizTalk.SqlMigrationDemo.btproj”

-dest=”C:\SQL_Mig\Blog.BizTalk.SqlMigrationDemoConverted” –uri=mssql://<server>//DemoDb? –username= –password=

Once this query completes, you get a notice and a brand new project.

The new project also contains a conversion report showing what was added and changed in the solution.  I can see two new files added, and two files that the migration tool says it can reuse with the new adapter.

If I open the actual project that the migration tool built, I can see new folders and a couple new files.  The SqlBindingProcedure.dbo.xsd schema is also new.

Notice that I have a new (WCF SQL Adapter) binding file for my “send” transmission that looks up patient details.  A note: the BizTalk project system in 2006 R2 is different than the new one in BizTalk 2009.  So, because I transferred my R2 project to my 2009 environment and THEN ran the wizard, my new project is still in the R2 format.  I had to manually create a new 2009 project and include all the files generated by the wizard instead of just opening up the generated btproj file.

The verdict?  Well, pretty mixed.  The schema it generated to replace my “query” schema is a mess.  I get an untyped result set now.

And the map that the migration tool created simply took my original “patient query” format and mapped it to their new one.  I guess I’m supposed to apply that at the send port and keep my existing “complaint to patient” map that’s in my orchestration?

Also, because the migration tool doesn’t appear to look at the runtime application configuration, I still have to manually create the receive location, which also seems like I have to manually recreate my inbound schema that can comply with the new WCF SQL Adapter format.  I haven’t done all that yet because I’m not that motivated to do so.

So, there could be a few reasons for my non-seamless experience.  First, I used stored procedures on all sides, so maybe that part isn’t fully baked yet.  I also switched environments and took a working BizTalk 2006 R2 solution and ran the conversion tool on a BizTalk 2009 box.  Finally, there’s a good chance this falls under the “Seroter Corollary” which states that when all else fails, let’s assume I’m an idiot.

Anyone else run this migration tool yet on an existing project?  Any obvious things I may have missed that made my migration more work that rebuilding the project from scratch?

Technorati Tags: ,

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

8 thoughts

  1. Firstly, thanks for providing your valuable feedback on the tool.

    Well, we are in process for also putting up a detailed blog post for the Migration Tool.

    The tool does not work for BTS 2009 projects.
    Regarding the response part of the Stored Procedure – as mentioned on the blog and
    we have backward compatibility for all inbound operations. So the response that we get for Stored Procedure can still be used with the old SQL Adapter schema.

    Regarding the map, currently the changing of maps for stored procs is not supported. We will soon be updating the same.

    We generate a binding file that you would be required to import at the runtime for the required ports. The tool is not 100% though, you will have to do some tweaks here and there in your project to get it completely working based on your scenario.

  2. Hi,

    I want to migrate my Biztalk server 2006 R2 project to Biztalk server 2009. From the above infomation I came to know that the SQL server migration tool is facing some problem . Can anyone please tell me if there is any other tool to migrate my SQL artifacts to Biztalk server 2009.
    If I’ll not migrate the SQL Artifacts will it create any problem as BIztalk server 2009 supports SQL server 2005 also.

    Thanks in Advance…

  3. Hi Madhusree,

    Note that my write up was of a pre-release version. You should pull down the since-released final version and take it for a spin before discounting what it may be able to do for you.

  4. Thanks a lot Richard for the information.
    One more query how can I upgrade my 2006 R2 configuration binding information to Biztalk server 2009 .

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.