Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. 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.

  2. 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:

Code:

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

Transport-level error

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 @@Version is

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?

share|improve this question
add comment

2 Answers 2

I'm not sure what authentication method you are using, but I've seen issues when using Windows Authentication when the DC (Domain Controller) is being overwhelmed with requests you can see issues. Usually it is a timeout issue though.

If you are using Windows Auth, try switching to SQL Auth to rule that out. Sorry I tried to add a comment but it wouldnt let me b/c i'm new.

share|improve this answer
    
If it were Domain Controller, then I wouldn't be able to connect to same SQL from other machines, which work fine, right? –  NealWalters Feb 20 at 21:34
    
The issue I saw before was it would have intermittent connectivity issues. Basically what it was is the DC response was outside the SQL connection keep alive timeout. Basically if you switch authentication methods and your loop works fine then you know it is a kerberos or NTLM related issue at least. –  drinkingcraft.com Feb 20 at 21:58
    
@NealWalters I can convert this to a comment for drinkingcraft, but if it is basically an attempt to answer the question I'd like to keep it as an answer (which can be edited/improved/voted), what do you think? –  Jack Douglas Feb 21 at 8:02
    
@JackDouglas - It's okay as an answer, but not the answer I needed. The entire project to install this cluster is about to be given to another outside team due to ongoing issues similar to this. –  NealWalters Feb 21 at 15:23
add comment
up vote 1 down vote accepted

We opened a Microsoft Ticket, and this was the response from our infrastructure team. I didn't get the HotFix KB #.

Steps to fix the Problem:

1) Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

2) Disable Receive Side-Scaling from the NIC Configuration/Advanced Tab on all 4 nodes. This must also be performed from the command prompt with: netsh int tcp set global rss=disabled

3) Disable TSC Chimney from the NIC Configuration/Advanced Tab on all 4 nodes. This must also be performed from the command prompt with: netsh int tcp set global chimney=disabled

4) Disable Large Send Offload V2 (IPv4) from the NIC Configuration/Advanced Tab

5) Reboot All Nodes

6) After nodes are back on-line, launch a command prompt and verify that the Connection Properties are still Disabled with the command: netsh int tcp show global

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.