Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL 2012 Enterprise Peer to Peer over VPN

Posted on 2014-01-21
11
Medium Priority
?
450 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.
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

WatchGuard Case Study: NCR

With business operations for thousands of customers largely depending on the internal systems they support, NCR can’t afford to waste time or money on security products that are anything less than exceptional. That’s why they chose WatchGuard.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

916 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