Microsoft SQL Replication Between Windows Azure Virtual Machines
You can use SQL Server Replication technologies to move data between local on premises SQL Server to SQL Server running in Windows Azure Virtual Machines or between Windows Azure Virtual Machines. Similar to move data between non-trusted corporate domains, keys are 1) use SQL Server Authentication and 2) configure a client alias for the server name.
Run SQL Server Configuration Manager and specify both 32bit and 64bit ALIAS for the SQL Server Subscriber. For my demo the Publisher and Distributor are the same computer, therefore only Subscriber ALIAS is required.
In the ALIAS dialog, use the virtual server name as the ALIAS name and the Server is the Fully Qualified Windows Azure server name that will be your SQL Server Replication Subscriber.. For example:
In the examples below I’ve already created a Publication. I’m now adding a new Replication Subscriber. In the “Add Subscriber” dialog I provide the ALIAS name as the SQL Server name.
Be sure to connect using SQL Server authentication. For testing, I’ve created a MySQLLogin on the Subscriber as “system administrator”. Later I can change and tighten down the security as needed.
If I try to connect using Windows Azure Virtual Server Name “myazuresqlvm.cloudapp.net” I’ll get the error below.
On the Distribution Agent Security dialog you’ll need to indicate what security accounts Replication should use when making connections.
For the Distribution Agent connection properties, I’m specifying SQL Server login for the connection to the Subscriber.
On the “Initialize Subscription” properties dialog I cleared the “Initialize” option as I have already ensured the Publisher and Subscriber have identical copies of the database. I accomplished this by backing up the Publisher database and restoring it on the Subscriber. This allows me to skip applying the “Snapshot” and simplifies the Replication setup.
If I wish to generate and apply a Snapshot, I would have to move the Snapshot files to the local Subscriber, then specify the Alternate Snapshot Location property in the Distribution Agent to point to the local copy of the Snapshot files. Clearing the initialize works much easier as long as the data is identical on Publisher and Subscriber..
Chris Skorlinski
Microsoft SQL Server Escalation Services
Even with the aliases put in place i keep getting the error mentioned in your post.
Got it to work with a combination of aliases and SQL Script for the on-premises publisher..
use [DB_PUBLISHER]
exec sp_addsubscription
@publication = N'srv-otcex',
@subscriber = N'cloud',
@destination_db = N'DB_PUBLISHER',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
exec sp_addpushsubscription_agent
@publication = N'srv-otcex',
@subscriber = N'cloud',
@subscriber_db = N'DB_PUBLISHER',
@job_login = null, @job_password = null,
@subscriber_security_mode = 0,
@subscriber_login = N'login', @subscriber_password = N'password',
@frequency_type = 64, @frequency_interval = 0,
@frequency_relative_interval = 0, @frequency_recurrence_factor = 0,
@frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0,
@active_end_time_of_day = 235959, @active_start_date = 20140918, @active_end_date = 99991231,
@enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'
GO
Nice Article. Got error when i initially put 'alias name' different from the cloud virtual machine name. Once i put the alias name same as cloud virtual machine name, it worked. Thanks