Solved

SQL 2012 Enterprise Peer to Peer over VPN

Posted on 2014-01-21
11
446 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39799772
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
ID: 39800070
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
ID: 39800219
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 38

Expert Comment

by:Jim P.
ID: 39800233
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
ID: 39800262
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39800316
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
ID: 39800350
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.
ID: 39800414
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
ID: 39800471
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
ID: 39836244
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
ID: 39846818
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

626 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