0

i have some difficulty in setting up SQL replication over the internet. I am using a static public IP thru my firewall and port forward to the distribution server.

basically everything is fine, i was able to register and connect the remote database.

these are the steps i did to do this:

  1. published my public IP in my firewall and port forward 1433 request to distributor (remote computer)
  2. created alias to remote computer, alias name = IP; port=1433; protocol=tcp/ip; server=servername
  3. added ip to hosts file (192.168.. servername) computer name of remote server = servername of sql
  4. also did this to the local pc (subscriber) except the IP is the public IP
  5. ping the servername of the remote in the local pc and result is the servername of the remote PC (publisher/distributor) - okay
  6. tested in SQL management studio and it successfully connect to the remote pc

** now configuring the distributor **

i tried both sa and windows account for this

i followed the step by step procedure from here: http://www.cbizsoft.com/techsupport/ReplicationPage3.htm

i also tried placing this in the snapshot folder:

//servername/c/ReplData

** Now creating the publication ** i followed this: http://www.cbizsoft.com/techsupport/ReplicationPage4.htm

i chose both SQL server 2005 and 2008 in the subscriber type

Now i checked the snapshot agent and it's working fine ([100%] A snapshot of 1 article(s) was generated.)

Now creating the subscription

I tried push and pull subscription for this In the pull subscription i created the subscription in the local pc (subscriber) In the push subscription i created the subscription in the remote pc (publisher) For the push subscription i followed this: http://www.cbizsoft.com/techsupport/ReplicationPage6.htm everything was fine, no error occurred by the way, i attached the copy of the database from remote pc to local pc and chose it while setting these up.

Now i checked the Synchronization Status after everything is completed and it says Connecting to Publisher 'remote servername' (servername of publisher/distributor) and Connecting to Subscriber 'local servername' (servername of subscriber)

and when i checked the selected table it was empty. no record from the remote pc was transferred. The View Synchronization Status keeps on loading until timeout is reached.

my assumption is that, the snapshot agent cannot communicate or pass thru between subscriber and publisher.

I hope someone can help me ... i'm hoping this to work... i hope i'm almost there and i just missed something... i have been doing this for months already.

by the way i am using sql server 2014.

thank you guys.

jake
  • 167
  • 2
  • 14
  • You need to check Replication Monitor and drill down to the subscription and see if there are any errors. If so, post them here. If not, you may need to enable verbose agent logging to get the error. – Brandon Williams Aug 27 '14 at 17:11
  • hi the error says: The process could not read file \\hostname\ReplData\... due to OS error 5. – jake Aug 29 '14 at 06:41
  • OS error 5 is a permissions related error and means access is denied. Verify the Merge Agent process account has the permissions required in Replication Agent Security Model (http://msdn.microsoft.com/en-us/library/ms151868.aspx). – Brandon Williams Aug 29 '14 at 17:43
  • is it necessary to have a similar windows account for this that should be used in setting up the publication and subscription?on my first trial, i only set my firewall to forward SQL request thru port 1433 to the local machine which acts as a distributor... soon i realized that the subscriber might also need to access the ReplData which resides in the distributor, therefore a changed everything to VPN. I added the subscriber to the domain thru the VPN and it was successful. however when i use the username:domain\username to setup the publisher it says something like not a valid windows account – jake Aug 31 '14 at 11:28
  • hi i was able to make it work, now im struggling on which port/protocol should i retain in my firewall. basically i added SQL server port, LDAP, DNS, DHCP, Net Bios Datagram, Netbios Name servce, netbios session, http, https ... i am now encountering problem using windows authentication when trying to connect to the Sql Remote Server. Using SA account is fine though... maybe port should be opened for this on my firewall ... will post how i make it work later when everything is complete for the benefit of others who's trying to setup similar thing... tnx for those who helped – jake Sep 01 '14 at 08:36

1 Answers1

0

What is the exact status message for this subscription that you see in replication monitor? Can you also run this on your subscriber and see if the date of the table corresponds to when you ran the distribution agent last? select name, crdate from sysobjects where type='u' order by crdate desc

HilaryCotter
  • 109
  • 1
  • hi the error says: The process could not read file \\hostname\ReplData\... due to OS error 5. btw, i tried to use VPN so that the subscriber can access the shared folder of ReplData, and it was successfull, i was able to access it outside the network thru VPN, i was able to brwose the ReplData on the suscriber machine but still the error persist – jake Aug 29 '14 at 06:41
  • i also tried to use windows account, and the error says: Proxy domain\username is not a valid windows User. – jake Aug 29 '14 at 06:49
  • i also tried to change the logon account for SQL Server Agent of the distribution machine under services using domain account, but the problem is how to do similar thing on my subscriber since it is not a member of the domain. i tried it though but the domain cannot be found. i can only use the local account – jake Aug 29 '14 at 08:10