Microsoft SQL Replication Between Windows Azure Virtual Machines

Share this Post

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.

image

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:

image

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.

image

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.

image

If I try to connect using Windows Azure Virtual Server Name “myazuresqlvm.cloudapp.net” I’ll get the error below.

image

On the Distribution Agent Security dialog you’ll need to indicate what security accounts Replication should use when making connections.

image

For the Distribution Agent connection properties, I’m specifying SQL Server login for the connection to the Subscriber.

image

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.

image

image

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


Share this Post

3 thoughts on “Microsoft SQL Replication Between Windows Azure Virtual Machines”

  1. 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

  2. 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

Leave a Reply to Clement-Edwin ALBERT Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.