We are building a new BizTalk cluster, with two BizTalk Application Servers and two SQL Servers. From SSMS on BizTalk AppServer #1 (and similarly from #2), we have two scenarios:
Current - our infrastructure team just rebuilt 2012/R2 Windows. It cannot connect to one specific SQL Server, but it can connect to others. The perplexing part is that any other machine seems to be able to connect to the same SQL Server just fine, and also run queries against it.
Yesterday - (We rebuilt the box since yesterday, so can't go back... just wanted to describe the issues.) It seemed to be connecting sporadically. When it was connected, I tried a few queries, created a test database and table, and tried the following:
declare @MaxLoops int = 100 declare @LoopCounter int = 0 while (@LoopCounter < @MaxLoops) begin set @LoopCounter = @LoopCounter + 1 --select SYSDATETIME(), * from NealTest.dbo.NealTest waitfor delay '00:00:01' print @LoopCounter print SYSDATETIME() end
My original intent of the script was to see if we lost connections after it got "rolling", in other words I could set it up and increase the
@MaxLoops and let it run for an hour or something. (Could also add try/catch to help it keep going to see if their were sporadic connectivity problems.)
The script (with the
Select statement commented out) gave a
So we started highlight one or two lines at a time. The declare worked fine, and I could print the values after the declare. When we lowered the
@MaxLoops to 5 it actually ran. We increased to 15 it failed with
Transport-level error. The same script runs fine (even up to
@MaxLoops = 1000) from any other client in SSMS 2008 or 2012 client running against the same SQL Server.
This lead me to think that the packet size involved might have been the issue. I put about 20 rows of data with 40 bytes per row, and was able to select the entire table, which seemed to nullify the idea that the packet size was the issue.
And further, from the BizTalk App Server #1, we can use SSMS to connect to any other SQL Server in our shop, and it works fine. So the issue seems to be specifically between these two pairs of servers.
We are stuck, trying to decide if we should call Microsoft, rebuild the SQL Server, or what.
The SQL Server
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Dec 28 2012 20:23:12
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 (Build 9200: )
The original issue was that the BizTalk configure utility was having the same "Transport-level errors". In the current scenario, BizTalk is not even installed. If SSMS cannot connect, we don't expect BizTalk to connect.
There is no firewall between these internal servers. Could some GroupPolicy have any impact?