| Author |
Topic |
|
robvolk
Most Valuable Yak
15732 Posts |
 Posted - 2011-06-10 : 13:29:33
|
| Quick background: I need to configure a filtered transactional publication to replicate to remote locations. Right now I'm trying to replicate from NY to Delaware, but ultimately will go from NY to Hong Kong.I've done local testing on the Delaware servers and got it working fine. My test from NY to DE is giving me "The process could not connect to Distributor" errors. The publisher is in DE, the distributor in DE, and subscriber in NY. It's set up as a pull subscription but I don't think I've configured it correctly.Any ideas or resources? The agents are using the SQL Agent account, and it has full permission on all servers. The snapshot folders are shared.Thanks. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-10 : 14:21:22
|
| Publisher/Distributor and subscriber are part of same domain?Also, is it the publisher or the subscriber that can't connect to the distributor? (guessing subscriber)One more question:Version/Edition of all 3 pleaseOh yeah, more questions:Is the SQL Agent account a domain account with full permissions on the snapshot folder and the published dbs?Can you show the full error from the SQL Log, or Event Log please? |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-10 : 14:35:59
|
Ok, another thing to check  Assuming a VPN connection right?Firewall isn't blocking access? And Win Firewall is off on all 3 machines?EDIT: I keep thinking of other things to check...the distributor_admin account is properly set? Does the subscriber see the repl_distributor under linked servers?SPN is properly set on all 3 boxes? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 14:59:12
|
| Pub/Dist/Sub all on same domain. Looks like the sub cannot connect to the distributor; from my reading of the repl. monitor the distributor is up-to-date.OS/SQL is 2008R2 Enterprise on all servers. SQL Agent account is a domain account with privileges/permissions for all the relevant folders. I did change the replication agents to use sa but it hasn't fixed anything.There is a VPN, it allows all ports (just for testing). Windows firewall allows 1433, 1434 and 5022 on all machines. TCP/IP and Named pipes enabled everywhere. The only error messages I've found on the subscriber are:"Replication-Replication Transaction-Log Reader Subsystem: agent subscribed_server-Test-1 scheduled for retry. The process could not execute 'sp_replcmds' on 'subscribed_server'."and on the publisher:"Replication-Replication Distribution Subsystem: agent publisher-article_DB-othersubscriber-13 scheduled for retry. TCP Provider: An existing connection was forcibly closed by the remote host."This seems to be for another subscriber though, not the problematic one.Thanks!Mirroring works between locations, and file copy operations. We're using replication because we have to split some data off to a new location, and the database is already mirrored. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 15:25:21
|
| Ugh, what a mess (my post and other things).Both servers have been set up as distributors, both have repl_distributor linked servers. I'm not sure how to check which one is being used though. The publication was created through the GUI and scripted out. I've gotten subscriptions to work for the same publication between servers in the same office in DE.Distributor_admin has sysadmin rights and is on the PAL list. I've done everything through the GUI, and haven't modified any particular settings outside the ones I've mentioned.Telnet works, but there's a wrinkle. I forgot to mention, because of our connectivity between offices (T1 and T3 connections) I had to create a server alias for the publisher's name on the subscriber, using the T3 address. Telneting to the subscriber name doesn't connect, but it does work for the T3 IP address. There may be a similar issue of multiple network paths/IP addresses when the server is finally installed in its new home.Regarding the SPN, does that have to do with this: http://technet.microsoft.com/en-us/library/bb735885.aspxIf so, I'll need to get my network people to set that up. It's the same domain account we use for SQL Agent.Thanks! |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 15:32:40
|
| Ugh, more mess, more update:Looks like I misspoke about telnet. I can't telnet from the subscriber, the utility is not on that server. Don't know why, I'll try to get it installed.Also forgot to mention that the publisher is a clustered instance. If I do telnet tests do I use the SQL cluster IP or the node's IP? The server alias I set up uses the SQL cluster IP and works fine in Management Studio on the subscriber.I'm about ready to filter the replication to the same server and either log ship that database, or use disk replication on it. What a PITA.UPDATE: I got telnet installed, will reply after I test further. Looks like it works on the T3 IP address though (sub->pub)UPDATE 2: Telnet does not work on server name nor T1 IP address. Ping works on all 3, and resolves the server to the T1 IP. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 15:41:34
|
| The alias name is the actual server name, but uses the T3 IP address as the server. That's the only route it can use between sites. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 16:15:11
|
| Update Part 2: The Sequel:I tried putting an entry in the hosts file but that didn't help. I think I'm going to call it quits for today.Would a DNS alias help to fix this? We have a weird VPN and network scheme for security reasons (credit card processing, PCI, etc.) and will need multiple connection paths.We anticipate using NY and DE as failover sites via database mirroring. The replication is strictly to get a portion of data to a 3rd location where it will reside permanently. We can't just back up and restore for a number of reasons:- limited bandwidth at 3rd site- DB size is prohibitive, filtered set is ~10% of total data- downtime must be minimized (actually can't have any) and sync must occur within a 5 minute window, or lessIs replication viable for something like that? If it weren't for the time constraints I'd be bcp'ing or SSIS'ing the thing. I have another crazy scheme I already mentioned but don't want to go that route (haha, route. I crack myself up) if I can avoid it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 17:11:11
|
| That's the conclusion we've reached, we'll change the routing at a later time. The hardware is shipping to Hong Kong on Monday and we'll test it when it gets there, which is where it will remain anyway.Thanks for your help, both of you! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-10 : 17:15:17
|
| ^ Agree with Tara. But DNS alias may solve.As for SPN, the only reason I mentioned it is for the SQL Agent accounts not to get login failed/local service etc. But replication should use the distribution sql login. Regarding the telnet, use the cluster name/IP not the physical server(s).I've setup replication across geographically distributed sites a couple of times (including with the publisher on a cluster), but used actual server names/cluster name, so you have one variable I don't.I'll test your setup over the weekend and see if I come up with anything really smart :DOne thing to look at in the meantime is the entries in master.sys.servers on both the distributor and subscriber(s). Make sure they are what you expect and have the right IP addresses. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-10 : 17:16:51
|
| Keep us updated please Rob. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 17:30:50
|
| I'm pretty sure it's all in the network, because all the logins, distribution, and other settings worked fine when it was local. I checked each server's repl_distributor and they point to themselves, which is what I expected. I have time to play with this once they arrive.I'll tell ya though, re: our network, it's like the TV show on Spike where everyone is jumping over obstacles, and they throw more obstacles at them. And if you're looking at VPNs, and you're considering CheckpointDON'TGo with fishing line and paper cups before you go with Checkpoint. Worst. Support. Ever. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-10 : 18:05:24
|
Does this mean you get a "free" trip to Honk Kong  |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 19:19:29
|
| Nope. Between SQL Rally, SQL Saturday, and some family stuff I'm done traveling for a bit. |
|
|
|
|
Replication problem