none
BizTalk 2006 R2 SQL Adapter Failover RRS feed

Answers

  • Hi Ben,

    I think I might have found the issue. When I used SQLOLEDB as a Provider in the Connection String, it doesn't worked but if I changed it to SQLNCLI it worked, so I guess the limitation is with SQL Adapter using SQLOLEDB. After changing it to SQL Native Client (SQLNCLI) it seems wokring as expected.

    Thank you very much!

    Regards,

    Allan
    Tuesday, December 8, 2009 8:05 PM

All replies

  • The failover in this article refers to having a separate SQL Server instance to fail over to. Are you looking for something more like a backup port where if one fails then the other will be used? I think some of the adapters have this as a port setting but not the SQL adapter. You would need to build some sort of custom mechanism to handle this.

    A custom way would likely either involve using Error filters to reroute a problem message to the backup port or use of the ESB toolkit to do some custom routing in the event of failure.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, December 8, 2009 2:17 PM
    Moderator
  • Hi Ben,

    Thank you for your reply. I forgot to mention that the SQL Server has a mirror. So in this sample URI:

    sql://primary_server/db_name?FailoverPartner=mirrored_server&InboundId=db_table

    Does this actually worked? What I mean is when the primary db server failed for whatever reason and it's setup for automatic failover to the secondary, will the receive location just switched over to the secondary?

    Currently, we have a dummy URI and it's not set to anything like the above sample URI.

    Thank you.

    Allan
    Tuesday, December 8, 2009 2:45 PM
  • I think this is correct - it should failover. What events cause the failover to occur are a larger question. Could you try it to see if it fails over properly?

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, December 8, 2009 4:11 PM
    Moderator
  • Yeah, I tried that but it didn't seems to work. I setup a small environment to try this settings and I even used the sample application in the SDK (C:\Program Files\Microsoft BizTalk Server 2006\SDK\Samples\AdaptersUsage\SQLAdapter) and setup mirroring for the database "BTS2004_SQL_Adapter_Loans" and process one loan application and make sure that BTS processed it properly and it did then I manually failed over to the mirrored server then submit another loan application and event log shows a failure audit.

    Event Type: Failure Audit
    Event Source: MSSQLSERVER
    Event Category: (4)
    Event ID: 18456
    Date:  12/8/2009
    Time:  11:12:19 AM
    User:  VIZIER\btdummy
    Computer: BIZTALKALL
    Description:
    Login failed for user 'VIZIER\btdummy'. [CLIENT: 10.0.10.239]

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 18 48 00 00 0e 00 00 00   .H......
    0008: 0b 00 00 00 42 00 49 00   ....B.I.
    0010: 5a 00 54 00 41 00 4c 00   Z.T.A.L.
    0018: 4b 00 41 00 4c 00 4c 00   K.A.L.L.
    0020: 00 00 07 00 00 00 6d 00   ......m.
    0028: 61 00 73 00 74 00 65 00   a.s.t.e.
    0030: 72 00 00 00               r...   


    This I think means that it's still trying to login to the primary server rather than the mirrored.

    P.PEvent Type: Failure Audit
    Event Source: MSSQLSERVER
    Event Category: (4)
    Event ID: 18456
    Date:  12/8/2009
    Time:  11:12:19 AM
    User:  VIZIER\btdummy
    Computer: BIZTALKALL
    Description:
    Login failed for user 'VIZIER\btdummy'. [CLIENT: 10.0.10.239]

    For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Data:
    0000: 18 48 00 00 0e 00 00 00   .H......
    0008: 0b 00 00 00 42 00 49 00   ....B.I.
    0010: 5a 00 54 00 41 00 4c 00   Z.T.A.L.
    0018: 4b 00 41 00 4c 00 4c 00   K.A.L.L.
    0020: 00 00 07 00 00 00 6d 00   ......m.
    0028: 61 00 73 00 74 00 65 00   a.s.t.e.
    0030: 72 00 00 00               r...   


    P.S.
    This test is just a simulation i'm making as we have current prod environment and biztalk is facing some issues during the failover.



    Tuesday, December 8, 2009 4:21 PM
  • Just wondering, is vizier\btdummy a local account? Try with a Windows account like your BizTalk App host account. I think the account is going to master because this is specified in SQL as the default database for the account.

    Its possible there could still be some bugs in the failover functionality.

    Thanks,
    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, December 8, 2009 7:57 PM
    Moderator
  • Hi Ben,

    I think I might have found the issue. When I used SQLOLEDB as a Provider in the Connection String, it doesn't worked but if I changed it to SQLNCLI it worked, so I guess the limitation is with SQL Adapter using SQLOLEDB. After changing it to SQL Native Client (SQLNCLI) it seems wokring as expected.

    Thank you very much!

    Regards,

    Allan
    Tuesday, December 8, 2009 8:05 PM
  • Glad you got it working. Those SQL providers have tripped me up in the past too.

    Thanks,


    If this answers your question, please use the "Answer" button to say so | Ben Cline
    Tuesday, December 8, 2009 8:44 PM
    Moderator