Solved

SQL 2012 Enterprise Peer to Peer over VPN

Posted on 2014-01-21
11
443 Views
Last Modified: 2014-02-10
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
Comment
Question by:jonmenefee
  • 7
  • 4
11 Comments
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 

Author Comment

by:jonmenefee
Comment Utility
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
 

Author Comment

by:jonmenefee
Comment Utility
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
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 

Author Comment

by:jonmenefee
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 

Author Comment

by:jonmenefee
Comment Utility
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
 
LVL 38

Expert Comment

by:Jim P.
Comment Utility
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
 

Author Comment

by:jonmenefee
Comment Utility
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
 

Accepted Solution

by:
jonmenefee earned 0 total points
Comment Utility
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
 

Author Closing Comment

by:jonmenefee
Comment Utility
I didn't receive any experts comments on Domain Controller issues or DNS issues.  I had to figure this one out myself
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now