-2

We have a dedicated server that will host our client's database for a web portal. To connect to this database, you need an ip address.

I figured I'd try Merge Replication since I already have the snapshot Publication on the server done.

When I attempt to create a subscription from a client, it will not let me put the ip address in, I am getting:

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name.

Do I have my publication and subscription confused? How do I connect to a Publication that is not local?

John Gardeniers
  • 27,458
  • 12
  • 55
  • 109
ErocM
  • 226
  • 7
  • 23
  • 1
    Are you using SQL Server 2008 or 2008 R2? Please don't tag your questions with two different product versions unless your question is specifically about two different versions. – MDMarra Aug 24 '12 at 19:41
  • fixed... does it matter? no – ErocM Aug 24 '12 at 21:53
  • 3
    @ErocM Actually, it does matter. The fact that you don't appreciate or respect different product versions being different is probably deeply intertwined with the problem you're experiencing. – HopelessN00b Aug 24 '12 at 22:28
  • Why do you need to use an IP address? Why can't you either create a DNS entry for the server or use a name already assigned to that IP address and port forward if necessary? You're trying to solve this from the wrong angle. – John Gardeniers Aug 24 '12 at 23:22
  • -1 cause I disagree with you? 'lol' Anyhow, that wasn't my issue. I have solved my issue. I have to use a vpn... which again, has NOTHING to do with it being R2 or NOT. Look at the issue next time and stop nit picking. I have to wait to answer my own question. – ErocM Aug 24 '12 at 23:51
  • @JohnGardeniers It's a dedicated server and it's not on a local domain. I either have to use IIS for replication or put it on a vpn. I chose the latter. – ErocM Aug 24 '12 at 23:57
  • @ErocM for what it's worth I wasn't the one who downvoted you, like you accused me of doing. I ran out of votes several hours ago. Though, in the interests of honesty, coming back to see your comments did prompt me to use a vote on your question just now. I'll leave it up to you to guess which way that vote went. :/ – HopelessN00b Aug 25 '12 at 00:07
  • @HopelessN00b Your name fits you well. – ErocM Aug 25 '12 at 00:10
  • If DNS isn't an option then this could be one of those rare times where an entry in the hosts file might be appropriate. – John Gardeniers Aug 25 '12 at 00:13
  • That's an idea. I'll give it a try. – ErocM Aug 25 '12 at 00:17
  • Yeah, still same message. Cannot use a server alias. Thanks anyhow! – ErocM Aug 25 '12 at 00:25

2 Answers2

1

The error message is correct: replication is "funny" about server names. To find out what you need to use, do select name from sys.servers where server_id = 0 on every participant in the topology.

Ben Thul
  • 3,024
  • 17
  • 24
  • "Publication that is not local" so a server name wouldn't be valid regardless how I spelled it... – ErocM Aug 24 '12 at 21:54
  • The publication is always local to something; it's a matter of perspective. What is the T-SQL you're trying to run and in the documentation for whatever sproc(s), where does it say it needs to be run (publisher or subscriber)? – Ben Thul Aug 25 '12 at 02:42
0

Of course I have 3 options but only 2 fit my situation. I'm clarifying what they are since I was on the site:

  1. Merge replication - Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

  2. Snapshot replication - Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data. When synchronization occurs, the entire snapshot is generated and sent to Subscribers.

Now to the heart of the issue:

I copied the following from here.

Different types of replication over the Internet have different security levels. Additionally, when transferring replication files using FTP sites, precautions must be taken to secure the site and still make it accessible to replication agents.

Virtual Private Network

Using a Virtual Private Network (VPN) is the most secure option for implementing replication over the Internet. VPNs include client software so that computers connect over the Internet (or in special cases, even an intranet) to software in a dedicated computer or a server. Optionally, encryption at both ends as well as user authentication methods keep data safe. The VPN connection over the Internet logically operates as a Wide Area Network (WAN) link between the sites.

A VPN connects the components of one network over another network. This is achieved by allowing the user to tunnel through the Internet or another public network (using a protocol such as Microsoft Point-to-Point Tunneling Protocol (PPTP) available with the Microsoft® Windows NT® version 4.0 or Microsoft Windows® 2000 operating system, or Layer Two Tunneling Protocol (L2TP) available with the Windows 2000 operating system). This process provides the same security and features previously available only in a private network.

To sum it up, I need to use to be on the same domain, a VPN, or IIS to publish. SQL Server will not publish directly from Sql Server to Sql Server unless it is on the same domain.

ErocM
  • 226
  • 7
  • 23