1

I have created an Azure VM, along with sql server. I can connect to it just fine. I'm able to create databases. What my goal is to move entirely to the azure cloud.

Locally on-premise I created a publication.

Now I'm trying to set the Azure db as a subscriber

Error

Cannot connect to myazuredatabase.westus.cloudapp.azure.com,1433.

===================================

SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'myazuredatabase'. (Replication.Utilities)

I followed a pluralsight guide, it is a bit outdated. I do have DNS name etc.. what is wrong?

  • Are you using SQL in a VM (IaaS) or SQL DB (PaaS) for your SQL Server? – Dan Rediske Mar 27 '17 at 22:43
  • IaaS in VM - I did that mostly because many years ago PaaS SQL was not the same and limited ... –  Mar 27 '17 at 23:57
  • what does `@@servername` returns – TheGameiswar Mar 28 '17 at 05:08
  • check this out:http://stackoverflow.com/questions/8212032/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-t – TheGameiswar Mar 28 '17 at 05:15
  • `@@servername` returns `myazuredatabase` –  Mar 28 '17 at 05:43
  • Problem is that I do that it is looking locally –  Mar 28 '17 at 05:45
  • Looks like typical replication issue to me, have you tried setup an [alias](https://msdn.microsoft.com/en-us/library/ms190445%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396) on local machine for myazuredatabase.westus.cloudapp.azure.com? – Hello Mar 28 '17 at 14:07
  • 1
    Also make sure you add alias under both SQL Native Client Configuration and SQL Native Client Configuration(32bit). – Hello Mar 28 '17 at 14:11
  • Should I do alias and such on my local desktop or azure vm ? –  Mar 28 '17 at 16:38
  • @JeremyMiller Do that on the machine where your on-perm SQL Server resides. – Hello Mar 29 '17 at 00:50
  • on perm = ? 1. I have Local Desktop machine with SSMS 2. On premise Server 3. Azure –  Mar 29 '17 at 00:54

1 Answers1

1

Just tested it with snapshot replication after back to the office. In your scenario, you would need to setup 2 aliases in your environment:

  1. Alias on your local machine for SSMS. This needs to be done under SSCM>>SQL Native Client Configuration(32bit).

  2. Alias on on-premise server. This needs to be done under SSCM>>SQL Native Client Configuration if you have 64 bit SQL Server. Otherwise just setup it under SSCM>>SQL Native Client Configuration(32bit).

Here's what you alias should look like: enter image description here

Hello
  • 632
  • 7
  • 14
  • I added to the 32 bit. For Now I am using my local machine which is running a restored copy of a database so i used SSCM and added in the alias , locally i'm using SQL server 2014, the Azure VM is running sql server 2016 , the NEW error message that I am getting is `The selected subscriber does not satify the minimum version compatibility level of the selected publication` –  Mar 29 '17 at 17:32
  • So one is 2014 and other is 2016 , only thing that i can think of is that i restored a database that was originally on sql server 2008 R2 , thoughts? –  Mar 29 '17 at 17:33