Installing BizTalk Server 2010 in a Basic Multi-Computer Environment: Testing environment connectivity’s (Part 6)

Before you install and configure BizTalk Server is always a good practice to test or validate the connectivity’s between BizTalk Server and SQL Server machines.

To accomplish that we can use 4 Tools:

TCPView – This is a Windows program that will show you detailed listings of all TCP and UDP endpoints on your system and can be obtained from Microsoft: TCPView
DTCPing – This tool is designed to assist with troubleshooting Microsoft DTC Firewall Issues and can be obtained from Microsoft: DTCPing.exe
DTCTester – This tool tests a distributed transaction against a specified Microsoft SQL Server. This tool helps to test distributed transactions across firewalls or against networks. The tool performs distributed transactions by using ODBC API against a SQL Server database and can be obtained from Microsoft: How To Use DTCTester Tool
SQL Server 2008 R2 Client Tools – Tools to communicate with the remote instance of SQL Server

TCPView

TCPView is a Windows program that will show you detailed listings of all TCP and UDP endpoints on your system, including the local and remote addresses and state of TCP connections.

To execute this tool, just download it, extract all files and execute Tcpview.exe file.

DTCPing

Before running this tool and since this tool runs on a dynamic port, we have to check which port this tool is listening on both machines and add an inbound rule in each firewall. To accomplish that, we can use the tool TCPView (see picture above)..

To test DTCPing tool you must run this tool on both the machines, then you can test whether the normal RPC communication is working or not.

On SQL Server machine put the following configuration:

On BizTalk Server machine put the following configuration:

Test ping on both machines, you receive a similar message:

Please refer to following log file for details:
C:\Users\…\Desktop\dtcping\BTSSQL1704.log
Invoking RPC method on BTSSQL
WARNING:the CID values for both test machines are the same
Please send following LOG to Microsoft for analysis:
Partner LOG: BTSSQL 1704.log
My LOG: BTSSQL 1704.log
RPC test is successful
++++++++++++RPC test completed+++++++++++++++

DTCTester

To test DTCTester you must:

Create an ODBC Data Source in BizTalk machine
- Go to Start -> Data sources (ODBC)

Select the “Add” button and select “SQL Server” option

Configure the New Data Source

Create ODBC Data SQL Server New- Data Source

Leave Default options

Create ODBC Data SQL Server authentication

Leave Default options

Leave Default options

Press Finish
Test Data Source

Press Ok and Ok

SQL Server 2008 R2 Client Tools

This is the easy part, just open SQL Server Management Studio from BizTalk Server machine and try to connect to Database Engine and Analysis Services by using servername\instance or the Alias that we create earlier.

Related Links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira

2 thoughts on “Installing BizTalk Server 2010 in a Basic Multi-Computer Environment: Testing environment connectivity’s (Part 6)”

Hi Sandro,

When I try to configure Biztalk on a remote DB server, I got the following error.
“Failed to create Management database “BizTalkMgmtDb” on DB Server. CREATE INDEX Failed
because the following SET options have incorrect settings: ARITHABORT. Verify that SET options are correct for use with indexed views and or/indexes on computer columns..”

It looks like there is a problem with MSDTC.
I followed the links from Steef reply in this post –

http://social.msdn.microsoft.com/Forums/en-US/biztalkgeneral/thread/5733806f-65cc-40c3-a8ef-ce511d07b97d?prof=required

to troubleshoot, still I could not solve this DTC issue and cant configure the Group and Biztalk Runtime sections.

DGTPXXX01 is my DB server and DGTPYYY01 is Biztalk Server. I got the below error log while DTCPing from XXX01 to YYY01 but when I DTCPing from YYY01 to XXX01, RPC is successful.

DTCPing from DGTPXXX01(DB Server) to DGTPYYY01 (Biztalk Server):

++++++++++++lmhosts.sam++++++++++++

++++++++++++hosts ++++++++++++

09-07, 12:52:52.429–>Error(0x424) at clutil.cpp @256
09-07, 12:52:52.432–>–>OpenCluster
09-07, 12:52:52.441–>–>1060(The specified service does not exist as an installed service.)

++++++++++++++++++++++++++++++++++++++++++++++

DTCping 1.9 Report for DGTPXXX01
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
09-07, 12:53:08.943–>Start DTC connection test
Name Resolution:
DGTPXXX01–>192.168.191.135–>DGTPXXX01.sesami.com
09-07, 12:53:08.965–>Start RPC test (DGTPXXX01–>dgtpYYY01)
09-07, 12:53:16.877–>RPC server:DGTPXXX01 received following information:
Network Name: DGTPXXX1
Source Port: 62135
Partner LOG: DGTPYYY012204.log
Partner CID: B87F8C46-4338-403F-A786-D6274A0AA259
Problem:fail to invoke remote RPC method
Error(0x6BA) at dtcping.cpp @303
–>RPC pinging exception

–>1722(The RPC server is unavailable.)
RPC test failed

DTCPing from DGTPYYY01(Biztalk Server) to DGTPXXX01 (DB Server):

++++++++++++lmhosts.sam++++++++++++

++++++++++++hosts ++++++++++++

09-07, 16:09:25.867–>Error(0x424) at clutil.cpp @256
09-07, 16:09:25.867–>–>OpenCluster
09-07, 16:09:25.867–>–>1060(The specified service does not exist as an installed service.)

++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for DGTPYYY01
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
09-07, 16:09:36.446–>Start DTC connection test
Name Resolution:
dgtpXXX01–>192.168.191.159–>dgtpXXX01.sesami.com
09-07, 16:09:36.461–>Start RPC test (DGTPYYY01–>dgtpXXX01)

RPC test is successful
Partner’s CID:C606A712-5F63-49AE-AD1D-B0665F4A4E3F
++++++++++++RPC test completed+++++++++++++++

Do you have any suggestions on this…
——————————————————————————–

VENUGOPAL

Sandro Pereira says:

September 7, 2012 at 1:41 pm

Hi,

First thing like Steef mention: you will need to be sysadmin for your database, which a prerequisite to be able to configure BizTalk machine. To avoid problems I advise you to log on the server using a Domain User who has Administrator rights on both the SQL Server and the BizTalk Server.

Can you check three things:
1. Did you Configure Microsoft Distributed Transaction Coordinator in both machines (http://sandroaspbiztalkblog.wordpress.com/2012/01/04/installing-biztalk-server-2010-in-a-basic-multi-computer-environment-preparing-computers-for-installation-important-considerations-before-set-up-the-servers-part-3/)
2. Before running DTCPING tool and since this tool runs on dynamic port, we have to check which port this tool is listening on both machines and add an inbound rule in each firewall. You can use TCPView to see the port.
3. Can you check in your SQL Server if for some crazy reason BizTalkMgmtDb database exists.

Reply