none
WCF - How to dynamically switch between databases? RRS feed

  • Question

  • I'm a newbie to BizTalk, and I have a situation where I need to access a number of databases containing the same table structure on six different SQL instances. I'd like to use the WCF adapter to perform this, but I'm not sure how to dynamically switch the database binding to poll each database. I've looked at several articles on this, and it appears I need to use WCF channel model access, but again I'm not sure. Anyone tried this before, or do you have any suggestions? Any help would be greatly appreciated!
    Tuesday, May 21, 2013 8:00 PM

Answers

  • Can you clarify a bit, are you saying you have about 150 (nearly) identical databases across 6 instances of SQL Server and you'd like to poll the same table in each?

    Hmm...ok.  First, there's no way to dynamically change a Receive Location in the way you're asking.

    You can create a db that the BizTalk app owns and join the tables with a view.  Use a trigger to write records to a queue table in the BizTalk owned database.  You could use dynamic-ish sql in a stored procedure and union the results.

    How about this, keep a table of the tables to query and poll that.  Push the results to an Orchestration and build a dynamic Solicit-Response that queries the table in each database.  What instance it's in is largely irrelevant.

    Wednesday, May 22, 2013 2:29 PM

All replies

  • You say "poll each database", so I assume you are referring to a Receive Location here? If so you can just have 6 different Receive Locations in one Receive Port each pointing to an individual SQL instance. 

    If you are referring to a Solicit-Response Send Port? I think it would depend on how you trigger these Send Ports and whether the addresses of the instances and the number of them are fixed, if not I would consider a dynamic Send Port otherwise I would make 6 static Solicit-Reponse Ports and trigger them as desired. 

    Morten la Cour

    Wednesday, May 22, 2013 4:34 AM
  • Thanks for responding, Morton! Yes, I'm talking about Receive Locations, I'm aware of linking multiple Receive Locations to a single Port. My real issue is dealing with multiple databases on each SQL instance, approx. 150 total. We'd like to eliminate dynamic SQL queries where we possibly can, which is why I'nm asking about dynamic switching from one database to the next on a specific SQL Server instance. I'm already using WCF ports I've constructed using the WCF SQL adapter, but those have been built pointing at a specific database. I really appreciate the resources and talent in this community, I've benefited quite a bit from the help already provided to others with issues I've run into, and I'm sure I'm not the only person who's run into this problem!
    Wednesday, May 22, 2013 1:08 PM
  • have you tried with Dynamic Send Port for WCF - SQL , Where you can set connection properties inside orchestration Like :


    DynamicSendPort_SQL_RqRs(Microsoft.XLANGs.BaseTypes.Address)="mssql://xxxx//xxxx?";

    DynamicSendPort_SQL_RqRs(Microsoft.XLANGs.BaseTypes.TransportType)="WCF-Custom";

    Wednesday, May 22, 2013 1:13 PM
  • Thanks, M-G! No, haven't tried that yet, but certainly sounds like a great idea! I can certainly create a Solicit-Response port in the orchestration and use that to access the stored procedure.. I'll give this a shot and let everyone know how it works!
    Wednesday, May 22, 2013 1:43 PM
  • Can you clarify a bit, are you saying you have about 150 (nearly) identical databases across 6 instances of SQL Server and you'd like to poll the same table in each?

    Hmm...ok.  First, there's no way to dynamically change a Receive Location in the way you're asking.

    You can create a db that the BizTalk app owns and join the tables with a view.  Use a trigger to write records to a queue table in the BizTalk owned database.  You could use dynamic-ish sql in a stored procedure and union the results.

    How about this, keep a table of the tables to query and poll that.  Push the results to an Orchestration and build a dynamic Solicit-Response that queries the table in each database.  What instance it's in is largely irrelevant.

    Wednesday, May 22, 2013 2:29 PM
  • Thanks, boatseller! My thoughts as well, we already have a common database table with the database names we'll need to poll, so this will work well. Everyone's been so helpful, Thanks!
    Wednesday, May 22, 2013 2:47 PM