I created a publication on my server. I had to use this special option so that I could initialize replication from a backup:
exec sp_addpublication ... @allow_initialize_from_backup = N'true', ...
Then, I took a backup of my database:
BACKUP DATABASE [MyDatabase] TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak' WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5; GO
And restored from it on the target server:
RESTORE DATABASE [MyDatabase] FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak' WITH CREDENTIAL = 'mycredential' ,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf' ,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf' ,STATS = 5 GO
Now I'm ready to create the subscriber. The problem I'm having is that I don't know what options to provide to the sp_addsubscription procedure.
I can make it through the process using a regular hard drive instead of a URL. I could see data being replicated from source to target. I back up to disk, copy the backup to the target server, restore, and then initialize from backup using this command:
exec sp_addsubscription @publication = N'MyPub', @subscriber = N'MyTargetServer', @destination_db = N'MyDatabase', @subscription_type = N'Push', @sync_type = N'initialize with backup', @article = N'all', @update_mode = N'read only', @subscriber_type = 0, @backupdevicetype = 'disk', @backupdevicename = 'D:\Backups\MyDatabase.bak'
How do I tweak these options in order to initialize replication from a URL (which in my case is an Azure blob storage container that both servers have the credentials for)?