• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

SQL 2012 Enterprise Peer to Peer over VPN

Hey guys

Ok here is one for the MS SQL gurus.

Two servers.  Identical hardware, Operating systems and software.  One server in Dallas, another in Houston.  VPN is open to allow the whole subnet (192.168.200.xx in Houston, 192.168.99.xx in Dallas) to talk to each other.  So I can ping from any of the 4 VMs in both physical servers to the other with zero loss of packets.  

The Operating systems are Server 2012 and the SQL is MS SQL 2012 Enterprise

The Router in Houston is a Cisco 5000 series and the Router in Dallas is a Cisco Small Business RV042

Now, here is what is happening.

I can open SQL Server Management Studio in Houston and connect to the local database with zero issues (of course) and when I go to connect to the Dallas SQL server I can connect with only about a 3-5 second delay and the folders expand quickly.  

Now, if I open the SQL Server Management Studio in Dallas I can connect to the local database with zero issues but when I try and connect to the Houston SQL server it takes at least 15-20 seconds and it take another 30 seconds minimum to expand all the folders (in fact it even causes the Management Console to go to Not Responding).

At first I thought it was because the DC was in Houston, so I created another DC in Dallas and got it all synced up (I tested the Dallas SQL server to make sure it was logging on to the Dallas DC by running Set LogonServer and it showed the Dallas DC as its logon server)

So now I am stumped as to what or where the problem is.  I can ping both names and IP's from both locations and get zero loss.  10ms roundtrip on the pings is average

I have turned on SQL Browser and SQL Agent on both servers, and I enabled all the communication pathways.  Named Pipes, IP etc.

Any ideas?

Thanks!!
PS  Oh, btw, the whole idea of trying to get each to communicate to each other is to setup Peer to Peer replication.  A subscriber and a publisher on both servers
0
jonmenefee
Asked:
jonmenefee
  • 7
  • 4
1 Solution
 
Jim P.Commented:
Try turning off Named Pipes on both ends, including using cliconfg.exe at the server level to only enable TCP/IP. Then the restart the SQL and Browser services and see how it reacts.

NP was designed for small, fast LANs. It goes to the firs DC for authentication and then the DC redirects to the SQL server. TCP goes to the SQL server and SQL then sends to a DC for a valid authentication.
0
 
jonmenefeeAuthor Commented:
Ok, I turned off Named Pipes at both ends and now only the Houston SQL Management Studio can connect to the Dallas SQL server.  The Dallas gives me this error.

TITLE: Connect to Server
------------------------------

Cannot connect to LOC2012P2PHOU.

------------------------------
ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

------------------------------
BUTTONS:

OK
------------------------------


The firewalls at both ends are turned off (just for testing purposes).  Any ideas?
0
 
jonmenefeeAuthor Commented:
When I reenable named pipes at both ends, I can connect but its very slow from Dallas to Houston.  From Houston to Dallas the connection takes less than 4 seconds to be made, but from Dallas to Houston it takes exactly 16 seconds to make.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jim P.Commented:
Is this a linked server?

Or is via SSMS? Turn off NP again. When the pop-up comes up that has the server name in it, the details tabs give you an option to select the protocol that you want to use. A lot of times NP is automatically selected being the first one in the list and stays as the default.
0
 
jonmenefeeAuthor Commented:
I tried choosing advanced options and chose tcp/ip and it timed out.
All via SSMS. Just out of curiosity though I saw some multiserver options when I right click on SQL agent that mentioned multiserver. Master or target. Is there anything in that area I should be looking at?

Linked server?  Enlighten me on that please
0
 
Jim P.Commented:
I tried choosing advanced options and chose tcp/ip and it timed out.

If you have a choice that means SSMS still thinks that the NP and TCP/IP are enabled.

Another thing to try is to use the IP instead of the server name. If that works then you may have to look at the DNS server(s).

Linked server?  Enlighten me on that please.

If you go under <servername> --> Server Objects --> Linked Servers you can link the two together.

Then from a query window you can do
SELECT *
FROM [RemoteServer].[DBName].[dbo].[TableName]

Open in new window

from the local server.
0
 
jonmenefeeAuthor Commented:
Would Linked Server help with this or is it just another tool.

Ok, I tried IP address connecting with TCP/IP and it timed out.   It appears the only way it connects is via NP.  If I go into Configuration and go to Network Protocols and Right click on TCP/IP and go to the 2nd tab it shows all the various connections.  They all show active but the enable part says No, in all of them.  They all show port 1433.  Should I enable all of them?
0
 
Jim P.Commented:
They all show active but the enable part says No, in all of them.  They all show port 1433.  

The SQL Server Browser Service is designed to receive SQL requests on the default port of 1433 and then redirect it as needed. So if you have your default ServerName instance and a ServerName\Instance1 and ServerName\AppNameInstance on the same server the ServerName instance may or may not use 1433 directly. But ServerName\Instance1 can use port 867 and ServerName\Instance1 might use port 2177. The browser service points to those ports.

Should I enable all of them?

Yes. Then restart at least the browser service.
0
 
jonmenefeeAuthor Commented:
Ok. I will try. It just seems strange that one server can connect with very little problem but the other can't. Makes me think it's a router issue.
0
 
jonmenefeeAuthor Commented:
Turns out it was a DNS issue.  I got the DNS reconfigured on both ends and now both servers can connect to each other with zero issues.
0
 
jonmenefeeAuthor Commented:
I didn't receive any experts comments on Domain Controller issues or DNS issues.  I had to figure this one out myself
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now